onstat -x command: Print database server transaction information

Use the onstat -x command to display transaction information on the database server.

Figure 1: Syntax:

1  onstat   -x
The transaction information is required only in the following situations:
  • X/Open environment
  • Database server participation in distributed queries
  • Database server uses the Microsoft™ Transaction Server (MTS) transaction manager

Example output

Figure 2: onstat -x command output

Transactions

address   flags userthread  locks  begin    current     isol   est.     retrys coord
                                    logpos   logpos             rb_time
a6d8028   A---- a695028     0      -         -          COMMIT  -        0      
a6d8348   A---- a695878     0      -         -          COMMIT  -        0      
a6d8668   A---- a6960c8     0      -         -          COMMIT  -        0      
a6d8988   A---- a696918     0      -         -          COMMIT  -        0      
a6d8fc8   A---- a698208     0      -         -          COMMIT  -        0      
a6d92e8   A---- a6979b8     0      -         -          COMMIT  -        0      
a6d9608   A---- a698a58     0      -         -          COMMIT  -        0      
a6d9928   A---- a6992a8     1      -         -          DIRTY   -        0      
a6d9c48   A---- a6992a8     0      -         -          NOTRANS -        0      
a6d9f68   A---- a69a348     0      -         -          COMMIT  -        0      
a6da288   A---- a69ab98     0      -         -          COMMIT  -        0      
a6da5a8   A---- a69b3e8     0      -         -          COMMIT  -        0      
a6da8c8   A---- a69bc38     0      -         -          COMMIT  -        0      
a6dabe8   A---- a69c488     0      -         -          COMMIT  -        0      
a6daf08   A---- a699af8     0      -         -          COMMIT  -        0      
a6db228   A---- a6992a8     0      -         -          COMMIT  -        0      
a6db548   A---- a69ccd8     1      -         -          DIRTY   -        0      
a6db868   A---- a69d528     1      -         -          DIRTY   -        0      
a6dbb88   A---- a69ccd8     0      -         -          COMMIT  -        0      
a6dbea8   A---- a69dd78     0      -         -          COMMIT  -        0      
a6dc1c8   A---- a69e5c8     0      -         -          COMMIT  -        0      
a6dc4e8   A-B-- a69ee18   502    33:0x25018  34:0x486fc COMMIT  0:07     0      
 22 active, 128 total, 23 maximum concurrent

Output description

You can interpret output from the onstat -x command as follows:
address
The shared-memory address of the transaction structure
flags
The flag codes for position 1 (current transaction state):
A
User thread attached to the transaction
S
TP/XA suspended transaction
C
TP/XA waiting for rollback
The flag codes for position 2 (transaction mode):
T
Tightly-coupled mode (MTS)
L
Loosely-coupled mode (default mode)
The flag codes for position 3 (transaction stage):
B
Begin work
P
Distributed query prepared for commit
X
TP/XA prepared for commit
C
Committing or committed
R
Rolling back or rolled back
H
Heuristically rolling back or rolled back
The flag code for position 4:
X
XA transaction
The flag codes for position 5 (type of transaction):
G
Global transaction
C
Distributed query coordinator
S
Distributed query subordinate
B
Both distributed query coordinator and subordinate
M
Redirected global transaction
userthread
The thread that owns the transaction (rstcb address)
locks
The number of locks that the transaction holds
begin logpos
The position within the log when the BEGIN WORK record was logged.
current logpos
The current log position of the most recent record that the transaction is wrote too (As a transaction rolls back, the current log position will actually wind back until it gets to the beginning log position. When the beginning log position is reached, the rollback is complete.)
isol
The isolation level.
est. rb time
The estimated time the server needs to rollback the transaction. As a transaction goes forward, this time increases. If a transaction rolls back, the time decreases as the transaction unwinds.
retrys
Are the attempts to start a recovery thread for the distributed query
coord
The name of the transaction coordinator when the subordinate is executing the transaction

This field tells you which database server is coordinating the two-phase commit.

The last line of the onstat -x command output indicates that 8 is the maximum number of concurrent transactions since you initialized the database server. 
8 active, 128 total, 8 maximum concurrent