Resources required at each stage

Cursors use memory and require locks on tables, data pages, and index pages. When you open a cursor, memory is allocated to the cursor and to store the query plan that is generated. While the cursor is open, Adaptive Server holds intent table locks and sometimes row or page locks. Figure 28-3 shows the duration of locks during cursor operations.

Figure 28-3: Resource use by cursor statement

The memory resource descriptions in Figure 28-3 and Table 28-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 28-1: Locks and memory use for isql and Client-Library client cursors

Cursor command

Resource use

declare cursor

When you declare a cursor, Adaptive Server uses only enough memory to store the query text.

open

When you open a cursor, 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 System Administration Guide for more information.

fetch

When you execute a fetch, 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 you close a cursor, Adaptive Server releases the locks and some of the memory allocation. You can open the cursor again, if necessary.

deallocate cursor

When you deallocate a cursor, Adaptive Server releases the rest of the memory resources used by the cursor. To reuse the cursor, you must declare it again.