Oracle and DB2, Comparison and Compatibility/Storage Model/Data Types/Summary

From Wikibooks, open books for an open world
< Oracle and DB2, Comparison and Compatibility‎ | Storage Model‎ | Data Types
Jump to: navigation, search

Summary[edit]

Oracle and DB2 Data Type Comparison Matrix

In this matrix, each data type described for Oracle is listed in the left hand column, and is paired with its DB2 equivalent in the right hand column. Where DB2 has a number of named data types that equate to a single Oracle data type (for example in Numeric Data Types), the related Oracle data type is shown duplicated but with a precision and scale that equal the DB2 data type. If there is no equivalence, the data type is left blank and a mention is made in the Description. The net is that for each data type previously identified for Oracle and DB2 there is an entry in the following matrix.

Oracle and DB2 Character Data Types
Ora DB2 Character Data Types.jpg
Oracle and DB2 Numeric Data Types
Ora DB2 Numeric Data Types.jpg
Oracle and DB2 Date Data Types
Ora DB2 Date Data Types.jpg


  • Oracle has the built-in SQL functions DBTIMEZONE and SESSIONTIMEZONE that you can query to verify the database and session time zones. If the database time zone or the session time zone has not been set manually, Oracle Database uses the operating system time zone

DB2 has a series of special registers that are storage areas that can be referenced in SQL statements. CURRENT TIMEZONE specifies the TIMEZONE parameter in the form of a time duration with a data type of DECIMAL(6,0) in the format HHMMSS. The time duration is derived by the instance that executes the SQL statement. To get the CURRENT TIME or CURRENT TIMESTAMP adjusted to GMT/UTC, subtract the CURRENT TIMEZONE register from the CURRENT TIME or TIMESTAMP.

The assumption is that the clock is set to local time and the TIMEZONE parameter is zero, or the clock is set to GMT and the TIMEZONE parameter gives the difference from GMT.

The net of this is that TIMESTAMP, TIMESTAMP WITH TIMEZONE and TIMESTAMP WITH LOCAL TIMEZONE are all really the same thing. They are a timestamp with the format YYYY / MM / DD / HH:MM:SS:FFFFFF that is adjusted based on the setting of the clock on the server of the database instance, AND the value of a register (DB2) or built-in function (Oracle). It’s just something you need to pay attention to in conversion/compatibility. What are the settings where you are, and what are the settings where you are going.

Oracle and DB2 LOB Data Types
Ora DB2 LOB Data Types.jpg
Oracle and DB2 Graphic Data Types
Ora DB2 Graphic Data Types.jpg


Oracle and DB2 ROWID data Types
Ora DB2 ROWID Data Types.jpg


Because of how DB2 points to rows within a page, the ROWID points to the page that contains the row. Where the actual row exists in the page is handled by pointers (since a row can move around in a page, and it’s offset is stored in the ID Map at the bottom of the page), so the ROWID will get you to the DB2 page, and then it’s ID Map will get you to the row. In Oracle, the ROWID points to the row location in the block, and if that location is a pointer to another block, this is a relative address (UROWID).

Oracle and DB2 XML Data Type
Ora DB2 XML Data Type.jpg


  • SQL statements that create tables and clusters can also use ANSI and DB2 data types. Oracle provides the XMLType datatype to handle XML data. Oracle will recognize the ANSI or DB2 data type name and use this as the name of the data type of the column. The actual data is stored as an Oracle native data type. XML data stored in DB2 is stored in the UTF-8 code set. XML values are stored in an internal representation that is not a string and is not directly comparable to string values.