Help Center> Data Warehouse Service> Tool Guide> DSC - SQL Syntax Migration Tool> SQL Syntax Migration Reference> DB2 Syntax Migration> Table
View PDF

Table

Gauss keyword

If a keyword is used as a column name, quotes (") must be added, for example, "order".

DB2 Syntax

Syntac After Migration

 
CREATE TABLE tbl_bd2
(
ORDER NUMBER(10),
USER varchar2(30),
DATE VARCHAR(10
);
 
CREATE TABLE tbl_bd2
(
"ORDER" NUMBER(10),
"USER" varchar2(30), 
"DATE" VARCHAR(10
);

Data Type (I)

LONG VARCHAR should be changed to CLOB.

DB2 Syntax

Syntac After Migration

 
CREATE TABLE tbl_db2
(
ID VARCHAR(36),
NAME LONG VARCHAR
);
 
CREATE TABLE tbl_db2
(
ID VARCHAR(36),
NAME CLOB
);

LONG VARGRAPHIC.

DB2 Syntax

Syntac After Migration

 
CREATE TABLE tbl_db2
(
ID VARCHAR(36),
NAME LONG VARGRAPHIC
);
 
CREATE TABLE tbl_db2
(
ID VARCHAR(36),
NAME CLOB 
);

Foreign Key

Below attributes of Foreign key constraint should be commented:

  • ON UPDATE RESTRICT
  • ENFORCED
  • ENABLE QUERY OPTIMIZATION

DB2 Syntax

Syntac After Migration

 
ALTER TABLE "SCH"."TBL_DB2"
ADD CONSTRAINT "Const_Name" FOREIGN KEY("ID")
REFRENCES "SCH"."TBL_DB2_1"("ID")
ON DELETE CASCADE
ON UPDATE RESTRICT
ENFORCED
ENABLE QUERY OPTIMIZATION;
 
ALTER TABLE "SCH"."TBL_DB2"
ADD CONSTRAINT "Const_Name" FOREIGN KEY("ID")
REFRENCES "SCH"."TBL_DB2_1"("ID")
ON DELETE CASCADE
/*ON UPDATE RESTRICT
ENFORCED
ENABLE QUERY OPTIMIZATION*/;

Sequence

built-in auto-increment function.

DB2 Syntax

Syntac After Migration

 
CREATE TABLE tbl_db2
(
ID BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (  
      START WITH +1  
      INCREMENT BY +1  
      MINVALUE +1  
      MAXVALUE +9223372036854775807  
      NO CYCLE  
      CACHE 20  
      NO ORDER ) ,
NAME VARCHAR2(50),
ORDER VARCHAR2(100)
);

CREATE SEQUENCE mseq_tbl_db2_id
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE +9223372036854775807
NOCYCLE
CACHE 20
NOORDER;
CREATE TABLE tbl_db2
(
ID BIGINT NOT NULL DEFAULT mseq_tbl_db2_id.NEXTVAL,
NAME VARCHAR2(50),
"ORDER" VARCHAR2(100)
);

Tablespace

TABLESPACE for a table to be placed is specified with IN clause.

DB2 Syntax

Syntac After Migration

 
CREATE TABLE tbl_db2
(
ID number(20) NOT NULL DEFAULT IDENTITY.NEXTVAL,
NAME VARCHAR2(50)
)
IN tbs1 ; 
CREATE TABLE tbl_db2
(
ID number(20) NOT NULL DEFAULT IDENTITY.NEXTVAL,
NAME VARCHAR2(50)
)
TABLESPACE tbs1 ;

Default

WITH DEFAULT is specified to specified DEFAULT value.

DB2 Syntax

Syntac After Migration

 
CREATE TABLE tbl_db2
(
ID number(20) ,
NAME VARCHAR2(50),
STATUS CHAR(1) WITH DEFAULT '0'
);
 
CREATE TABLE tbl_db2
(
ID number(20) ,
NAME VARCHAR2(50),
STATUS CHAR(1) DEFAULT '0'
);

DEFAULT specified without value.

DB2 Syntax

Syntac After Migration

 
CREATE TABLE tbl_db2
(
ID number(20) ,
NAME VARCHAR2(50),
STATUS CHAR(1) WITH DEFAULT
);
 
CREATE TABLE tbl_db2
(
ID number(20) ,
NAME VARCHAR2(50),
STATUS CHAR(1) 
);

Data Type (II)

CLOB(1048576)

DB2 Syntax

Syntac After Migration

 
CREATE TABLE tbl_db2
(
ID number(20) ,
NAME VARCHAR2(50),
REMARKS CLOB(1048576));
 
CREATE TABLE tbl_db2
(
ID number(20) ,
NAME VARCHAR2(50),
REMARKS CLOB
);

BLOB(2048000)

DB2 Syntax

Syntac After Migration

 
CREATE TABLE tbl_db2
(
ID number(20) ,
NAME VARCHAR2(50),
REMARKS BLOB(2048000)
);
 
CREATE TABLE tbl_db2
(
ID number(20) ,
NAME VARCHAR2(50),
REMARKS BLOB
);

LOB Options

LOGGED/UNLOGGED

DB2 Syntax

Syntac After Migration

 
CREATE TABLE tbl_db2
(
"ID" number(20) ,
"NAME" VARCHAR2(50),
"REMARKS" BLOB LOGGED
);
 
CREATE TABLE tbl_db2
(
"ID" number(20) ,
"NAME" VARCHAR2(50),
"REMARKS" BLOB /*LOGGED */ 
);

COMPACT/NOT COMPACT

DB2 Syntax

Syntac After Migration

 
CREATE TABLE tbl_db2
(
"ID" number(20) ,
"NAME" VARCHAR2(50),
"REMARKS" BLOB LOGGED NOT COMPACT
);
 
CREATE TABLE tbl_db2
(
"ID" number(20) ,
"NAME" VARCHAR2(50),
"REMARKS" BLOB /*LOGGED */ /* NOT COMPACT*/
);

Organize By

Organize By

DB2 Syntax

Syntac After Migration

 
CREATE TABLE tbl_db2
(
"ID" number(20) ,
"NAME" VARCHAR2(50),
"REMARKS" BLOB
)
IN tbs1
ORGANIZE BY ("ID","NAME");
 
CREATE TABLE tbl_db2
(
"ID" number(20) ,
"NAME" VARCHAR2(50),
"REMARKS" BLOB
)
TABLESPACE tbs1
/*ORGANIZE BY ("ID","NAME")*/;
Parent topic: DB2 Syntax Migration