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

is table updated when no commit is done?


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

New User


Joined: 01 Mar 2005
Posts: 20

PostPosted: Wed Oct 15, 2008 7:32 am
Reply with quote

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

Moderator Emeritus


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

PostPosted: Wed Oct 15, 2008 8:07 am
Reply with quote

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

New User


Joined: 01 Mar 2005
Posts: 20

PostPosted: Wed Oct 15, 2008 8:25 am
Reply with quote

I got you. But do we have to handle ROLLBACK manually in the program in case of program abends?
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 Oct 15, 2008 8:37 am
Reply with quote

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

New User


Joined: 01 Mar 2005
Posts: 20

PostPosted: Thu Oct 16, 2008 3:46 am
Reply with quote

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

Moderator Emeritus


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

PostPosted: Thu Oct 16, 2008 4:47 am
Reply with quote

Hello,

Quote:
Iam sorry for not being clear. I should have been very clear.
Not to worry icon_smile.gif 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
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Thu Oct 16, 2008 2:26 pm
Reply with quote

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

Active User


Joined: 11 May 2005
Posts: 344
Location: Spain

PostPosted: Thu Oct 16, 2008 3:06 pm
Reply with quote

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

Moderator Emeritus


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

PostPosted: Thu Oct 16, 2008 8:29 pm
Reply with quote

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

New User


Joined: 01 Mar 2005
Posts: 20

PostPosted: Fri Oct 17, 2008 11:43 pm
Reply with 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.

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

Moderator Emeritus


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

PostPosted: Fri Oct 17, 2008 11:57 pm
Reply with quote

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
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 Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top