Federation between two DB2 databases running on AIX

db2girl · #1 (**permalink**) 08-06-09, 18:25

Does anyone have an easy-to-follow example of setting up federation between two DB2 databases running on AIX (DB2 v9.1 in case it matters)?

db2girl · #2 (**permalink**) 08-06-09, 18:47

Correction/Clarification:

There are two AIX servers, each has DB2 installed. The real tables are created on the server with DB2 v9.5. I need to setup federation from the server with DB2 v9.1.

dr_te_z · #3 (**permalink**) 08-07-09, 07:21

Never used it, have been playing with it though.
Suppose you have server A and server B. Whenever you select data in server A, the database in server B is also accessed.
On the instance on A, set the DBM CFG FEDERATED to "YES" and re-start the instance.
On server B I created an userid "dbtest"
Then go to the control centre/federated database objects and let the widgets guide you thought the steps. I did it on windows and saved the SQL:
step 1: create the wrapper

Code:

CREATE WRAPPER DRDA LIBRARY 'db2drda.dll';

step 2: the server

Code:

CREATE SERVER C90BANK TYPE DB2/UDB VERSION '9.5' WRAPPER DRDA AUTHID "dbtest" PASSWORD "*****" OPTIONS( ADD DBNAME 'C90BANK');

step 3: the user mapping

Code:

CREATE USER MAPPING FOR myUserId SERVER C90BANK OPTIONS ( ADD REMOTE_AUTHID 'dbtest', ADD  REMOTE_PASSWORD '*****') ;

step 4: the nickname

Code:

CREATE NICKNAME NICKschema.tablename FOR C90BANK.schema.tablename ;

This worked for me and a nice side-effect: doyouDB2.com - What just happened to me - doyouDB2

This works for "free" when you connect 2 DB2 servers. When you want to connect to collegue-databases you must buy additional licenses.

Cougar8000 · #4 (**permalink**) 08-07-09, 10:06

Bella,

If I am not mistaken you have to use diff id's when you are creating nickname. If I find a script I will get it to you.

db2girl · #5 (**permalink**) 08-07-09, 13:11

dr_te_z and Cougar, thx for responding. I was asked to setup federation and know almost nothing about it. So, this is Federation 101 for me. I'll RTFM on the weekend and see if I can find any other helpful info.

Alex, please send me the script if you find it. Thx

Cougar8000 · #6 (**permalink**) 08-07-09, 14:56

you got mail. It has been a while. but it should work.

db2girl · #7 (**permalink**) 08-07-09, 15:13

Got it. Thx

db2girl · #8 (**permalink**) 08-10-09, 15:38

I completed my testing and asked my manager to post "federation setup info" on his blog page. I hope people who are new to federation will find this info helpful.

https://www.ibm.com/developerworks/m...luw_databases2

n_i · #9 (**permalink**) 08-10-09, 16:01

Nice job Bella, I'm sure I'm not the only one here to appreciate you sharing this information.

db2girl · #10 (**permalink**) 08-10-09, 22:59

Thanks, Nick. I saved the setup info for myself so that I can repeat the same in the real env. I've decided to modify it for other DBAs since I couldn't find any good examples on the web and even the IBM team who supports federation could not provide with any helpful info.

dr_te_z · #11 (**permalink**) 08-11-09, 14:15

Do not forget to monitor both databases with "db2top", there's a separate "federated" screen.

db2girl · #12 (**permalink**) 08-11-09, 14:34

Thanks. I've been using snapshots to monitor non-federated env, but db2top is something I should look at. I don't know if snapshots capture any info for the federation env...

Someone mentioned to me that I should look at creating indexes on nicknames (specification only) and update statistics using the info from the server where actual tables are stored. Does anyone have any experience with indexes/stats on nicknames. Does it really make a difference if I won't be joining the actual tables and nicknames. The client will only be accessing nicknames located on the AIX server.

rangupt · #13 (**permalink**) 03-31-10, 14:40

Bella the blog has really good information about how to set up federation. On a side node, the hostname 'panipuri' is really funny. Did you know that panipuri is an Indian appetizer and tasty too? lol -

db2girl · #14 (**permalink**) 03-31-10, 15:15

Quote:

Originally Posted by rangupt

Bella the blog has really good information about how to set up federation.

Thank you.

Quote:

Originally Posted by rangupt

On a side node, the hostname 'panipuri' is really funny. Did you know that panipuri is an Indian appetizer and tasty too? lol -

Yes, my Indian colleagues told me the meaning of this hostname when I joined the company. I had some Indian food in the past but never tried panipuri...

Internet Services

Web Development

Digital Marketing

Consumer Tech