Friends and db2 masters!

I have one query. I need to record all the errors from db2 stored procedures in a log file. I am not sure whether there is an in-built sp logging facility in db2.

( I understand that error logging in db2 can be found in db2diag.log file . But here, I could find only the database related activities like server startup, execution and shut down logs)

My requirement is: I need the following error logs while a sp encounter an error.
1. Error Timestamp
2. Error Description
3. Sp name
4. Table name
4. Sp Operation ( insert/update, delete etc)


I appreciate quick help for solving my issue...!

You did not give your DB2 version or OS.

For DB2 8/9 for LUW, there is no built in facility to track this information. You will have to edit the Stored Procedures to do the logging when they encounter an error. Or if the Sp itself returns and error, the calling program will have to log it.

Andy

Andy thanks a lot for a quick reply. I am using DB2 Version 8.
Did u mean like
Manually creating an error table with all required fields and populating in thru sp?

That could be one way, but there are drawbacks to that approach. An example is what if your SP inserted a row in some table. It then tried to insert another row which fails for whatever reason. If you then insert the log row and commit it, you would also commit the first insert, which may not be what is desired.

It would probably be better to just write to a flat log file (like db2diag.log).

This link might be of use:

http://www.ibm.com/developerworks/db...0302izuha.html

Andy

The link helped out to get an idea Thank you for clearing my query