INFO: DB2OLEDB Supports UPDATE/DELETE from SQL Linked Server

The Microsoft OLE DB Provider for DB2 (DB2OLEDB) that ships with Host Integration Server 2000 or SNA Server 4.0 Service Pack 4 supports SELECT, INSERT, UPDATE, and DELETE statements from SQL Server Linked Server.

All earlier versions of DB2OLEDB support only SELECT and INSERT statements when used with SQL Server Linked Server.

For an UPDATE or DELETE, the minimum requirements are as follows:

• Host Integration Server 2000 or SNA Server 4.0 Service Pack 4
• SQL Server 2000 or SQL Server 7.0 Service Pack 2

To execute INSERT, UPDATE, and DELETE statements using four-part linked server queries, the SQL Server Distributed Query Processor (DQP) feature uses bookmark support in the underlying OLE DB provider. The OLE DB Provider for DB2 does not support bookmarks. However, when DQP loads the provider using service components, the client cursor engine (CCE) is invoked. The CCE provides scrollable cursors and bookmarks.

INSERT, UPDATE, and DELETE statements when using four-part linked server queries can be supported using the CCE. However, some statements may fail or may update incorrect columns when there is no unique key column on the target tables, or when there are not enough unique values for the CCE to accurately determine which columns to update. In these cases, unpredictable results may occur, or DQP may fail to perform the update.

SELECT, INSERT, UPDATE and DELETE are also supported with passthrough query using OpenQuery syntax.

For additional information on DB2OLEDB and Linked Server, click the article numbers below to view the articles in the Microsoft Knowledge Base: