StarQuest Technical Documents
How to Replicate Oracle DATE Data Types to DB2 DATE Data Types
Last Update: 07 September 2005
Product: StarQuest Data Replicator
Version: 3.x
Article ID: SQV00DR014
Abstract
This document explains how to define an SQDR subscription that properly replicates Oracle Date data types to the DB2 Date data types. This solution has been tested with Oracle 9i using Oracle 9 ODBC clients.
Solution
When replicating Oracle Date fields to DB2, SQDR sets the default destination data type to Timestamp because the Oracle Date data type more closely resembles a DB2 Timestamp. However, you may prefer that the destination DB2 data type be Date, especially if you are replicating to an existing DB2 table that has been defined to use the Date data type.
The solution is to replace the existing Date column with a derived column that uses an Oracle TO_CHAR function to convert the Date field to a Char field. The properly formatted Char field can be then replicated to a DB2 Date field.
- In the Columns Tab of the SQDR subscription, highlight the Date column and click Delete Column.
- Click the Insert Derived Column button and set the properties as
follows:
Expression: TO_CHAR (<original_date_column_name>, 'YYYY-MM-DD')
Column Name: use the same name as the original column name
Data Type: DATE - Click OK to save the derived column. Click OK in the SQDR subscription properties to save the changes.
In the example below, the original column name is "REGION".
If your DB2 system uses a different DATE format than what is shown in the example, you can change the TO_CHAR function to conform to the desired format. Please see Oracle documentation for more date formatting options.
DISCLAIMER
The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization. The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.