OLE DB Provider for DB2

The Microsoft OLE DB Provider for DB2, distributed with Microsoft® Host Integration Server 2000, allows Microsoft SQL Server™ 2000 distributed queries to query data in DB2 databases.

To create a linked server to access a DB2 database

1. Install the Windows NT Client for Host Integration Server 2000 or the Windows 9x Client for Host Integration Server 2000 on a computer running an instance of SQL Server. Select the options to install the OLE DB Provider for DB2 and the network components needed to communicate with an IBM computer running in an SNA network.
   
   
2. Determine the connection string the OLE DB Provider for DB2 needs to access the DB2 data source you want to query. The best way to determine a connection string is to build a Data Link file using the Host Integration Server New OLE DB Data Source application. For more information, see the Microsoft Host Integration Server 2000 documentation.
   
   
3. Execute sp_addlinkedserver to create a linked server, specifying DB2OLEDB as the provider_name, the name of the DB2 catalog containing the data you want to access as catalog, and the connection string from Step 2 as provider_string.

   This example shows how to use sp_addlinkedserver to create a linked server definition accessing a DB2 database:

   EXEC sp_addlinkedserver @server = 'DB2SRV',
      @srvproduct = 'Microsoft OLE DB Provider for DB2',
      @catalog = 'SEATTLE',
      @provider = 'DB2OLEDB',
      @provstr =
        'NetLib=SNA;NetAddr=;NetPort=;RemoteLU=SEATTLE;LocalLU=LOCAL;
         ModeName=QPCSUPP;InitCat=SEATTLE;
         Default Schema=WNW3XX;PkgCol=WNW3XX;TPName=;Commit=YES;
         IsoLvl=NC;AccMode=;CCSID=37;PCCodePage=1252;BinAsChar=NO;
         Data Source=Seattle_WNW3XX'

4. Execute sp_addlinkedsrvlogin to create login mappings from SQL Server 2000 logins to DB2 logins.

   This example maps the SQL Server 2000 login SQLJoe to DB2 login DB2Joe:

   EXEC sp_addlinkedsrvlogin 'DB2SRV', false, 'SQLJoe', 'DB2Joe', 'JoePwd'
   

After completing these steps, you can use the linked server name DB2SRV as the server name in four part names and as linked_server in the OPENQUERY function. For example:

SELECT *
FROM DB2SRV.SEATTLE.WNW3XX.DEPARTMENT

Or

SELECT *
FROM OPENQUERY(DB2SRV, 'SELECT * FROM SEATTLE.WNW3XX.EMP_ACT')

When the distributed queries against DB2 data sources involve NULL comparisons, use IS NULL or IS NOT NULL rather than comparison operators, such as =, <, or >. In addition, INSERT statements should supply values for all columns in a table even if certain columns in the table can be NULL or have default values.