sp_iqtransaction procedure

Function

Shows information about transactions and versions.

Syntax

sp_iqtransaction

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.

The sp_iqtransaction output does not contain rows for connections that do not have a transaction started. To see all connections, use sp_iqconnection.

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

Table 10-47: sp_iqtransaction columns

Column name

Description

Name

The name of the server.

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 nonmultiplex databases and multiplex write servers, the VersionID is the same as the TxnID. In multiplex query servers, the VersionID is the TxnID of the transaction that created the database version on the multiplex write server. 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.

MainTableKBCreated

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

MainTableKBDropped

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.

TempTableKBCreated

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

TempTableKBDropped

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.

Dbremote

A bit data column that indicates the transaction is an internal transaction used to replicate multiplex version information between a query server and the write server within a multiplex database.

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.

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

MainTableKBCreated MainTableKBDropped TempTableKBCreated TempTableKBDropped
================== ================== ================== ==================
                 0                  0              65824                  0
                 0                  0                  0                  0
                 0                  0                  0                  0
                 0                  0                  0                  0
              3960                152                  0                  0
                 0                  0                  0                  0
              2440               1992                  0                  0
                 0                  0                  0                  0
                 0                  0               2912              22096
                 0                  0                  0                  0

TempWorkSpaceKB TxnCreateTime      Dbremote CursorCount SpCount SpNumber
==============  ================== =======  ==========  ======  ======
       0    2006-06-26 13:17:27.612     0           1       3       2
  102592    2006-06-26 13:27:28.491     0           1       1       0
       0    2006-06-26 13:30:27.548     0           0       1       0
       0    2006-06-26 13:31:27.151     0           0      24     262
       0    2006-06-26 13:35:02.128     0           0       0       0
       0    2006-06-26 13:43:58.805     0           0      39     408
     128    2006-06-26 13:45:28.379     0           0       1       0
       0    2006-06-26 14:05:15.759     0           0      42     413
     680    2006-06-26 14:57:51.104     0           1       2      20
       0    2006-06-26 15:09:30.319     0           0       1       0