Considerations and restrictions for the replication of timestamp data
The following considerations and restrictions exist for the replication of timestamp data:
- Time zones in string format, such as Americas/Toronto, are not supported.
- Only time zone offsets in the form of +/-HH:MM will be supported for TIMESTAMP (with variable precision) and TIMESTAMP WITH TIME ZONE data types.
- String representations of TIMESTAMP WITH TIME ZONE, whether expressed as an offset or with a region name, and BC dates are not supported in expressions
- If you are replicating data using TIMESTAMP WITH TIME ZONE columns, you need to ensure that your database has the most current Olson time region database otherwise data discrepancies can occur. Follow your database vendor's instructions on how to upgrade to the latest version of the time region database.
- TIME fields with fractional seconds are not supported
- CDC Replication does not support the replication of data types INTERVAL, TIMESPAN, and TIME WITH TIMEZONE.
- The CDC Replication Engine for DB2® for LUW supports the replication TIMESTAMP (with variable precision) in DB2 LUW version 9.7 and later
- The CDC Replication Engine for DB2 for z/OS® supports the replication TIMESTAMP (with variable precision) and TIMESTAMP WITH TIME ZONE in DB2 for z/OS version 10 and later.
- TIMESTAMP WITH TIME ZONE columns may only be mapped to other TIMESTAMP WITH TIME ZONE columns. If other mappings or transformations are required, expressions will need to be used.
- In the CDC Replication Engine for DB2 for z/OS version
10.2 and later, replicated TIMESTAMP columns can have varying scale
from 0 to 12 digits, as can TIMESTAMP WITH TIME ZONE. Previous versions
of the CDC Replication Engine for DB2 for z/OS only
supported default TIMESTAMP columns which had a fixed length of 26
bytes and scale of 6 - the scale information was not returned by the
following column information APIs.
- chcGetSrcColInfoByName ();
- chcGetSrcColInfoBySeq ();
- chcGetTgtColInfoByName ();
- chcGetTgtColInfoBySeq ();
Since the the length, precision and scale can now vary, those values are now returned by the column information APIs. The length is the string length of the TIMESTAMP and can be used as the buffer size for the timestamp getters and setter APIs.
- chcGetTimestamp ();
- chcGetTimestampWithTimeZone ();
- chcSetTimestampWithTimeZone ();
- If you are replicating the TIMEZONE data type and the source and target have a different TIMEZONE value, the data replicated will be adjusted and use the source TIMEZONE value.
- TIMESTAMP fields with negative years sourced from Oracle databases are supported. If the target database does not support negative years, user exits can be used to resolve the issue.
Understanding how CDC Replication handles TIMESTAMPS with negative years
TIMESTAMP fields with negative years sourced from Oracle databases are supported. If the target database does not support negative years, user exits can be used to resolve the issue.
The format for TIMESTAMP(12) columns as passed to expressions or user exits is eCCCC-MM-DD-HH.MM.SS[.FFFFFFFFFFFF], where the epoch, e, is the '-' character if the year is negative. The epoch character is not included otherwise for compatibility with the legacy representation of positive years.
In expressions or user exits that request more data than the actual length of the timestamp data, for example. %TOCHAR(<timestamp col>,35), CDC Replication will extend the data by the following means:
- The CDC Replication Engine for DB2 for z/OS will show as many fractional digits as supplied, right pad with ' ' (blank), leave the dash between the data and time, and leave the time delimiter as a period.
- All other engines will right pad with 0 (zero) characters, replace the dash between the date and time with a blank and replace the time delimiter with a colon.
The format for TIMESTAMP WITH TIME ZONE columns is: eCCCC-MM-DD-HH.MM.SS[.FFFFFFFFFFFF] sHH:SS, where the epoch, e, is the '-' character if the year is negative. The epoch character is not included otherwise for compatibility with the legacy representation of positive years.
The timestamp portion is in local time (in the database's external representation). The time zone offset immediately follows the timestamp portion and the sign, s, is not optional – it will be either '+' or '-' to indicate the time zone offset relative to UTC.