Programming Considerations When Using the OLE DB Provider for DB2

The Microsoft OLE DB Provider for DB2 provides pass-through support for SQL statements. No SQL parsing is provided. The user must know what SQL syntax is supported for the target DB2 implementation. For information about what SQL syntax is supported, see the specific DB2 SQL Reference and DB2 Application Programming and SQL Guide for the DB2-specific platform.

The OLE DB Provider for DB2 does not parse the SQL statements to qualify table names. Consequently, users of the OLE DB Provider for DB2 must use either two-part or three-part (fully qualified) object names when naming tables, views, and stored procedures in DB2. A two-part table name would consist of the user ID and table, <UserID>.<Table>. One-part names (just the table name) do not succeed unless the combination of the DB2 collection and schema name correspond directly to the OLE DB User ID. (The OLE DB DBPROPSET_DBINIT property is equal to the OLE DB DBPROP_AUTH_USERID property.)

All the OLE DB objects exposed by the OLE DB Provider for DB2 support aggregation. Each OLE DB object has two classes, one that delegates its IUnknown calls and one that controls the object as a whole.

The free-threading model is supported, allowing multiple threads to access the objects safely.

The current implementation of the OLE DB Provider for DB2 services all OLE DB Session, Command, and Rowset objects present in a given instance of the DataSource object through a single APPC connection or TCP/IP connection. One implication of this design is that if two Rowset objects, each created from a different OLE DB Session object, use explicit commitment control through the ITransaction interface, they will interfere with each other. When a Commit or Abort for one instance is invoked, all work for the DataSource object will be either committed or aborted. This may yield undesirable results. The work around to this problem is to instantiate two instances of the DataSource object.

The OLE DB Provider for DB2 does not work with OLE DB Session Pooling.

The OLE DB Provider for DB2 in Host Integration Server 2006 supports distributed transactions, DRDA Distributed Unit of Work, and can participate in a distributed transaction coordinated by Microsoft Distributed Transaction Coordinator.

This option requires that the SNA LU 6.2 service is selected as the network transport and Microsoft Transaction Server (MTS) is installed. The Microsoft OLE DB Provider for DB2 does not support OLE DB automatic transaction enlistment under Microsoft Transaction Server.

The Microsoft Data Access Components (MDAC) support the option of using a client cursor engine. This service component is implemented as part of OLE DB, ADO, and Remote Data Services (RDS). When using ADO, a client cursor is enabled by setting the CursorLocation property on the recordset to adUseClient. When using the ADO Client Cursor Engine with DB2 for OS/390, you must set the OLE DB Provider for DB2 Auto Commit Mode property in the data link or connection string to FALSE. This is not required when connecting to DB2 for OS/400.

The OLE DB Provider for DB2 included with Host Integration Server 2006 supports updating capabilities when used with a client cursor engine when the following requirements are met:

  • To support updates (UPDATE, INSERT, and DELETE) using a client cursor engine, the values in at least one column in the target table must be unique.

When the intent is to update records, DB2 requires that the SQL SELECT statement also include the FOR UPDATE option. For example, to select all records from the AUTHORS table in the DB2 collection called PUBS with intent to update requires the following SQL syntax:


When using DB2 for MVS V4R1 and DB2 for OS/400 V3R2, there are further requirements to indicate the columns that you intend to update. For example, to update the AU_LNAME and AU_FNAME columns in the PUBS.AUTHORS table, the following SQL syntax must be used:


This section contains: