Oracle VS DB2 data types

=========================oracle VS db2====================================

This article transferred from: http://www.bitscn.com/oracle/install/200604/16541.html
First, the following table describes the relationship between Oracle and db2/400 data types, is a one-to-many relationship, the specific use of the corresponding relationship, the specific problems should be analyzed.

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) [email protected]_com network management software download 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 bbs.bitsCN.com

Precautions:

DATE and Time

The date in Oracle contains the year, month, day, hour, minute, and second, and the date in the db2/400 does not correspond to each other, the date in db2/400 is only the year, the month, the day, and the time type contains hours, minutes, seconds, so the dates and time types are converted, refer to the following table.

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 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 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 ROWID 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.

For example:

CREATE TABLE Mylib/zhong (Hanzi CHAR (CCSID) 935 not NULL Withdefault)

Oracle VS DB2 data types