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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-91
Examples of CREATE TABLE
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 forth 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
SQL/MX cannot partition the table. If you attempt to use POS with such a table, you
will not receive an error. POS creates a nonpartitioned table in the same way that
NonStop SQL/MX creates a nonpartitioned table without the LOCATION clause as part
of the CREATE TABLE statement. The location of this table is not based on
POS_LOCATIONS or automatic disk location.
Partitioning Columns
Use the PARTITION BY clause to decouple the partitioning key from the clustering key.
Without the PARTITION BY clause, the partitioning columns of the table are same as
the clustering key columns. When you use the POS feature, you cannot choose
partitioning columns for automatically created partitions. The partitioning columns of
automated partitioned tables created through MXCS/JDBC sessions using POS are
the same as the clustering key columns of the table.
SQL/MX Extensions to CREATE TABLE
This statement is supported for compliance with ANSI SQL:1999 Entry Level. SQL/MX
extensions to the CREATE TABLE statement are [NOT] DROPPABLE, ASCENDING,
DESCENDING, STORE BY, LOCATION, PARTITION, ATTRIBUTE, and LIKE clauses.
Examples of CREATE TABLE
•
This example creates a table stored by primary key. The clustering key is the
primary key.
CREATE TABLE SALES.ODETAIL
( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,