SQL Replication SQL ReplicationSQL Replication supports new data types that were introduced with DB2® for Linux, UNIX, and Windows Version 9.7 to make it easier to migrate applications to DB2.
Some of the new data types require special considerations for a replication environment. The following sections provide details:
SQL replication supports replication of TIMESTAMP data with extended precision that ranges from TIMESTAMP(0) to TIMESTAMP(12). You can map columns of non-matching precision. If both the source and target databases and Capture and Apply are Version 9.7 or newer, the source data is padded or truncated at the target database.
In a mixed-level environment where only the source DB2 is at Version 9.7, TIMESTAMP columns might also require padding or truncation. Replication of such columns can occur only when both Capture and Apply are at Version 9.7 or later. For example, if you replicated a source at V9.7 to a target at V9.5 and a registered table included a TIMESTAMP(12) column, the V9.7 Apply would truncate six-digits from the fractional seconds portion of the TIMESTAMP value. The truncation is necessary because DB2 Version 9.5 does not support extended precision, and so for V9.5 databases TIMESTAMP values have a fractional seconds portion that equates to the default V9.7 precision of TIMESTAMP(6). Table 1 shows the value at the source and resulting truncated value at the target.
Note: When handling these new data types, SQL
replication treats a DB2 for z/OS® source or target the same
as DB2 for Linux, UNIX,
and Windows Version 9.5
or older.
| Source value in TIMESTAMP(12) | Target value in TIMESTAMP(6) |
|---|---|
| 2009-07-10-10.33.42.458499823012 | 2009-07-10-10.33.42.458499 |
If the target database is older than V9.7, TIMESTAMP values of lower precision than the default TIMESTAMP(6) are padded automatically by DB2 so the fractional seconds portion contains six places.
The date compatibility option stores the DATE type with an additional time portion (HH:MM:SS). This format conforms to the date representation by other relational database management systems such as Oracle, where the DATE data type includes YYYY-MM-DD HH:MM:SS.
SQL Replication treats databases without date compatibility the same as DB2 databases prior to V9.7, and the same as DB2 for z/OS subsystems. When date compatibility is enabled, DB2 handles columns that are defined as DATE in the same way that it handles columns defined as TIMESTAMP(0).
Enable the DATE as TIMESTAMP(0) support by setting bit position number 7 (0x40) of the DB2_COMPATIBILITY_VECTOR registry variable before you create a database. With SQL Replication you can create the following column mappings between DATE and TIMESTAMP(0):
The NUMBER data type supports applications that use the Oracle NUMBER data type. DB2 treats NUMBER data internally as DECFLOAT if no precision or scale are specified, and as DECIMAL with precision or scale if these attributes are specified.
Because SQL Replication already supports DECFLOAT and DECIMAL, you can map columns defined with any of these three numeric types to each other: NUMBER to DECFLOAT or DECIMAL, DECFLOAT to NUMBER or DECIMAL, and DECIMAL to NUMBER or DECFLOAT.
