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

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

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