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.

  1. In the Columns Tab of the SQDR subscription, highlight the Date column and click Delete Column.
  2. 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

  3. 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. 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 information in technical documents may be gathered from various sources, including IBM, Microsoft, and other organizations.