SQL/MP Programming Manual for COBOL

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
4-15
Virtual Sequential Block Buffering (VSBB)
FOR UPDATE clause. In this case, your cursor points to the actual data and has cursor
stability.
Virtual Sequential Block Buffering (VSBB)
The SQL/MP optimizer often uses Virtual Sequential Block Buffering (VSBB) as an
access path strategy. Conflicting UPDATE, DELETE, or INSERT statements can
invalidate a cursor’s buffering for a table. Each invalidation forces the next FETCH
statement to send a message to the disk process to retrieve a new buffer, which can
substantially degrade a program’s performance. These statements invalidate the buffer
for cursor operations:

An INSERT statement on the same table by the current process

A stand-alone UPDATE or DELETE statement on the same table (directly or
through a view) by the same process

An UPDATE...WHERE CURRENT or DELETE...WHERE CURRENT statement
using a different cursor to access the same table (directly or through a view) by the
same process
For example, a loop containing both a FETCH statement and a stand-alone UPDATE
or DELETE statement on the same table invalidates the cursor’s buffer on every loop
iteration. You can minimize or eliminate this problem by following these guidelines:

Do not use INSERT statements within a cursor operation.

Use the UPDATE...WHERE CURRENT or DELETE...WHERE CURRENT
statement for a cursor rather than a stand-alone UPDATE or DELETE statement.

Do not open multiple cursors on a table if any of the cursors are used to update
that table.
DECLARE CURSOR Statement
The DECLARE CURSOR statement names and defines a cursor and associates the
cursor with a SELECT statement that specifies the rows to retrieve. A COBOL program
requires no special authorization to execute a DECLARE CURSOR statement.
Follow these guidelines when you use a DECLARE CURSOR statement:

The cursor name specified in the DECLARE CURSOR statement is an SQL
identifier and is not case-sensitive. For example, SQL/MP considers Cur, cur, CUR,
and CuR as equivalent names.

Declare all host variables you use in the associated SELECT statement before the
DECLARE CURSOR statement. Host variables must also be within the same
scope as all SQL statements that refer to them.

Place the DECLARE CURSOR statement in listing order before the other SQL
statements, including the OPEN, FETCH, INSERT, DELETE, UPDATE, and