NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-79
CONTROL TABLE Directive
This option applies to both partitioned tables and partitioned indexes, but affects
only the main query in a SELECT statement without an INTO clause. (SQL always
stops processing and returns an error when a required partition is unavailable for a
subquery, a SELECT statement in the search condition of an UPDATE or DELETE
statement, a SELECT with an INTO clause, or any other DML or DDL statement.)
The default is STOP AT UNAVAILABLE PARTITION.
Any of the following conditions make a partition unavailable:
The volume is not available (error 66)
The file is bad (error 59)
No more opens are permitted on the volume (error 61)
A path or network error occurs (errors 200-255)
See Getting Partial Query Results (Local Autonomy)under Considerations—
CONTROL TABLE on page C-80 for an example.
SYNCDEPTH { 0 | 1 }
controls the method of writing to the disk process for nonaudited tables and views:
The default SYNCDEPTH for a table or protection view is 1.
(For audited tables, or views with audited underlying tables, SYNCDEPTH is
always 1; directives to change it are ignored.)
TABLELOCK { OFF | ON | ENABLE }
specifies whether to use table locks for subsequently compiled DML statements that
access the table or view:
ENABLE SQL decides whether to use table locks.
SKIP If a required partition is unavailable, issue warning 8239 (Partition was
skipped), open the next partition, and return the next row that satisfies the
search conditions of the query.
STOP AT If a required partition is unavailable, return an error and stop processing the
query.
0 prevents the disk process from sending checkpoint messages. Might slightly
improve performance but makes modifications less reliable because an error
during an update that modifies several rows halts processing of the statement.
1 enables retry of a message to the disk process, if necessary. Each time data is
written to a disk process, the primary disk process sends a checkpoint message
to the backup disk process with a description of the operation. This is the
preferred option for nonaudited tables, though it is not as safe as auditing.
OFF never use table locks
ON always use table locks