SQL/MP Programming Manual for COBOL85

Data Retrieval and Modification
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
4-14
Cursor Position
Cursor Position
The cursor position is similar to the record position in a sequential file. The SQL
statements that affect the cursor position in a program are:
Cursor Stability
Cursor stability guarantees that a row at the current cursor position cannot be modified
by another program. For SQL/MP to guarantee cursor stability, declare the cursor with
the FOR UPDATE clause or specify the STABLE ACCESS option. In some cases, a
program might be accessing a copy of a row instead of the actual row. For example, a
program might be accessing a copy of the row if the associated SELECT statement
defining the cursor requires that the system perform any of these operations:
•
Ordering the rows by a column
•
Removing duplicate rows
•
Performing other operations that require the selected table to be copied into a
result table before it is used by a program
If your program is accessing a copy of a row instead of the actual row, the cursor
points to a copy of the data, and the data is concurrently available to other programs.
Accessing a copy of the data, however, never occurs if the cursor is declared with the
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
SQL Statement Cursor Position or Action
OPEN Positions the cursor before the first row.
FETCH Positions the cursor at the retrieved row (or the current position).
DELETE Positions the cursor between rows. For example, if the current row is
deleted, the cursor is positioned either between rows or before the next
row and after the preceding row.
SELECT Determines the order in which the rows are returned. To specify an
order, include an ORDER BY clause; otherwise, the order is undefined.
CLOSE Causes no position; release the result table established by the cursor.