ROWID Equivalent

[Scott Holland]

OK. I have a problem and need some help from DB2 Gurus. In ORACLE, I
have a trigger which fires on an update event, sending the ROWID and
TRANSACTION_ID via a pipe to my application. The application can then
issue SQL to read the row that was updated (directly accessing it by
ROWID) and confirm that the transaction has committed before taking
this information by looking at open transactions.

QUESTIONS:

1) Is there ANY concept of ROWID in DB2 that is accessible to the
program?
2) Is there any concept of transaction_id accessible to the program?
3) What is the best equivalent of dbms_pipes in DB2? Is it to send
via MQSeries? Is this transactionally controlled so I would not need
transaction_id?

Thanks in advance!

[Serge Rielau]

Scott Holland wrote:

OK. I have a problem and need some help from DB2 Gurus. In ORACLE, I
have a trigger which fires on an update event, sending the ROWID and
TRANSACTION_ID via a pipe to my application. The application can then
issue SQL to read the row that was updated (directly accessing it by
ROWID) and confirm that the transaction has committed before taking
this information by looking at open transactions.

QUESTIONS:

1) Is there ANY concept of ROWID in DB2 that is accessible to the
program? Not in DB2 UDB for LUW. That's what primary keys are for. Given that you
just accessed the row it should be in the bufferpool (i.e. cheap to get
the second time) 2) Is there any concept of transaction_id accessible to the program? No 3) What is the best equivalent of dbms_pipes in DB2? Is it to send
via MQSeries? I don't knwo what DBMS pipes are, but I suppose you could either
implement a pipe through a C-UDF or use MQ Series.Is this transactionally controlled so I would not need transaction_id?

Not sure with MQ Series. Should be described in the SQL Ref though.

You got some weird logic there. What's the purpose of these acrobatics?
Maybe there is another way to do the same thing...
E.g. Note that the second application would not be able to read the row
until the triggering app has commited unless you use dirty reads.
So your app should naturally wait for the transaction to clear.

Cheers
Serge

[Paul Reddin]

Scott,
fyi:
- Doesn't solve your problem directly, but maybe some food for
thought.

The performance/overhead of this is USELESS if you are doing high
transaction rates!!

we use it just to be able to keep track of clp transactions (normally
txn_ids
are supplied from the app. server via one of the special client
registers).

We used the appl_id and sequence monitor elements to identify
transaction boundaries.

i.e if 2 statements occured in the same transaction we wanted to know
that
in the trigger. The mechanism we used was
SET (v_appl_id, v_sequence_no) = (SELECT appl_id,sequence_no
FROM TABLE (SNAPSHOT_APPL_INFO('DBNAME',CAST(NULL AS
INTEGER))) t
WHERE t.appl_id = dba.application_id());

- where dba.application_id() is a function from developer works.

In this way you can keep a seperate 'transaction table' where you can
easily apply a surrogate txn id to all statements in the same
transaction.

How you get this back to the app. is another problem which I have no
input on.

Paul.