Support of nested transactions within IBM DB2 Stored Procedure

Posted on 2006-05-08
Last Modified: 2007-11-27
Does IBM DB2 support nested transactions? I would like to have a stored procedure that logs messages and errors to a database table and then leverage the stored procedure within other stored procedures. However, when I have a stored procedure that is in the middle of a database transaction and then calls the "logging stored procedure", I want to make sure the log entry is written which requires a commit. This commits everything including the calling stored procedures transaction. I need to write/commit the log entry while leaving the calling stored procedures transaction open.

From what I can tell it doesn't seem that DB2 allows for nested database transactions, so I'm not sure how I can accomplish this.

Any ideas?
Question by:artpowell303
    LVL 45

    Accepted Solution

    Hi artpowell303,

    Using the dreaded 6 character name, Oracle allows this kind of behavior, but I'm unaware of anything in UDB that is the equivalent.  Sorry.

    Good Luck!
    LVL 7

    Assisted Solution

    I not sure if its possible in DB2 SQL PL


    can write Java Stored Procedure and use java functions to achive similar results.

    LVL 13

    Assisted Solution

    try using the SAVEPOINT handler in conjunction with the ROLLBACK statement
    LVL 2

    Assisted Solution

    Am I right to assume that you have a SQL Serrver or Sybase background?
    Let me answer the question by level-setting the semantics:
    IN MS SQL Server transactions get started explicitly and if you start a transaction within a transaction you get a "nested transaction". Any COMMIT or ROLLBACK allways affects the current transaction.
    However a nested transaction isn't really, irreversably commited unless the outermost transaction also commits.

    In DB2 (and other DBMS) a COMMIT or ROLLBACK is unscoped. That is when you type COMMIT the outermost level commit.

    As aluded to by ghp7000 this is where savepoints need to be thrown into mix.
    In DB2 issuing a savepoint is pretty much the same as starting a nested transaction in SQL Server.
    A "release" of a savepoint matches a nested commit.
    A "rollback to savepoint" matches a nested rollback.

    Savepoints can be nested.

    In essence savepoint = nested transcation with one difference:
    The outer most savepoint is not equal to the outermost transaction.

    Now this may sound strange. Why doesn't DB2 (and the SQL standard) use one concept for all?
    The answer is that the philosophy of coding in DB2 is to generally NOT perform COMMIT in procedures.
    Instead procedures shoudl always use savepoints.
    It is the applications job to sign of or cancel a transaction.
    As an addendum (and in case you have an Oracle background rtaher than an SQL Server background) one further note:
    Netsed transactions are conceptually orthogonal to "autonomous transaction". And autonomous transaction is a transaction that is completely separate from the parent transaction. Autonomous transactiosn are used for e.g. auditing, nested transactions are useless for auditing since a rollback of the parent will wipe out teh nested transaction.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now