Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

SQL Server Replication Using the OLE DB Provider for DB2

Host Integration Server 2006 supports using the Microsoft OLE DB Provider for DB2 and Microsoft SQL Server Replication.

When using transactional replication, object names such as tables and constraints can be longer in Microsoft SQL Server than in DB2. Therefore, when moving data from Microsoft SQL Server to DB2, one of the following strategies should be used for compatibility:

  • Create the Microsoft SQL Server tables and constraints so that the names do not exceed the limits of DB2. (For details, see the DB2 SQL Reference for the target platform and version of DB2.)
  • Create the Microsoft SQL Server tables and constraints with names up to the maximum supported by Microsoft SQL Server, and then run ALTER TABLE SQL scripts against the Microsoft SQL Server distribution database after replication has created the publication, but before creating the subscriptions.

For replication to work properly, SQL Server-to-DB2 data type mapping entries must be provided for several data types. These data type mappings must be stored in the Microsoft SQL Server MSdatatype_mappings table in the msdb database. Data type mapping entries must be provided to properly support the CHAR and VARCHAR data types when used to store SBCS character strings or binary data (BINARY and VARBINARY) with replication. The mappings for CHAR and VARCHAR are needed due to the limits for the boundary values using the OLE DB Provider for DB2. The mappings for binary data types are required because binary data is stored as character data using the OLE DB Provider for DB2.

This SQL Server msdb.MSdatatype_mappings table can be updated using a stored procedure that comes with SQL Server. The exec sp_add_datatype_mapping command is a stored procedure that comes with SQL Server. You should run these data mappings in the SQL Query Analyzer (Isqlw.exe). Each line of the exec sp_add_datatype_mapping procedure will add one data mapping to msdb.Msdatatype_mappings.

The Microsoft OLE DB Provider for DB2 when accessing DB2 on all platforms and versions does not support the following Microsoft SQL Server data types with direct mappings. The following is a summary of the limitations.

Microsoft OLE DB Provider for DB2 does not support DB2 GRAPHIC, VARGRAPHIC, and LONG VARGRAPHIC data types. Mappings to these DB2 types should be avoided.

Microsoft OLE DB Provider for DB2 does not support DB2 Large Object data types, such as CLOB, DBCLOB, and BLOB, which are used in DB2 Universal Database. Microsoft OLE DB Provider for DB2 does not support the DB2 BIGINT data types, which are used in DB2 Universal Database for Windows NT.

Microsoft SQL Server NCHAR, NVARCHAR, NTEXT, and SYSNAME data types do not map well to supported DB2 data types. The Microsoft SQL Server TIMESTAMP data type should be mapped to a DB2 CHAR FOR BIT DATA data type. The TIMESTAMP data type in SQL Server is used only for the purposes of table logging and not for end-user data. Therefore, you should not map the SQL Server TIMESTAMP data type to the DB2 TIMESTAMP.

The sample mapping table entries map Microsoft SQL Server TEXT and IMAGE to DB2 VARCHAR and DB2 VARCHAR FOR BIT DATA respectively. These Microsoft SQL Server data types can represent up to 2 gigabytes (GB) of data. In contrast, the DB2 VARCHAR data types can hold at most approximately 32 kilobytes (KB) of data. As such, data truncation may occur.

For Microsoft SQL Server data types, MONEY, SMALLMONEY, DECIMAL, and NUMERIC, data values can be replicated to DB2 correctly, but these floating-point data types are mapped to STRING instead of FLOAT. After these data values are replicated to DB2, the string values should be converted back to float before performing calculations. An alternative is to do the calculations and update using SQL Server before replicating these data values over to DB2.

This section contains:

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.