Help Q Replication and Event Publishing

Data type support for native Oracle targets

Replicating data between DB2® and a native Oracle target requires consideration of data type compatibility and other issues. The following sections provide details.

Supported types

The following data types are supported for Q Apply for Oracle.

Mapping between datetime and string data types

The Q Capture program sends datetime data type values as strings in compact messages to Q Apply rather than using a database internal format. This method allows you to replicate datetime strings directly to character types if the target character type is large enough.

Q Apply for Oracle uses the following datetime session parameters:

The Q Capture program uses the following datetime string formats when it replicates datetime data:

Type Format Standard
TIME HH.MM.SS Old ISO (DB2)
DATE YYYY-MM-DD ISO 8601
TIMESTAMP YYYY-MM-DD-HH.MM.SS.NNNNNNNNNNNN DB2

You can map a datetime type to a character type in one of several ways:

All of the mappings results in formatting as shown in table above, with the exception of the TO_CHAR function, which results in formatting as specified in TO_CHAR function.

Exception: Do not use the default expression or the TO_CHAR function without formatting when you map DB2 TIME data types to string data types. Internally, the source data is cast to DATE and then applied as DATE with Oracle performing the conversion from DATE to string according to the session settings. Therefore, using a default expression or an expression using the TO_CHAR function without formatting does not result in the desired data at the target. An error (ORA-12899: value too large for column column_name (actual: 10, maximum: 8)) occurs when you use CHAR or VARCHAR2(8) as a target. The correct way to replicate DB2 TIME to Oracle string is to use the regular mapping or use an expression with the TO_CHAR function and the desired format. For example, the expression for the time format 'HH24:MI:SS' is:
TO_CHAR(:COLNAME, 'HH24:MI:SS')

Automatic promotion of VARCHAR2 to CLOB when adding columns

The maximum length of the source DB2 VARCHAR type (32672 bytes) can exceed the default Oracle target type VARCHAR2 (4000 bytes). When you use the ADDCOL signal to add a VARCHAR column to a Q subscription and the column exceeds 4000 bytes, the Q Apply program creates the target column with a data type of CLOB. VARCHAR and CLOB are compatible types.

If the target column already exists and cannot store the maximum size of data from the source, the Q subscription is deactivated.

Oracle XML data type not supported

The Oracle XML data type as target is not supported. The Replication Center does not create Q subscriptions with XML target columns and the Q Apply program does not activate such Q subscriptions if set up manually. You can replicate DB2 XML values to Oracle CLOB values. The source XML value is serialized into a character string and can be stored in the CLOB type.

Default mapping for floating types when adding columns

The DB2 REAL, FLOAT and DOUBLE types are mapped in Oracle to the following default type if the target column does not exist.

DB2 source type Oracle target type
REAL BINARY_FLOAT
FLOAT or DOUBLE BINARY_DOUBLE

Handling of a TIMESTAMP 24.00.00 value

Oracle does not allow a time portion of 24.00.00.000000 (24 hours and zero minutes, seconds and subseconds). If you try to use this value in the VALUES, SET, or WHERE clause, Oracle returns the error message “ORA-01850: hour must be between 0 and 23.” The Q Apply program tries to insert all timestamp values without any check. If Q Apply encounters the ORA-01850 error, it checks for a 24 value in the hours portion of the timestamp data sent by the Q Capture program and retries with a value of 23.59.59.999999.

Parent topic: Q Replication to native Oracle targets

Send your feedback | Information roadmap | The Q+SQL Replication Forum

Update icon Last updated: 2013-10-25