Monitor a global transaction

Use the onstat -x command to track open transactions and determine whether they have been heuristically rolled back.

For example, in the output, an H flag in the flags field identifies a heuristic rollback, the G flag identifies a global transaction, the L flag indicates loosely coupled mode, and the T flag indicates tightly coupled mode.

The curlog and logposit fields provide the exact position of a logical-log record. If a transaction is not rolling back, curlog and logposit describe the position of the most recently written log record. When a transaction is rolling back, these fields describe the position of the most recently “undone” log record. As the transaction rolls back, the curlog and logposit values decrease. In a long transaction, the rate at which the logposit and beginlg values converge can help you estimate how much longer the rollback is going to take.

For more information about and an example of onstat -x output, see the HCL Informix® Administrator's Reference.

You also can use the onstat -u and onstat -k commands to track transactions and the locks that they hold. For details, see the monitoring transactions topics in your . For a description of the fields that onstat -x displays, see the HCL Informix Administrator's Reference.

On a secondary server, when transaction completion after failover is enabled (by setting the FAILOVER_TX_TIMEOUT configuration parameter), it is possible that two global transactions might have the same global transaction identifier: one is a local temporary global transaction, and the other is the global transaction that belongs to the recovery thread. A quick way to tell the real global transaction from the temporary transaction is that the real transaction has a B flag if the transaction has performed any operations. You can also check the owner of the transaction by using the onstat -g ath command. The temporary global transaction on the secondary server is deleted after the xa_end() function is called.

The following onstat utility example output illustrates XA transaction support on both primary and secondary servers in a high-availability cluster environment. The onstat -x, onstat -G, and onstat -ath commands are separately documented, but output from the combined onstat -xG command is of special interest for global transactions. The examples show each state of a redirected transaction.

In the examples, the global transaction shown running on the secondary server is a temporary transaction. The temporary transaction is used to support the SQL statements performed on the secondary server (not transactions redirected to the primary server). The temporary transaction is only shown when a user thread is actively associated with the global transaction branch.

The following example shows output from the onstat -xG command run on a secondary server after an xa_start() function:

Transactions
                                                                                           est.
address          flags userthread       locks  begin_logpos      current logpos    isol    rb_time  retrys coord
7000000104d4190  AT--G 7000000104a7b68  0      -                 -                 LC      -        0
7000000104d8bd0  ALB-G 7000000104a5aa8  1      180:0x0           180:0x4eb018      DIRTY   0:00     0

Global Transaction Identifiers
address           flags  isol    timeout  fID        gtl  bql  data
7000000104d4190   AT--G  COMMIT  0        5067085    15   4    000102030405060708090A0B0C0D0E0F000000
7000000104d8bd0   ALB-G  DIRTY   0        5067085    15   4    000102030405060708090A0B0C0D0E0F000000

Output from onstat -g ath run on the secondary server:

Threads:
 tid     tcb              rstcb            prty status                vp-class       name
 317     7000001500902c8  7000000104a7b68  1    cond wait  netnorm      1cpu         sqlexec
 84      7000001403a7dc0  7000000104a5aa8  3    sleeping secs: 1        5cpu         xchg_2.0

Output from onstat -xG run on the primary server:

Transactions
                                                                                           est.
address          flags userthread       locks  begin_logpos      current logpos    isol    rb_time  retrys coord
7000000104d9e60  ATB-M 7000000104a8bc8  2      180:0x4ea018      180:0x4eb018      COMMIT  0:00     0

Global Transaction Identifiers
address           flags  isol    timeout  fID        gtl  bql  data
7000000104d9e60   AT--M  COMMIT  0        5067085    15   4    000102030405060708090A0B0C0D0E0F000000

The M flag in the previous example indicates that the global transaction was started from a secondary server. Global transactions started on the primary server display a G flag. The M flag is displayed only on the primary server.

Output from the onstat -g ath|grep 7000000104a8bc8 command:

 196     70000013012d3a8  7000000104a8bc8  1    sleeping secs: 1        4cpu         proxyTh

The following example shows output from the onstat -xG command run on secondary server after the xa_end() function:

Transactions
                                                                                           est.
address          flags userthread       locks  begin_logpos      current logpos    isol    rb_time  retrys coord
7000000104d8bd0  ALB-G 7000000104a5aa8  1      180:0x0           180:0x4ee018      DIRTY   0:00     0

Global Transaction Identifiers
address           flags  isol    timeout  fID        gtl  bql  data
7000000104d8bd0   ALB-G  DIRTY   0        5067085    15   4    000102030405060708090A0B0C0D0E0F000000

Output from the onstat -xG command run on the primary server:

Transactions
                                                                                           est.
address          flags userthread       locks  begin_logpos      current logpos    isol    rb_time  retrys coord
7000000104d9e60  -TB-M 0                2      180:0x4ea018      180:0x4ee018      COMMIT  0:00     0


Global Transaction Identifiers
address           flags  isol    timeout  fID        gtl  bql  data
7000000104d9e60   -T--M  COMMIT  -1       5067085    15   4    000102030405060708090A0B0C0D0E0F000000

Output from the onstat -xG command run on the secondary server after running the xa_prepare() function:

Transactions
                                                                                           est.
address          flags userthread       locks  begin_logpos      current logpos    isol    rb_time  retrys coord
7000000104d8bd0  ALX-G 7000000104a5aa8  1      180:0x0           180:0x4ef018      DIRTY   0:00     0

Global Transaction Identifiers
address           flags  isol    timeout  fID        gtl  bql  data
7000000104d8bd0   ALX-G  DIRTY   0        5067085    15   4    000102030405060708090A0B0C0D0E0F000000

Output from the onstat -xG command run on the primary server:

Transactions
                                                                                           est.
address          flags userthread       locks  begin_logpos      current logpos    isol    rb_time  retrys coord
7000000104d9e60  -TX-M 0                2      180:0x4ea018      180:0x4ef018      COMMIT  0:00     0

Global Transaction Identifiers
address           flags  isol    timeout  fID        gtl  bql  data
7000000104d9e60   -TX-M  COMMIT  -1       5067085    15   4    0

Copyright© 2019 HCL Technologies Limited