All you need to know about the FEDERATION and remote database connection in Db2 LUW
Before becoming a DBA, I worked at IBM as a Db2 LUW support engineer. The very first ticket I got was about some issue between Db2 LUW and Oracle databases and I was told that it was about federation. At that time word “federation” was a complete mystery to me…
After a few years in the Db2 world I know a bit more. I had a chance to set up a federated environment a couple of times and today I am sharing what I’ve learned to help those who didn’t get their hands dirty with the federation topic yet.
Federation lets you use remote data as though it were local
It looks like it’s here
Let’s assume you work with a database called “LOCAL_DB” and from time to time you need to use data from a table “TAB1” that resides on another database named “REMOTE_DB”. What can You do?
Though there are plenty ways to go, e.g. you can each time export the table from REMOTE_DB and import it into LOCAL_DB, you can try some data engineering and create an ETL script or you can use some API… but the there is only one proper answer to that: federation*.
*there is also another answer called “remote database connection” that solves part of the problem; if you want to use remote database connection without federation just read the first half of this article ;)
Remote database connection
Federation might be complicated and sometimes painful for newbies, so if your only goal is to access the remote data (not necessarily combine local and remote data in one SQL query), choose the simpler way. If you’re interested in federation only, scroll down to the next section.
How to set up the remote database connection?
At first, you have to catalog a node. Assuming that your REMOTE_DB database resides on a server with DNS “your.server.com” and listens on port “54321", the command will look like this (“REMOTE_NODE” is just a name you give to your node):
db2 catalog tcpip node REMOTE_NODE remote your.server.com server 54321
In the next step you need to catalog the REMOTE_DB database on the freshly baked node:
db2 catalog db REMOTE_DB at node REMOTE_NODE
That’s it! The remote database connection is configured. You can now connect to the REMOTE_DB database from your local server. To do so, you have to connect as a user that resides on the remote server (your.server.com) and provide its password, as follows:
db2 connect to REMOTE_DB user remote_user using password
Hmm… this remote database connection might be a quite useful thing, but it doesn’t link the databases. I am either connected to LOCAL_DB or REMOTE_DB. What if I just want to use data from both databases in one query?
Here comes the federation
Let’s return to the TAB1 case. You want to use a table from the REMOTE_DB as though it was in the LOCAL_DB. Here is how to do it.
1. Set the Db2 profile variable OR set the dbm variable
Firstly, set DB2_FEDERATION variable to “YES”.
db2set DB2_FEDERATION=YES
Alternatively, you can enable the database manager variable named “FEDERATED” (this change requires the instance restart to take effect).
db2 "update dbm cfg using FEDERATED YES"
2. Create the wrapper
Depending on the RDBMS of the remote database (Db2 or not), choose correct wrapper:
- DRDA wrapper → for Db2 — Db2 federation (both LUW and z/OS),
- ODBC wrapper → for federation outside Db2 (e.g. Db2 — Oracle)
And create it like this:
CREATE WRAPPER DRDA;
You may need to add library or other options, e.g.:
CREATE WRAPPER DRDA
LIBRARY 'libdb2drda.so'
OPTIONS (DB2_FENCED 'N');
More about creating wrappers you can find here. The link comes from the IBM Db2 LUW documentation.
3. Create the federated server
The next step is to create the federated server. To do so, you need to have the same information we used to create a remote database connection, namely:
- user you are going to use to connect to the remote database,
- its password,
- host (DNS or IP or hostname if configured)
- port number,
- remote database name.
In our case, the server definition may look like this:
CREATE SERVER REMOTE_SERVER
TYPE DB2/UDB
VERSION '12.1'
WRAPPER DRDA
AUTHORIZATION "remote_user"
PASSWORD "password"
OPTIONS (HOST 'your.dns', PORT '54321', DBNAME 'REMOTE_DB');
More about creating federated server you can find here (again, it’s the IBM Db2 documentation).
4. Create user mapping
If the remote username is different from the local username, you must create a user mapping for the newly created server, as follows:
CREATE USER MAPPING FOR LOCAL_USER
SERVER REMOTE_SERVER
OPTIONS (REMOTE_AUTHID 'remote_user', REMOTE_PASSWORD 'password');
At this moment, the federation should already work. You can test the connection using the PASSTHRU command.
5. Create nicknames
There is one last step: creating nicknames for tables (and other objects) that you’re going to use often. Thanks to the nicknames you don’t have to type “REMOTE_DB” each time you query a table from your remote database. This step is optional, but it really makes the remote tables look like they’re local. Here is how to create a nickname for your TAB1 table that resides in the REMOTE_DB database:
CREATE NICKNAME LOCAL_SCHEMA.TAB1 FOR REMOTE_SERVER.REMOTE_SCHEMA.TAB1;
“LOCAL_SCHEMA” is a name of your choice. I usually keep the schema name from the remote table, so the nicknames just help me get rid of the database name and instead of:
REMOTE_DB.SCHEMA.TAB1
I just type:
SCHEMA.TAB1
And this is it! Let me know if you managed to set up the federation following the steps above. Maybe you have some federation tips or comments to this instruction?
