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.
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
Visit the new-look IDUG Website , register to gain access to the excellent content.
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?
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 v18.104.22.168", "s060120", "MI00146", and FixPak
Product is installed at "/opt/IBM/db2/V8.1".