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 SAP Sybase IQ, connection status, database version status, and so on.
Simplex and multiplex.
SELECT connection_property ( 'Number' )
When called with an input parameter of a valid connhandle, sp_iqconnection returns the one row for that connection only.
Requires the DROP CONNECTION, MONITOR or SERVER OPERATOR system privilege. Users without one of these system privileges must be granted EXECUTE permission 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 SAP Sybase IQ side of the SAP Sybase IQ engine. Rows are ordered by ConnCreateTime.
The column MPXServerName stores information related to internode communication (INC), as shown:
Server Where Run |
MPXServerName Column Content |
|---|---|
Simplex server |
NULL (All connections are local/user connections) |
Multiplex coordinator |
|
Multiplex secondary |
|
p.put("RemotePWD",",,CON=myconnection");
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 SAP Sybase IQ side, if any. The command type reflects commands defined at the implementation level of the engine. These commands consist 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 SAP 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 that appears is the lowest cursor state of all the cursors; that is, the furthest from completion. Cursor state reflects internal SAP 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 SAP 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 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 SAP Sybase IQ temporary tables are reflected in TempTableSpaceKB. |
IQConnID |
The ten-digit connection ID included 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 SAP 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 SAP 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 SAP 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. |
LSName |
The logical server name of the connection. NULL if logical server context is unknown or not applicable. |
INCConnName |
The name of the underlying INC connection for a user connection. The data type for this column is varchar(255). If sp_iqconnection shows an INC connection name for a suspended user connection, that user connection has an associated INC connection that is also suspended. |
INCConnSuspended |
The value "Y" in this column indicates that the underlying INC connection for a user connection is in a suspended state. The value "N" indicates that the connection is not suspended. |
sp_iqconnection
ConnHandle Name Userid LastReqTime ReqType === =================== ====== ========================== ======= 1 'SQL_DBC_100525210' 'DBA' '2011-03-28 09:29:24.466' 'OPEN' IQCmdType LastIQCmdTime IQCursors LowestIQCursorState ==================== ============== ========= =================== 'IQUTILITYOPENCURSOR' 2011-03-28 09:29:24.0 0 'NONE' IQthreads TxnID ConnCreateTime TempTableSpaceKB TempWorkSpaceKB ========= ======= ===================== =============== =============== 0 3352568 2011-03-28 09:29:20.0 0 0 IQconnID satoiq_count iqtosa_count CommLink NodeAdd LastIdle MPXServerName ======== ============ ============ ======== ======= ======== ============ 34 43 2 'local' '' 244 (NULL) LSName INCConnName INCConnSuspended ========== =================== ================ Finance_LS 'IQ_MPX_SERVER_P54' 'Y'