sp_iqtransaction Procedure

Shows information about transactions and versions.

Syntax

sp_iqtransaction

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Description

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.

Note: 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.

You can use this procedure in a multiplex environment. See Using Sybase IQ Multiplex.

sp_iqtransaction columns
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 displayed as zero. 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 details 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`. `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 not visible to any transaction will be garbage collected. Once the transaction state is `ROLLED_BACK`, `COMMITTED`, or `APPLIED`, it will cease to own any locks other than those held by open cursors.
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.

Example

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