Access to transaction data in trigger

Arthur Sannen

Access to transaction data in trigger
Hi

env: DB2 V8.1 on z/OS

Is it somehow possible for a trigger to have access to data created in the
program that modified the table on which the trigger is defined?

I would like to set a transaction ID in my main program and have all the
triggers that get fired during an update to pick up that same transaction ID.

I did try using a declared global temporary table but that didn't work as the
trigger execution is a different process and as such does not have access to
the same session.table.

This has to work in a multi-threaded environment so a single row on a
persistent table also doesn't solve the problem.

Any suggestions would be most welcome.

Regards

Arthur

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
** If you are going to attend only one conference this year, this is it!

_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Dave Nance

Re: Access to transaction data in trigger
(in response to Arthur Sannen)
Arthur,
   Take a look at special registers, see if you can make use of any of them. If not, then you would have to add something else programatically. For instance, update a transaction id column, then using n.tran_id in your triggers.
 
David Nance




________________________________
From: Arthur Sannen <[login to unmask email]>
To: [login to unmask email]
Sent: Tue, February 9, 2010 5:28:48 PM
Subject: [DB2-L] Access to transaction data in trigger

Hi

env: DB2 V8.1 on z/OS

Is it somehow possible for a trigger to have access to data created in the
program that modified the table on which the trigger is defined?

I would like to set a transaction ID in my main program and have all the
triggers that get fired during an update to pick up that same transaction ID.

I did try using a declared global temporary table but that didn't work as the
trigger execution is a different process and as such does not have access to
the same session.table.

This has to work in a multi-threaded environment so a single row on a
persistent table also doesn't solve the problem.

Any suggestions would be most welcome.

Regards

Arthur

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *  http://IDUG.ORG/NA *
**  If you are going to attend only one conference this year, this is it!

_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to $1600 off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L





_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Scott Hodgin

Re: Access to transaction data in trigger
(in response to Dave Nance)
Arthur,
Did you try _created_ global temp tables instead of _declared_ global_ temp
tables.

In other words you actually issue CREATE GLOBAL TEMP TABLE
schema.TRAN_ID_TRACKER (TRAN ID CHAR(blah)). This CREATE
occurs outside of an application and you only need to define it once. The
definition is stored in the DB2 catalog, but no space is allocated yet. Only
when you start accessing it, is it actually created and I would assume any
program (trigger) in that LUW would be able to get to it

Your main program should be able to insert a TRAN_ID into the _created_
global temp table and the triggers should be able to select from
schema.TRAN_ID_TRACKER.


PS:

Schema is whatever you choose.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L