Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2007
    Posts
    33

    Unanswered: Can 2 databases tables be used in single query

    I would like to know the steps if single query [merge] that are using 2 databases [e.g. source - STAGE and target - PROD].

    We are currently having Merge stagements for updating/inserting target (Prod) tables

    I would like to know since stage tables are involved in query and in future we may have following options(2-4) and would like to know how to take care merge[insert/update] statement.

    Option 1: Have prod and stage schema on same server (curently we are using this, but will be change to one of the following).
    Option 2: Have same server, same instance but 2 databases - one for prod and one for stage
    Option 3: Have same server, but 2 instances with one database each for prod and stage
    Option 4: Have seperate server, 1 instance/one database per server. First server for prod and second for stage

    Given the above options/scenarios (option 2 - 4), the current merge/insert/update statement will not work, How do we take care of this? Do we have any features like Alias or synonyms.. Could you please get this clarified.

    Thanks
    Diwakar

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Federated databases is the option

    You should be able to find information on the IBM developerworks ...

    BTW, What is the reason you want to separate staging and prod tables ? It is not common to see this approach .. Unless there is a strong argument in its favour, generally you are doing more harm than good by using this approach ... (poorer performance, high maintanence, more system resources etc ) ... Especially, ones like MERGE will take a big performance hit ... of course, it depends on your scenario

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2007
    Posts
    33
    Sathya,

    Thanks a lot for reponse... Is Federated database the only option.. do we have any simple way to do this... Can you provide me the high level steps..

    I am working on the feasibility of the diff options..But shd know more about pros and cons before recommend anything...

    Any lead would help.

    Thanks
    Diwakar

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    Federation is not so difficult to configure and no additional software is needed. You can federate different versions of db2 and different versions of operating system. We use federation on db2 Linux on zSeries to remote database on db2 VSE/VM for about 4 years and it works fine without any big problems. This two systems use different code pages and different encodings but DB2 federation is capable of handling all this problems.

    Further more you can also make a select of two different database vendors databases for example db2 on AIX and MS-SQL server on Windows. But if using non-IBM databases you need to buy additional product Websphere Information Integrator.

    Don't know what databases you have? Can you provide more info about your system, operating system, db2 version and level of fixpacks?

    Hope this helps,
    Grofaty

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you want to access two databases in the same statement, federation is the only option AFAIK ..

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Jan 2007
    Posts
    33
    Thanks for quick reponse..

    My understanding is Federated database is used for multiple databases from different version or platform.

    I will be having all the different databases on same DB2 UDB version and OS (either linux or AIX). Still we can use Federated Database concept or something else.

    Thanks
    Diwakar

  7. #7
    Join Date
    Jan 2007
    Posts
    33
    Currently we are using DB2 UDB v8.2 on Linux. But it will be migrated to AIX.


    DB21085I Instance "db2inst1" uses "32" bits and DB2 code release "SQL08024"
    with level identifier "03050106".
    Informational tokens are "DB2 v8.1.0.104", "s060120", "MI00146", and FixPak
    "11".
    Product is installed at "/opt/IBM/db2/V8.1".


    Thanks
    Diwakar

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    In this case too, it is Federation
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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