SQL Server: link DB2 table

Question

I would like to query a DB2 table in SQL Server. Is it possible to link a DB2 table in SQL Server?

I do not have that much experience with SQL Server and could not find much documentation on this subject. Any help would be appreciated!

Answer 1

Starting from SQL Server 2019 CTP 2.0 you could use also PolyBase:

What is PolyBase?

PolyBase enables your SQL Server instance to process Transact-SQL queries that read data from external data sources. SQL Server 2016 and higher can access external data in Hadoop and Azure Blob Storage. Starting in SQL Server 2019 CTP 2.0, you can now use PolyBase to access external data in SQL Server, Oracle, Teradata, and MongoDB.

The same queries that access external data can also target relational tables in your SQL Server instance. This allows you to combine data from external sources with high-value relational data in your database. In SQL Server, an external table or external data source provides the connection to Hadoop.

Configure PolyBase to access external data in Teradata as example:

CREATE DATABASE SCOPED CREDENTIAL credential_name 
WITH IDENTITY = 'username', Secret = 'password';

CREATE EXTERNAL DATA SOURCE external_data_source_name
WITH (LOCATION = teradata://<server address>[:<port>],
-- PUSHDOWN = ON | OFF,
CREDENTIAL =credential_name);

CREATE EXTERNAL TABLE ...;

SELECT * FROM external_table_name;

---

CREATE EXTERNAL DATA SOURCE

LOCATION = '://'

Provides the connectivity protocol and path to the external data source.

ODBC odbc [:port] Starting with SQL Server 2019 (15.x) - Windows only

CREATE EXTERNAL TABLE

Answer 2

I would like to query a DB2 table in SQL Server. Is it possible to link a DB2 table in SQL Server?

This is possible by involving linked servers

Example of SQL to create a linked server object:

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

EXEC sp_addlinkedsrvlogin 'WNW3XX', false, NULL, 'WNW3XX', 'WNW3XX'

And a sample query to a remote object on DB2 instance:

SELECT * FROM WNW3XX.OLYMPIA.WNW3XX.DEPARTMENT

The answer is based on: Creating a linked server to DB2 using Microsoft OLE DB provider for DB2

Other references:

• https://www.mssqltips.com/sqlservertip/2151/how-to-create-a-sql-server-link-server-to-ibm-db2/
• http://www.sqlcoffee.com/Tips0013.htm
• How to add an IBM DB2 server to SQL Server's Linked Server