Support of nested transactions within IBM DB2 Stored Procedure

Posted on 2006-05-08
Medium Priority
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 46

Accepted Solution

Kent Olsen earned 256 total points
ID: 16634834
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!

Assisted Solution

sachinwadhwa earned 248 total points
ID: 16639270
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

ghp7000 earned 248 total points
ID: 16639451
try using the SAVEPOINT handler in conjunction with the ROLLBACK statement

Assisted Solution

srielau earned 248 total points
ID: 16651106
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Integration Management Part 2
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Suggested Courses

621 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