Data-type mapping for objects that are copied to heterogeneous databases

When you copy database tables from a source database to a heterogeneous target database, the source data types are mapped to the target data types that most closely resemble the source data types.

The following tables show the default mapping of source data types to target data types. If optional data types are shown for a target data type, you can change a mapped data type from its default in the Paste Database Objects wizard.

Note: Currently, data types in Oracle routines and DB2® for Linux®, UNIX®, and Windows® PL/SQL routines are not mapped. To be copied successfully, the data types in source routines must be valid data types in the target database.

Table 1 shows the default mapping of data types when you are copying objects from an Oracle source database to a DB2 for Linux, UNIX, and Windows target database.

Table 1. Default mapping of Oracle data types to DB2 for Linux, UNIX, and Windows data types
Oracle data type DB2 for Linux, UNIX, and Windows data type
CHAR(n)
for 1 ≤ n ≤ 254:
   CHAR(n)
   optional  VARCHAR(n)

for 255 ≤ n ≤ 2000:
   VARCHAR(n)
NCHAR(n)
for 1 ≤ n ≤ 127:
   GRAPHIC(n)
   optional  VARGRAPHIC(n)  

for 128 ≤ n ≤ 2000:
   VARGRAPHIC(n)
NVARCHAR2(l)
VARGRAPHIC(l)
VARCHAR(l)
VARCHAR2(l)
VARCHAR2(l)
VARCHAR2(l)
LONG
CLOB
RAW(l)
VARCHAR (l) FOR BIT DATA
LONG RAW
BLOB
NUMBER
NUMBER
optional  SMALLINT
optional  NUMBER(p , s)  
NUMBER(p , s)
for 1 ≤ p ≤ 31:
   NUMBER(p , 0), for s < 0
   NUMBER(p , s), for 0 ≤ s ≤ 31
   optional  INTEGER

for 32 ≤ p ≤ 38:
   DECFLOAT(34)
   optional  NUMBER(p , 0), for s < 0
   optional  INTEGER, for s = 0
   optional  NUMBER(p , s), for 0 ≤ s ≤ 38
NUMERIC
DECIMAL(5,0)
FLOAT
DOUBLE
BINARY_FLOAT
FLOAT(53)
BINARY_DOUBLE
DOUBLE
DECIMAL
NUMBER
DECIMAL(p , s)
NUMBER(p , s)
INTEGER
DECFLOAT(34)
optional  SMALLINT
PLS_INTEGER
INTEGER
INT   NOT NULL
DECFLOAT(34)   NOT NULL
SMALLINT
DECFLOAT(34)
REAL
DOUBLE
DOUBLE PRECISION
DOUBLE
DATE
TIMESTAMP
optional  DATE
optional  TIME
TIMESTAMP
TIMESTAMP
INTERVAL YEAR TO MONTH
CHAR(50)
INTERVAL DAY TO SECOND
CHAR(50)
BFILE
BLOB(1048576) DEFAULT   NULL
BLOB
BLOB(1048576)
CLOB
CLOB(100000000)
NCLOB
DBCLOB(100000000)
ROWID
CHAR(18)
UROWID
CHAR(18)
BOOLEAN
INTEGER
XMLTYPE
XML

Table 2 shows the default mapping of data types when you are copying objects from a DB2 for Linux, UNIX, and Windows source database to an Oracle target database.

Table 2. Default mapping of DB2 for Linux, UNIX, and Windows data types to Oracle data types
DB2 for Linux, UNIX, and Windows data type Oracle data type
GRAPHIC(l)
NCHAR(l)
CHAR FOR BIT DATA(l)
RAW(l)
VARGRAPHIC(l)
NVARCHAR2(l)
LONG VARGRAPHIC
NVARCHAR2(l)
CHAR(l)
CHAR(l)
VARCHAR(l)
VARCHAR2(l)
VARCHAR2(l)
VARCHAR2(l)
LONG VARCHAR(l)
VARCHAR2(l)
NUMBER(l)
INTEGER(l)
NUMERIC(l)
NUMERIC(l)
DECFLOAT(l)
NUMBER(l)
BIGINT(l)
NUMBER(l)
INTEGER
INTEGER
optional  SMALLINT
SMALLINT
SMALLINT
DATE
DATE
BOOLEAN
BOOLEAN
optional  SMALLINT
RID_BIT
ROWID
BLOB
BLOB
XML
XMLTYPE
CLOB
CLOB
DBCLOB
NCLOB
REAL
FLOAT
FLOAT
FLOAT
DOUBLE
DOUBLE PRECISION
DOUBLE PRECISION
DOUBLE PRECISION
TIMESTAMP
TIMESTAMP
TIME
TIMESTAMP
DECIMAL
DECIMAL
optional  INTEGER
optional  SMALLINT
LONG VARCHAR FOR BIT DATA
RAW
VARCHAR FOR BIT DATA
RAW

Table 3 shows the default mapping of data types when you are copying objects from a DB2 for Linux, UNIX, and Windows source database to a DB2 for z/OS® target database.

Table 3. Default mapping of DB2 for Linux, UNIX, and Windows data types to DB2 for z/OS data types
DB2 for Linux, UNIX, and Windows data type DB2 for z/OS data type
GRAPHIC(l)
GRAPHIC(l)
CHAR FOR BIT DATA(l)
CHAR FOR BIT DATA(l)
VARGRAPHIC(l)
VARGRAPHIC(l)
LONG VARGRAPHIC
VARGRAPHIC(l)
CHAR(l)
CHAR(l)
VARCHAR(l)
VARCHAR(l)
VARCHAR2(l)
VARCHAR(l)
LONG VARCHAR
LONG VARCHAR
NUMBER(l)
INTEGER
NUMERIC(l)
NUMERIC(l)
DECFLOAT(l)
DECFLOAT(l)
BIGINT
BIGINT
INTEGER
INTEGER
SMALLINT
SMALLINT
DATE
DATE
BLOB
BLOB
XML
XML
CLOB
CLOB
DBCLOB
DBCLOB
REAL
REAL
FLOAT
FLOAT
DOUBLE
DOUBLE
DOUBLE PRECISION
DOUBLE PRECISION
TIMESTAMP
TIMESTAMP
TIME
TIME
DECIMAL(p , s)
DECIMAL(p , s)
LONG VARCHAR FOR BIT DATA
LONG VARCHAR FOR BIT DATA
VARCHAR FOR BIT DATA
VARCHAR FOR BIT DATA

Table 4 shows the default mapping of data types when you are copying objects from a DB2 for z/OS source database to a DB2 for Linux, UNIX, and Windows target database.

Table 4. Default mapping of DB2 for z/OS data types to DB2 for Linux, UNIX, and Windows data types
DB2 for z/OS data type DB2 for Linux, UNIX, and Windows data type
GRAPHIC(l)
GRAPHIC(l)
CHAR FOR BIT DATA(l)
CHAR FOR BIT DATA(l)
VARGRAPHIC(l)
VARGRAPHIC(l)
CHAR(l)
CHAR(l)
VARCHAR(l)
VARCHAR(l)
LONG VARCHAR
LONG VARCHAR
NUMERIC(l)
NUMERIC(l)
DECFLOAT(l)
DECFLOAT(l)
BIGINT
BIGINT
INTEGER
INTEGER
SMALLINT
SMALLINT
DATE
DATE
BLOB
BLOB
XML
XML
CLOB
CLOB
DBCLOB
DBCLOB
REAL
REAL
FLOAT
FLOAT
DOUBLE
DOUBLE
DOUBLE PRECISION
DOUBLE PRECISION
TIMESTAMP
TIMESTAMP
TIME
TIME
DECIMAL(p , s)
DECIMAL(p , s)
LONG VARCHAR FOR BIT DATA
LONG VARCHAR FOR BIT DATA
VARCHAR FOR BIT DATA
VARCHAR FOR BIT DATA

Feedback