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

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—544536-007
7-15
Creating and Using Keys
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 partitioning 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).
When you create range partitioned tables, the set of columns you specify for the
partitioning key can be identical to or a subset of the clustering key columns (excluding
the SYSKEY, if present), and you can specify the columns in any order.
When you create hash partitioned tables, the subset of columns you specify for the
partitioning key can be the same as or less than the clustering key columns (excluding
the SYSKEY, if present), and you can specify the columns in any order.
For more information about the partitioning key, see Creating Indexes for SQL/MX
Tables on page 7-33 and the SQL/MX Reference Manual.