AQT executables and install file are now digitally signed.
We've made it easier for a user to load an AQT v11 license, when they currently have an activated AQT v10 license. AQT will automatically deactivate the AQT v10 license; if this fails the AQT v11 license will still be loaded.
the Query Builder truncated a large CASE statement
a number of miscellaneous problems with displays for PostregSQL
Export to Grid. AQT can export your data as a Saved Grid. This Saved Grid can then be opened in AQT as if the query had been run by interactive AQT. This is useful when generating reports in an overnight script - they can then be opened in interactive AQT.
Your export filename can contain <date>, <time> or <datetime> to include the current date/time in the filenames.'
Native Export can now export in Excel 2016 format
when exporting as Insert statements, there is a new option Format SQL statements for. This allows you to export the data in a format compatible for another database type.
when doing the same export multiple times, the filename and Excel sheet will be retained from one export to another, rather than being reset to defaults.
scripting has some new statements. These enable you to include formatting of your query results with a saved query.
Caption. This specifies a caption for the display grid, plus for the window tab
ColFormat. This specifies how columns in the subsequent query are to be formatted
GridOptions. This specifies a number of options about the formatting of a display grid.
the SetParm statement now allows you to set a parameter to the value of an AQT option. This is useful for setting the parm to (for example) AQT's default export directory.
the SQL window:
you can now make the text bigger/smaller with Ctrl + Up/Down
num - turn a value into a consistent numeric format
trunc - truncate a number at a given number of decimal places
round - round a number at a given number of decimal places
ifnum - apply a function to numeric values
deperc - turn a percentage value into a numeric value
when using Column Functions, you can now specify a Column Type of all for the function to be applied to all columns.
when loading from Excel, you can specify whether the raw values or formatted values are used
AQT windows now use a more modern font. A number of windows have improved layout.
for Oracle, we've improved the way AQT decides whether to use the ALL or DBA views.
Data Compare now has an option to automatically run the resync script once the compare has completed.
the Find dialog in the Data Display window now remembers your previous searches
the Product Registration window now has a button Show Use of License. This will show you the devices you have activated your license on. This will make it easier for users to manage the use of their license.
when displaying a Saved Results file in the Query Explorer, there is now an option for viewing a sample of the results in the right pane.
when running an Export, you can include a description of the export. This description is shown when you display the Export History.
for DB2/LUW and DB2 z/OS, AQT will now generate the DDL for a View of a View
in the Query Builder, View > Clear all Grids also removed the SQL panel.
Export Directory - Change of Behavior
If you export to an unqualified file name, it will now go to the Default directory for Exports. By default this is the Exports sub-directory of the Queries directory, however can be specified in Options > File Locations.
In a batch job, you can set this directory with:
Batch Jobs - Changes of Behaviour
There have been a significant number of changes and enhancements of batch processing.
in the System Monitor, the left and bottom displays would not be refreshed once the main grid had been sorted
Windows > Reposition Windows did not work successfully
generate DDL (and some other functions) for DB2 z/OS got the message "recursive situation detected"
in the Database Explorer window, the Favorites was not repainted successfully when the list was sorted
in the Data Loader (and other places) some users hit a problem while displaying a file in Notepad when the file name contained a space.
the Export dialog didn't remember position / size between sessions
the AQTDemo database has been moved from the AQT program directory to the appdata directory (eg. C:\Users\<username>\AppData\Roaming\Advanced Query Tool). This allows the database to be updated, which could not always be done when in the program directory.
when using Disconnect if Idle and Auto-Reconnectoptions, AQT would not automatically reconnect when displaying information in the Database Explorer window.
for MySQL, table DDL sometimes had the column data types displayed in hex. View text were also sometimes displayed in hex.
on various grids, highlighted colors were lost once the cells had been selected / deselected.
Data Compare didn't correctly save / remember the unique keys for a query.
Data Compare Export Results to Excel didn't work well when View > Only show rows which are different was used.
creating a chart sometimes hit error "Cannot convert xxxx to Boolean".
charts sometimes had a spurious footer "1".
For DB2 v11, AQT didn't show the full list of database and schema authorities
improved the display of table columns for PostgreSQL and Yellowbrick
setparm did not set value <date(MMdd_hhmm)> correctly
in the SQL window, AQT sometimes hit the error "Cannot convert '' to Boolean" when canceling a query
in the Database Explorer window, the Abort button became disabled when clicking on Display multiple times
when you connect via a connection string, the database name for the connection was taken from the full connection string. This was erroneous as it could include the userid/password. It was corrected to being the ODBC Driver or DSN name.
the $list parameter on --aqt queryparm / setparm only allowed select statements and not show. It will now allow show.
On the SQL window, View > Explain Plan hit an error for databases such as SQLServer / MySQL which do not have explain tables. This option will now be disabled for these databases.
when running a non-Select statement against Teradata, AQT would give the message Error at splitsql_queryinfo
for Teradata, AQT didn't display the column types for Period columns
In some rare circumstances, a query would fail with Function sequence error
For Yellowbrick, AQT didn't correctly parse a cross-database query
New to AQT v10.1.1
Note: we have made improvements to the Encrypted Password feature. This is used both by Save Password feature of the Signon window, plus when using passwords in batch scripts.
If you use encrypted passwords in batch scripts it is recommended that you regenerate the passwords using this release.
Passwords saved by AQT v10.1.1 (and later) cannot be used by earlier releases of AQT.
This is a major new feature of AQT. This is described in Visual Explain.
this has been implemented for DB2/LUW, Oracle, MS SQL Server and PostgreSQL. We are also working on this for DB2 z/OS - please get in touch with us if you are interested in using this.
for DB2/LUW, you can run the DB2 Index Advisor and Explain Formatter
on the Run SQL window you can click on Run > Show Query Cost. This will show the cost of a query without having to see the Visual Explain window.
Other New Features
The queryparm command now allows you to specify a hidden parameter (idparm). The user can be shown a set of values which are meaningful to them (eg. customer names), but a hidden value (eg. customer ids) is passed to the query.
In the History window you can restrict the display to queries which ran against a particular database. You can also filter the display to only show particular entries.
On the Database Explorer, Data Display and Row Detail windows, you can increase the font size with Ctrl+Up (and decrease it with Ctrl+Down).
Support has been added for Redshift
For PostrgeSQL, support has been added for Stored Procedures and Materialized Views. The Database Explorer window will now show Types and Domains.
The Data Loader will now prompt before dropping or deleting the contents of a table.This will help prevent inadvertent loss of data.
now has an option to limit the resync script to lines of 72 characters or less
the window will now increase the size of the query boxes when the window is resized
When displaying the detail of a data value, there is now an option to split the text into 72-byte lines. This is useful when displaying Db2 z/OS View, Package and Stored Procedure text as this is often stored within Db2 z/OS without linebreaks.
There is now an option to specify how Bit / Boolean values are to be displayed. In a Checkbox or as 0/1 or False/True.
The Query Explorer now has a display Newest First. This makes it easy to see recently added or amended queries.
On the Run SQL window, you can now display the full column list for a table (as opposed to select *) with Edit > Show Full Column List (or Alt +L).
you can now select a recent directory for your export file
when exporting to Excel, the Native File export will be selected by default. However, for compatibility with earlier releases, when you run an export script and xlnative is not specified, the default will be a non-native export.
for Excel exports, there is a new option Format numeric values as numbers. This deals with the case of numeric values being held in a character column. At present, these values will be exported to Excel as a text value. When this option is selected, these values will be exported as a numeric value.
Communication with our activation server will now use https rather than http.
on a small number of systems, AQT crashed when connecting to a database. This seemed to happen for people running an old level of the .Net Framework. Now fixed.
For Oracle, the Database Explorer was very slow for displaying schemas, plus a few other functions. This was caused by the system view ALL_OBJECTS being absurdly slow. For instance the query: Select Object_Type, Count(*) from all_objects Group By Object_Type can take up to 30 minutes.
when exporting to Excel, the header would be overwritten when multiple queries were written to the same sheet
the start and end text would ignore a <cr> if this was at the end of the text
when exporting to a file which already existed, the user got multiple File Already Exists messages
there have been a few improvements to the display of Sequences and Aliases
the Database Explorer window did not display for DB2 v8
when running a Stored Procedure which returns an XML value, the returned data was garbage
didn't generate correct DDL for *CHAR FOR BIT DATA columns
for some versions of DB2, the Database Explorer window did not show Tablespace Containers
Index Sizing sometimes showed an incorrect list of indexes
will now show the LastUsed date for Indexes
Summary Tables did not show table partitions
in the Data Display window:
AQT did not display images correctly when View as was select from within the Detail of a Data Value dialog
AQT sometimes did not use the correct display program for the View as function from the Detail of a Data Value dialog
the Row Detail window could not be opened if the database had been closed or AQT could not determine the name of the query table
by default, unprintable characters were displayed as $(:). This has been changed to not be the default.
for the Data Loader:
wasn't able load MySQL tables which had a space in the column names
the Create Table option didn't get the correct data types when loading a Unicode file
the Create Table option generated columns with types Datetime and Double, which are not valid for PostgreSQL. Changed to Timestamp and Decimal.
when opening a saved load script for a table using Create mode, the data types were not retrieved
values being loaded had trailing spaces removed. We have now added an option to specify whether you wish to be done.
Load all Tables in Schema: when loading from Files or Excel Files in Create Table mode and the "First row has column names" option was specified, the tables were created with the correct columns names.
when loading from file using Create Table mode, the file would be unnecessarily scanned for the column types even through these have been specified.
when loading from file using Create Table mode, a table would sometimes be created with the wrong Null attribute.
failed to load a numeric value correctly when a comma was used as the thousand separator.
the Query Builder window:
running queries could not be cancelled
queries which used a function in the ON clause were not parsed correctly
the Where window blanked the second value in a BETWEEN clause
in the Where Value column of the Columns tab, Access date values were incorrectly enclosed in #s
now supports the syntax SELECT ALL
some improvements to the handling of WITH statements
didn't handle a sub-select enclosed in multiple spurious brackets
the Build IN clause dialog would remove duplicate values when added from the right panel. An option has been added to allow duplicates to be retained.
did not correctly parses a query when it had a Case clause with an alias which doesn't use AS. Example: case flag1 when 0 then 'False' else 'True' end Unpaid.
the Add Table dialog sometimes showed an incorrect list of tables after switching to a new database.
AQT now allows you to define Shared Queries. These are queries which are shared amongst members of your work group. The Queries display of the Database Explorer window will show you these in addition to your personal queries.
The Signon window has been altered to make it easier to connect to some databases (DB2 / Oracle / MySQL / SQL Server) without having to define a Datasource.
A number of enhancements to the Query Builder:
it now supports multiple statements Unioned together
will now write the query time in the standard year-month-date hour:minute:second format. Previously it was in Windows system format which Windows (for some formats) would not recognize as a valid date (!).
Ctrl+C didn't copy text from the SQL or Results boxes.
AQT has a new option Use System Colors. When this is selected, AQT windows display better when using a Windows Theme such as High Contrast.
This option will be switched on if AQT detects that your Windows system is using a High Contrast theme.
There is now an option to disable the Show Password button on the sign window.
There is now an option to enable Oracle numeric columns defined without precision/scale to be displayed without loss of precision.
SQL History will now write the query time in the standard year-month-date hour:minute:second format. Previously it was in Windows system format which Windows (for some formats) would not recognize as a valid date (!).
User Defined Relationships (UDRs) now allows you to define an Alias for a relationship. This makes it easy to locate a particular relationship.
Generate Text now has an option to include a Tab character in the text.
Generate DDL now allows you to specify whether you want Comments to be generated.
The Run Procedure window now can write INOUT and OUT parameter values to a file. This is useful for procedures which return large values.
AQT now releases memory more promptly when you close a Data Display, SQL or Query Builder window.
The display of Oracle Indexes will now show Index Type.
Added support for Sequences, Tablespaces, Users/Roles and authorities for PostgreSQL.
Added support for Teradata v16.
Added support for MariaDB.
Added support for Snowflake.
Added support for SAP IQ (previously known as Sybase IQ).
The Connect and Disconnect scripting statements now have a parameter batchonly=true. When this is coded these statements are ignored when run in interactive AQT.
After apply Windows 10 Update 1803, AQT 32-bit would get numerous error messages Processing error at Get_Setting. Requested registry access is not allowed.
It seems that, after update 1803 has been applied, AQT was denied access to the LOCAL_MACHINE part of the registry. AQT doesn't really use this part of the registry, but reads settings from there in case the user has loaded some system-wide settings into this.
A few fixes for the Database Explorer window
the Schema Filter did not recognise the not indicator (a minus sign at the start)
when running a Query or Favourite with a WITH statement, AQT gave the message "SQL not run as contains a non-select statement"
AQT didn't give an error message when an error was hit switching to new SQL Server database.
Schema Filters were sometimes not applied if you signed onto a database multiple times.
For recent version of MySQL, the column type was displayed incorrectly.
The list of databases was erroneously limited to the value of Options > Max Rows Displayed. All databases will now be shown.
The setparm statement can now read a list of values from an external file (csv or Excel) or from a query.
This is a frequently-requested piece of functionality. It allows you to read a list of values from (say) an Excel file and use these in an IN list for a query.
There is now an Option to specify whether you want the parameter values remembered between AQT sessions
The delparm statement can now delete all parameters, or a set of parameters matching a mask
The Data Loader will now remember the table and load-from-file dropdown lists between AQT sessions.
In the Query Builder window, you an now deactivate a Where clause. This provides an easy way to "remove" a where clause then add it back in again later.
Object Compare now allows you specify <schema> and <table> in the report and resync file names. When comparihng multiple objects, this allows you to have separate report/resync files per object compared.
Objects Compare can now compare DB2/LUW Functions and Procedures.
There is a new scripting command WriteMsg to write info to the Debug file plus Log File (when running in batch).
Activation can now be done through a Proxy Server.
AQT now supports the use of the SYSTSOOLS schema for DB2/UDB Explain Tables
There is now a setting (Adjust column widths) to make the columns width in the Data Display window a bit wider.
a very useful feature is to right-click a cell and select Only show rows with this value. There is now a second option Only show rows with this value (another condition) to add this condition to the existing ones
when in Create Table mode, you can now specify the column names / types / null-flag of the new table on the Mapping tab. Previously this could only be done when the Create Table statement was previewed, which was a bit clunky.
for Create Table mode, for DB2 z/OS you can now specify the name of the Database the table is to be created in. This allows the use of the In Database xxxx clause.
when loading from a delimited file, you can now specify a hex column delimiter.
The Signon window would give error message Processing error at GetDataSources. Object Reference not set to an instance of an Object when there were no datasources.
Activation can now deal with users who have roaming profiles.
When created a SQL Server Stored Procedure, the Copy From button was not present.
The Batch samples did not run successfully as they were in the Program Files directory structure, which Windows protects. They have now been moved to the Application Data directory, so can run successfully.
AQT would crash when connecting to a database if the Windows setting Fips Algorithm Policy was set. This has now been fixed.
New to AQT v10
Major New Features
The Database Explorer window has a Find Objects feature. This allows you to find all objects that contain a particular name (such as a column name).
There is a new window to Load all the tables in a schema. This is built on top of the Data Loader and allows you to load all the tables in a schema in a single operation.
There is a new window to Compare all the tables in a schema. This is built on top of the Data Compare and allows you to compare a tables across two schemas in a single operation.
you can select which of updates / inserts / deletes are to be included in the resync script
there is now an option Generate Updates as Deletes/Inserts. When this option is selected, AQT will update a row by deleting it and inserting a new row, rather than using an update statement. This avoids the problem whereby some rows cannot be updated due to foreign key constraints.
AQT can now Auto-Reconnect to a database if it has detected that the database connection has been closed.