Distributed Query Support Using the OLE DB Provider for DB2

The OLE DB Provider for DB2 supports remote database access when configured as a linked server to SQL Server using distributed queries. The distributed queries feature of SQL Server is sometimes referred to as the Distributed Query Processor (DQP). SQL Server 2000 supports distributed queries to the OLE DB Provider for DB2 supplied with Host Integration Server 2006.

When using Microsoft SQL Server 2000 distributed queries and the OLE DB Provider for DB2 supplied with Host Integration Server, the following OLE DB provider options (displayed in the same order as in the Microsoft SQL Server Enterprise Manager) are supported.

Provider options in SQL Server 2000 Comments

Dynamic parameter

SQL Server generates parameterized queries as an optimization for providers that support the '?' parameter marker syntax for parameterized queries in dynamic SQL.

This option should not be enabled for the OLE DB Provider for DB2.

Nested queries

SQL Server generates nested queries for providers that support nested SELECT queries in the FROM clause. Some versions of DB2 have support for nested queries.

This option should not be enabled for OLE DB Provider for DB2.

Level zero only

A level zero OLE DB provider is a basic provider that does not support commands, and only level zero OLE DB interfaces are invoked against the provider. The Microsoft OLE DB Provider for DB2 is not a basic provider and uses commands.

This option should not be enabled for the OLE DB Provider for DB2.

Allow InProcess

SQL Server enables the OLE DB provider to be instantiated as an in-process server. The default behavior is to instantiate the OLE DB provider outside the SQL Server process. Instantiating the provider outside the SQL Server process protects the SQL Server process from errors in the OLE DB provider.

SQL Server requires an in-process server for handling specific types of data including long columns, text, and image data. The OLE DB Provider for DB2 does not currently support the DB2 Large Object (LOB) types.

This option may be enabled or disabled for the OLE DB Provider for DB2, but this option is normally unnecessary when using SQL Server 2000.

Non transacted updates

SQL Server disables support for transacted updates if this option is enabled. The Microsoft OLE DB Provider supports transactions, so this option is not appropriate.

This option should not be enabled for the OLE DB Provider for DB2.

Index as access path

SQL Server uses the OLE DB Index object with OLE DB providers that support this feature. The OLE DB Provider for DB2 does not currently support the Index object, so this option is not appropriate.

This option should not be enabled for the OLE DB Provider for DB2.

Disallow ad hoc accesses

SQL Server uses this option when only an ODBC driver is available.

This option should not be enabled for the OLE DB Provider for DB2.

The OLE DB provider options for managing distributed queries can be set using SQL Server Enterprise Manager. In the left pane of SQL Server Enterprise Manager, right-click a SQL Server instance and then select the Security tree to define a new linked server or change the properties of an existing linked server. Right-click an existing linked server or create a new linked server. On the General tab, select the Other data source button and select the OLE DB Provider for DB2 for the Provider name from the drop-down list box. Click the Provider Options button below the selected OLE DB provider to set the options for distributed queries. Select the appropriate check boxes to enable an option for this linked server. Note that these options operate at the provider level. When the appropriate options are set for the OLE DB Provider for DB2, these settings apply to all linked server definitions using the same OLE DB Provider for DB2.

The OLE DB Provider for DB2 performs data type and code page conversions on behalf of the OLE DB consumer application, in this case the SQL Server Distributed Query Processor. First, the provider converts DB2 numeric and datetime data types to OLE DB numeric and datetime data types. The provider does this on a best-match basis using information provided by DB2 in the DRDA SQL reply data structure for the result set. Second, the provider converts the character data from the DB2 Coded Character Set Identifier (CCSID) to the Windows ANSI code page. For example in the case of an SQL SELECT fetch of data from DB2 for OS/400, the provider converts character data from EBCDIC to UNICODE and UNICODE to ANSI. The source EBCDIC CCSID value comes from the table column descriptor or from the DB2 database if the column CCSID is undefined. The target ANSI code page value comes from the value of the PC Code Page data source property. For example, to convert character data from CCSID 1026, IBM EBCDIC Turkish (Latin-5), to ANSI code page 1254, Turkish, the PC CodePage would need to be set to 1254. OLE DB consumers may convert the data once again from OLE DB to some native data type. In this case, the OLE DB consumer application is a distributed query processor. When using distributed queries, SQL Server does perform numeric and datetime conversions from OLE DB to SQL Server data types.

UNIONs in SELECT statements are not supported by the current version of the OLE DB Provider for DB2 when used with distributed queries. For example, the following SELECT statement will fail:

SELECT * FROM ( SELECT TITLE_ID FROM SNA.TITLE )

The SQL parser built into the OLE DB Provider for DB2 does not properly parse these UNION statements in a way compatible with DB2. The preceding statement will generate a type 199 error "Keyword FROM not expected. Valid Tokens: LEFT CROSS INNER EXCEPTION." When performing the same query with correlation names added, the error becomes a type 104 error "Token . was not valid. Valid tokens: LEFT CROSS INNER EXCEPTION."

Linked server definitions can also be created or deleted using stored procedures as well as through the SQL Server Enterprise Manager.

When creating linked server definitions, the @catalog parameter of the sp_addlinkedserver procedure corresponds to the OLE DB provider-specific Initial Catalog property. Additionally, when creating linked server definitions, you can use the contents of a universal data link (.udl) file for the @provstr parameter value or enter the short provider string keyword arguments (for details, see the ADO ConnectionString property), which are consumed through the OLE DB DBPROP_INIT_PROVIDERSTRING (Extended Properties) property.

The following example illustrates how to create a linked server for DB2/MVS using an SNA connection.

USE master
GO
EXEC sp_dropserver 'DB2MVS_SNA', 'droplogins'
GO
EXEC sp_addlinkedserver 
    @server = 'DB2MVS_SNA', 
    @srvproduct = 'Microsoft OLE DB Provider for DB2',
    @provider = 'DB2OLEDB', 
    @catalog = 'P390D37',
    @provstr='InitCat=P390D37;NetLib=SNA;LOCALLU=MVSRUS;
       REMOTELU=P390L37;MODENAME=IBMRDB;PkgCol=MSPKG;DefSch=DB2DEMO'
GO
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname='DB2MVS_SNA', 
    @useself=false, 
    @locallogin=NULL, 
    @rmtuser='wnw999', 
    @rmtpassword='wnw999'
GO

The following example illustrates how to create a linked server for DB2/MVS using a TCP/IP connection.

USE master
GO
EXEC sp_dropserver 'DB2MVS_IP', 'droplogins'
GO
EXEC sp_addlinkedserver 
    @server = 'DB2MVS_IP', 
    @srvproduct = 'Microsoft OLE DB Provider for DB2',
    @provider = 'DB2OLEDB', 
    @catalog = 'P390D37',
    @provstr='InitCat=P390D37;NetLib=TCPIP;NetAddr=MVSrUS;
        NetPort=446;PkgCol=MSPKG;DefSch=DB2DEMO'
GO
EXEC sp_addlinkedsrvlogin 
    @rmtsrvname='DB2MVS_IP', 
    @useself=false, 
    @locallogin=NULL, 
    @rmtuser='wnw999', 
    @rmtpassword='wnw999'
GO

The following example illustrates several DB2 linked server queries using a TCP/IP connection.

/* SELECT using four-part linked server query:   */
/*    <Linked Server>.<Catalog>.<Schema>.<Table> */
SELECT * FROM DB2MVS_IP.P390D37.DB2DEMO.DEPARTMENT
SELECT DEPTNAME FROM DB2MVS_IP.P390D37.DB2DEMO.DEPARTMENT WHERE DEPTNO = 'A00'

/* SELECT using pass-through OPENQUERY with      */
/*     three-part naming convention              */
SELECT * FROM OPENQUERY(DB2MVS_IP,"SELECT * FROM P390D37.DB2DEMO.EMP_ACT")

/* SELECT using pass-through OPENROWSET with     */
/*     two-part naming convention                */
SELECT * FROM OPENROWSET 
    (
    'DB2OLEDB', 
    'InitCat=P390D37;NetLib=TCPIP;NetAddr=MVSrUS;NetPort=446;
         PkgCol=MSPKG;DefSch=DB2DEMO;User ID=WNW999;Password=WNW999', 
    'SELECT * FROM DB2DEMO.EMPLOYEE'
    )

The following example illustrates several SQL Server Views to access a DB2 linked server using a TCP/IP connection.

/* Create SQL Server View using DB2 linked server query */
USE DB2Demo
GO
DROP VIEW DB2VIEW
GO
CREATE VIEW DB2View
AS
SELECT ORD_ID, ORDERDATE, ORDERSTATUS
    FROM DB2MVS_IP.P390D37.DB2DEMO.ORDERS
GO

/* Access Db2 data using SQL Server View */
SELECT * FROM DB2Demo.dbo.DB2View

The following example illustrates the SQL SELECT, UPDATE, and DELETE commands using four-part linked server queries to DB2 over a TCP/IP connection.

SELECT * FROM DB2MVS_IP.P390D37.DB2DEMO.CUSTOMERS
INSERT INTO DB2MVS_IP.P390D37.DB2DEMO.CUSTOMERS VALUES (1002,
    'password', 'User', 'DB2Demo', 'One Microsoft Way', 'Redmond',
    '425-882-8080', 'WA', '98052', 'mssna@microsoft.com')
UPDATE DB2MVS_IP.P390D37.DB2DEMO.CUSTOMERS  SET PHONE = '206-882-8080'
    WHERE CUST_ID = 1002
DELETE FROM DB2MVS_IP.P390D37.DB2DEMO.CUSTOMERS WHERE CUST_ID = '1002'

The following example illustrates invoking DB2 linked server queries from SQL Server stored procedure.

USE DB2Demo
DROP PROCEDURE spGetOrderDetails_DB2MVS_IP

CREATE PROCEDURE spGetOrderDetails_DB2MVS_IP
    @ORDER_ID INT
AS
BEGIN
    SELECT a.*, b.Title, b.SubTitle, b.Author, b.ISBN, b.Weight  
        FROM DB2MVS_IP.P390D37.DB2DEMO.ORDERDETAILS AS a
    INNER JOIN Titles AS b
        ON a.Prod_Id = b.TitleId   
        WHERE a.Ord_Id >= @ORDER_ID
END

EXEC spGetOrderDetails_DB2MVS_IP 1001

For more information about the preceding stored procedures, see the Microsoft SQL Server Books Online.

INSERT, UPDATE, and DELETE statements when using four-part linked server queries invoke the client cursor engine (CCE). This means that some statements may fail or update incorrect columns. For example, if there is not a unique key column on the target tables or if there are not enough unique values for the CCE to accurately determine which columns to update, the statement may fail. For INSERT, UPDATE, and DELETE linked server queries, there must be either a unique index or unique values.

Using distributed queries, SQL Server returns all rows for the target table locally, and then sorts through results using the client cursor engine to find a unique value (composed of all columns if need be). If the CCE cannot find a unique value, the distributed query fails to perform the INSERT, UPDATE, or DELETE statement.

The OLE DB Provider for DB2 does not support bookmarkable cursors. Therefore, you may be able to optimize your queries using OPENQUERY syntax rather than the traditional 4-part naming of Linked Server syntax. The following examples show how you might optimize your queries using OPENQUERY.

SELECT * FROM OPENQUERY (DSN1, 'SELECT * FROM DSN1D037.NWIND.AREAS')

SELECT * FROM OPENQUERY(DSN1,'SELECT * FROM NWIND.AREAS') WHERE AREAID = 99999

INSERT INTO OPENQUERY(DSN1,'SELECT * FROM DSN1D037.NWIND.AREAS') (AREAID, AREADESC, REGIONID) VALUES (99999, 'Everywhere', 999)

UPDATE OPENQUERY(DSN1,'SELECT * FROM DSN1D037.NWIND.AREAS') SET AREADESC = 'Nowhere' WHERE AREAID = 99999 

DELETE FROM OPENQUERY(DSN1,'SELECT * FROM DSN1D037.NWIND.AREAS') WHERE AREAID = 99999

Note, however, that using the OLE DB Provider for DB2, SQL Server distributed queries return the incorrect precision for DECIMAL data types when running OPENQUERY. The correct precision is returned for DECIMAL data types with using a linked server query.

Show: