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

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

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

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

If you want to access two databases in the same statement, federation is the only option AFAIK ..

Cheers
Sathyaram

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

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

In this case too, it is Federation