Right now I have one database (8.2) with 2 schemas, test and production. I did this because I often need to run queries comparing data in the test schema to data in the production schema. I would love to make them 2 databases instead of two schemas, but I have not figured out how to reference tables from 2 different databases in one sql statement. On sql server this is easy because you can reference a table using the 4 part syntax. Does anyone know how I can accomplish this on DB2 UDB 8.2?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
how to reference 2 databases in an sql query
- Thread starter ddiamond
- Start date
I work with Workgroup Edition and I have a number of workarounds for not having federation:
1. Use Reporting tool like Business Objects to link query output from multiple databases
2. Use MS Access to use create Jet SQL queries over temp tables that are created with (mostly) pass-through queries to DB2.
3. Use tool like latest WinSQL to compare databases and schemas (compare objects)
Ties Blom
Information analyst
1. Use Reporting tool like Business Objects to link query output from multiple databases
2. Use MS Access to use create Jet SQL queries over temp tables that are created with (mostly) pass-through queries to DB2.
3. Use tool like latest WinSQL to compare databases and schemas (compare objects)
Ties Blom
Information analyst
- Thread starter
- #5
Thanks blom0344. I have been attempting to install a federation for some time now without much success, so a none federation solution is welcome. I have used the MS-Access approach for some things, but MS-Access does not support the minus operator as far as I can tell. For example we often do the following:
select ... from test.mytable where ...
minus
select ... from production.mytable where ...
I don't have any experience with Business Objects or WinSQL. Would they be able to help me accomplish something similar to my example above?
select ... from test.mytable where ...
minus
select ... from production.mytable where ...
I don't have any experience with Business Objects or WinSQL. Would they be able to help me accomplish something similar to my example above?
I do not think you need the use of minus operator.
If you fetch that data into temporary Access tables you can then create a outer join to show only the records that exist in the test.mytable (through the is null condition on the other end)
WinSQL offers a tool to compare objects (tables, fields, indices) between 2 schema's or 2 databases.
Won't help you with actual data itself...
The BO solution works by fetching the primary key + minimal 1 other fields from each table (in the BO document create 2 dataproviders)
The datasets can than be 'linked' over the primary key.
If you then incorporate the additional fields into one report table, then BO will create a full outer join over the datasets. This enables me to quickly check the non-intersection.
I realize this will sound a bit vague, but this way I can even compare data coming from totally different databases (like DB2 compared with SQL server)
If you have Business Objects anywhere available, it will surely be a good solution
Ties Blom
Information analyst
If you fetch that data into temporary Access tables you can then create a outer join to show only the records that exist in the test.mytable (through the is null condition on the other end)
WinSQL offers a tool to compare objects (tables, fields, indices) between 2 schema's or 2 databases.
Won't help you with actual data itself...
The BO solution works by fetching the primary key + minimal 1 other fields from each table (in the BO document create 2 dataproviders)
The datasets can than be 'linked' over the primary key.
If you then incorporate the additional fields into one report table, then BO will create a full outer join over the datasets. This enables me to quickly check the non-intersection.
I realize this will sound a bit vague, but this way I can even compare data coming from totally different databases (like DB2 compared with SQL server)
If you have Business Objects anywhere available, it will surely be a good solution
Ties Blom
Information analyst
- Status