Shows information about transactions and versions.
sp_iqtransaction
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
sp_iqtransaction returns a row for each transaction control block in the Sybase IQ transaction manager. The columns Name, Userid, and ConnHandle are the connection properties Name, Userid, and Number, respectively. Rows are ordered by TxnID.
sp_iqtransaction output does not contain rows for connections that do not have a transaction started. To see all connections, use sp_iqconnection.
Although you can use sp_iqtransaction to
identify users who are blocking other users from writing to a table, sp_iqlocks is
a better choice for this purpose.
Column name |
Description |
|---|---|
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. This is the same as the transaction ID displayed in the .iqmsg file by the BeginTxn, CmtTxn and PostCmtTxn messages as well as the Txn ID Seq logged when the database is opened. |
CmtID |
The ID assigned by the transaction manager when the transaction commits. It is zero for active transactions. |
VersionID |
In simplex databases, the VersionID is the same as the TxnID. For the multiplex coordinator, the VersionID is the same as the TxnID of the active transaction and VersionID is the same as the CmtID of a committed transaction. In multiplex secondary servers, the VersionID is the CmtID of the transaction that created the database version on the multiplex coordinator. It is used internally by the 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 Sybase IQ implementation detail and is subject to change in the future. At the time of this writing, transaction states are NONE, ACTIVE, ROLLING_BACK, ROLLED_BACK, COMMITTING, COMMITTED, and APPLIED. |
ConnHandle |
The ID number of the connection. |
IQConnID |
The ten-digit connection ID displayed 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, this is 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 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 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 |
The value indicates if an active transaction is from an inter-node 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 value indicates the global transaction ID associated with the current transaction. Zero if there is no associated global transaction. |
Here is an example of sp_iqtransaction output:
Name Userid TxnID CmtID VersionID State ConnHandle IQConnID ======= ===== ====== ====== ========= ========== =========== ======== red2 DBA 10058 10700 10058 COMMITTED 419740283 14 blue1 DBA 10568 0 10568 ACTIVE 640038605 17 DBA 10604 0 10604 ACTIVE 2094200996 18 fromSCJ DBA 10619 0 10619 ACTIVE 954498130 20 blue2 DBA 10634 10677 10634 COMMITTED 167015670 21 ntJava2 DBA 10676 0 10676 ACTIVE 1779741471 24 blue2 DBA 10678 0 10678 ACTIVE 167015670 21 nt1 DBA 10699 0 10699 ACTIVE 710225777 28 red2 DBA 10701 0 10701 ACTIVE 419740283 14 DBA 16687 0 16687 ACTIVE 1306718536 23 MainTableKBCr MainTableKBDr TempTableKBCr TempTableKBDr ============= ================== ================================ 0 0 65824 0 0 0 0 0 0 0 0 0 0 0 0 0 3960 152 0 0 0 0 0 0 2400 1992 0 0 0 0 0 0 0 0 2912 22096 0 0 0 0 TempWorkSpaceKB TxnCreateTime CursorCount SpCount SpNumber ============== ================== ========== ====== ====== 0 2009-06-26 13:17:27.612 1 3 2 102592 2009-06-26 13:27:28.491 1 1 0 0 2009-06-26 13:30:27.548 0 1 0 0 2009-06-26 13:31:27.151 0 24 262 0 2009-06-26 13:35:02.128 0 0 0 0 2009-06-26 13:43:58.805 0 39 408 128 2009-06-26 13:45:28.379 0 1 0 0 2009-06-26 14:05:15.759 0 42 413 680 2009-06-26 14:57:51.104 1 2 20 0 2009-06-26 15:09:30.319 0 1 0 MPXServerName GlobalTxnID ============= =========== (NULL) 0 (NULL) 0 (NULL) 0 (NULL) 0 (NULL) 0 (NULL) 0 (NULL) 0 (NULL) 0 (NULL) 0 (NULL) 0
