Microsoft OLE-DB Provider for DB/2 ( for SQL Server v2014 )

Download

Microsoft bundles a database connectivity provider for DB/2.

It is bundled within the Feature Pack.

Depending on the version of SQL Server you have installed, you will be downloading a different package.

For us, we are on SQL Server 2014.  And, so will be downloading the Feature Pack for that version.

Download Link

Microsoft SQL Server Feature Pack is available here

Link

Download

Link

Image

Details

There are two files, ENU\DB2OLEDBV5_x64.msi and ENU\DB2OLEDBV5_x86.msi.

Depending on your OS bitness, SQL Server bitness, and use-cases Server ( Engine [ Linked Server], Development [Business Intelligence Development Studio [BIDS] ) you will likely need one or both files.

Installation

Install Microsoft OLE-DB Provider for DB/2

License Agreement

Image

licenseAgreement_20170922_0208PM

 

Registration Information

Image

RegistrationInformation_20170922_0208PM

 

Feature Selection

Outline

  1. Changed installation path from “C:\Program Files\Microsoft OLE DB Provider for DB2” to “E:\Program Files\Microsoft OLE DB Provider for DB2”
    • The basis for the change being we will like to reserve our system drive (C: ) for the OS

 

Image

featureSelection_20170922_0209PM

 

Revised

featureSelection_20170922_0210PM

 

Ready to install the Program

ReadyToInstallTheProgram_20170922_0210PM

 

Installing

 

Installing_20170922_0211PM

 

Validation

 

UDL File

Steps

  1. Launch Windows Explorer
  2. Create a new udl file
    • The file can have any name as long as it’s extension is udl
  3. Once the file is created, please right click on it, and choose the Open menu button
  4. Screens
    • Connection

 

Images

Connection

The first tab that comes to focus is the “Connection” tab.  We will be good if we are trying to a SQL Server DB, but as we will be connecting to DB/2, we will go back and choose the Provider tab.

Provider

Provider – Original

Provider – Revised

Provider – Explanation
  1. Changed provider from “Microsoft OLE DB Provider for SQL Server” to “Microsoft OLE DB Provider for DB2

 

Advanced

Advanced – Original

SQL Server

Linked Server

Add new Linked Server

Linked Server – General

Image

Textual
Item Meaning Value we used
Linked Server The name that the Linked Server will be referred to DB2
Server Type Other Data Sources Other Data Sources
Provider Provider name of the providers installed on the system Microsoft OLE DB Provider for DB2
Product Name Can be anything
Data Source Please leave blank
Provider String Please get Provider String from your DB/2 team Data Source=dbraq.labdomain,org;Initial Catalog=MVSBQ;Provider=DB2OLEDB.1;Persist Security Info=True;Network Address=dbraq.labdomain.org;Network Port=3300
Location Disabled when provider is DB/2
Catalog  Initial default catalog MVSBQ

Linked Server – Server Options

Image

 

Validate Linked Server

sp_tables_ex

Query


exec sp_tables_ex @table_server=N'DB2RAQ'

Image

 

TroubleShooting

Network Address – When “Fully Qualified Domain Name” not used
Script

USE [master]
GO

if exists
(
    select *
    from   sys.servers
    where  [name] = N'DB2RAQ'
)
begin

    sp_dropserver  @server = N'DB2RAQ', @droplogins='droplogins'

end
go

/****** Object:  LinkedServer [DB2RAQ]    Script Date: 9/23/2017 8:49:54 AM ******/
EXEC master.dbo.sp_addlinkedserver
      @server = N'DB2RAQ'
    , @srvproduct=N'Microsoft OLE DB Provider for DB2'
    , @provider=N'DB2OLEDB'
    , @provstr=N'Data Source=dbraq;Initial Catalog=MVSBQ;Provider=DB2OLEDB.1;Persist Security Info=True;Network Address=raq;Network Port=33006;Package Collection=MSNC001;'

 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB2RAQ',@useself=N'False',@locallogin=NULL,@rmtuser=N'acct',@rmtpassword='password'
GO


exec sp_tables_ex @table_server=N'DB2RAQ'

Image

Textual

OLE DB provider "DB2OLEDB" for linked server "DB2RAQ" returned message "A TCPIP socket error has occured (10022): An invalid argument was supplied.".

Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41 [Batch Start Line 35]
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "DB2RAQ".

Incorrect user credentials
Script

USE [master]
GO

if exists
(
    select *
    from   sys.servers
    where  [name] = N'DB2RAQ'
)
begin

    exec sp_dropserver  @server = N'DB2RAQ', @droplogins='droplogins'

end
go


EXEC master.dbo.sp_addlinkedserver
      @server = N'DB2RAQ'
    , @srvproduct=N'Microsoft OLE DB Provider for DB2'
    , @provider=N'DB2OLEDB'
    , @provstr=N'Data Source=dbraq.labdomain.org;Initial Catalog=MVSBQ;Provider=DB2OLEDB.1;Persist Security Info=True;Network Address=dbraq.labdomain.org;Network Port=33006;Package Collection=MSNC001;'

 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DB2RAQ',@useself=N'False',@locallogin=NULL,@rmtuser=N'acct',@rmtpassword='password'
GO


exec sp_tables_ex @table_server=N'DB2RAQ'

Image

 

Textual

OLE DB provider "DB2OLEDB" for linked server "DB2RAQ" returned message "The user does not have the authority to access the host resource. Check your authentication credentials or contact your system administrator.".

Summary

Microsoft provides a very capable OLE-DB Provider for DB/2.

Please keep in mind it is not an ODBC nor JDBC connectivity library.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s