Shows information about connections and versions, including which users are using temporary dbspace, which users are keeping versions alive, what the connections are doing inside IQ, connection status, database version status, and so on.
sp_iqconnection [ connhandle ]
The input parameter connhandle is equal to the Number connection property and is the ID number of the connection. The connection_property system function returns the connection ID:
SELECT connection_property ( 'Number' )
When called with an input parameter of a valid connhandle, sp_iqconnection returns the one row for that connection only.
sp_iqconnection returns a row for each active connection. The columns ConnHandle, Name, Userid, LastReqTime, ReqType, CommLink, NodeAddr, and LastIdle are the connection properties Number, Name, Userid, LastReqTime, ReqType, CommLink, NodeAddr, and LastIdle respectively, and return the same values as the system function sa_conn_info. The additional columns return connection data from the IQ side of the Sybase IQ engine. Rows are ordered by ConnCreateTime.
Column name |
Description |
|---|---|
ConnHandle |
The ID number of the connection. |
Name |
The name of the server. |
Userid |
The user ID for the connection. |
LastReqTime |
The time at which the last request for the specified connection started. |
ReqType |
A string for the type of the last request. |
IQCmdType |
The current command executing on the IQ side, if any. The command type reflects commands defined at the implementation level of the engine. These commands consists of transaction commands, DDL and DML commands for data in the IQ store, internal IQ cursor commands, and special control commands such as open and close db, backup, restore, etc. |
LastIQCmdTime |
The time the last IQ command started or completed on the IQ side of the Sybase IQ engine on this connection. |
IQCursors |
The number of cursors open in the IQ store on this connection. |
LowestIQCursorState |
The IQ cursor state, if any. If multiple cursors exist on the connection the state displayed is the lowest cursor state of all the cursors, i.e. the furthest from completion. Cursor state reflects internal IQ implementation detail and is subject to change in the future. For this version, cursor states are: NONE, INITIALIZED, PARSED, DESCRIBED, COSTED, PREPARED, EXECUTED, FETCHING, END_OF_DATA, CLOSED and COMPLETED. As suggested by the names, cursor state changes at the end of the operation. A state of PREPARED, for example, indicates that the cursor is executing. |
IQthreads |
The number of IQ threads currently assigned to the connection. Some threads may be assigned but idle. This column can help you determine which connections are using the most resources. |
TxnID |
The transaction id of the current transaction on the connection. 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. |
ConnCreateTime |
The time the connection was created. |
TempTableSpaceKB |
The number of kilobytes of IQ Temporary Store space in use by this connection for data stored in IQ temp tables. |
TempWorkSpaceKB |
The number of kilobytes of IQ Temporary Store space in use by this connection for working space such as sorts, hashes and temporary bitmaps. Space used by bitmaps or other objects that are part of indexes on IQ temporary tables are reflected in TempTableSpaceKB. |
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. |
satoiq_count |
An internal counter used to display the number of crossings from the ASA side to the IQ side of the Sybase IQ engine. This may occasionally be useful in determining connection activity. Note that result sets are returned in buffers of rows and DO NOT increment satoiq_count or iqtosa_count once per row. |
iqtosa_count |
An internal counter used to display the number of crossings from the IQ side to the ASA side of the Sybase IQ engine. This may occasionally be useful in determining connection activity. |
CommLink |
The communication link for the connection. This is one of the network protocols supported by Sybase IQ, or is “local” for a same-machine connection. |
NodeAddr |
The node for the client in a client/server connection. |
LastIdle |
The number of ticks between requests. |
Dbremote |
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. |
Here is an example of sp_iqconnection output:
ConnHandle Name Userid LastReqTime ReqType IQCmdType ========== ======== ====== ========================= ==================== ==================== 419740283 red2 DBA 2004-01-02 15:54:54.605 STMT_EXECUTE_IMM INSERT 640038605 blue1 DBA 2004-01-02 13:32:42.505 CURSOR_PREFETCH NONE 2094200996 DBA 2004-01-02 13:30:27.486 STMT_EXECUTE_ANY_IMM NONE 954498130 fromSCJ DBA 2004-01-02 15:55:02.787752 STMT_DROP NONE 167015670 blue2 DBA 2004-01-02 13:45:50.232752 STMT_DROP NONE 1306718536 DBA 2004-01-02 15:08:36.716 STMT_EXECUTE_ANY_IMM NONE 1779741471 ntJava2 DBA 2004-01-02 15:54:58.558752 STMT_DROP NONE 710225777 nt1 DBA 2004-01-02 15:56:02.729 CURSOR_OPEN IQUTILITYOPENCURSOR LastIQCmdTime IQCursors LowestIQCursorState IQthreads TxnID ConnCreateTime ======================= ========= =================== ========= ===== ======================== 2004-01-02 15:54:54.630 1 EXECUTED 7 10701 2004-01-02 13:17:27.599 2004-01-02 13:32:42.295 1 FETCHING 2 10568 2004-01-02 13:21:19.953 2004-01-02 13:30:27.548 0 NONE 1 10604 2004-01-02 13:24:35.145 2004-01-02 15:55:02.590 0 NONE 1 10619 2004-01-02 13:31:26.001 2004-01-02 13:45:50.225 0 NONE 1 10678 2004-01-02 13:35:01.160 2004-01-02 15:09:30.320 0 NONE 1 16687 2004-01-02 13:37:50.814 2004-01-02 15:54:58.553 0 NONE 1 10676 2004-01-02 13:43:57.907 2004-01-02 15:56:02.755 0 NONE 1 10699 2004-01-02 14:05:15.748 TempTableSpaceKB TempWorkSpaceKB IQconnID satoiq_count iqtosa_count CommLink NodeAddr LastIdle ================ =============== ======== ============ ============ ======== ========== ======= 68736 680 14 82 2031 TCPIP 157.133.82.17 9905 0 102592 17 76 360 local 606 0 0 18 397 688 TCPIP 157.133.83.151 8322 0 0 20 709 1541 TCPIP 157.133.83.151 5378 0 128 21 131 2082 local 5122 0 0 23 18313 821 TCPIP 157.133.83.151 10000 0 0 24 994 1667 TCPIP 157.133.83.151 1467 0 0 28 900 478 TCPIP 157.133.83.151 5473
