Description of Oracle and DB2 Data Types

Http://database.51cto.com/art/200703/41790.htm

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, the date and time types must be converted,

See the following table.

Considerations for Oracle DB2/400 Oracle Data Types precautions for DB2 UDB data types 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 (timestamp) l you can use to_char () in Oracle () the function is used to obtain the string of date to match the date and time of DB2/400 respectively. Varchar2 (n) n <= 4000 char (n) varchar (n) l if n <= 32766, use the char type and varchar long N in DB2/400 <= 2 GB varchar (n) clob (n) l if n <= 32 K, the Char and varchar types 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) is used) for BIT data or varchar (n) for BIT data l if n <= 2 GB, blob (n) blob n <= 4 GB blob (N) if n is <= 2 GB, blob (n) clob n <= 4 GB clob (n) is used. If n is <= 2 GB, clob (n) is used) nclob n <= 4 GB dbclob (n) if n <= 2 GB, use dbclob (n/2) number smallint/Integer/bigint decimal (P, S) /number (P, S) float (N)/real/double L varchar if number (p) or number is defined in Oracle (P, S), use samllint/Integer/bigint L if number (P, S) is defined in Oracle, use decimal (P, S) l if number is defined in Oracle, float (N)/real/double number the number type in Oracle can correspond to many types in DB2/400, this correspondence relationship depends on the type of data that the number in Oracle will be used to store, whether it is an integer or a real-type data with decimal places. In addition, we also need to consider the storage space occupied by the type, for example: samllint occupies 2 bytes, and integer occupies 4 bytes; bigint occupies 8 bytes; the storage space occupied by the number type in Oracle depends on its definition, for example, by default, the number is 38 characters long, occupying 20 bytes. 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, the data type of create table libraryname/orders2 (orderno rowid, shipped_to varchar (36), order_date date) orderno is rowid, which is used to store the order number. When a row is inserted, the system automatically generates a value and stores it in 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.