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
 

 

is table updated when no commit is done?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: is table updated when no commit is done?
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

Site Director


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

PostPosted: Wed Oct 15, 2008 8:07 am    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Wed Oct 15, 2008 8:37 am    Post subject:
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    Post subject: Reply to: is table updated when no commit is done?
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

Site Director


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

PostPosted: Thu Oct 16, 2008 4:47 am    Post subject:
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    Post subject:
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    Post subject: Reply to: is table updated when no commit is done?
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

Site Director


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

PostPosted: Thu Oct 16, 2008 8:29 pm    Post subject:
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:
http://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    Post subject:
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

Site Director


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

PostPosted: Fri Oct 17, 2008 11:57 pm    Post subject:
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    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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
No new posts Getting soc4 on internal table in cobol abdulrafi COBOL Programming 13 Fri May 06, 2016 3:39 pm


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