How to create federation between two DB2 databases

The other day developer were asking if they access production table in their test databases. Here when we suggested them that we have Federation support across multiple RDMS vendors.

So lets get started quickly on how to create federation between two DB2 databases

It involves 5 steps Enable database to support federation, Create wrapper, server, usermapping, nicknames . First we need to check whether Federation support is switched on for our database, If you see that FEDERATION is set to off go head and update to YES and recycle the instance

db2inst1@test-machine:~$ db2 get dbm cfg | grep -i FEDERATED
 Federated Database System Support           (FEDERATED) = NO

db2inst1@test-machine:~$ db2 update dbm cfg using FEDERATED YES
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed 
successfully.

db2inst1@test-machine:~$ db2stop
19/02/2014 17:41:06     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

db2inst1@test-machine:~$ db2start
02/19/2014 17:41:10     0   0   SQL1063N  DB2START processing was successful.

SQL1063N  DB2START processing was successful.

db2inst1@test-machine:~$ db2 get dbm cfg | grep -i FEDERATED
 Federated Database System Support           (FEDERATED) = YES

Wrapper : The CREATE WRAPPER statement registers a wrapper with a federated server. A wrapper is a mechanism by which a federated server can interact with certain types of data sources,The default wrapper name for the DB2 family data sources is DRDA.

Syntax : CREATE WRAPPER <wrappername> 

Server : The CREATE SERVER statement defines a data source to a federated database.

Syntax : CREATE SERVER <servername> TYPE <datasourcename> VERSION <serverversion> WRAPPER <wrappername> AUTHORIZATION "<remoteauthid>" PASSWORD "<password>" OPTIONS (DBNAME 'dbname') 

Usermapping : The CREATE USER MAPPING statement defines a mapping between an authorization ID that uses a federated database and the authorization ID and password to use at a specified data source.

Syntax : CREATE USER MAPPING for <authname> SERVER <servername> OPTIONS (REMOTE_AUTHID 'username',REMOTE_PASSWORD 'password')

Nicknames : The CREATE NICKNAME statement defines a nickname for a data source object. we use this nick name to fetch records from the federated database.

Syntax: : CREATE NICKNAME <nickname> for <servername>.<schenaname.tablename>

Now let me take a scenario and explain it using two db2 databases. My aim is to access table in TESTA database from the database MURALI 


murali@test-machine:~$ db2 connect to murali

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = MURALI
 Local database alias   = MURALI

create wrapper drda
DB20000I  The SQL command completed successfully.

create server fedserver TYPE DB2/UDB VERSION 9.7 WRAPPER drda  AUTHORIZATION "murali" password "murali" OPTIONS(DBNAME 'TESTA')
DB20000I  The SQL command completed successfully.

create user mapping for murali SERVER fedserver OPTIONS(REMOTE_AUTHID 'db2inst1',REMOTE_PASSWORD 'db2inst1')
DB20000I  The SQL command completed successfully.

create nickname mytable for fedserver.db2inst1.tab1
DB20000I  The SQL command completed successfully.

murali@test-machine:~$ db2 connect to murali

   Database Connection Information

 Database server        = DB2/LINUXX8664 9.7.6
 SQL authorization ID   = MURALI
 Local database alias   = MURALI

murali@test-machine:~$ db2 "select * from mytable"

ID      NAME      
------- ----------
     1. murali    
     2. sharath   
     3. balaji    
     4. praks     

  4 record(s) selected.

Post your comments and doubts about this topic here.