On the Linux server where the DB resides the replication trigger captures details about the update and uses DBTASKID to get the current transaction#. Another process checks these "in abeyance" details and tests _Trans to see if the transaction has completed. If completed the change is committed and can be dealt-with.
"Another process" is critical.
How did this other process discover the transaction# or usr# of the "in abeyance" details?
I'm trying to get my head around the situation where a trigger executes on an AppServer -- which is on a Windows machine. Will the DBTASKID function return the transaction# that the server is using to embrace this activity that the client is causing? If it is then exactly the same trigger code will work on the AppServer client as is used on the server. But if not then how can I know when the client transaction is committed?
On the appserver side when you call dbtaskid() it is going to report that appServer session's current trx# (if it has one active) or ?. It has no built-in awareness of the transaction state of the client who called it.
A replication trigger runs within the context of the session that starts the transaction, so it has knowledge of the dbtaskid() it is running within. It is not running in any process context other than that of the session that triggered the transaction.
When you say "a trigger executes on an AppServer" what do you mean?
I take that to mean that something called an appServer and that appServer did something which updates the database. Therefore a transaction is created. The trigger code is going to be run by the appServer and within the context of the session that started the transaction. That trigger code will have the same dbtaskid() as the code which started the transaction. (Because they are all the same process and session.)
If, on the other hand, you mean that
the trigger code is calling an appserver procedure then that appServer procedure will NOT have the same dbtaskid() as the caller.
Regarding the "Another process" again - If this other process is able to check _Trans then it seems like you must have coded an API of some sort to pass the usr# or similar information to it somehow. You need some way to communicate between processes. Writing to a text file, or a named pipe, or sockets, or a JMS queue or whatever.
For the sake of argument perhaps you wrote it to a text file and your "in abeyance" process reads that file to see which transactions it wants to look into. The local "dbtaskid()" would be just as meaningless to such a process as it is to a called appServer. But your code to check _Trans should be just as useful so long as you had a way to pass data between the processes.
On the other hand, if your "in abeyance" code running on Linux seems like it is getting the dbtaskid() *without* having it passed via some mechanism then I am afraid that you must be running that code within the context of the original transaction.
As for "how can I know when the client transaction is committed"? The active transaction will disappear but it could have just as easily been undone. That might, or might not, be important to what you are doing. I'm not sure. If you are writing these "in abeyance" details to a table in the same database and you are doing so within the context of the original transaction in question then the ability to FIND those details with an EXCLUSIVE-LOCK from the context of a different process demonstrates that the transaction committed.
Are you sure that using CDC wouldn't be a whole lot simpler?