Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369

    Unanswered: Federation between two DB2 databases running on AIX

    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)?

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    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.

  3. #3
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    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.

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    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

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    you got mail. It has been a while. but it should work.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Got it. Thx

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    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

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Nice job Bella, I'm sure I'm not the only one here to appreciate you sharing this information.
    ---
    "It does not work" is not a valid problem statement.

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    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.

  11. #11
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Do not forget to monitor both databases with "db2top", there's a separate "federated" screen.

  12. #12
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    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.

  13. #13
    Join Date
    Jan 2008
    Posts
    96
    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 -

  14. #14
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by rangupt View Post
    Bella the blog has really good information about how to set up federation.
    Thank you.


    Quote Originally Posted by rangupt View Post
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •