Support of nested transactions within IBM DB2 Stored Procedure

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?
artpowell303Asked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
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!
Kent
0
 
sachinwadhwaCommented:
I not sure if its possible in DB2 SQL PL

but

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

0
 
ghp7000Commented:
try using the SAVEPOINT handler in conjunction with the ROLLBACK statement
0
 
srielauCommented:
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.
 
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.