Issue with DB2 Commit

veeramanimurugesan · New User Joined: 03 Nov 2009 Posts: 19 Location: chennai

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.

GuyC · Posted: Wed Mar 02, 2011 10:43 pm

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.

dick scherrer · Posted: Wed Mar 02, 2011 11:30 pm

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?

veeramanimurugesan · New User Joined: 03 Nov 2009 Posts: 19 Location: chennai

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.

veeramanimurugesan · New User Joined: 03 Nov 2009 Posts: 19 Location: chennai

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.

dick scherrer · Posted: Thu Mar 03, 2011 12:30 am

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.