SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-92
Examples of CREATE TABLE
unit_price NUMERIC (8,2) NO DEFAULT NOT NULL,
qty_ordered NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL,
PRIMARY KEY (ordernum, partnum) NOT DROPPABLE )
STORE BY PRIMARY KEY;
•
This example creates a table stored by the key column list. The clustering key is
ordernum, partnum, SYSKEY.
CREATE TABLE SALES.ODETAIL
( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
unit_price NUMERIC (8,2) NO DEFAULT NOT NULL,
qty_ordered NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL)
STORE BY (ordernum, partnum);
•
This example creates a table stored by the SYSKEY. The clustering key is the
SYSKEY, type LARGEINT.
CREATE TABLE SALES.ODETAIL
( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
unit_price NUMERIC (8,2) NO DEFAULT NOT NULL,
qty_ordered NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL)
;
•
This example creates a table like the JOB table with the same constraints:
CREATE TABLE SAMDBCAT.PERSNL.JOB_CORPORATE
LIKE SAMDBCAT.PERSNL.JOB WITH CONSTRAINTS;
•
This example creates table tab1 with partitions named partition1 and
partition2. It then creates table tab2 like tab1 with partitions. tab2’s
partitions have different names than the partitions on tab1.
•
Create tab1:
>>Create table tab1
( a int not null primary key, b int)
range partition by (a)
(add first key 2 location $HIJO NAME partition1 ,
add first key 512 location $CHINA NAME partition2 )
attribute
extent (1024, 1024),
maxextents 16;
--- SQL operation complete.
•
Create tab2:
>>create table tab2 like tab1 with partitions;
--- SQL operation complete.
•
Perform SHOWDDL to display tab1’s properties:
>>showddl tab1;
CREATE TABLE J1.SCH1.TAB1