SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-89
Considerations—CONTROL TABLE
underlying table) within the same process flushes the buffer and interrupts the
sequential operation.

For INSERT and UPDATE operations, any errors the disk process encounters
while flushing the buffer are returned to the statement that triggers the buffer
flush, rather than to the INSERT or UPDATE statement. For more information,
see the discussion on page C-90.

For sequential read operations that use STABLE access, virtual sequential block
buffering (VSBB) can reduce concurrency for other applications that need
exclusive locks on the rows in a block. (STABLE access normally provides greater
concurrency than REPEATABLE access for sequential read operations, but this is
not true with VSBB because the disk process does not release locks on any rows
within a virtual block until the cursor moves to the next block.)
To disable VSBB for read operations, use this directive:
CONTROL TABLE * SEQUENTIAL READ OFF
For inserts into a key-sequenced table that uses a SYSKEY column or a timestamp
as the primary key, VSBB is the usual method for insert operations. If concurrent
applications are inserting into the table, a high percentage of lock waits and
timeouts might occur. (For sequential insert operations, the disk process acquires a
range protector lock on the row that follows the last row inserted. If the last row
inserted is at the end of the file, the range protector lock is placed at the end of the
file, preventing other servers from inserting rows at the end of the table or view.)
To disable VSBB for INSERT operations, use this directive:
CONTROL TABLE * SEQUENTIAL INSERT OFF

SQL buffers INSERT or UPDATE operations on nonaudited tables only if the
SEQUENTIAL option is ON and SYNCDEPTH is 0. (You can specify both these
options with CONTROL TABLE.) For INSERT operations, you must also set the
TABLELOCK option to ON (again, with CONTROL TABLE), or use a LOCK TABLE
statement that specifies IN EXCLUSIVE MODE. For an INSERT operation, specify
FOR REPEATABLE ACCESS also.
To ensure data integrity you must use the FREE RESOURCES statement to flush
buffers for nonaudited tables before you exit SQLCI or the host program. For host
programs, any flush errors are returned to the SQLCA.