Monitor Cursor Statements

Adaptive Server version 15.7 monitors cursor statements based information from the monCachedStatement monitoring table.

For example, although you declare the new_cursor cursor (specified by “sq0267364184_1108036110ss”), sp_cursorinfo does not display its plan:

declare new_cursor cursor for select id from sysroles
go
sp_cursorinfo
go
Cursor name 'new_cursor' is declared on procedure '*sq0267364184_1108036110ss*'
The cursor is declared as NON-SCROLLABLE cursor.
The cursor id is 983044.
The cursor has been successfully opened 0 times.
The cursor will remain open when a transaction is committed or rolled back.
The number of rows returned for each FETCH is 1.
The cursor is updatable.
This cursor is declared on a stored procedure. It is presently using '860' 
bytes. However, the memory usage will increase when the cursor is opened because 
the query plan will be associated with the cursor at that time.

Adaptive Server compiles cursors when you open them.

Use the enable functionality group configuration parameter to enable and disable monitoring cursor statements.

See "Cursors: Accessing Data" in the Transact-SQL Users Guide.