SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-36
Creating Table Partitions
Sample Table Definition
This example shows the definition for the PARTLOC table. This table contains data
maintained at three different sites: New York, Los Angeles, and San Francisco. The
table is partitioned by its primary key (which consists of LOC_CODE and PARTNUM)
into three partitions, one for each site:
CREATE TABLE \NY.$WHS1.INVENT.PARTLOC (
LOC_CODE CHARACTER (3) NO DEFAULT,
PARTNUM NUMERIC (4) UNSIGNED NO DEFAULT,
QTY_ON_HAND NUMERIC (7) NO DEFAULT,
PRIMARY KEY (LOC_CODE, PARTNUM
)
CATALOG \NY.$WHS1.INVENT
PARTITION (\SF.$WHS2.INVENT.PARTLOC
CATALOG \SF.$WHS2.INVENT
FIRST KEY ("G00", 0) ,
\LA.$WHS3.INVENT.PARTLOC
CATALOG \LA.$WHS3.INVENT
FIRST KEY ("P00", 0)
) ;
This example creates a partitioned table with a primary partition and two secondary
partitions. The example shows the use of DEFINE names to identify a table and
catalog.
-- DEFINEs were previously added during this SQLCI
-- session or inherited from the command interpreter.
>> INFO DEFINE =MCAT;
DEFINE NAME =MCAT
CLASS CATALOG
SUBVOL \SYS1.$VOL1.MFG
>> INFO DEFINE =ORDERS;
DEFINE NAME =ORDERS
CLASS MAP
FILE $VOL1.MFG.ORDERS
>> CREATE TABLE =ORDERS
+> (ORDERNUM DECIMAL (6) UNSIGNED NO DEFAULT NOT NULL,
+> ORDER_DATE DATETIME YEAR TO DAY NO DEFAULT NOT NULL,
+> DELIV_DATE DATETIME YEAR TO DAY NO DEFAULT NOT NULL,
+> SALESREP DECIMAL (4) UNSIGNED DEFAULT SYSTEM,
+> CUSTNUM DECIMAL (4) UNSIGNED NO DEFAULT NOT NULL,
+> PRIMARY KEY ORDERNUM)
+> PARTITION (=ORDERS2
+> CATALOG =MCAT
+> FIRST KEY 20000,
+> =ORDERS3
+> CATALOG =MCAT
+> FIRST KEY 40000)
+> EXTENT (1000,100)
+> BLOCKSIZE 2048