sp_iqtransaction Procedure

Name

The name of the application.

Userid

The user ID for the connection.

TxnID

The transaction ID of this transaction control block. The transaction ID is assigned during begin transaction. It appears in the .iqmsg file by the BeginTxn, CmtTxn, and PostCmtTxn messages, and is the same as the Txn ID Seq that is logged when the database is opened.

CmtID

The ID assigned by the transaction manager when the transaction commits. For active transactions, the CmtID is zero.

VersionID

For simplex and multiplex nodes, a value of 0 indicates that the transaction is unversioned, and the VersionID has not been assigned.

For the multiplex coordinator, the VersionID is assigned after the transaction establishes table locks. Multiplex secondary servers receive the VersionID from the coordinator. The VersionID is used internally by the SAP Sybase IQ in-memory catalog and the IQ transaction manager to uniquely identify a database version to all nodes within a multiplex database.

State

The state of the transaction control block. This variable reflects internal SAP Sybase IQ implementation details and is subject to change in the future. Currently, transaction states are NONE, ACTIVE, ROLLING_BACK, ROLLED_BACK, COMMITTING, COMMITTED, and APPLIED.

NONE, ROLLING_BACK, ROLLED_BACK, COMMITTING and APPLIED are transient states with a very small life span.

ACTIVE indicates that the transaction is active.

COMMITTED indicates that the transaction has completed and is waiting to be APPLIED, at which point a version that is invisible to any transaction is subject to garbage collection.

Once the transaction state is ROLLED_BACK, COMMITTED, or APPLIED, ceases to own any locks other than those held by open cursors.

ConnHandle

The ID number of the connection.

IQConnID

The ten-digit connection ID that is included as part of all messages in the .iqmsg file. This is a monotonically increasing integer unique within a server session.

MainTableKBCr

The number of kilobytes of IQ store space created by this transaction.

MainTableKBDr

The number of kilobytes of IQ store space dropped by this transaction, but which persist on disk in the store because the space is visible in other database versions or other savepoints of this transaction.

TempTableKBCr

The number of kilobytes of IQ temporary store space created by this transaction for storage of IQ temporary table data.

TempTableKBDr

The number of kilobytes of IQ temporary table space dropped by this transaction, but which persist on disk in the IQ temporary store because the space is visible to IQ cursors or is owned by other savepoints of this transaction.

TempWorkSpaceKB

For ACTIVE transactions, a snapshot of the work space in use at this instant by this transaction, such as sorts, hashes, and temporary bitmaps. The number varies depending on when you run sp_iqtransaction. For example, the query engine might create 60MB in the temporary cache but release most of it quickly, even though query processing continues. If you run sp_iqtransaction after the query finishes, this column shows a much smaller number. When the transaction is no longer active, this column is zero.

For ACTIVE transactions, this column is the same as the TempWorkSpaceKB column of sp_iqconnection.

TxnCreateTime

The time the transaction began. All SAP Sybase IQ transactions begin implicitly as soon as an active connection is established or when the previous transaction commits or rolls back.

CursorCount

The number of open SAP Sybase IQ cursors that reference this transaction control block. If the transaction is ACTIVE, it indicates the number of open cursors created within the transaction. If the transaction is COMMITTED, it indicates the number of hold cursors that reference a database version owned by this transaction control block.

SpCount

The number of savepoint structures that exist within the transaction control block. Savepoints may be created and released implicitly. Therefore, this number does not indicate the number of user-created savepoints within the transaction.

SpNumber

The active savepoint number of the transaction. This is an implementation detail and might not reflect a user-created savepoint.

MPXServerName

Indicates if an active transaction is from an internode communication (INC) connection. If from INC connection, the value is the name of the multiplex server where the transaction originates. NULL if not from an INC connection. Always NULL if the transaction is not active.

GlobalTxnID

The global transaction ID associated with the current transaction, 0 (zero) if none.

VersioningType

The snapshot versioning type of the transaction; either table-level (the default), or row-level. Row-level snapshot versioning (RLV) applies only to RLV-enabled tables. Once a transaction is started, this value cannot change.

Blocking

Indicates if connection blocking is enabled (True) or disabled (False). You set connection blocking using the BLOCKING database option. If true, the transaction blocks, meaning it waits for a conflicting lock to release before it attempts to retry the lock request.

BlockingTimeout

Indicates the time, in milliseconds, a transaction waits for a locking conflict to clear. You set the timeout threshold using the BLOCKING_TIMEOUT database option. A value of 0 (default) indicates that the transaction waits indefinitely.