Originally posted by Walter Janissen
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).