467,199 Members | 1,006 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,199 developers. It's quick & easy.

ROWID Equivalent

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!
Nov 12 '05 #1
  • viewed: 8989
Share:
2 Replies
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
Nov 12 '05 #2
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.
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by droope | last post: by
2 posts views Thread by captain | last post: by
1 post views Thread by Tenkre | last post: by
3 posts views Thread by TP | last post: by
reply views Thread by UJ | last post: by
2 posts views Thread by arivudai2 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.