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.........

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

Specify "-l logfile.log" when running the DB2 command processor, if that's what you use for "my insert/update/delete commands".

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....

i am implementing my insert/update/delete in a stored procedure.

Could you provide an example where you explain what exactly your stored procedures are doing and which information you want to log?

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

i get Internet Explorer cannot display the webpage

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

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

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:
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.

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

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.

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

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.