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:
DB2/6000 is the correct setting for AIX, Linux or Solaris (see Help button).
Enter the host name or IP address.
The default port 50000 may not be correct.
CDCSRC – my DB2 database
MSDBCOL – a new DB2 schema to contain OLE DB functions
DB1INST1 – my DB2 schema (standard DB2 demo instance)
A DB2 UTF-8 locale was correct for me and much more likely than the default.
Linux login created by the DB2 demo instance in my case.
Leave “Save password” unchecked, or it will be hard to change later.
No changes for me here.
Set “Auto Commit” to False (the only change, depends on your requirements).
Pressing the “Connect” button tested OK.
Pressing the “Packages” button produced the above dialogue.
Selecting the “Connection String” tab displays what we need.
Press Ctrl-A and Ctrl-C to copy to the clipboard.
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:
We first set some global options for best results by right-clicking on “DB2OLEDB” and choosing “Properties”:
- Defaults are all unchecked.
- Errors result unless “Allow inprocess” is set.
- Other changes are required for full functionality.
- Press “OK” to save the changes.
Right-click on “Linked Servers”, and choose “New Linked Server”:
- Enter a name of your choice in the first “Linked Server” field (“DEMO” in this example).
- Choose “Microsoft OLE DB Provider for DB2” from the “Provider” drop-down menu.
- Paste the “Provider string” field contents in from the clipboard with Ctrl-V.
Before pressing “OK”, select the “Security” page:
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.:
Copy to Clipboard
For example:
Copy to Clipboard
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.:
Copy to Clipboard
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:
Copy to Clipboard
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.
Contact us
If you have any questions or would like to find out more about Microsoft SQL Linked Server and DB2, simply contact us.
