Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 Sql to perform commit operation every 'n' seconds

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: DB2 Sql to perform commit operation every 'n' seconds
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    Post subject:
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

Site Director


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

PostPosted: Wed Sep 10, 2008 7:58 pm    Post subject:
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    Post subject:
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    Post subject: Reply to: DB2 Sql to perform commit operation every 'n' seco
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

Site Director


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

PostPosted: Thu Sep 11, 2008 7:23 pm    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Thu Sep 11, 2008 10:19 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Joinkeys operation when key1 and key2... Susanta DFSORT/ICETOOL 5 Wed Nov 02, 2016 5:35 pm
No new posts Joinkeys operation betwen VB and FB file Susanta SYNCSORT 4 Sun Sep 25, 2016 9:49 pm
No new posts How to perform Digest Access Authenti... vasanthz All Other Mainframe Topics 0 Tue Mar 15, 2016 4:47 pm
No new posts Commit limit for Delete query through... mf_karthik DB2 10 Thu Dec 17, 2015 12:56 am
No new posts Commit a DB2 Table on Unix Server and... Vinodh S DB2 3 Sun Apr 12, 2015 5:05 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us