ARTICLE: Linking DB2 databases together via federation

This article describes the process of linking two remote DB2 databases via a remote DB2 client. The process is known in DB2 as Database Federation. Once linked, tables in the source database server can be accessed on the destination database server. I wrote this as I found a dearth of information online regarding this as well as the IBM Inforcentre to be confusing.

Assumptions

• The first remote server shall be referred to as source and second remote server shall be referred to as destination. Once the linking / federation has been completed, the source database tables can be accessed when connected to the destination database.
• That the source, destination and client database software are installed correctly and in working order.
• Readers have a basic understanding of SQL and the dialects used by the above vendor.
• DB2 command usages coloured blue, examples coloured green.
• Note that as of DB2 9.7 FP2, the process of linking DB2 databases together via federation is at no additional cost to the servers purchased - you do not need to purchase IBM Infosphere to link two DB2 database together.

Environment

• Source and destination server version / platform: 64-bit DB2 Enterprise Server Edition v9.7 FP2 on 64-bit Red Hat Enterprise Linux v5 r2. 
• Client version / platform: 64-bit DB2 Data Server Client v9.7 FP2 on 64-bit Windows 7 Professional.
• Settings for DB2 are added / modified via the use of a command line client (DB2CMD) initialised for DB2, connected only to the destination server. 
• The following is to be used in a development environment with no thought give to database security between servers - this is a factor which bears consideration in all production environments and even some development environments.
• In the scenario below, the source server resides on DB2SRCSRV and the destination server resides on DB2DESTSRV. Both run on port's 50000. The DB2 user which is used below on both servers is db2inst1 with password db2inst1.

DB2 - Federation

1. Via the client, catalogue the destination server node. Usage: CATALOG TCPIP NODE [dest_node_name] REMOTE [dest_hostname|dest_ip] SERVER [dest_port] WITH "[short_desc]". Example: CATALOG TCPIP NODE DB2V97D REMOTE DB2VDESTSRV SERVER 50000 WITH "SRC NODE"
2. Via the client, attach to the destination server node. Usage: ATTACH TO [dest_node_name] USER [dest_username] USING [dest_password]. Example: ATTACH TO DB2V97D USER db2inst1 USING db2inst1
3. Ensure that the DB2 Database Manager's FEDERATED variable is set to YES on just the destination server. This should not be done for the source server.  Usage: UPDATE DBM CFG USING FEDERATED [YES|NO]. Example: UPDATE DBM CFG USING FEDERATED YES. Note that once completed, the database manager and database instance may need to be restarted.
4. The source server node needs to be catalogued on the destination server. Usage: CATALOG TCPIP NODE [src_node_name] REMOTE [src_hostname|src_ip] SERVER [src_port] WITH "[short_desc]". Example: CATALOG TCPIP NODE DB2V97S REMOTE DB2SRCSRV SERVER 50000 WITH "SRC NODE"
5. The source database needs to be catalogued (at the above src DB2V97S node) on the destination server. Usage: CATALOG DATABASE [src_db] AT NODE [src_node_name] AUTHENTICATION SERVER WITH "[short_desc]". Example: CATALOG DATABASE SRCDB AT NODE DB2V97S AUTHENTICATION SERVER WITH "SRC DB"
6. Via the client, connect to the destination database. Usage CONNECT TO [dest_db] USER [dest_user] USING [dest_password]. Example: CONNECT TO DESTDB USER db2inst1 USING db2inst1
7. Create the DRDA wrapper to allow the destintation database to connect to the source database. Usage / example: CREATE WRAPPER DRDA LIBRARY 'libdb2drda.so';
8.  Create the server definition which will manage the remote connection to the source database. Usage: CREATE SERVER [src_db] TYPE DB2/UDB VERSION '9.7' WRAPPER DRDA AUTHID "[dest_db_user]" PASSWORD "[dest_db_password]" OPTIONS (ADD DBNAME '[src_db]');. Example:
   CREATE SERVER SRCDB TYPE DB2/UDB VERSION '9.7' WRAPPER DRDA AUTHID "db2inst1" PASSWORD "db2inst1" OPTIONS(ADD DBNAME 'SRCDB');
9. Create the user mapping for the source database to allow the destination database to connect. Usage: CREATE USER MAPPING FOR [dest_db_user] SERVER [src_db] OPTIONS (ADD REMOTE_AUTHID '[src_db_user]', ADD REMOTE_PASSWORD '[src_db_password]');. Example: CREATE USER MAPPING FOR DB2INST1 SERVER SRCDB OPTIONS (ADD REMOTE_AUTHID 'db2inst1', ADD REMOTE_PASSWORD 'db2inst1');
10. Unlike Oracle or SQL Server where remote database tables can be accessed directly from a connection to the local database with SQL such as SELECT * FROM [user].[table]@[src_database_link] for Oracle and SELECT * FROM [src_linked_server].[database].[dbo].[table] for SQL Server, DB2 requires an additional step before these remote databases can be accessed: to create nicknames for each of the tables in the remote database as it has no sense of directly referencing the link in the SQL itself. The nickname of the remote table must be unique to to all existing tables within the destination database. This nickname allows a user with sufficient privileges to access a table on the source database while being connected to the destination database. Note that the ZZZ prefix ensures that the source database table nickname will remain unique in the destination database. Usage: CREATE OR REPLACE NICKNAME [dest_db_user].[remote_table_name] FOR [src_db].[src_table]. Example: CREATE OR REPLACE NICKNAME db2inst1.ZZZMyTable FOR SECSRC.db2inst1.MyTable;.
11. Finally, to access the source database table via it's new destination database nickname, use the client to connect to the destination database execute the following. Usage: SELECT * FROM [src_table_nickname];. Example: SELECT * FROM ZZZMyTable;