By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

Oracle PRAGMA AUTONOUMOUS_TRANSACTION equivalent in DB2?

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
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

P: n/a
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.