We have a db2 file that contains a number of stored procedures.
About every other day or 2, the process will bomb out on a user.
We have no idea at what point it is bombing out. Also it cannot be duplicated at will.
Mind you the stored procedure is about 10000 lines and calls other stored procedures to perform specific updates, inserts, and formatting etc...
So we have created a new table ( a log table ). Right before we do something in the stored procedure such as updating or inserting into any number of tables, we will be calling a stored procedure to insert information about what is about to be performed into this new table.
So the question begs asking, when the program bombs out and calls for a rollback of the transaction, how do we preserve the data written to this new table while still rolling back everything else?
Thanks In Advance...
No answer to my question?
Just curious if you are you are running DB2 on LUW or DB2 on z/OS...
I know IBM has restrictions on Rolling back work and I am not sure if what you trying to do is possible. Perhaps instead of logging to a DB2 table, try logging to something that is not DB2 controlled - that might get you closer to what you desire.
DB2 on LUW...I think. I am not the DBA. There are 4 programmers, we write the db2 and sql files and submit them to the DBAs for processing. This is a state agency...everybody has a slice of the pie as the saying goes.
We have about 15 to 20 stored procedures that get called from our program for this particular process. The program uses a Transaction token so in the event an error occurs or it times out, the transaction gets logged out.
In order to locate where in the 15 to 20 stored procedures the process has bombed out the developer has created a new table. And has created a new Stored Procedure that records events that is called right before major inserts and updates are applied to tables in these 15 to 20 stored procedures.
It is too late in the game to rewrite everything....this was done 14 years ago by a contract company and this db2 file that contains the 15 to 20 stored procedures is about 10k lines of code. The Powers that be are against a time consuming rewrite.
I suggested ( not knowing at the time ) doing a commit inside the stored procedure that does the insert on the new table that logs what is being done. But I am sure that commits everything done up to that point. Correct?
So my question is:
Is there anyway to tell the database to roll back everything except the calls to this new table?
IE: Selective Rollback.
I do not believe there is a 'selective rollback'.
I would suggest looking into the stored proc that logs events - and change that to logging to a file - instead of a DB2 table.
We tried it. We called a secondary stored procedure several times that created entries into a log table within the primary stored procedure. We committed the entries in the secondary SP and at the end of the Primary SP we caused it to fail. It rolled back everything in the Primary but preserved the Secondary commits.
At best I would describe that as "Selective Rollback" for a lack of a better term.