Oracle and DB2 Data Types

First, the following table describes the ing between Oracle and DB2/400 data types, which is a one-to-many relationship. The specific ing relationship should be analyzed according to specific issues.

Note:

Date and Time

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, the storage space occupied by the type should be considered. For example, samllint occupies 2 bytes, integer occupies 4 bytes, and bigint occupies 8 bytes; the storage space occupied by the number type in Oracle depends on its definition. For example, the number in the default precision is 38 characters long, occupying 20 bytes of space. For specific mappings, see the table above.

Row and LOB type

DB2/400 provides varchar and clob that correspond to raw and long raw in Oracle. Oracle also supports large objects: blob, clob, clob, and nclob. Blob and clob in Oracle can provide 4 GB space, while blob and clob in DB2/400 can only store 2 GB of data; dbclob in DB2/400 corresponds to nclob 2 GB in Oracle. The bfile data type in Oracle is used to manage binary data other than the database. Tables in the database point to the bfile file stored outside the database. DB2/400 also provides a similar data type corresponding to datalink.

Rowid

The Oracle rowind virtual column is used to uniquely identify a column in the table. This data type also exists in DB2/400, which is similar to the rowid function in Oracle. Rowid in DB2/400 can store 40 bytes of data to uniquely identify each row in the table. It does not have the ccsid attribute and does not contain information about datafile, block, or row.

For example:

Create Table libraryname/orders2 (orderno rowid, shipped_to varchar (36), order_date date) the data type of orderno is rowid, which is used to store the order number. When a row is inserted, the system automatically generates a value, enter this field. You can use operation navigator to view its content.

Character Type

The Char and varchar types of DB2/400 correspond to the varchar2 (n) types in Oracle, but the varchar2 (n) type in Oracle is only used to store small strings, here N is less than 4000, so in this case, it is best to use a fixed length char (n) type to correspond to the Oracle varchar2 (n), which not only improves the efficiency, it can also save storage space. If varchar (n) type is used, it is best to use the allocate parameter, which can improve database performance and reduce input/output operations between memory and hard disk.

Pay attention to the problem of string Chinese Input in DB2/400. To enter an SQL statement such as Chinese application on DB2/400, the ccsid 935 here represents Simplified Chinese.

For example:

Create Table mylib/Zhong (Hanzi char (30) ccsid 935 not null withdefault)