Mapping Data Types

For each source column you want to replicate, you must select a data type for the corresponding destination column. Usually it is best to choose a destination data type that closely corresponds to the source data type, and to set any modifiable destination Precision and Scale settings to match the corresponding source settings.

However, some data types do not have exact equivalents in other environments, and allowable Precision and Scale settings vary for some data types that are otherwise similar. If you are replicating floating point data from one database system to another, there may be slight changes in the data precision due to the architectural differences of the source and target database systems. The data format and precision for dates and time also commonly differ among database systems.

SQDR Plus informationData type differences for columns that are used as a primary key or index can be particularly problematic if they are relied upon to track change data for incremental subscriptions. A key that is unique on the source may no longer be unique on the target if the target supports a different precision value or otherwise changes the value. If you are using SQDR with Db2 for i or Oracle, you can configure SQDR to add a RRN to the staging table to provide a field that will be unique on both the source and target databases.

This topic describes the major differences in how data types are supported by Db2, SQL Server, and Oracle database management systems.

Db2 and SQL Server Data Type Restrictions

The Decimal type exists for both Db2 and SQL Server, but has different allowable values for Precision in the two environments: Db2 allows up to 31 digits, but SQL Server only allows up to 28. For very large decimal values, you may prefer to use a destination data type that uses exponential notation, such as Float or Real, which can preserve the proper magnitude of the data, though it introduces the possibility of rounding errors for replications with a SQL Server destination.

SQL Server does not provide equivalents for Db2 types Date and Time. For these source column types, Replicator Manager sets the default destination data type to Char, which preserves the data, although in a slightly less convenient form. If you manually change the destination data type to the SQL Server type Datetime, Data Replicator manages the difference between source and destination column structures as follows if the records are inserted using ODBC:

If you replicate a numeric source column type to a non-numeric destination column type, be sure that the destination column has a sufficiently large Precision setting to contain all the characters in the data to be replicated, including decimal point characters.

If you want to replicate a Db2 column of type Timestamp, set the corresponding SQL Server destination column to use either a data type of Datetime or a data type of Char and a Precision of 26. Do not attempt to use the SQL Server Timestamp data type, which is reserved for columns in which SQL Server obtains time information from the local computer system, rather than via replication.

Your choice between destination data types of Datetime and Char should be based on the form and precision of the data your application needs. If you replicate a Db2 Timestamp column to a SQL Server Datetime column, the date and time nature of the data is preserved, but the data is truncated because the SQL Server Datetime data type stores fewer characters than the Db2 Timestamp data type. If you replicate a Db2 Timestamp column to a SQL Server column of type Char with a Precision of 26, the full precision of the Db2 Timestamp data is preserved with minimum use of disk space, but SQL Server treats it as simple character data, rather than date and time data.

If you define a replication to use the SQL Server Bulk Copy facility (BCP) for inserting records, you can insert a derived column to handle the differences between the Db2 Date and Time data types and the SQL Server DATETIME data type. Delete the current column that has the DATE data type from the Columns tab of the properties for the subscription, and insert a derived column that has the following format, where DATEFIELD is the name of the Db2 source DATE column:

Expression: TIMESTAMP(CHAR(DATEFIELD)||'-00.00.00.000000')

Then select Datetime for the data type of the derived column.

Db2 and Oracle

When replicating from Db2 to Oracle, DATE fields are now mapped to Oracle DATE fields (older versions of SQDR mapped them to CHAR(10)).

Oracle Data Type Restrictions

ODBC drivers for Oracle support more restricted sets of data types than either SQL Server or Db2. If you use an ODBC driver for Oracle to connect to Oracle from an application other than Data Replicator, and if you then use the SQL CREATE command to create a new table, the data type restrictions may not be apparent. If you specify an unsupported data type (such as INTEGER) for a table column, a supported data type (such as DECIMAL) may be automatically substituted.

When you work with an Oracle destination table in StarQuest Data Replicator, Replicator Manager lists only the destination data types actually supported by your current ODBC driver for Oracle. However, as with any situation where an exact equivalent of the source data type is not supported for the destination, Data Replicator attempts to substitute appropriate default values for the destination column data type, precision, and scale. When replicating Oracle Date fields to DBS, Data Replicator sets the default destination data type to Timestamp because the Oracle Date data type more closely resembles a Db2 Timestamp. Refer to Defining Derived Columns for an example of how you can add a derived column to properly replicate an Oracle Date column to a Db2 Date field if you prefer to have the destination use the Date data type.

Oracle allows negative values in columns of the data type DATE. However, the corresponding ODBC data type TIMESTAMP and Db2 and SQL Server equivalents do not allow negative values. If you attempt to replicate an Oracle DATE column that contains negative values, one or more problems may occur. If the destination database is SQL Server, the replication may fail or may insert incorrect data into the destination table. If the destination database is Db2, the processor utilization on the destination computer may climb to near 100%. If this occurs, stop and restart the Replicator Service to return processor utilization to normal levels.

If your source system supports CASE expressions in SQL statements, you can avoid this situation by creating a derived destination column that uses a CASE expression to convert negative DATE values to appropriate equivalents. These changes take place during replication, and do not affect the actual source data. Alternatively, you can add a WHERE clause to avoid replicating any records that contain negative values in a DATE column.

See Also

Error Replicating Text or Image Data to SQL Server
Error Replicating TIMESTAMP
Row Count Errors