Cross-Database Queries

With AQT you cannot run a query that accesses tables in more than one database (for instance a table in Oracle and a table in DB2).

When you run a query, AQT runs it against one database and displays the rows that are returned. If your query refers to tables which aren't known to that database, then the query will fail.

This limitation applies to most query tools we are aware of, with the exception of MS Access. With Access you can run multi-database queries; this is discussed below.

AQT setparm statement

AQT has a feature which gives a basic degree of cross-database functionality. This allows you to populate a list of values from either a database query or a csv/excel file. You can then use this list of values in an IN statement against another database.

An example is below:

--aqt setparm,parm=custlist,value=$list(sql="select cust_code from order_details",db=database1)

select * from Customer_Details where cust_code in ($custlist)

The way this works is as follows:

• AQT runs a query against database1. The results of this are placed, as a comma-separated list, in parameter custlist
• the main query references the parameter custlist in an IN clause. The values retrieved in the previous step are substituted into the SQL prior to the query being run against the database.
• Note that the main query can be against a different database than database1

There is further info on this feature at SetParm - $list

MS Access

MS Access is able to access and join tables from multiple remote databases. It does this by reading the rows from both tables and perform it's own join on the data. If your tables are large, this will be very slow and will take a lot of memory on your PC.

You can use this feature of MS Access within AQT, as follows:

• open an Access database using MS Access
• define a Linked Table for your remote tables - eg. a Linked Table for your DB2 table and a Linked Table for your Oracle table.
• now close MS Access and open the Access database in AQT. The Linked Tables will appear in AQT as Synonyms. You can access them like any other Access table, including running queries that join them.

Database Gateways

Some databases have Database Gateways, which allow you to connect to a remote database. Tables in the remote database can be accessed as if they were local tables.

• In DB2 this feature is call Federated Databases, which comes as a standard part of DB2/UDB. A more comprehensive DB2 product is DataJoiner, also called Information Integrator.
• in SQL Server, this feature is called Linked Databases.
• for Oracle, if you are wishing to join to another Oracle database, you can define a DBLink to the remote database.
• if you are wishing to join an Oracle database to a non-Oracle database, you can use either Oracle Transparent Gateway or Oracle Database Gateway for ODBC.

Other Alternative

If the above options are not feasible, then the only other alternative is to have all the data on a single database. This will involve loading the data from one database onto the other.

Whether this is a feasible option will depend on the quantity of data involved, and the frequency with which it changes (and needs to be reloaded).

The AQT Data Loader can simplify the job of doing this; it can load between different types of databases and can be run in an unattended mode on a regular basis.