Data Types of Oracle and DB2 (1)

This article introduces the Data Types of Oracle and DB2, and describes in detail, it should be noted that DATE in Oracle contains year, month, day, hour, minute, second, it does not correspond to the DATE in DB2/400. The DATE in DB2/400 only includes the year, month, and day. The TIME type includes the TIME, minute, and second, therefore, the date and time types must be converted.

First, the table below describes the ing relationship with the DB2/400 data types, which is a one-to-many relationship. The specific ing relationship should be analyzed for specific issues.

Note:

The DATE in Oracle contains the year, month, day, hour, minute, and second. It does not correspond to the DATE in DB2/400. The DATE in DB2/400 only includes the year, month, and day, the TIME type includes TIME, minute, and second. Therefore, to convert the date and TIME types, see the following table.

Oracle		DB2/400
Oracle Data Type	Notes	DB2 UDB Data Type	Notes
DATE		DATE TIME TIMESTAMP l	If only MM/DD/YYY is used, the DATE type is used. L if only HH: MM: SS is used, the TIME type is used. L if you want to use the date and time, use the TIMESTAMP type) L you can use the TO_CHAR () function in Oracle to obtain the DATE string and match the DATE and TIME of DB2/400 respectively.
VARCHAR2 (n)	N <= 4000	CHAR (n) VARCHAR (n) l	If n <= 32766, the CHAR type and VARCHAR type in DB2/400 are used.
LONG	N <= 2 GB	VARCHAR (n) CLOB (n) l	If n <= 32 K, CHAR and VARCHAR in DB2/400 are used. L if 32 K = <n <= 2 GB, CLOB is used.
ROW & LONG ROW	N <= 255	CHAR (n) FOR BIT DATA VARCHAR (n) FOR BIT DATA BLOB (n) l	If n <= 32 K, CHAR (n) for bit data or VARCHAR (n) FOR BIT DATA L if n <= 2 GB, BLOB (n) is used)
BLOB	N <= 4 GB	BLOB (n)	If n <= 2 GB, BLOB (n) is used)
CLOB	N <= 4 GB	CLOB (n)	If n is <= 2 GB, CLOB (n) is used)
NCLOB	N <= 4 GB	DBCLOB (n)	If n is <= 2 GB, DBCLOB (n/2) is used)
NUMBER		SMALLINT/INTEGER/BIGINT DECIMAL (p, s)/NUMBER (p, s) Float (n)/REAL/DOUBLE l	If NUMBER (p) or NUMBER (p, s) is defined in Oracle, SAMLLINT/INTEGER/BIGINT L if NUMBER (p, s) is defined in Oracle, DECIMAL (p, s) is used) L if NUMBER is defined in Oracle, FLOAT (n)/REAL/DOUBLE is used.

NUMBER

The NUMBER type in Oracle can correspond to many types in DB2/400. This relationship depends on the type of data in Oracle that number will be used to store, whether it is integer or real-type data with decimal places, in addition, we need to consider the storage space occupied by the type. For example, SAMLLINT occupies 2 bytes, INTEGER occupies 4 bytes, BIGINT occupies 8 bytes, and NUMBER occupies 8 bytes in Oracle.