onstat -x command: Print database server transaction information

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


Syntax:

>>-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

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