FIX: Data replicated into incorrect table in DB2

View products that this article applies to.

Symptoms

Data that are being replicated from Microsoft SQL Server to DB2 may be replicated to an incorrect location after you apply the Microsoft OLE DB Provider for DB2 (DB2OLEDB) update. For more information about the update, click the following article number to view the article in the Microsoft Knowledge Base:

329853� VSD puts spaces around the table name and causes an error with DB2OLEDB

For example, a table named TABLE1 that is being replicated to DB2/MVS may be incorrectly replicated to the following location:

DB2USER.QUALIFIER.TABLE1

In this scenario:

DB2USER is the User ID specified in the connection string of OLE DB Provider for DB2 or the user name in the Universal Data Link (UDL) of OLE DB Provider for DB2.
QUALIFIER is the Default Schema specified in the connection string or the UDL.
TABLE1 is the name of the table that is being replicated.

Before you applied the DB2OLEDB update described in article 329853, TABLE1 was correctly replicated to the following location:

QUALIFIER.TABLE1

↑ Back to the top

Cause

After you apply the Host Integration Server 2000 DB2OLEDB update that is described earlier in this article, the DB2OLEDB encloses table names in double quotation marks ("). DB2 qualifies table names enclosed in double quotation marks with the DB2 authorized User ID that was specified in the OLE DB Provider for DB2 connection string or UDL file. Therefore, the replicated data is put in the wrong destination table.

↑ Back to the top

Resolution

Service pack information

To resolve this problem, obtain the latest service pack for Microsoft Host Integration Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

328152� How to obtain the latest service pack for Host Integration Server 2000

Hotfix information

The global version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.

   Date         Time   Version      Size    File name
   --------------------------------------------------
   06-Mar-2003  11:50  5.0.0.900   311,568  Crtpkg.dll
   06-Mar-2003  11:49  5.0.0.900   667,920  Db2oledb.dll
   06-Mar-2003  11:50  5.0.0.900    53,552  Ddmstr.dll
   06-Mar-2003  11:50  5.0.0.900    78,096  Mseidb2c.dll
   06-Mar-2003  11:49  5.0.0.900   880,912  Mseidb2d.dll
   06-Mar-2003  11:50  5.0.0.900    24,848  Mseidpm.dll
   06-Mar-2003  11:50  5.0.0.900   262,416  Mseidrda.dll
   06-Mar-2003  11:49  5.0.0.900   147,728  Mseidt.dll
   06-Mar-2003  11:50  5.0.0.900    65,808  Crtpkg.exe
   01-Oct-2002  12:17  5.0.0.900    82,192  Ddmserv.exe

NOTE: Because of file dependencies, the most recent fix that contains the preceding files may also contain additional files.

↑ Back to the top

Status

Microsoft has confirmed that this is a problem in Microsoft Host Integration Server 2000 Service Pack 1.

This problem was corrected in Microsoft Host Integration Server 2000 Service Pack 2.

↑ Back to the top

More information

To disable support for the DBLITERAL_QUOTE_PREFIX and DBLITERAL_QUOTE_SUFFIX, you can apply the updated Host Integration Server 2000 OLE DB Provider for DB2, and then add the following extended property to the OLE DB connection string:

Extended Properties="Quoted Prefix=false"

The following is an example of a connection string that has DBLITERAL_QUOTE_PREFIX and DBLITERAL_QUOTE_SUFFIX support enabled, which is the default setting:

Provider=DB2OLEDB;Cache Authentication=False;Password=password;Persist Security Info=True;User ID=USER1;Initial Catalog=DB2TEST;Data Source=DB2MVS;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=DB2MVS;Network Port=446;Package Collection=USER1;Default Schema=USER1;Alternate TP Name="";Process Binary as Character=False

The following is the same connection string with support for DBLITERAL_QUOTE_PREFIX and DBLITERAL_QUOTE_SUFFIX disabled:

Provider=DB2OLEDB;Cache Authentication=False;Password=password;Persist Security Info=True;User ID=USER1;Initial Catalog=DB2TEST;Data Source=DB2MVS;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=DB2MVS;Network Port=446;Package Collection=USER1;Default Schema=USER1;Alternate TP Name="";Process Binary as Character=False;Extended Properties="Quoted Prefix=false"

↑ Back to the top