SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-164
Examples—CREATE TABLE
\SYS1.$VOL3.SALES.ODETAIL
CATALOG \SYS1.$VOL1.SALES
EXTENT (16368,64)
MAXEXTENTS 919
FIRST KEY 040000
,
... --indicates 20 more
\SYS5.$VOL23.SALES.ODETAIL --partition
CATALOG \SYS5.VOL1.SALES --specifications
EXTENT (16368,64)
MAXEXTENTS 919 FIRST KEY 980000 )
LOCKLENGTH 6
EXTENT (16368,64)
MAXEXTENTS 919
NOPURGEUNTIL OCT 31 2003, 23:59
NO AUDIT;
Some of the attributes specified apply to the entire table and some only to the
primary partition. NOPURGEUNTIL, NO AUDIT, and LOCKLENGTH apply to all
partitions. The example specifies NO AUDIT for an initial load operation, after
which the attribute can be changed to AUDIT. The EXTENT and MAXEXTENTS
attributes apply to the primary partition.

This example creates a partitioned, key-sequenced table with a clustering key:
CREATE TABLE HD.HISTORY (
REGION_ID INTEGER,
SYSTEM_ID SMALLINT UNSIGNED,
CPU SMALLINT UNSIGNED,
PIN SMALLINT UNSIGNED,
PROGRAM_FILE_NAME VARCHAR (34),
AGE LARGEINT
)
CATALOG HC
CLUSTERING KEY (REGION_ID DESC, SYSTEM_ID)
PARTITION (
\SA.$VOL1.HD.HISTORY FIRST KEY
( 1000 -- region_id
-- system_id has low value
)
CATALOG \SA.$VOL1.HC,
\NA.$VM.HD.HISTORY FIRST KEY
( 500 -- region_id
-- system_id has low value
)
CATALOG \NA.$VM.HC
);

This example creates a table with the primary key of the TIMESTAMP data type
and partitions the table on a TIMESTAMP value. Another column has the TIME
data type.
CREATE TABLE $VOL1.SUBV1.PARTTIME
( A TIMESTAMP DEFAULT CURRENT NOT NULL
, B TIME DEFAULT TIME "11:00:00" NOT NULL