471,090 Members | 1,334 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

Oracle PRAGMA AUTONOUMOUS_TRANSACTION equivalent in DB2?

Does DB2 have anything equivalent to Oracle's PRAGMA AUTONOMOUS_TRANSACTION
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.
Sep 7 '06 #1
3 3317
Fred Morrison wrote:
Does DB2 have anything equivalent to Oracle's PRAGMA
AUTONOMOUS_TRANSACTION 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
Sep 7 '06 #2
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_TRANSACTION 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
Sep 7 '06 #3
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/
Sep 7 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Mark Wilson CPU | last post: by
38 posts views Thread by Mike | last post: by
125 posts views Thread by Rhino | last post: by
133 posts views Thread by jonathan | last post: by
2 posts views Thread by gimme_this_gimme_that | last post: by
6 posts views Thread by Shri | last post: by
8 posts views Thread by Steve Richter | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.