Tracks and displays, by connection, information about statements that are currently executing.
sp_iqcontext [ connhandle ]
The input parameter connhandle is equal to the Number connection property and is the ID number of the connection.
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.
sp_iqcontext lets the DBA determine what statements are running on the system at any given moment, and identify the user and connection that issued the statement. With this information, you can use this utility to:
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 or CURSOR. |
ConnHandle |
The ID number of the connection. |
Name |
The name of the server. |
Userid |
The user ID for the connection or cursor. |
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. |
IQthreads |
The number of IQ threads currently assigned to the connection. Some threads may be assigned but idle. |
TxnID |
The transaction ID of the current transaction. |
ConnOrCurCreateTime |
The time this connection or cursor was created. |
IQConnID |
The 10-digit 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. See “Setting query priority” in Chapter 3, “Optimizing Queries and Deletions” of the Performance and Tuning Guide. |
CmdLine |
First 4096 characters of the user command being executed. |
The following example shows an excerpt from output when sp_iqcontext is issued
with no parameter, producing results for all current connections.
CONNECTION 701773517 dba7 DBA 6 1 1324 2009-06-04 09:24:17.000 4 NO COMMAND CURSOR 701773517 dba7 DBA 1 0 1324 2009-06-04 09:24:46.000 4 2 select * from foo1 CURSOR 701773517 dba7 DBA 2 0 1324 2009-06-04 09:24:47.000 4 2 select a from foo1 ... CURSOR 701773517 dba7 DBA 6 0 1324 2009-06-04 09:24:47.000 4 2 select e from foo1 CONNECTION 1271624950 dba7 DBA 0 12 1377 2009-06-04 09:24:12.000 3 2 sp_iqcheckdb CONNECTION 1841476383 dba7 DBA 10 1 1337 2009-06-04 09:24:19.000 5 2 call sp_iqcontext() CURSOR 1841476383 dba7 DBA 1 0 1337 2009-06-04 09:24:47.000 5 2 select * from foo ... CURSOR 1841476383 dba7 DBA 10 0 1337 2009-06-04 09:24:48.000 5 2 select i from foo
The first line of output shows connection 701773517 (IQ connection ID 4). This connection is on server dba7, user DBA. It has six active cursors and one IQ thread, and was created from transaction 1324. This connection was not executing a command when sp_iqcontext was issued. The next six lines of output list cursors in use by this connection (only three are shown here.)
Two connections are running stored procedures. Connection 1271624950 is running sp_iqcheckdb directly from dbisql, has no active cursors but is using 12 IQ threads. Connection 1841476383 has called sp_iqcontext as a procedure, is using only 1 IQ thread, and has 10 active cursors (only the first and last are shown here.) In both cases, the name of the stored procedure appears but not the line of code executing within it.
The connection handle (701773517 for the first connection in this example) identifies results in the -zr log. The IQ connection ID (4 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 2 before the user command fragment indicates that this is a medium priority query.
