Examining SQL Cursor Status

Because SQR programs select and manipulate data from a SQL database, you should understand how SQR processes SQL statements and queries.

SQR programs can perform multiple SQL statements. Moreover, they can run the same SQL statement multiple times.

When a program runs, a pool of SQL statement handles, called cursors, is maintained. A cursor is a storage location for one SQL statement—for example, SELECT, INSERT, or UPDATE. Every SQL statement uses a cursor for processing. A cursor holds the context for the execution of a SQL statement.

The cursor pool contains 30 cursors, and you cannot change its size. When a SQL statement is rerun, its cursor can be immediately reused if it is still in the cursor pool. When an SQR program runs more than 30 different SQL statements, cursors in the pool are reassigned.

To examine how cursors are managed, use the -S command-line flag. This flag displays cursor status information at the end of a run.

The following information appears for each cursor:

Cursor #nn:
SQL = <SQL statement>
Compiles = nn
Executes = nn
Rows = nn

The listing also includes the number of compiles, which varies according to the database and the complexity of the query. With Oracle, for example, a simple query is compiled only once, while on other database platforms, a SQL statement may be compiled before it is first run and recompiled for the purpose of validation during the SQR compile phase. Therefore, you may see two compiles for a SQL statement. Later, when the SQL is rerun, if its cursor is found in the cursor pool, then it can proceed without recompiling.