onstat -x command: Print database server transaction information

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

Read syntax diagramSkip visual syntax diagram

>>-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 1. onstat -x command output

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:
The shared-memory address of the transaction structure
The flag codes for position 1 (current transaction state):
User thread attached to the transaction
TP/XA suspended transaction
TP/XA waiting for rollback
The flag codes for position 2 (transaction mode):
Tightly-coupled mode (MTS)
Loosely-coupled mode (default mode)
The flag codes for position 3 (transaction stage):
Begin work
Distributed query prepared for commit
TP/XA prepared for commit
Committing or committed
Rolling back or rolled back
Heuristically rolling back or rolled back
The flag code for position 4:
XA transaction
The flag codes for position 5 (type of transaction):
Global transaction
Distributed query coordinator
Distributed query subordinate
Both distributed query coordinator and subordinate
Redirected global transaction
The thread that owns the transaction (rstcb address)
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.)
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.
Are the attempts to start a recovery thread for the distributed query
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

Copyright© 2018 HCL Technologies Limited