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.
DBA authority required. Users without DBA authority must be granted EXECUTE permission in order to run the stored procedure.
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.
The column MPXServerName stores information related to multiplex Inter-Node Communication (INC), as shown in Table A-1:
Server where run |
MPXServerName column content |
|---|---|
Simplex server |
NULL (All connections are local/user connections.) |
Multiplex coordinator |
|
Multiplex secondary |
|
In Java applications, specify Sybase IQ-specific connection properties from TDS clients in the RemotePWD field. This example, where myconnection becomes the IQ connection name, shows how to specify IQ specific connection parameters:
p.put("RemotePWD",",,CON=myconnection");
For more details about using the RemotePWD parameter, see SQL Anywhere Server Programming.
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 SQL Anywhere 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 SQL Anywhere 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. |
MPXServerName |
If an INC connection, the varchar(128) value contains the name of the multiplex server where the INC connection originates. NULL if not an INC connection. |
The following is an example of sp_iqconnection output:
ConnHandle Name Userid LastReqTime ReqType === =============== ====== ========================== ======= 9 'IQ_MPX_SERVER_H' 'dbo' '2008-11-18 13:15:00.035' 'EXEC' 11 'IQ_MPX_SERVER_H' 'dbo' '2008-11-18 13:15:00.046' 'EXEC' 13 'IQ_MPX_SERVER_H' 'dbo' '2008-11-18 14:52:55.003' 'EXEC' 15 'IQ_MPX_SERVER_H' 'dbo' '2008-11-18 14:53:25.005' 'EXEC' 17 'SQL_DBC_49450e8' 'DBA' '2008-11-18 14:59:45.680' 'OPEN' 44 'Sybase Central 1' 'DBA' '2008-11-18 14:59:45.023' 'CLOSE' IQCmdType LastIQCmdTime IQCursors LowestIQCursorState ==================== ============== ========= =================== 'NONE' 2008-11-18 13:15:00.0 0 'NONE' 'NONE' 2008-11-18 13:15:00.0 0 'NONE' 'NONE' 2008-11-18 14:52:55.0 0 'NONE' 'NONE' 2008-11-18 14:53:25.0 0 'NONE' 'IQUTILITYOPENCURSOR' 2008-11-18 14:59:45.0 0 'NONE' 'NONE' 2008-11-18 14:43:33.0 0 'NONE' IQthreads TxnID ConnCreateTime TempTableSpaceKB TempWorkSpaceKB ========= ===== ===================== =============== =============== 1 0 2008-11-18 13:14:09.0 0 0 1 0 2008-11-18 13:14:34.0 0 0 1 0 2008-11-18 13:14:55.0 0 0 1 0 2008-11-18 13:15:25.0 0 0 1 50024 2008-11-18 13:28:08.0 0 0 1 50545 2008-11-18 14:03:50.0 0 0 IQconnID satoiq_count iqtosa_count CommLink NodeAdd LastIdle MPXServerName ======== ============ ============ ======== ======= ======== ============ 23198 28 12 'local' '' 2977 'mpx0631_r1' 23202 28 12 'local' '' 1503 'mpx0631_r2' 23207 127 12 'local' '' 10000 'mpx0631_w1' 23212 127 12 'local' '' 10000 'mpx0631_w2' 23267 658 66 'TCPIP' '10.18.60.181' 9375 23443 510 54 'local' '' 1238
