View previous topic :: View next topic
|
Author |
Message |
i_suman
New User
Joined: 01 Mar 2005 Posts: 20
|
|
|
|
I am trying to insert few records into a table from cobol program and didn't call the commit (not commiting the changes). But I can see the table updated with these changes (newly inserted records) in the table.
After these inserts program is terminating normally.
Is it possible to update the tables without executing COMMIT statement in the prog?
Thanks in advance for your reply. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
Is it possible to update the tables without executing COMMIT statement in the prog? |
Yes.
If the program ends normally, uncommitted work is commited.
If the program abends, uncommitted work is rolled back. |
|
Back to top |
|
|
i_suman
New User
Joined: 01 Mar 2005 Posts: 20
|
|
|
|
I got you. But do we have to handle ROLLBACK manually in the program in case of program abends? |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
But do we have to handle ROLLBACK manually in the program in case of program abends? |
What ROLLBACK would you do manually? The abend already caused the uncommitted work to be rolled back? |
|
Back to top |
|
|
i_suman
New User
Joined: 01 Mar 2005 Posts: 20
|
|
|
|
Iam sorry for not being clear. I should have been very clear.
DOES DB2 ROLLBACK WORK AUTOMATICALLY ON -911, -913 or -904?
I see DB2 rollsback uncommitted data on other -ve sql codes but not on above sql codes. So why I was asking that question in my earlier post "do we have to call rollback in our program in case of -911, -913 or -904?" |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
Iam sorry for not being clear. I should have been very clear. |
Not to worry This is why we carry on a dialog - to get better clarity.
Keep in mind that an SQL code is not the same thing as a system or user abend. When an SQL code is returned, the code is still "in control" and can do whatever is appropriate for code returned from the request. Most code checks for a few specific values to deal with (i.e. "not found") and goes to a common error routine for anythiing else. The common error routine usually contains a ROLLBACK. When an abend occurs, control is taken from the code and uncommitted updates are rolled back.
Quote: |
"do we have to call rollback in our program in case of -911, -913 or -904?" |
The -911 is rolled back by the system (or so the manual says). The others (IIRC), you must decide whether to commit or rollback in the code. |
|
Back to top |
|
|
vini_srcna
Active User
Joined: 26 May 2005 Posts: 178 Location: Copenhagen, Denmark
|
|
|
|
Dick,
Did you mean -911 return code will make DB2 to rollback automatically.? what if the program is asked to do nothing after -911.? will it still rollback.?
ex: IF SQLCODE = -911 THEN ;
IF I have any uncomiited work before the SQL statement giving -911, will it be rolled back to previous commit point.? I doesnt makes sense..Have I got u right.?
Also execution of ROLLBACK statement closes the cursors irrespective of WITH HOLD ...right.? |
|
Back to top |
|
|
acevedo
Active User
Joined: 11 May 2005 Posts: 344 Location: Spain
|
|
|
|
fwiw:
DSNT408I SQLCODE = -911, ERROR: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON , TYPE OF RESOURCE , AND RESOURCE NAME
DSNT415I SQLERRP = SQL PROCEDURE DETECTING ERROR |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
IF I have any uncomiited work before the SQL statement giving -911, will it be rolled back to previous commit point.? I doesnt makes sense.. |
I suppose that depends on your perspective. . .
DB2 determined that there was no way to proceed and automatically "cleaned up". The code has no control over this - it has already been backed out before the code continues. If the code issued a commit, it would execute, but nothing would be committed and this could surely confuse someone later who had to maintain the code.
Suggest you look here for info about cursor/rollback:
publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqj10/5.42?
Scroll down to the "with hold" info. |
|
Back to top |
|
|
i_suman
New User
Joined: 01 Mar 2005 Posts: 20
|
|
|
|
I am passing -ve sqlcode in xpeditor (devlopment region) and allowing the program to go into abend para to see if db2 rolls back the changes or not.
To my surprise its not rolling back the updates done to the table.
Is that running a program through xpediter, DB2 doesn't roll back the changes automatically when the program is terminated?
What other parameters should I check to see if DB2 rollsback the uncommitted changes?
Thanks for all your time. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
I am passing -ve sqlcode in xpeditor (devlopment region) and allowing the program to go into abend para to see if db2 rolls back the changes or not.
To my surprise its not rolling back the updates done to the table. |
DB2 does not roll back the updates without being told to do so. Does your abend para issue a rollback? You might insert a display before and after issuing the rollback to make sure it is executed. If the abend para selectively does the rollback, it may or may not be issued and so would not back out the updates.
Another possibility is that the updates were already committed and there was nothing to back out even if the rollback was issued. |
|
Back to top |
|
|
|