SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-92
Examples of CREATE TABLE
•
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
(
A INT NO DEFAULT -- NOT NULL NOT DROPPABLE
, B INT DEFAULT NULL
, CONSTRAINT J1.SCH1.TAB1_264669593_1268 PRIMARY KEY
(A ASC) NOT DROPPABLE
, CONSTRAINT J1.SCH1.TAB1_535649593_1268 CHECK
(J1.SCH1.TAB1.A IS NOT NULL)
NOT DROPPABLE
)
LOCATION \CARNAG.$SARA.ZSDCL87P.DMP33T00
NAME CARNAG_SARA_ZSDCL87P_DMP33T00
ATTRIBUTES EXTENT (1024, 1024), MAXEXTENTS 16
PARTITION
(
ADD FIRST KEY (2)
LOCATION \CARNAG.$HIJO.ZSDCL87P.GHV33T00
NAME PARTITION1
EXTENT (1024, 1024) MAXEXTENTS 16
, ADD FIRST KEY (512)
LOCATION \CARNAG.$CHINA.ZSDCL87P.ZN133T00
NAME PARTITION2
EXTENT (1024, 1024) MAXEXTENTS 16
)
STORE BY (A ASC)