View previous topic :: View next topic
|
Author |
Message |
arun85
New User
Joined: 09 Oct 2006 Posts: 10
|
|
|
|
Hi, I would like to know whether is there a facility in DB2 Sql to perform commit operation every 'n' seconds? I have to implement such activity in my task for deleting records from a parent table which has child tables with huge records. So instead of committing the records after a particular count, if we can commit every few seconds say 5 seconds, it would be easier to bypass the Internal DB2 error abend. Please help. |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
are you sure you want to commit after every 5 secs ??? do you have a backplan incase the delete fails in between ? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Suggest you define a logical unit of work (luw) for your process and then issue a checkpopint after each luw. Every n records or every n seconds does not provide much for proper restart/recovery.
Quote: |
it would be easier to bypass the Internal DB2 error abend. |
Which internal db2 abend? |
|
Back to top |
|
|
arun85
New User
Joined: 09 Oct 2006 Posts: 10
|
|
|
|
Thanks for your replies. ABEND S04E was the internal DB2 error. Also this abend occurs while running the query via a COBOL program. This occurs as due to storing of deleted records in temp storage(in DB2) before it gets committed is huge, this abend occurs. If a commit happens, that data in temp storage will be emptied. We performed a check that every 5000 records it should get committed but it proved futile as the abend occurred again. If you could provide me how to create a luw, it would be of great help as i haven't tried it before. |
|
Back to top |
|
|
XungoPejcao
New User
Joined: 04 Sep 2008 Posts: 5 Location: España
|
|
|
|
Hi,
It is necessary to bear in mind that would be more ideal the commit fulfils every 100 or 200 records. Always it has to be a short section of records to liberate work to Db2 on having increased the frequency of the Commit. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
If you could provide me how to create a luw, it would be of great help as i haven't tried it before. |
A logical unit of work is competely situation dependent. As far as i'm aware, there is no "generic" luw.
There are some sets of work that you want saved completely, or not at all. You don’t want detail lines of an order to be successfully saved if the save of the header row fails for some reason. A logical unit of work is a set of transactions where either all are successfully applied against the database, or all are rolled back.
You would need to define what is an luw for your situation.
Does the process try to delete all of the children to be deleted and then delete the appropriate parents or does it delete all of the children for one parent at a time. You might define a luw to be each time the parent id changes. . . I don't know enough about your specifics to say for sure. |
|
Back to top |
|
|
arun85
New User
Joined: 09 Oct 2006 Posts: 10
|
|
|
|
Thanks for the explanation of luw. My Deletion of records occurs by deleting the parent record which inturn delete its child records. Here the number of child records is huge some parent records. So we face that abend issue. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Depending on the tables and the work flow using those tables, you might be better off unloading the tables, deleting the data to be deleted in the sequential files, and re-loading the tables after the unneeded data has been removed. |
|
Back to top |
|
|
|