Help Center> Data Warehouse Service (DWS)> Tool Guide (ME-Abu Dhabi Region) > DSC: SQL Syntax Migration Tool> DB2 Syntax Migration> Tables
Updated on 2023-03-17 GMT+08:00
View PDF

Tables

GaussDB Keyword

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

DB2 Syntax

Syntax 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

Syntax 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

Syntax 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

    Syntax 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

Syntax 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

Syntax 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

Syntax 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

Syntax 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

Syntax 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

Syntax 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

Syntax 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

Syntax 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

Syntax 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