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
 

 

Issue with DB2 Commit

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
veeramanimurugesan

New User


Joined: 03 Nov 2009
Posts: 19
Location: chennai

PostPosted: Wed Mar 02, 2011 10:18 pm    Post subject: Issue with DB2 Commit
Reply with quote

Hi,

I am testing a simple cobol+db2 program which will read a file and insert the record in table if that key is not present already.

Now, when testing i took sample 14 records. My commit frequency is 3. After processing 8 records (8 inserts) i am force abending the program. The statistics show that 2 commits were issued as expected which would mean that there should be 6 committed rows.

But i see only 3 rows in the table. I changed commit freq as 2 and abend the program at 8 rows. In this case also, though 8 rows need to be present in table, only 2 are.

So, upon abend DB2 is rolling back all rows till the FIRST issued commit. I issued displays and i could see that all 6/8 rows are indeed getting successfully inserted in DB2 but upon rollback all of them except till the first commit get deleted.

Please advise.
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Mar 02, 2011 10:43 pm    Post subject:
Reply with quote

I'm pretty sure your problem is in your coding and not in db2.

Maybe you should put a display at the commit. My guess is you forgot to reset the counter to 0 and only commit when count = 2 and not every 2.
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 Mar 02, 2011 11:30 pm    Post subject:
Reply with quote

Hello,

Run a test COMMITing after every insert (6 or 8 - it won't matter) and force the abend. Are all of the rows in the table? They most likely will be.

Then determine why the rows were either not really inserted or why the commit frequency was not followed.

Does the code check the sqlcode after every sql statement?
Back to top
View user's profile Send private message
veeramanimurugesan

New User


Joined: 03 Nov 2009
Posts: 19
Location: chennai

PostPosted: Wed Mar 02, 2011 11:40 pm    Post subject:
Reply with quote

Thanks for the quick turnaround GuyC.

I had put displays inside my commit para and also displayed the SQLCODE after commit. I could see successful commits and sqlcode of 0.

But still, the abend causes to rollback everything till first commit. I saw in the BMC abend logs as well that 2 commits have been issued.
Back to top
View user's profile Send private message
veeramanimurugesan

New User


Joined: 03 Nov 2009
Posts: 19
Location: chennai

PostPosted: Wed Mar 02, 2011 11:47 pm    Post subject:
Reply with quote

Hello Dick,

Thanks for your response.

Yes the code checks and handles sqlcode after every sql statement.

Commit frequency is also honored which i could see from displays. Commit gives sqlcode 0.

I had put a SELECT as soon as the INSERT is done. There, the program is indeed fetching the row which just got inserted. When commit freq is 3, i see all 8 SELECTS after INSERTS are successful. But when it abends, except for first 3 rows everything gets rolled back.

On a normal basis, the program just runs fine and all rows are correctly updated and committed. The rollback till first commit problem arises when an abend occurs.
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 Mar 03, 2011 12:30 am    Post subject:
Reply with quote

Hello,

Well, it is almost certain that the problem is in the code.

If DB2 was to not honor successful commits occasionally, we would have many topics on this as well as clarification from IBM.

Did you run the test where a COMMIT is issued after every INSERT? If not do so now and let us know what happens. Suggest you comment out the frequency check for this test and place the COMMIT immediately after the sqlcode check following the INSERT.
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 Execessive parameter issue Sumeendar JCL & VSAM 5 Mon Dec 19, 2016 4:35 pm
No new posts DFHRESPONSE returns issue divated CICS 3 Wed Nov 02, 2016 6:32 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm
No new posts REXX Screen not working due to LINKED... sundarkudos CLIST & REXX 1 Mon May 09, 2016 1:44 pm
No new posts Issue in sending zip file as mail att... ajithajt JCL & VSAM 8 Thu Apr 07, 2016 9:11 am


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