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
- Mapping between datetime and string data types
- Automatic promotion of VARCHAR2 to CLOB when adding columns
- Oracle XML data type not supported
- Default mapping for floating types when adding columns
- Handling of a TIMESTAMP 24.00.00 value
Supported types
The following data types are supported for Q Apply for Oracle.
- CHAR
- VARCHAR2
- NCHAR
- NVARCHAR2
- RAW
- TIMESTAMP
- BINARY_DOUBLE
- BINARY_FLOAT
- NUMBER
- DATE
- CLOB
- NCLOB
- BLOB
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:
- NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD-HH24.MI.SSXFF'
- NLS_DATE_FORMAT = 'YYYY-MM-DD'
- NLS_TIME_FORMAT = 'HH24.MI.SS'
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:
- Regular mapping
- Expression, including these types:
- Default expression that is generated by the Replication Center
- TO_CHAR function without format
- TO_CHAR function with format
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.
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.