Oracle PRAGMA AUTONOUMOUS_TRA NSACTION equivalent in DB2?

[Fred Morrison]

Does DB2 have anything equivalent to Oracle's PRAGMA AUTONOMOUS_TRAN SACTION
that allows a stored procedure to BEGIN, COMMIT or ROLLBACK a totally
independent transaction while possibly (but not necessarily) nested inside a
different ("parent") transaction?

I need to write (INSERT) data to an EVENT_LOG table and COMMIT that INSERT
without COMMIT'ing any other work in progress in the transaction my
usp_write_event _log might be called from within.

[Knut Stolze]

Fred Morrison wrote:

Does DB2 have anything equivalent to Oracle's PRAGMA
AUTONOMOUS_TRAN SACTION that allows a stored procedure to BEGIN, COMMIT or
ROLLBACK a totally independent transaction while possibly (but not
necessarily) nested inside a different ("parent") transaction?

I need to write (INSERT) data to an EVENT_LOG table and COMMIT that INSERT
without COMMIT'ing any other work in progress in the transaction my
usp_write_event _log might be called from within.

No, there is nothing like that in DB2. However, you could use an external
proceduce that establishes a new connection to the DBMS (and thus, a new
session/transaction context).

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[4.spam]

I always wanted to ask following question:

DB2 v8.2.1, Windows.
Try this;
---
declare global temporary table session.t (i int) with replace on commit
preserve rows not logged on rollback preserve rows@
create table logger_test(i int)@
create procedure logger
(
cmd varchar(255)
, tmp varchar(255)
)
language sql
begin atomic
declare stmt varchar(255);
set stmt='insert into '||tmp||' values (1)';
execute immediate stmt;
set stmt='insert into logger_test values (1)';
execute immediate stmt;
execute immediate cmd;
end@
call logger('execute me, please!!!', 'session.t')@
select i i_session from session.t@
select i i_logger_test from logger_test@
---
This small example shows that we can rollback changes in a cataloged
table but preserve changes in session table if error occured in atomic
block.
But it works despite of documentation
(http://publib.boulder.ibm.com/infoce...n/r0003272.htm)
:
---
Notes:
Instantiation and Termination:
....
When a rollback operation terminates a unit of work or a savepoint in
P, and that unit of work or savepoint includes a modification to
SESSION.T, then if NOT LOGGED was specified, the rollback includes the
operation DELETE from SESSION.T, else the changes to T are undone.
---
Is this behavior a feature or... ?
If this Works As Designed, then, Fred, you can use this technique for
logging.

Sincerely,
Mark B.

Fred Morrison wrote:

Does DB2 have anything equivalent to Oracle's PRAGMA
AUTONOMOUS_TRAN SACTION that allows a stored procedure to BEGIN, COMMIT or
ROLLBACK a totally independent transaction while possibly (but not
necessarily) nested inside a different ("parent") transaction?

I need to write (INSERT) data to an EVENT_LOG table and COMMIT that INSERT
without COMMIT'ing any other work in progress in the transaction my
usp_write_event _log might be called from within.

No, there is nothing like that in DB2. However, you could use an external
proceduce that establishes a new connection to the DBMS (and thus, a new
session/transaction context).

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

4.****@mail.ru wrote:

I always wanted to ask following question:

DB2 v8.2.1, Windows.
Try this;
---
declare global temporary table session.t (i int) with replace on commit
preserve rows not logged on rollback preserve rows@
create table logger_test(i int)@
create procedure logger
(
cmd varchar(255)
, tmp varchar(255)
)
language sql
begin atomic
declare stmt varchar(255);
set stmt='insert into '||tmp||' values (1)';
execute immediate stmt;
set stmt='insert into logger_test values (1)';
execute immediate stmt;
execute immediate cmd;
end@
call logger('execute me, please!!!', 'session.t')@
select i i_session from session.t@
select i i_logger_test from logger_test@
---
This small example shows that we can rollback changes in a cataloged
table but preserve changes in session table if error occured in atomic
block.
But it works despite of documentation
(http://publib.boulder.ibm.com/infoce...n/r0003272.htm)
:
---
Notes:
Instantiation and Termination:
...
When a rollback operation terminates a unit of work or a savepoint in
P, and that unit of work or savepoint includes a modification to
SESSION.T, then if NOT LOGGED was specified, the rollback includes the
operation DELETE from SESSION.T, else the changes to T are undone.
---
Is this behavior a feature or... ?
If this Works As Designed, then, Fred, you can use this technique for
logging.

For a bug it's using quite explicit syntax language :-)
This feature was added to support SQL procedure tracing
http://www-128.ibm.com/developerwork...dm-0409rielau/

I have submitted a doc bug report using the feedback button on the DB2 9
Information Center.

You are correct that this feature is well suited for for event logging.
For auditing things get a bit more tricky.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/