Distributed Query Support Using the OLE DB Provider for DB2

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

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

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 radio button and select the OLE DB Provider for DB2 for the Provider name from the dropdown listbox. Click the Provider Options button below the selected OLE DB provider to set the options for distributed queries. Check the appropriate checkboxes 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.

When using Microsoft SQL Server 7.0 and distributed queries with the OLE DB Provider for DB2 supplied with Host Integration Server 2000 or SNA Server 4.0, the Allow InProcess option must be enabled. This option is needed because SQL Server 7.0 will pass the proper authentication across the remote procedure call only when the OLE DB Provider for DB2 is configured for Allow InProcess. When creating a linked server for use with the OLE DB Provider for DB2 using SQL Server 7.0, you can use the Microsoft SQL Server Enterprise Manager to configure the OLE DB options for linked servers. Configure the OLE DB Provider for DB2 to be loaded in-process (click the options button and check Allow InProcess). This will enable SQL Server 7.0 to initialize an instance of the OLE DB Provider for DB2 for distributed queries. Without the Allow InProcess option enabled, the user will receive the following error:

Server: Msg 7302, Level 16, State 1, Line 12; Could not create an instance of OLE DB provider 'DB2OLEDB'

The OLE DB Provider for DB2 performs data type and code page conversions on behalf of the OLE DB consumer application, in this case SQL Server's Distributed Query Processor. First, the provider will convert DB2 numeric and datetime data types to OLE DB numeric and datetime data types. The provider will do 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 will convert 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, then 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 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 above 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, one can use the contents of a UDL for the @provstr parameter value or enter the short provider string keyword arguments (see the ADO ConnectionString property for details), which are consumed via the OLE DB DBPROP_INIT_PROVIDERSTRING (Extended Properties) property.

The sample below 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 sample below 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 sample below 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 sample below 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 sample below 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 sample below 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

Note: for more information on the above stored procedures, see the Microsoft SQL Server books online.

INSERT, UPDATE and DELETE statements when using four-part linked server queries will 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 there are not enough unique values for the CCE to accurately guess which columns to update. For INSERT, UPDATE and DELETE linked server queries, there must be either a unique index or unique values.

Using distributed queries, SQL Server will return all rows for the target table locally, then sort through results using the client cursor engine to find a unique value (composed of all columns if need be). If the CCE can't find a unique value, then the distributed query will fail to perform the INSERT, UPDATE, or DELETE statement.

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