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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-94
Examples of CREATE TABLE
NAME CARNAG_CHINA_ZSDCL87P_S1645W00
EXTENT (1024, 1024) MAXEXTENTS 16
)
STORE BY (A ASC)
;
--- SQL operation complete.
>>
•
This example creates table mytable with hash partitions.
create table mytable
( col1 timestamp default current_timestamp not null
, col2 int not null
, col3 varchar (30)
, col4 smallint not null
, primary key (col4, col1) )
location $VOL1
hash partition by (col4)
( add location $VOL2
, add location $VOL3
, add location $VOL4)
attribute
extent (1024, 1024),
maxextents 16
;
create unique index mytable_idx1 on mytable(col2, col1)
LOCATION $vol1
hash partition by (col2)
( add location $VOL2
, add location $VOL3
, add location $VOL4
, add location $VOL5)
;
•
This example creates a table stored by primary key. These defaults are in effect:
POS_LOCATIONS is set to $VOL1, $VOL2, $VOL3 and POS_NUM_OF_PARTNS is
set to 3.
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,
PRIMARY KEY (ordernum, partnum) NOT DROPPABLE )
STORE BY PRIMARY KEY;
NonStop SQL/MX will place the primary partition on $VOL1, the second partition
on $VOL2, and the third partition on $VOL3.