Displays detailed information about cursors currently open on the server.
sp_iqcursorinfo [ cursor-name ] [, conn-handle ]
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
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.
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 |
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:
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 |
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()
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
