SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-12
Determining the Organization of the Physical File
+> BLOCKSIZE 4096
+> MAXEXTENTS 24
+> ORGANIZATION KEY SEQUENCED
+> SECURE "GGOO";
--- SQL operation complete.
Creating a Key-Sequenced Table With a Clustering Key
If you want the rows in a key-sequenced table ordered by a column or combination of
columns whose values do not uniquely identify rows, you can specify these columns as
a clustering key in the CLUSTERING KEY clause of the CREATE TABLE statement. A
clustering key is part of the primary key; SQL/MP adds a system-defined SYSKEY
column to the clustering key to make the primary-key value in each row unique. The
total key length, including the SYSKEY column, cannot exceed 255 bytes.
This example shows a table created with a CLUSTERING KEY definition that consists
of the ORDERITEM and ORDERNUM columns. Internally, the actual primary key used
will consist of the ORDERITEM, ORDERNUM, and SYSKEY columns.
>> CREATE TABLE ODETAIL
+> (ORDERITEM DECIMAL (6) UNSIGNED NO DEFAULT NOT NULL,
+> ORDERNUM NUMERIC (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)
+> CLUSTERING KEY (ORDERITEM,ORDERNUM)
+> EXTENT (100,100)
+> BLOCKSIZE 4096
+> MAXEXTENTS 64
+> ORGANIZATION KEY SEQUENCED
+> SECURE "GGOO";
--- SQL operation complete.
If you do not specify the organization in the CREATE TABLE statement, the
organization defaults to key sequenced.
Creating a Key-Sequenced Table With Dependent Objects
Example 5-1 on page 5-13 creates a table and a set of objects that depends on the
table. The example uses DEFINE names in SQL statements. The INFO DEFINE
command displays the DEFINEs. You would usually enter this set of commands into an
EDIT file you could use as an OBEY command file within SQLCI.