Results 1 to 15 of 15
  1. #1
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221

    Unanswered: Creating Log files

    Is there any process through which i can create logs in my physical file system in which i can enter the diagnostic information of my insert/update/delete commands in db2

    i am running db2 over linux.........

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Do you mean that you want a running log of all the failures/successes of all the inserts, updates, and deletes? Would that not be an application issue?

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Specify "-l logfile.log" when running the DB2 command processor, if that's what you use for "my insert/update/delete commands".
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Quote Originally Posted by ARWinner
    Do you mean that you want a running log of all the failures/successes of all the inserts, updates, and deletes? Would that not be an application issue?

    Andy

    well if i have to make it an application issue i wil have to return all the required diagnostic values to the application. Since my stored procedure involves lots of inserts and updates the parameters returned will be enormous in my case.... hence the requirement....

    Quote Originally Posted by n_i
    Specify "-l logfile.log" when running the DB2 command processor, if that's what you use for "my insert/update/delete commands".
    i am implementing my insert/update/delete in a stored procedure.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Could you provide an example where you explain what exactly your stored procedures are doing and which information you want to log?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    suppose i have a procedure A which has insert/update/delete statements....

    e.g.

    create procedure A
    begin

    insert into table A values XYZ

    update table B set C = D

    delete table X
    end

    now in case of insert and update i have to get diagnostics to get the number of rows effected and check whether there was any error in each case and then return it after i store it in some variable

    in case of delete have to return the SQLCODE to check if there is any error....

    if there are more insert/update statements returning the values for each of them will make my parameter list for the procedure too long and to maintain will be a pain.....

    so i was thinking whether i could directly insert the data into log something like

    create procedure A
    begin

    insert into table A values XYZ
    add diagnostic/SQLCODE values into log file

    update table B set C = D
    add diagnostic/SQLCODE values into log file

    delete table X
    add diagnostic/SQLCODE values into log file

    end

    hope you get what i am trying to acheive.....

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by nick.ncs
    suppose i have a procedure A which has insert/update/delete statements....

    e.g.

    create procedure A
    begin

    insert into table A values XYZ

    update table B set C = D

    delete table X
    end

    now in case of insert and update i have to get diagnostics to get the number of rows effected and check whether there was any error in each case and then return it after i store it in some variable

    in case of delete have to return the SQLCODE to check if there is any error....

    if there are more insert/update statements returning the values for each of them will make my parameter list for the procedure too long and to maintain will be a pain.....

    so i was thinking whether i could directly insert the data into log something like

    create procedure A
    begin

    insert into table A values XYZ
    add diagnostic/SQLCODE values into log file

    update table B set C = D
    add diagnostic/SQLCODE values into log file

    delete table X
    add diagnostic/SQLCODE values into log file

    end

    hope you get what i am trying to acheive.....

    I understand what you are trying to do. THis article will probably help you to achieve what you want:

    http://www-128.ibm.com/developerwork...0302izuha.html

    Andy

  8. #8
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Quote Originally Posted by ARWinner
    I understand what you are trying to do. THis article will probably help you to achieve what you want:

    http://www-128.ibm.com/developerwork...0302izuha.html

    Andy
    i get Internet Explorer cannot display the webpage

    can you check out the link again or get an alternate link

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Goto the DB2 online documentation at:

    http://publib.boulder.ibm.com/infoce.../v8//index.jsp

    and search for "fenced". Pick the "Guidelines for Stored Procedures"

    Andy

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Here is another article describing how to access files: http://www-128.ibm.com/developerwork...303stolze.html

    Also, how about inserting the log data into a temp table? Then you could retrieve the desired information via SQL.

    And yet another approach would be to return the log data as result set of the procedure. To that end, you create a cursor for a SQL statement like this:
    Code:
    SELECT *
    FROM   TABLE ( VALUES ( log1_value, log1_descr ),
                          ( log2_value, log2_descr ), ... ) AS t
    The advantage is that you don't need a declared/created temp table and you don't have to worry about cleaning things up. The cleanup will be especially an issue with file I/O.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by stolze
    Also, how about inserting the log data into a temp table? Then you could retrieve the desired information via SQL.
    This would have to be done outside the current Unit of Work. Any rollbacks in the UOW would rollback the logging also. In Nick's case, this would not work because there are multiple items that need logging and they would have to be "saved" and then inserted after the UOW has either been committed or rollbacked, then inserted into the table is a separate UOW.

    Andy

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by ARWinner
    This would have to be done outside the current Unit of Work. Any rollbacks in the UOW would rollback the logging also. In Nick's case, this would not work because there are multiple items that need logging and they would have to be "saved" and then inserted after the UOW has either been committed or rollbacked, then inserted into the table is a separate UOW.
    Yes, that would be a problem. DB2 does not have something like autonomous transactions as Oracle does.

    However, I don't see why the data would have to be kept on ROLLBACK. Nick didn't say that anywhere. If the procedure returns an error, it does not imply a rollback operation, of course.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    i have to log everything.....even if there is an error.... so if a rollback occurs and i loose my log details then it wouldnt be useful as a log coz then i wont be able to identify which part of my insert/update/delete transaction did not work out......maybe i have to use the continue handler to continue logging to proceed in case of any sqlexception.....

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by nick.ncs
    i have to log everything.....even if there is an error.... so if a rollback occurs and i loose my log details then it wouldnt be useful as a log coz then i wont be able to identify which part of my insert/update/delete transaction did not work out......maybe i have to use the continue handler to continue logging to proceed in case of any sqlexception.....
    Since you have to log everything, I would suggest that you log it to a file and not directly to a table. You could write the info to the file in such a way as you could import it into a table for later analysis.

    This is the only viable solution if the SPs do not have total UOW control (the entire UOW is wholly contained within the SP). It would also make the SP simpler to code as the alternative would be very cumbersome.

    Andy

  15. #15
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by nick.ncs
    i have to log everything.....even if there is an error.... so if a rollback occurs and i loose my log details then it wouldnt be useful as a log coz then i wont be able to identify which part of my insert/update/delete transaction did not work out......maybe i have to use the continue handler to continue logging to proceed in case of any sqlexception.....
    Who or which part would do the identification of the error? If it is the application that called the procedure, then the application could evaluate the error in the same UOW where the procedure was called. If it is another transaction, you have to have a different, more persistent means like logging to a file.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •