The generated DDL is shown on the Preview DDL page in the Paste Database Objects wizard. In addition to the table definition, the generated DDL can contain SQL statements and SQL clauses for options that you select in the wizard.
Table 1 shows an example of the DDL that is generated for copying a table from an Oracle Database 11g source database to a DB2® Version 9.7 for Linux, UNIX, and Windows target database.
Oracle Database 11g table definition | Generated DDL for DB2 Version 9.7 for Linux, UNIX, and Windows database |
---|---|
CREATE TABLE scott.my_Oracle_datatypes (col1 NCHAR(3) NOT NULL , col2 NCHAR(129) , col3 NVARCHAR2(3) , col4 CHAR(1) PRIMARY KEY, col5 CHAR(256) , col6 VARCHAR2(3) , col7 ROWID , col8 UROWID , col9 DATE , col10 BFILE , col11 BLOB , col12 LONG , col13 CLOB , col14 NCLOB , col15 RAW(1) , col16 NUMBER(1,0) , col17 NUMBER(32,0) , col18 NUMBER(1,1) , col19 NUMBER(32,32) , col20 NUMBER , col21 DECIMAL(5,3) , col22 INTEGER , col23 SMALLINT , col24 FLOAT , col25 DOUBLE PRECISION , col26 REAL , col27 TIMESTAMP(3) ); CREATE UNIQUE INDEX my_Oracle_idx1 ON scott.my_Oracle_datatypes(col20 ASC,col22 DESC); |
CREATE TABLE DB2ADMIN.MY_ORACLE_DATATYPES)( COL1 GRAPHIC(3) NOT NULL, COL2 VARGRAPHIC(129), COL3 VARGRAPHIC(3), COL4 CHAR(1) NOT NULL, COL5 VARCHAR(256), COL6 VARCHAR2(3), COL7 VARCHAR (16) FOR BIT DATA, COL8 INTEGER, COL9 TIMESTAMP, COL10 BLOB(1048576), COL11 BLOB(1048576), COL12 CLOB(1), COL13 CLOB(100000000), COL14 DBCLOB(100000000), COL15 VARCHAR (1) FOR BIT DATA, COL16 NUMBER(1 , 0), COL17 DECFLOAT(34), COL18 NUMBER(1 , 1), COL19 DECFLOAT(34), COL20 NUMBER, COL21 NUMBER(5 , 3), COL22 DECFLOAT(34), COL23 DECFLOAT(34), COL24 DOUBLE, COL25 DOUBLE, COL26 DOUBLE, COL27 TIMESTAMP ) DATA CAPTURE NONE; ALTER TABLE DB2ADMIN.MY_ORACLE_DATATYPES ADD CONSTRAINT SYS_C0012334 PRIMARY KEY (COL4); CREATE UNIQUE INDEX my_Oracle_idx1 ON DB2ADMIN.my_Oracle_datatypes(col20 ASC,col22 DESC); |
Table 2 shows an example of the DDL that is generated for copying a table from a DB2 Version 9.7 for Linux, UNIX, and Windows source database to an Oracle Database 11g target database.
DB2 Version 9.7 for Linux, UNIX, and Windows database table definition | Generated DDL for Oracle Database 11g |
---|---|
CREATE TABLE my_db2_datatypes (col1 GRAPHIC(1) , col2 CHAR FOR BIT DATA , col3 VARGRAPHIC(1) , col4 LONG VARGRAPHIC , col5 VARCHAR(1) FOR BIT DATA , col6 LONG VARCHAR , col7 CHAR(1) NOT NULL , col8 VARCHAR(1) , col9 VARCHAR2(1) , col10 NUMBER(1) , col11 NUMERIC(1) , col12 DECFLOAT(16) , col13 INTEGER , col14 SMALLINT NOT NULL , col15 DATE , col16 XML , col17 BLOB , col18 CLOB , col19 DBCLOB , col20 REAL , col21 FLOAT , col22 DOUBLE , col23 TIMESTAMP , col24 TIME , col25 DECIMAL , col26 DOUBLE PRECISION , col27 LONG VARCHAR FOR BIT DATA, PRIMARY KEY (col7) ); CREATE UNIQUE INDEX my_db2_idx1 ON my_db2_datatypes(col13 ASC,col15 DESC); |
CREATE TABLE SCOTT.MY_DB2_DATATYPES (COL1 NCHAR(1), COL2 RAW(1), COL3 NVARCHAR2(1), COL4 NVARCHAR2(1), COL5 ROWID, COL6 VARCHAR2(1), COL7 CHAR(1) NOT NULL, COL8 VARCHAR2(1), COL9 VARCHAR2(1), COL10 DECIMAL(1 , 0), COL11 DECIMAL(1 , 0), COL12 NUMBER(16 , 0), COL13 INT, COL14 SMALLINT NOT NULL, COL15 TIMESTAMP(6), COL16 XMLTYPE, COL17 BLOB, COL18 CLOB, COL19 NCLOB, COL20 FLOAT(126), COL21 DOUBLE PRECISION, COL22 DOUBLE PRECISION, COL23 TIMESTAMP(6), COL24 TIMESTAMP(6), COL25 DECIMAL(5 , 0), COL26 DOUBLE PRECISION, COL27 ROWID ) LOGGING; ALTER TABLE SCOTT.MY_DB2_DATATYPES ADD CONSTRAINT SQL090126122431650 PRIMARY KEY (COL7); CREATE UNIQUE INDEX my_db2_idx1 ON my_db2_datatypes(col13 ASC,col15 DESC); |
Table 3 shows examples of the DDL that is generated for copying a table from a DB2 Version 9.7 for Linux, UNIX, and Windows source database to a DB2 Version 9.1 for z/OS® target database.
DB2 Version 9.7 for Linux, UNIX, and Windows database table definition | Generated DDL for DB2 Version 9.1 for z/OS |
---|---|
CREATE TABLE test (col1 CHAR(6) NOT NULL, col2 CHAR(4) FOR BIT DATA DEFAULT NULL, col3 VARCHAR(20), col4 NUMERIC(10), col5 DECFLOAT(34), col6 BIGINT, col7 INTEGER NOT NULL, col8 SMALLINT, col9 DATE, col10 TIMESTAMP NOT NULL, col11 DECIMAL(8 , 2) DEFAULT NULL, col12 REAL, col13 FLOAT(53) DEFAULT 123450, col14 DOUBLE DEFAULT 11e3, col15 DOUBLE PRECISION, col16 XML, col17 TIME, col18 VARCHAR(32) FOR BIT DATA, col19 BLOB, col20 CLOB ) DATA CAPTURE NONE ; |
CREATE TABLE TEST (COL1 CHAR(6) NOT NULL, COL2 CHAR (4) FOR BIT DATA WITH DEFAULT NULL, COL3 VARCHAR(20), COL4 DECIMAL(10 , 0), COL5 DECFLOAT(34), COL6 BIGINT, COL7 INTEGER NOT NULL, COL8 SMALLINT, COL9 TIMESTAMP, COL10 TIMESTAMP NOT NULL, COL11 DECIMAL(8 , 2) WITH DEFAULT NULL, COL12 REAL, COL13 DOUBLE WITH DEFAULT 123450, COL14 DOUBLE WITH DEFAULT 11e3, COL15 DOUBLE, COL16 XML, COL17 TIME, COL18 VARCHAR (32) FOR BIT DATA, COL19 BLOB(1048576), COL20 CLOB(1048576) ) AUDIT NONE DATA CAPTURE NONE CCSID EBCDIC; |
CREATE TABLE test26 (col1 GRAPHIC(20), col2 VARGRAPHiC(10) ); |
CREATE TABLE NEWTON.TEST26 (COL1 GRAPHIC(20), COL2 VARGRAPHIC(10) ) AUDIT NONE DATA CAPTURE NONE CCSID UNICODE; |
Table 4 shows examples of the DDL that is generated for copying a table from a DB2 Version 9.1 for z/OS source database to a DB2 Version 9.7 for Linux, UNIX, and Windows target database.
DB2 Version 9.1 for z/OS database table definition | Generated DDL for DB2 Version 9.7 for Linux, UNIX, and Windows |
---|---|
CREATE TABLE test1 (col1 CHAR(6) NOT NULL, col2 CHAR(4) FOR BIT DATA DEFAULT NULL, col3 VARCHAR(20), col4 NUMERIC(10), col5 DECFLOAT(34), col6 BIGINT, col7 INTEGER NOT NULL, col8 SMALLINT, col9 DATE, col10 TIMESTAMP NOT NULL, col11 DECIMAL(8 , 2) DEFAULT NULL, col12 REAL, col13 FLOAT(53) DEFAULT 123450, col14 DOUBLE DEFAULT 11e3, col15 DOUBLE PRECISION, col16 XML, col17 TIME, col18 VARCHAR(32) FOR BIT DATA, col19 BLOB, col20 CLOB); |
CREATE TABLE TEST1 (COL1 CHAR(6) NOT NULL, COL2 CHAR (4) FOR BIT DATA DEFAULT NULL, COL3 VARCHAR(20) DEFAULT NULL, COL4 DECIMAL(10 , 0) DEFAULT NULL, COL5 DECFLOAT(34) DEFAULT NULL, COL6 BIGINT DEFAULT NULL, COL7 INTEGER NOT NULL, COL8 SMALLINT DEFAULT NULL, COL9 DATE DEFAULT NULL, COL10 TIMESTAMP NOT NULL, COL11 DECIMAL(8 , 2) DEFAULT NULL, COL12 REAL DEFAULT NULL, COL13 DOUBLE DEFAULT 123450, COL14 DOUBLE DEFAULT 11E3, COL15 DOUBLE DEFAULT NULL, COL16 XML, COL17 TIME DEFAULT NULL, COL18 VARCHAR (32) FOR BIT DATA DEFAULT NULL, COL19 BLOB(1048576) DEFAULT NULL, COL20 CLOB(1048576) DEFAULT NULL ) DATA CAPTURE NONE ; |
CREATE TABLE test27 (col1 GRAPHIC(20), col2 VARGRAPHIC(10) ) CCSID UNICODE; |
CREATE TABLE DB2ADMIN.TEST27 (COL1 GRAPHIC(20) DEFAULT NULL, COL2 VARGRAPHIC(10) DEFAULT NULL ) DATA CAPTURE NONE; |