AQT Basics

Key Features and Benefits

Table of Content

Table of Content

Table of Content

Four main benefits of AQT

AQT has four main benefits for you:

  • AQT very quickly shows all the tables in your system, their definition and content. You can easily develop and save Queries against the tables.

  • for many databases, AQT will read the system tables to provide you with comprehensive information about the objects in your system. Not just the tables and views are accessed, but also triggers, procedures, packages, tablespaces, security rights. This feature is unique to AQT.

  • AQT provides a large number of useful tools for the DBA, developer and data analyst. Main Features of AQT lists these.

  • AQT has a powerful GUI query builder for easily building complex queries, including multi-table joins.

Advanced Query Tool has been extensively proven on the road. It was developed over a 10-year period by a Database Administrator frustrated with the inadequacies of existing tools.

AQT can handle large databases and unusual data types (BLOBs, binary fields) and has many workarounds for peculiarities of ODBC drivers (these are all problems that bedevil other ODBC-based products). AQT works.

Users have found that AQT gives them unprecedented visibility into their system; its simplicity and ease-of use are great time-savers.

How does AQT connect?

AQT can access any database that can be set up as an ODBC data source. This covers most databases on the market. You will need an ODBC driver to connect to your database.

Main features of AQT

Advanced Query Tool has the following main features to make your database work more productive.

Simple interface

In developing Advanced Query Tool, a large amount of effort has been invested into providing a simple interface that is fast and easy to use. Many users find Advanced Query Tool worthwhile just for this interface, which accesses all their database types.

  • AQT provides a very simple interface for viewing the tables in your database (both table definitions and content).

  • You can do this with minimal clicking, minimal scrolling, minimal typing and a minimal amount of hunting through menus. Usability concepts explains this further.

  • Given a certain job-function, we guarantee that you can do this faster with AQT than any other query tool we have seen.

Complex environments

  • AQT can run against large tables and large databases, including mainframes. AQT runs happily and fast against databases with tens of thousands of tables and millions of rows. Most other ODBC-based products are very slow in such environments.

  • AQT has workarounds for dealing with the many bugs / peculiarities of ODBC drivers we have come across while developing and using AQT.

  • A comprehensive facility allows Stored Procedures to be run.

Data types

  • AQT can handle many data types and other situations that normal ODBC-based systems have trouble with:

    CLOB and BLOB columns can be displayed, updated and inserted

    Binary columns can be displayed in either ASCII or hex mode.

  • AQT has facilities for displaying and updating large columns (thousands of bytes long).

Queries

  • Run SQL statements against your database.

  • A powerful Query Builder helps build Select statements.

  • Build Update, Delete and Insert statements.

  • You can apply a search condition (filter) to a table, to easily display particular rows of a table without knowing SQL. This builds a query that can be reused or saved (no saves in the evaluation version).

  • Queries can contain substitutional parameters. AQT prompts for their values when the query is run.

  • Queries can be saved for later use. Not in the evaluation version. See a list of evaluation limits.

  • Queries can be read from a file and run. This includes running SQL scripts containing hundreds of SQL statements.

  • Results from a query can be exported to a file (in a variety of formats).

  • A number of options are provided for analysing a table. This is a powerful way to quickly gain an understanding of the data held in a table without having to write SQL.

  • You can control queries: long-running queries or updates can be cancelled.

  • Once you have displayed a table, you can drill down and examine the rows in detail.

  • Queries can be run in unattended batch mode.

  • Query data can be Charted.

  • Display XML data in AQT's XML Viewer.

The term "query" is used rather loosely to refer to any SQL statement. AQT provides many features for updating your tables too.

Updating Data

  • AQT has comprehensive facilities for updating data, both individual rows and mass-updates.

  • Updates can run under Transactional Control – the changes are not made until Commit (or Rollback) is selected.

  • A Safe Update Mode prevents accidental updating / deleting of more rows than intended. If more than one row is updated or deleted you will be asked whether you wish the update/delete to proceed or not. This option can be switched off or configured.

System queries

For some databases AQT reads the system tables (a.k.a. catalog, dictionary) to provide you with comprehensive information about the objects in your system (not just tables but also the triggers, procedures, tablespaces, packages, security rights etc).

This provides you with a large amount of information useful for understanding and managing the database. Typically, between 50 and 150 system queries are supplied with AQT for these database types.

The system queries are held in config files, and can be changed or augmented if required (we provide a tool for assisting with this). Because the system queries are held in config files, we can implement new system queries in a very short space of time.

So far, system queries have been developed for

  • DB2 for LUW, DB2 for z/OS, DB2 for iSeries

  • Oracle

  • Sybase ASE

  • Sybase Anywhere

  • Sybase IQ

  • SQL Server

  • Informix

  • MySQL

  • SQLBase

  • Pervasive.SQL

  • Interbase / Firebird

  • PostgreSQL

  • SAPDB / MAXDB

  • Netezza

  • HP SQL/MX

Database types lists the versions for which system queries are available. Other databases will be added in the future. AQT still works with databases other than these - it is just that the system queries are not available for them.

The system-table query feature gives more information about this.

For the DBA

Developed by a DBA, Advanced Query Tool is designed to make the life of a DBA easier. Many features contribute to this.

Advanced Query Tool

  • has a compare facility for quickly comparing the definition of a table between databases. This is useful if your tables are defined in more than one database. This is fast; you can compare (say) 50 tables in a few minutes.

  • has a facility for simplifying the management of the security of your tables and views.

  • can show view definitions in formatted / interpreted mode. With this View you can easily match a view-column with the source of that column.

  • can run multiple SQL statements. This can be used to run a script file containing a number of SQL statements. Options to pause / amend / retry make AQT one of the best ways to run a large SQL script. The script file can contain hundreds or even thousands of SQL statements.

  • can generate (simple) DDL for your tables and views.

  • can generate text containing table and/or column names. This has a large number of uses:

    • from generating program code

    • to generating utility statements for your tables.

    • If you have a large number of tables in your system this feature can save you a large amount of time and typing.

Administration Component

This is part of the Extended Edition of AQT. This has a number of features that are useful for administering a database:

  • create / alter / drop objects. Not just tables but also indexes, tablespaces, procedures etc

  • a full DDL generator

  • a comprehensive Object Compare tool. This can compare a large number of attributes of tables - such as indexes, foreign keys, triggers etc.

  • a tool for managing the Authorities of your database objects.

The Administration component has been individually customized for the following databases:

  • DB2/UDB, DB2 z/OS, DB2/400

  • Oracle

  • SQL Server

  • Sybase ASE

  • Sybase Anywhere

  • Sybase IQ

  • PostgreSQL

  • Informix

  • MySql

  • Pervasive.SQL

  • SQLBase

  • MS Access

  • Interbase/Firebird

  • SQLite

  • Redshift

  • Ingres

Data Loader

This is part of the Extended Edition of AQT. This is a powerful tool for loading data into your tables:

  • data can be loaded from a variety of sources, such as other tables, queries, files, Excel.

  • data can be loaded between databases of different types

  • it is able to copy LOB data, making it one of the few tools on the market that can copy LOBs between different database types

  • contains a powerful mapper for mapping the data from the source to the target. Simple manipulation can be done on the data.

  • it is optimised for high-speed loading.

  • can be run in batch mode.

Data Compare

This is part of the Extended Edition of AQT. This is a powerful tool for comparing the contents of two tables, views or queries.

  • the tables can be on different databases.

  • Data Compare can generate a script to resync the contents of the two tables

  • can be run in batch mode.

System Monitor

For DB2/UDB, Oracle, Sybase and SQL Server, AQT has a System Monitor that shows you information on database processes and other internal information.