sp_iqcursorinfo procedure

Function

Displays detailed information about cursors currently open on the server.

Syntax

sp_iqcursorinfocursor-name ] [, conn-handle ]

Permissions

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

Usage

cursor-name The name of the cursor. If only this parameter is specified, sp_iqcursorinfo returns information about all cursors that have the specified name in all connections.

conn-handle An integer representing the connection ID. If only this parameter is specified, sp_iqcursorinfo returns information about all cursors in the specified connection.

The sp_iqcursorinfo procedure can be invoked without any parameters. If no parameters are specified, sp_iqcursorinfo returns information about all cursors currently open on the server. If both parameters are specified, sp_iqcursorinfo reports information about all of the cursors that have the specified name and are in the specified connection.

If you do not specify the first parameter, but specify the second parameter, you must substitute NULL for the omitted parameter. For example, sp_iqcursorinfo NULL, 1.

Table 7-11: sp_iqcursorinfo usage examples

Syntax

Output

sp_iqcursorinfo

Displays information about all cursors currently open on the server

sp_iqcursorinfo ‘cursor1’

Displays information about the all cursors named cursor1 in all connections

sp_iqcursorinfo NULL, 3

Displays information about all cursors in connection 3

sp_iqcursorinfo ‘cursor2’, 4

Displays information about all the cursors named cursor2 in connection 4

Description

The sp_iqcursorinfo stored procedure displays detailed information about cursors currently open on the server. The sp_iqcursorinfo procedure enables database administrators to monitor cursor status using just one stored procedure and view statistics such as how many rows have been updated, deleted, and inserted.

If you specify one or more parameters, the result is filtered by the specified parameters. For example, if cursor-name is specified, only information about the specified cursor is displayed. If conn-handle is specified, sp_iqcursorinfo returns information only about cursors in the specified connection. If no parameters are specified, sp_iqcursorinfo displays information about all cursors currently open on the server.

The sp_iqcursorinfo procedure returns information in the following columns:

Table 7-12: sp_iqcursorinfo columns

Column name

Description

Name

The name of the cursor

ConnHandle

The ID number of the connection

IsUpd

Y: the cursor is updatable; N otherwise

IsHold

Y: the cursor is a hold cursor; N otherwise

IQConnID

The ten digit connection ID displayed as part of all messages in the .iqmsg file. This number is a monotonically increasing integer unique within a server session.

UserID

User ID (or user name) for the user who created and ran the cursor

CreateTime

The time of cursor creation

CurrentRow

The current position of the cursor in the result set

NumFetch

The number of times the cursor fetches a row. The same row can be fetched more than once.

NumUpdate

The number of times the cursor updates a row, if the cursor is updatable. The same row can be updated more than once.

NumDelete

The number of times the cursor deletes a row, if the cursor is updatable.

NumInsert

The number of times the cursor inserts a row, if the cursor is updatable.

RWTabOwner

The owner of the table that is opened in RW mode by the cursor.

RWTabName

The name of the table that is opened in RW mode by the cursor.

CmdLine

The first 4096 characters of the command the user executed

Example

Display information about all cursors currently open on the server:

sp_iqcursorinfo

Name      ConnHandle      IsUpd      IsHold      IQConnID      UserID
---------------------------------------------------------------------
crsr1              1          Y           N           118         DBA
crsr2              3          N           N           118         DBA

CreateTime               CurrentRow      NumFetch      NumUpdate
----------------------------------------------------------------
2009-06-26 15:24:36.000          19      100000000     200000000
2009-06-26 15:38:38.000       20000      200000000

NumDelete     NumInsert      RWTabOwner      RWTabName        CmdLine
----------------------------------------------------------------------
 20000000    3000000000             DBA          test1    call proc1()
                                                          call proc2()

See also

Reference: Statements and Options: DECLARE CURSOR statement [ESQL] [SP], DECLARE CURSOR statement [T-SQL], UPDATE (positioned) statement [ESQL] [SP] and DELETE (positioned) statement [ESQL] [SP], FORCE_NO_SCROLL_CURSORS option, and FORCE_UPDATABLE_CURSORS option

“Using cursors in procedures” in Chapter 1, “Using Procedures and Batches,” in the System Administration Guide: Volume 2

“Cursors in transactions” in Chapter 10, “Transactions and Versioning” in the System Administration Guide: Volume 1