Tracks and displays, by connection, information about statements that are currently executing.
The input parameter connhandle is equal to the Number connection property and is the ID number of the connection. For example, SELECT CONNECTION_PROPERTY(‘NUMBER’).
When called with an input parameter of a valid connhandle, sp_iqcontext returns the information only for that connection.
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
Distributed Query Processing (DQP) worker threads will not be shown if you do not have MULTIPLEX ADMIN or DBA authority for the server that is the leader node of the thread.
Match the statement text with the equivalent line in sp_iqconnection to get resource usage and transactional information about each connection
Match the statement text to the equivalent line in the SQL log created when the -zr server option is set to ALL or SQL
Use connection information to match the statement text in sp_iqcontext to the equivalent line in the .iqmsg file, which includes the query plan, when Sybase IQ can collect it
Match statement text to an IQ stack trace (stktrc-yyyymmdd-hhnnss_#.iq), if one is produced
Collate this information with an operating system stack trace that might be produced, such as pstack on Sun Solaris
The maximum size of statement text collected is the page size of the catalog store.
Column name |
Description |
|---|---|
ConnOrCursor |
CONNECTION, CURSOR, or DQP. |
ConnHandle |
The ID number of the connection or 0 for DQP. |
Name |
The name of the server (leader name). |
Userid |
The user ID for the connection, cursor, or DQP worker. |
numIQCursors |
If column 1 is CONNECTION, the number of cursors open on this connection. If column 1 is CURSOR, a number assigned sequentially to cursors associated with this connection. If column 1 is DQP, then 0. CONNECTION can also return a value of 0. |
IQthreads |
The number of IQ threads currently assigned to the connection. Some threads may be assigned but idle. For DQP threads, indicates the number of threads assigned to the DQP worker. |
TxnID |
The transaction ID of the current transaction. In the case of a worker thread, indicates the leader’s transaction ID. |
ConnOrCurCreateTime |
The time this connection, cursor, or DQP worker was created. |
IQConnID |
The connection ID displayed as part of all messages in the .iqmsg file. This is a monotonically increasing integer unique within a server session. |
IQGovernPriority |
A value that indicates the order in which the queries of a user are queued for execution. 1 indicates high priority, 2 (the default) medium priority, and 3 low priority. A value of -1 indicates that IQGovernPriority does not apply to the operation. Set the IQGovernPriority value with the database option IQGOVERN_PRIORITY. For DQP connections, this column displays No command. |
CmdLine |
First 4096 characters of the user command being executed. For DQP connections, this column displays No command. |
Attributes |
Unique ID for the query being distributed. |
The following example shows an excerpt from output when sp_iqcontext is issued with no parameter, producing results for all current connections. Column names are truncated due to space considerations.
ConnOrCu.. ConnHandle Name UserId numIQ.. IQthr.. TxnID Conn.. IQcon.. IQGov.. Cmd.. Attributes CONNECTION 2 sun7bar dbo 0 0 0 2010-08-04 15:15:40.0 15 No command NO COMMAND CONNECTION 7 sun7bar dbo 0 0 0 2010-08-04 15:16:00.0 32 No command NO COMMAND CONNECTION 10 sun7bar dbo 0 0 0 2010-08-04 15:16:21.0 46 No command NO COMMAND ... CONNECTION 229 sun7bar DBA 0 0 1250445 2010-08-05 18:28:16.0 50887 2 select server_name, inc_state, coordinator_failover from sp_iqmpxinfo() order by server_name ... DQP 0 dbsrv2873_node_c1DBA 0 1 10000 2010-08-05 18:28:16.0 no command no command Query ID: 12345; Condition: c1 > 100; DQP 0 dbsrv2873_node_c1DBA 0 1 10001 2010-08-05 18:28:16.0 no command no command Query ID: 12346; Node #12 Join (Hash);
The first line of output shows connection 2 (IQ connection ID 15). This connection is on server sun7bar, user dbo. This connection was not executing a command when sp_iqcontext was issued.
Connection 229 shows the user command being executed (the command contains less than the maximum 4096 characters the column can display). The 2 before the user command fragment indicates that this is a medium priority query.
The connection handle (2 for the first connection in this example) identifies results in the -zr log. The IQ connection ID (15 for the first connection in this example) identifies results in the .iqmsg file. On UNIX systems, you can use grep to locate all instances of the connection handle or connection ID, making it easy to correlate information from all sources.
The second-last line (TxnID 10000) shows a DQP worker thread. The worker connection is running two invariant conditions.
The last line (TxnID 10001) shows connection is running a hash join.