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 Sybase 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 Sybase IQ side of the Sybase IQ engine. Rows are ordered by ConnCreateTime. In Java applications, specify Sybase IQ-specific connection properties from TDS clients in the Remote PWD field. For details, see “Using the RemotePWD parameter” in the Sybase IQ System Administration Guide.
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 Sybase 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, and others. |
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; that is, the furthest from completion. Cursor state reflects internal Sybase 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 Sybase 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 Sybase 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 might be occasionally useful in determining connection activity. 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 might be occasionally 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 |
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. |
The following is an example of sp_iqconnection output:
ConnHandle Name Userid LastReqTime ReqType IQCmdType ========== ======== ====== ========================= ==================== ==================== 419740283 red2 DBA 2006-06-262006-06-26 15:54:54.605 STMT_EXECUTE_IMM INSERT 640038605 blue1 DBA 2006-06-26 13:32:42.505 CURSOR_PREFETCH NONE 2094200996 DBA 2006-06-26 13:30:27.486 STMT_EXECUTE_ANY_IMM NONE 954498130 fromSCJ DBA 2006-06-26 15:55:02.787752 STMT_DROP NONE 167015670 blue2 DBA 2006-06-26 13:45:50.232752 STMT_DROP NONE 1306718536 DBA 2006-06-26 15:08:36.716 STMT_EXECUTE_ANY_IMM NONE 1779741471 ntJava2 DBA 2006-06-26 15:54:58.558752 STMT_DROP NONE 710225777 nt1 DBA 2006-06-26 15:56:02.729 CURSOR_OPEN IQUTILITYOPENCURSOR LastIQCmdTime IQCursors LowestIQCursorState IQthreads TxnID ConnCreateTime ======================= ========= =================== ========= ===== ======================== 2006-06-26 15:54:54.630 1 EXECUTED 7 10701 2006-06-26 13:17:27.599 2006-06-26 13:32:42.295 1 FETCHING 2 10568 2006-06-26 13:21:19.953 2006-06-26 13:30:27.548 0 NONE 1 10604 2006-06-26 13:24:35.145 2006-06-26 15:55:02.590 0 NONE 1 10619 2006-06-26 13:31:26.001 2006-06-26 13:45:50.225 0 NONE 1 10678 2006-06-26 13:35:01.160 2006-06-26 15:09:30.320 0 NONE 1 16687 2006-06-26 13:37:50.814 2006-06-26 15:54:58.553 0 NONE 1 10676 2006-06-26 13:43:57.907 2006-06-26 15:56:02.755 0 NONE 1 10699 2006-06-26 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
