Classification correspondence for Oracle and DB2 data types

First of all, the corresponding relationship between the db2/400 data types is described in the following table, which is a one-to-many relationship, and the specific problem should be analyzed concretely.

Precautions:

The date in Oracle contains the year, month, day, time, minute, and second, it does not correspond to date in db2/400, the date in db2/400 is only year, month, day, time type contains hour, minute, second, so date and time type to convert, please refer to the following table.

Oracle	db2/400
Oracle data types	Attention matters	DB2 UDB Data type	Attention matters
DATE		DATE Time TIMESTAMP L	If you are using only mm/dd/yyy, then use the date type. L Use the time type if only HH:MM:SS is used. L Use a timestamp type if you want to use a date and time (TIMESTAMP) L can use the To_char () function in Oracle to fetch the 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, then use the char type in db2/400, VARCHAR
LONG	N<=2gb	VARCHAR (N) CLOB (n) L	If n<=32k, then use the char type, VARCHAR in db2/400. L if 32k=< n <=2gb, then 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, then use CLOB (n)
NCLOB	N<=4gb	Dbclob (N)	If N<=2GB, then 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 float (n)/real/double

Number

Number types in Oracle can correspond to many of the types in db2/400, depending on what type of data will be used by number in Oracle, whether it is integer or with decimal, and also about the storage space occupied by the type, for example: Samllint accounted for 2 byte, the integer accounted for 4 byte; The storage space occupied by the number type in the bigint 8 byte;oracle depends on its definition, for example, under the default precision, numbers have 38 digits long and occupy a byte of space. Please refer to the above table for the specific corresponding relationship.

ROW and LOB types

DB2/400 provides varchar and CLOB corresponding to raw and long raw in Oracle. 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 only hold 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, the tables in the database point to the bfile files that are stored outside the database, and db2/400 provides a similar data type Datalink corresponding.

ROWID

Oracle Rowind virtual columns are used to uniquely label a column in a table, and in db2/400 there is a data type rowid similar to the ROWID functionality in Oracle. The ROWID in db2/400 can hold byte data to uniquely identify each row in the table, and it has no Ccsid property, and there is no information about datafile, block, or row.

For example:

CREATE TABLE libraryname/orders2 (ORDERNO ROWID, shipped_to VARCHAR (), order_date DATE) ORDERNO The data type is ROWID, which holds the order number, whenever When you insert a row, the system automatically generates a value that is stored in this field. You can view its contents with Operation navigator.

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 used only for storing smaller strings, where n is less than 4000, so in this case, It is best to use a fixed-length char (n) type to correspond to Oracle's VARCHAR2 (n), which will not only improve efficiency, but also save storage space, if you use the varchar (n) type preferably with allocate parameters, this can improve the performance of the database, It can reduce the input/output operation between memory and hard disk.

To pay attention to the db2/400 in the string Chinese input problem, in order to enter the db2/400 on the Chinese application such as SQL Creation table, where the Ccsid 935, representing Simplified Chinese.