SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-90
Considerations for CREATE TABLE
Creating Partitions Automatically
NonStop SQL/MX uses Partition Overlay Specification (POS) so that MXCS and
JDBC/MX users can automatically create hash-partitioned tables with the CREATE
TABLE statement. NonStop SQL/MX does not support automatic creation of range-
partitioned tables.
Applications can control whether POS is enabled, the number of partitions, and the
physical location of the partitions.
Two CONTROL QUERY DEFAULT attributes determine the number and physical
location of the partitions: POS_LOCATIONS and POS_NUM_OF_PARTNS.
POS_RAISE_ERROR controls how errors are displayed. For values and syntax of
these defaults, see Partition Management on page 10-50.
You enable POS by setting the CONTROL QUERY DEFAULT attribute
POS_NUM_OF_PARTNS to a numeric value greater than 1.
To activate POS, these conditions must be 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 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, that partition will reside
on the volume specified in that clause, not in the location specified in
POS_LOCATIONS. If you do not specify the LOCATION clause for the primary
partition, NonStop SQL/MX chooses the locations for the first (primary) through last
partitions.
If you do not specify the LOCATION clause and if you set POS_LOCATIONS, the
primary partition will reside on the first volume specified in POS_LOCATIONS. The
other partitions will reside on the volumes you specify in POS_LOCATIONS in a round
robin fashion.
If you do not specify the LOCATION clause and if you do not set POS_LOCATIONS,
NonStop SQL/MX chooses the locations for the first (primary) through last partitions.
NonStop SQL/MX does not use the default volume names in the =_DEFAULTS define
for the primary partition.
These examples show automatic partition creation, with different combinations of
attribute values:
1. Suppose that you have specified POS_NUM_OF_PARTNS as 3, and you have
listed three locations in POS_LOCATIONS, $VOL1, $VOL2, and $VOL3.