sp_iqconnection procedure

Function

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.

Syntax

sp_iqconnectionconnhandle ]

Usage

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.

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission in order to run the stored procedure.

Description

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:

Table A-1: MPXServerName column values

Server where run

MPXServerName column content

Simplex server

NULL (All connections are local/user connections.)

Multiplex coordinator

  • NULL for local/user connections

  • Contains value of secondary node’s server name (source of connection) for every INC connection (either on-demand or dedicated heartbeat connection.

Multiplex secondary

  • NULL for local/user connections

  • Contains value of coordinator’s server name (source of connection).

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.

Table A-2: sp_iqconnection columns

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.

Example

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