Support

FAQs

Table of Content

Table of Content

Table of Content

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.

Can AQT build Stored Procedures for DB2 z/OS?

Yes, as long as you are running DB2 for z/OS v9; this release introduced Native SQL Stored Procedures which are supported by AQT.

If you are running a release of DB2 earlier than z/OS v9, AQT cannot build Stored Procedures. This is because Stored Procedures could only be created by running JCL etc, rather than with a Create Procedure command.

Selects return only 50 rows

The evaluation version of AQT will return only 50 rows for a Select statement.

Once you have purchased a license you can reset this option in the options menu: Options > Display Options > Display Limits > Max Rows Displayed. We generally suggest not choosing "No limit" (except when you really need to) as your PC may run out of memory when doing Selects on very large tables.

You will probably also want to reset Max Col Size to 1000 at the same time.

Maximum column size 200 bytes

The evaluation version of AQT will display a maximum column size of 200 bytes.

Once you have purchased a license you can reset this option in the options menu: Options > Display Options > Display Limits > Max Col Size. We generally suggest not choosing "No limit" (except when you really need to) as your PC may run out of memory if you try to display large columns.

You will probably also want to reset Max Rows displayed to 10000 at the same time.

Export function set-up

This topic describes how to set up a batch script for exporting a table, or result of a query. Display the Export Data window in one of three ways:

  • build the query in either the Run SQL or GUI Query windows.

  • in the Run SQL window, select Export To > File (or F7).

  • in the GUI Query Window, select Query > Export Data (or F7).

Select the csv filetype, then in the Dialog Menu, select Tools > Generate Batch Control Statement (or Ctrl+ G ), copy the text from the window and paste it back into the Run SQL window above the Select statement. You can Run the statement to test it.

Next, copy the entire text from the Run SQL window into a .txt file, add a first line that calls AQT and connects to the database, eg

--aqt conn,dbs=test

Then create a .bat file to run AQT, specifying your text file.

Numerous sample .bat files (such as batch*.bat) are provided in the AQT directory. We strongly recommend that you view these files to see the types of options available.