Resources required at each stage

The memory resource descriptions in Figure 8-3 and Table 8-1 refer to ad hoc cursors for queries sent by isql or Client-Library™. For other kinds of cursors, the locks are the same, but the memory allocation and deallocation differ somewhat depending on the type of cursor being used, as described in “Memory use and execute cursors”.

**Table 8-1: Locks and memory use for isql and Client-Library client cursors**
Cursor command	Resource use
declare cursor	When a cursor is declared, Adaptive Server uses only enough memory to store the query text.
open	When a cursor is opened, Adaptive Server allocates memory to the cursor and to store the query plan that is generated. The server optimizes the query, traverses indexes, and sets up memory variables. The server does not access rows yet, unless it needs to build worktables. However, it does set up the required table-level locks (intent locks). Row and page locking behavior depends on the isolation level, server configuration, and query type. See the Performance and Tuning Series: Locking and Concurrency Control for more information.
fetch	When a fetch is executed, Adaptive Server gets the row(s) required and reads specified values into the cursor variables or sends the row to the client. If the cursor needs to hold lock on rows or pages, the locks are held until a fetch moves the cursor off the row or page or until the cursor is closed. The lock is either a shared or an update lock, depending on how the cursor is written.
close	When a cursor is closed, Adaptive Server releases the locks and some of the memory allocation. You can open the cursor again, if necessary.
deallocate cursor	When a cursor is deallocated, Adaptive Server releases the rest of the memory resources used by the cursor. To reuse the cursor, declare it again.