SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-90
Considerations for CREATE TABLE
•
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.
NonStop SQL/MX will place the primary partition on $VOL1, the second partition
on $VOL2, and the third partition on $VOL3.
2. Suppose that you have specified POS_NUM_OF_PARTNS as 5, and you have
listed 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. Suppose you have specified POS_NUM_OF_PARTITIONS as 4, and you have
listed three locations in POS_LOCATIONS, $VOL1, $VOL2, and $VOL3. In
addition, you have included a LOCATION clause in the CREATE statement that
specifies $DATA1.
NonStop SQL/MX will place the primary partition on $DATA1, the second partition
on $VOL1, the third partition on $VOL2, and the fourth partition on $VOL3.
Creating a Table Without STORE BY Clause or Primary Key
NonStop SQL/MX bases table partitioning on clustering key columns, specified by the
STORE BY clause or, if there is no STORE BY clause, the primary key columns. If you
do not specify the STORE BY or PRIMARY KEY columns on a table, NonStop