IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 Sql to perform commit operation every 'n' seconds


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
arun85

New User


Joined: 09 Oct 2006
Posts: 10

PostPosted: Wed Sep 10, 2008 11:03 am
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Sep 10, 2008 4:02 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Sep 10, 2008 7:58 pm
Reply with quote

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
View user's profile Send private message
arun85

New User


Joined: 09 Oct 2006
Posts: 10

PostPosted: Thu Sep 11, 2008 11:54 am
Reply with quote

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
View user's profile Send private message
XungoPejcao

New User


Joined: 04 Sep 2008
Posts: 5
Location: España

PostPosted: Thu Sep 11, 2008 12:08 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Sep 11, 2008 7:23 pm
Reply with quote

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
View user's profile Send private message
arun85

New User


Joined: 09 Oct 2006
Posts: 10

PostPosted: Thu Sep 11, 2008 8:50 pm
Reply with quote

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
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Thu Sep 11, 2008 10:19 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Use of Perform Thru Exit COBOL Programming 6
No new posts filter COMMIT/ROLLBACK statements DFSORT/ICETOOL 13
No new posts An write operation error - ABENDED S0... COBOL Programming 3
No new posts Utility for search operation TSO/ISPF 11
No new posts Joinkeys operation when key1 and key2... DFSORT/ICETOOL 5
Search our Forums:

Back to Top