# SYSCS_DIAG.TRANSACTION_TABLE diagnostic table

The SYSCS_DIAG.TRANSACTION_TABLE diagnostic table shows all of the transactions that are currently in the database.

You can reference the SYSCS_DIAG.TRANSACTION_TABLE diagnostic table directly in a statement. For example:

    SELECT * FROM SYSCS_DIAG.TRANSACTION_TABLE

When the SYSCS_DIAG.TRANSACTION_TABLE diagnostic table is referenced in a statement, a snapshot of the transaction table is taken. A snapshot is used so that referencing the diagnostic table does not alter the normal timing and flow of the application. It is possible that some transactions will be in a transition state when the snapshot is taken.

For a database for which authentication and SQL authorization are both enabled, only the database owner can access this diagnostic table. See "Configuring user authentication" and "Configuring user authorization" in the Derby Security Guide for more information.

The table has the columns shown in the following table.

Table 1. Columns in the SYSCS_DIAG.TRANSACTION_TABLE table
Column Name | Type | Length | Nullable | Contents
--- | --- | --- | --- | ---
XID | VARCHAR | 15 | false | The transaction id, which can be joined with the lock table virtual table's XID. See SYSCS_DIAG.LOCK_TABLE diagnostic table.
GLOBAL_XID | VARCHAR | 140 | true | The global transaction ID, set only if this transaction is a participant in a distributed transaction.
USERNAME | VARCHAR | 128 | true | The user name (the default value is APP). May appear null if the transaction is started by Derby.
TYPE | VARCHAR | 30 | false | Either 'UserTransaction' or an internal transaction spawned by Derby.
STATUS | VARCHAR | 8 | false | Either 'IDLE' or 'ACTIVE'. A transaction is IDLE only when it is first created or right after it commits. Any transaction that holds or has held any resource in the database is ACTIVE. Accessing the TransactionTable virtual table without using the class alias will not activate the transaction.
FIRST_INSTANT | VARCHAR | 20 | true | If null, this is a read-only transaction. If not null, this is the first log record instant written by the transaction.
SQL_TEXT | VARCHAR | 32,672 | true | If null, this transaction is currently not being executed in the database. If not null, this is the SQL statement currently being executed in the database.

Parent topic: SYSCS_DIAG diagnostic tables and functions
Related reference
SYSCS_DIAG.CONTAINED_ROLES diagnostic table function
SYSCS_DIAG.ERROR_LOG_READER diagnostic table function
SYSCS_DIAG.ERROR_MESSAGES diagnostic table
SYSCS_DIAG.LOCK_TABLE diagnostic table
SYSCS_DIAG.SPACE_TABLE diagnostic table function
SYSCS_DIAG.STATEMENT_CACHE diagnostic table
SYSCS_DIAG.STATEMENT_DURATION diagnostic table function