SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-14
Creating and Using Keys
NonStop SQL/MX generates a SYSKEY column and appends that to the clustering
key.
CREATE TABLE SALES.ODETAIL
( ORDERITEM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
ORDERNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
PARTNUM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
UNIT_PRICE NUMERIC (8,2) UNSIGNED NO DEFAULT NOT NULL,
QTY_ORDERED NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL )
STORE BY (ordernum, partnum);
Using the SYSKEY
A SYSKEY (system-defined clustering key) is a clustering or storage key defined by
NonStop SQL/MX rather than by the user. Tables in key-sequenced files with no
user-defined clustering keys must have their clustering keys defined by NonStop
SQL/MX and stored in a column named SYSKEY. The SYSKEY column’s character
type is LARGEINT SIGNED. The
SYSKEY can be appended to the clustering key, at the end of the column list as the
last column of the clustering key, to ensure its uniqueness.
When you insert a record in an SQL/MX table with a SYSKEY column, the
key-sequenced file system automatically generates a value for the SYSKEY column.
You cannot supply the value.
For more information about SYSKEY, see the SQL/MX Reference Manual.
Creating and Using a Partitioning Key
The partitioning key is made up of:
•
The columns you specify in the PARTITION BY clause of CREATE TABLE and
CREATE INDEX
•
The clustering key (omitting SYSKEY) if no PARTITION BY clause was specified
The partition key determines how data is distributed into partitions that are associated
with table or index objects. For a range-partitioned table or index, the partition key uses
the FIRST KEY option to specify the beginning of the range for the table or index
partition. The FIRST KEY clause specifies the lowest values in the partition for
columns stored in ascending order and the highest values in the partition for columns
stored in descending order. These column values constitute the partitioning key.
You specify the first value allowed in the associated partition for that column of the
partitioning key as a literal. If there are more storage key columns than literal items, the
first key value for each remaining key column is the lowest or highest value for the data
type of the column (the lowest value for an ascending column and the highest value for
a descending column).