Comparison of DB2 and Oracle field types

Find an online comparison of Oracle and DB2 data types, and write them down.

Oracle		db2/400
Oracle data types	Precautions	DB2 UDB Data type	Precautions
DATE		DATE Time TIMESTAMP L	If you use only mm/dd/yyy, use the date type. L Use the time type if you only use HH:MM:SS. L Use the timestamp type (TIMESTAMP) if you want to use the date and time L can use the To_char () function in Oracle to take a string of date to match the date and time of the db2/400, respectively.
VARCHAR2 (N)	n<=4000	CHAR (N) VARCHAR (n) L	If n<=32766, use the char type in the db2/400, VARCHAR
LONG	N<=2gb	VARCHAR (N) CLOB (n) L	If n<=32k, use the char type, VARCHAR, in db2/400. L if 32k=< n <=2gb, use CLOB.
row& LONG ROW	n<=255	CHAR (n) for BIT DATA VARCHAR (n) for BIT DATA BLOB (n) L	If n<=32k, use char (n) for BIT DATA or VARCHAR (n) for BIT DATA L if N<=2GB, use BLOB (n)
Blob	N<=4gb	BLOB (N)	If N<=2GB, use BLOB (n)
Clob	N<=4gb	CLOB (N)	If N<=2GB, use CLOB (n)
NCLOB	N<=4gb	Dbclob (N)	If N<=2GB, use Dbclob (N/2)
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, use Samllint/integer/bigint L if number (P,S) is defined in Oracle, use Decimal (p,s) L if number is defined in Oracle, use the float (n)/real/double

Description
Number

The number types in Oracle can correspond to many of the types in db2/400, depending on what type of data Oracle is using to hold, whether it is integer or real data with a decimal place, and also the storage space that the type occupies, such as: Samllint is 2 byte, and integer is 4 byte; The amount of storage occupied by the number type in bigint 8 byte;oracle is determined by its definition, for example, the number with the default precision is 38 digits long and occupies a space of three bytes. Please refer to the table above for the specific correspondence relationship.
　　 ROW and LOB types
　　DB2/400 provides varchar and CLOB in relation to raw and long raw in oracel. Oracle also supports large objects: BLOBs, CLOB, CLOB, and NCLOB, where BLOBs and CLOB in Oracle can provide 4GB of space, while BLOBs and db2/400 in CLOB can hold only 2GB of data; db2/ The DBCLOB in 400 corresponds to the NCLOB 2GB in Oracle. The bfile data type in Oracle is used to manage binary data outside the database, and the tables in the database will point to the bfile files that are stored outside the database, and db2/400 also provide a similar data type Datalink counterpart.
　　 ROWID
　　The Oracle rowind Virtual column is used to uniquely label a column in a table, and db2/400 has such a data type ROWID, which is similar to the functionality of ROWID in Oracle. The ROWID in db2/400 can hold up to a byte of data that uniquely identifies each row in the table, and it has no CCSID attribute, which does not contain information about datafile, block, or row. For example:
CREATE TABLE libraryname/orders2 (OrderNo ROWID, shipped_to VARCHAR, order_date DATE) OrderNo has a data type of ROWID, which is used to hold the order number whenever When inserting a row, the system automatically generates a value that is stored in this field. You can use Operation Navigator to view its contents.
　　Character type
　　The char, varchar type of db2/400 corresponds to the VARCHAR2 (n) type in Oracle, but the VARCHAR2 (n) type in Oracle is only used to hold smaller strings, where n is less than 4000, so in this case, It is best to use a fixed-length char (n) type that corresponds to Oracle's VARCHAR2 (n), which not only improves efficiency but also saves storage space, and is best used with the allocate parameter if the varchar (n) type is used, which improves the performance of the database. It can reduce the input/output operation between memory and hard disk.
To pay attention to the string Chinese input problem in db2/400, in order to enter a Chinese application such as SQL CREATE TABLE, here Ccsid 935, representing Simplified Chinese.

Comparison of DB2 and Oracle field types