MS-SQL DB2 Linked Server

 

Abstract

Microsoft SQL Server is a popular choice for consolidation of report data from multiple database products, as its Linked Server feature - which is included in the base product - makes it possible to access tables in heterogeneous databases within T-SQL via OLE DB.

However, there are some settings required to make this work seamlessly, which are described in this article as they are not that obvious.

Content

The products used were:

SQL Server 2014 Developer Edition ($60)

www.microsoftstore.com/store/msusa/en_US/pdp/SQL-Server-2014-Developer-Edition/productID.298540400

Allow redirection to the Microsoft Store for your country

 

DB2OLEDBV5_x64.msi from Microsoft SQL Server 2014 Feature Pack (free)

www.microsoft.com/en-gb/download/details.aspx?id=42295

Click on "Download" and select the package at the top (Internet Explorer recommended)

Most importantly, you must use an OLE DB driver, as Linked Servers using ODBC are not fully functional.

The first step is to compose a "provider string". Search for or run "Data Access Tool" (installed with the OLE DB driver) from the Start menu, and choose "File / New Data Source" from the menu. The following series of screen shots shows what worked for me:

Substitution: Image 1

DB2/6000 is the correct setting for AIX, Linux or Solaris (see Help button).

Substitution: Image 2

Enter the host name or IP address.

The default port 50000 may not be correct.

Substitution: Image 3

CDCSRC - my DB2 database

MSDBCOL - a new DB2 schema to contain OLE DB functions

DB1INST1 - my DB2 schema (standard DB2 demo instance)

Substitution: Image 4

A DB2 UTF-8 locale was correct for me and much more likely than the default.

Substitution: Image 5

Linux login created by the DB2 demo instance in my case.

Leave "Save password" unchecked, or it will be hard to change later.

Substitution: Image 6

No changes for me here.

Substitution: Image 7

Set "Auto Commit" to False (the only change, depends on your requirements).

Substitution: Image 8

Pressing the "Connect" button tested OK.

Substitution: Image 9

Pressing the "Packages" button produced the above dialogue.

Substitution: Image 10

Selecting the "Connection String" tab displays what we need.

Press Ctrl-A and Ctrl-C to copy to the clipboard.

Substitution: Image 11

Actually saving the data source in this program is optional.

Start up the "SQL Server 2014 Management Studio", and find "Linked Servers" in the Object Explorer:

Substitution: Image 12

We first set some global options for best results by right-clicking on "DB2OLEDB" and choosing "Properties":

Substitution: Image 13

  1. Defaults are all unchecked.
  2. Errors result unless "Allow inprocess" is set.
  3. Other changes are required for full functionality.
  4. Press "OK" to save the changes.

Right-click on "Linked Servers", and choose "New Linked Server":

Substitution: Image 14

  1. Enter a name of your choice in the first "Linked Server" field ("DEMO" in this example).
  2. Choose "Microsoft OLE DB Provider for DB2" from the "Provider" drop-down menu.
  3. Paste the "Provider string" field contents in from the clipboard with Ctrl-V.

Before pressing "OK", select the "Security" page:

Substitution: Image 15

Select "Be made using this security context" and enter the login and password.

Press "OK" to save the new Linked Server.

Once this is done, you can explore DB2 tables in SQL Server Management Studio, and reference them directly using SQL Server 4-part naming, i.e.:

linked-server.database.owner.table

For example:

SELECT TOP 10 * FROM DEMO.CDCSRC.DB2INST1.CUSTOMER

This is much more usable than wrapper functions such as the one below, which cannot employ nested loop joins between SQL Server and DB2 tables in the same statement, etc.:

SELECT * FROM OPENQUERY(DEMO, 'SELECT * FROM DB2INST1.CUSTOMER')

Conclusion

SQL Server Linked Servers can make it equally easy to access tables in other database products using a correctly configured OLE DB provider. For example, instead of unloading data, copying the file produced and reloading into SQL Server, this can be achieved much more efficiently in terms of coding, maintenance and run time with:

INSERT INTO local-table remote-query-statement

Disclaimer

The code fix suggested above is provided "as is" without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.