DB2 Federated Query Configurations

By Tom Nonmacher

Welcome to another blog post from SQLSupport.org, today we will be discussing DB2 Federated Query Configurations. A federated database system is an integrated collection of databases, each with its own DBMS. They are self-governing but participate in a federation to allow transaction management. To set up federation between DB2 and other databases like SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1, and Azure SQL, we need to create wrapper, server, user mapping and nickname.

First, let's create a wrapper. A wrapper is an object in DB2 that identifies and provides information about non-DB2 data sources. Here is how you create a wrapper for SQL Server: 


-- SQL code goes here

CREATE WRAPPER SQL_SERVER

-- Add 
 after each line to simulate line breaks

The next step is to create a server. A server is an object in DB2 that identifies a specific data source instance and the options for DB2 to interact with that data source. Here is an example of creating a server for SQL Server 2016: 


-- SQL code goes here

CREATE SERVER SQLSERV2016 

TYPE SQL_SERVER 

VERSION '2016' 

WRAPPER SQL_SERVER 

AUTHORIZATION "sqlUser" PASSWORD "sqlPassword" 

OPTIONS (NODE 'SQLSERV2016', DATABASE 'TestDB', PROTOCOL 'ODBC')

-- Add 
 after each line to simulate line breaks

After creating a server, the next step is to create a user mapping. User mapping provides a mapping of the DB2 user to a valid user ID on the non-DB2 data source. Here is an example: 


-- SQL code goes here

CREATE USER MAPPING FOR DB2USER 

SERVER SQLSERV2016 

OPTIONS (REMOTE_AUTHID 'sqlUser', REMOTE_PASSWORD 'sqlPassword')

-- Add 
 after each line to simulate line breaks

The final step is to create a nickname. A nickname is an object in DB2 that represents a table in a non-DB2 data source. Here is an example of creating a nickname for a table in SQL Server 2016: 


-- SQL code goes here

CREATE NICKNAME nick_for_sqlserv2016_table FOR SQLSERV2016.dbo.TestTable 

-- Add 
 after each line to simulate line breaks

After completing these steps, you can now query the non-DB2 data source from DB2 using SQL. DB2 federated query configurations provide a powerful tool to integrate data across multiple databases and platforms. Remember, when setting up federation, it's crucial to ensure that the DB2 server has network connectivity and the necessary drivers to connect to the non-DB2 data source.

Check out the latest articles from all our sites:

DB2



3E6D3A
Please enter the code from the image above in the box below.