筆記 DB2 建 table / index syntax

by

[2010/1/16 上午 10:25:03] _RUTEN 說 : 

CREATE TABLE DB2INST1.TBL_CODE 
( 
  ID BIGINT  NOT NULL  GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ) , 
  XDATE VARCHAR (8)  NOT NULL  WITH DEFAULT ‘000000′ , 
  XHOUR VARCHAR (2)  NOT NULL  WITH DEFAULT ‘00′ , 
  TAG1 VARCHAR (200)  NOT NULL  WITH DEFAULT ” , 
  TAG2 VARCHAR (300)  NOT NULL  WITH DEFAULT ” , 
  VAL BIGINT  NOT NULL  WITH DEFAULT 0  , 
  CONSTRAINT CC1263604830858 
  PRIMARY KEY ( ID)  
) IN USERSPACE1 INDEX IN NDXSPACE1 ;

CREATE  INDEX DB2INST1.NDX_XDATE ON DB2INST1.TBL_CODE (XDATE DESC)  PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS  PAGE SPLIT SYMMETRIC COLLECT  SAMPLED DETAILED  STATISTICS ;
CREATE  INDEX DB2INST1.NDX_XHOUR ON DB2INST1.TBL_CODE (XHOUR DESC)  PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS  PAGE SPLIT SYMMETRIC COLLECT  SAMPLED DETAILED  STATISTICS ;
CREATE  INDEX DB2INST1.NDX_TAG1 ON DB2INST1.TBL_CODE (TAG1 ASC)  PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS  PAGE SPLIT SYMMETRIC COLLECT  SAMPLED DETAILED  STATISTICS ;
CREATE  INDEX DB2INST1.NDX_TAG2 ON DB2INST1.TBL_CODE (TAG2 ASC)  PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS  PAGE SPLIT SYMMETRIC COLLECT  SAMPLED DETAILED  STATISTICS ;

你的第一個table建完了, 第二個之後可以直接下同樣的指令, 非unique/primary的index要另外建, 如上四行