Results 1 to 5 of 5
  1. #1
    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,734
    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

  3. #3
    Join Date
    Nov 2003
    Location
    Baltimore MD
    Posts
    21
    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

  4. #4
    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.

  5. #5
    Join Date
    Nov 2003
    Location
    Baltimore MD
    Posts
    21
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •