sp_iqcontext procedure

Function

Tracks and displays, by connection, information about statements that are currently executing.

Syntax

sp_iqcontextconnhandle ]

Usage

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.

Permissions

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

Description

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:

The maximum size of statement text collected is the page size of the catalog store.

Table 7-10: sp_iqcontext columns

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.

Example

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 the grep command 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.