SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-127
Considerations for CREATE TABLE
The POS_RAISE_ERROR attribute controls how errors are displayed. For values and
syntax of these attributes, see Partition Management on page 10-63.
To enable POS, set the POS_NUM_OF_PARTNS attribute to a value greater than 1.
To activate POS, ensure that the following conditions are true:
•
The POS feature is enabled during execution of a CREATE TABLE statement.
•
The application that issues the CREATE TABLE DDL statement is an MXCS/JDBC
session or an MXCI session.
•
The CREATE TABLE statement does not specify an add location using the
partitioning syntax.
•
The CREATE TABLE statement specifies either the PRIMARY KEY or the STORE
BY clause.
If you specify the LOCATION clause for the primary partition, the partition resides on
the volume specified in that clause and not in the location specified in
POS_LOCATIONS. If the LOCATION clause is not specified, the primary partition
location will be picked at random among those specified in POS_LOCATIONS. It will
not be the first location specified in POS_LOCATIONS.
If you do not specify the LOCATION clause and if you set POS_LOCATIONS, the
primary partition resides on the first volume specified in POS_LOCATIONS. The other
partitions reside on the volumes you specify in POS_LOCATIONS in a round-robin
fashion.
If the LOCATION clause is not specified in the CREATE TABLE statement and the
POS_LOCATIONS CQD is empty, NonStop SQL/MX randomly selects the location(s)
from the full set of audited volumes.
These examples show how partitions are created automatically using combinations of
attribute values:
1. Specify POS_NUM_OF_PARTNS as 3 and list three locations in
POS_LOCATIONS: $VOL1, $VOL2, and $VOL3.
NonStop SQL/MX will place the primary partition on $VOL1, the second partition
on $VOL2, and the third partition on $VOL3.
2. Specify POS_NUM_OF_PARTNS as 5 and list three locations in
POS_LOCATIONS: $VOL1, $VOL2, and $VOL3.
NonStop SQL/MX will place the primary partition on $VOL1, the second partition
on $VOL2, the third partition on $VOL3, the fourth on $VOL1, and the fifth on
$VOL2.
3. Specify POS_NUM_OF_PARTITIONS as 4, and list three locations in
POS_LOCATIONS: $VOL1, $VOL2, and $VOL3. In addition, include a LOCATION
clause in the CREATE statement that specifies $DATA1.