If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > autonomous commits and rollbacks in DB2?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Location: Baltimore MD
Posts: 21
Question autonomous commits and rollbacks in DB2?

We are running DB2 version 7.0 in Z/OS, and would like to know if in DB2, one can issue a COMMIT or ROLLBACK 'autonomously'. The question is based on the concept in Oracle known as an 'autonomous transaction'.
To illustrate ....
Let's say we are running a COBOL/DB2 program (call it PGM0001) which amongst other things, updates DB2 tables: TABLE_A and TABLE_B. Furthermore, PGM0001 contains its own COMMIT and ROLLBACK logic to respectively COMMIT and/or ROLLBACK the updates done to TABLE_A and TABLE_B. Now let's say, that we introduce into PGM0001 a call to some utility program (call it: PGM0002) whose sole purpose is to update TABLE_C and TABLE_D. Can a COMMIT or ROLLBACK command be issued within PGM0002 so that it only affects the updates done to TABLE_C and TABLE_D (without affecting the updates done by PGM0001 to TABLE_A and TABLE_B)?. Conversely, can the COMMIT or ROLLBACK command be issued within PGM0001 so that it only affects the updates done to TABLE_A and TABLE_B (without affecting the updates done by PGM0002 to TABLE_C and TABLE_D)?.
Whatever the answer is, does it make a difference if PGM0002 is a stored procedure as opposed to a ordinary dynamically called program. As an FYI, with DB2 version 7 and higher, you can now issue a COMMIT and ROLLBACK, directly within a stored procedure. (chapter 24, DB2 application developers guide).
Any feedback to this question is greatly appreciated.
regards: dcshnier
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,713
Is this CICS, TSO batch, etc?
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Location: Baltimore MD
Posts: 21
Quote:
Originally posted by Marcus_A
Is this CICS, TSO batch, etc?
Hi Marcus_A:

In response to your question, for the most part, it will be a TSO batch scenario.

regards
dcshnier
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Location: Germany
Posts: 62
Hi

I am not familiar with stored procedures, so I can not tell you in the case of stored procedures, if a commit only affects the updates done by that routine, but for the other cases, I know for sure, that a commit affects all updates done by the entire process. A commit ends a so called unit of recovery.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2003
Location: Baltimore MD
Posts: 21
Quote:
Originally posted by Walter Janissen
Hi

I am not familiar with stored procedures, so I can not tell you in the case of stored procedures, if a commit only affects the updates done by that routine, but for the other cases, I know for sure, that a commit affects all updates done by the entire process. A commit ends a so called unit of recovery.
Thanks Walter for your reply.
I do not know Oracle that well, but the Oracle people around here say that during standard processing, COMMITS and ROLLBACKS in Oracle also affect the 'entire' process. However, Oracle offers a unique option called an 'autonomous' transaction. This means, that in Oracle, one has the option of coding sub-routine within the confines of an 'autonomous' transaction - and when done that way, the commits and rollbacks issued within such an 'autonomous' sub-routine would be isolated so-to-speak, and only affect the data-base updates which took place in the sub-routine, and not affect the process which evoked the sub-routine.

So my question regarding DB2 was, is there a way to offer similar 'autonomous' functionality in DB2 as one finds in Oracle ?
Albeit, if such a way even exists, it would demand unique coding and/or definitions over and above the standard methodology.

Using standard DB2 methodology, we agree with your assessment, that
a COMMIT and ROLLBACK in DB2 does indeed affect the "entire" process (both what took place in the calling module and the called module).

As far as DB2 stored-procedures go, before version 7.0, one could 'not' issue the COMMIT or ROLLBACK within the stored-procedure. However back then, you could define the stored-procedure so that when the stored-procedure completed, a commit would 'automatically' take place . And on those 'automatic' commits, everything was commited (both what was inside the calling program and the stored-procedure). Now that version 7.0 allows for COMMITS and ROLLBACKS to be done inside the stored procedure, I do not know if they behave any differently. But since I have not read otherwise, I would assume that they would also affect the entire process. (I would run a test - but it is not easy to create ad-hoc stored-procedures here).

regards
dcshnier
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On