• Article

OLE DB Provider for DB2

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

Note

For information about how to install and configure the OLE DB provider, see the documentation that is included with the provider.

To create a linked server to access a DB2 database

  1. Install Host Integration Server Client software on a computer that is running an instance of SQL Server and select the options to install the OLE DB Provider for DB2.

  2. Determine the connection string the OLE DB Provider for DB2 requires 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 by using the Host Integration Server New OLE DB Data Source application. For more information, see the Microsoft Host Integration Server documentation.

  3. Execute sp_addlinkedserver to create a linked server, specifying DB2OLEDB as the provider_name, the name of the DB2 catalog that contains the data you want to access as catalog, and the connection string from step 2 as provider_string.

    The following example shows how to use sp_addlinkedserver to create a linked server definition that accesses 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 logins to DB2 logins.

    The following example maps the SQL Server login SQLJoe to DB2 login DB2Joe:

    EXEC sp_addlinkedsrvlogin 'DB2SRV', false, 'SQLJoe', 'DB2Joe', 'x894jd03?'

    Note

    Passwords will be transmitted unencrypted. Passwords may be visible in data source definitions and scripts saved to disk, in backups, and in log files. Never use an administrator password in this kind of connection. Consult your network administrator for security guidance specific to your environment.

After you complete the previous 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 ISNULL or IS NOT NULL instead of comparison operators, such as equals (=), greater than (>), or less than (< ). Additionally, 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.