improvements to the running of PostgreSQL procedures and functions which return a cursor
improved support for SAP Adaptive Server Enterprise v16 (aka Sybase ASE)
for SQL Server, added an option to rebuild indexes
for Snowflake, added an option to automatically add a LIMIT x clause onto queries (as is currently done for PostgreSQL, MySQL and other databases).
for Oracle, added a number of new admin functions - Lock Table, Shrink Table, Move Table. Validate Table, Rebuild Index
For PostgreSQL, added a number of displays to the Database Explorer, including inherited and inheriting tables.
Export now has an option to not create the export file if no rows are exported.
The Export/Compare/Load Multiple Tables windows now have drag-over select for the list of tables. This makes it quicker to select a set of tables.
The Run using SQL*PLUS function of the SQL window will now run a SET SCHEMA command of appropriate.
Charting now has a feature Data Zoom. This allows you to zoom in on a selected range of the chart.
There is a new option to position dialog windows in the center of the main AQT window. By default, window positions are remembered from the last AQT session. This can be a problem for systems with multiple monitors as it can result in windows being position out of sight.
Bug Fixes
Open Saved Results displayed an empty string as NULL
The position / size of the Procedure History window wasn't remembered between sessions
a highlighted cell would loose its highlight color after it had been clicked on
View > Only Show Rows which are Different would hang or crash AQT when there were a large number of rows
For Oracle, the list of procedures did not include INVALID procedures
When running a Stored Procedure that returned multiple results, the SQL window would only display one of these if View > Display Multiple Grids was not selected.
For MySQL,, Procedure and Function descriptions were displayed in hex rather than ascii
didn't work correctly when a value referenced another parm
didn't recognize a value as a query when it used a WITH rather than a SELECT
the value=$list() clause didn't work when the query contained brackets
When running Explain for SQL Server, and the SQL has an error, the error message was not displayed.
When Block Selection Mode was used with the SQL window, text was not copied correctly when it was selected from the bottom up.
For the queryparm command, the pwd=y clause did not work
When using the Rename Tables dialog, running the rename would not work if you were still editing the cell.
When using Uncomitted Read for SAP Adaptive Server Enterprise, some queries failed with message The optimizer could not find a unique index which it could use to perform an isolation level 0 scan. To avoid this problem we have made Committed Read the default for Sybase ASE.
When restoring a session and AQT could not connect to a database, you would get multiple Not Connected to Database messages
The Database Explorer window would sometimes get an error when the object tree was refreshed
The Connect window would sometimes get multiple errors if the Enter key is held down
On the Row Display / Update window, the header row didn't have the correct color
When comparing results to saved results, date and timestamp values were sometimes flagged as different when they were the same.
In the Query Builder, the list of functions were not shown on the Columns grid when there was only one column.
The Data Loader, when running in Create Table mode, sometimes did not determine the correct data type or Nulls flag for a column.
For SQL Server, display of Default Text was not successful.
DDL for MySQL tables specified the database engine with Type rather than Engine.
New to AQT v11.0.6
New Features
AQT has a new feature Visual Where. This displays a Where statement in a visual format which makes it easy to understand.
Visual Where is available in:
the Query Builder window
the SQL window (by clicking on View > Visual Where, or Ctrl+Shift+W)
For Oracle, AQT will now display Tables, Views and Synonyms in the same item, rather than as separate items.
When running Explain for DB2 z/OS, AQT can now set the SQLID to the Plan table Schema. This ensures the explain info is written to the plan tables in that schema.
In the Run SQL window, parms were not replaced before Run as DB2 Command or Run using SQL*PLUS.
Support has been added for Databricks
A few improvements to the displays for PostgreSQL and Greenplum databases
On the Setparm $list parameter, you can now specify a list of values. Setparm can reformat these values to be inside quotes or to use a different delimiter.
a problem with the Reconnect function. If the disconnect was unsuccessful, AQT would ignore the error and establish a new connection. This resulted in an "orphaned" database connection which could not be accessed from AQT. Now, when a connection cannot be closed, AQT will retain the connection.
when a saved query contained a script of statements, only 20 were run.
AQT sometimes got the error "Invalid descriptor index" when displaying the columns for a table
no information was shown for a Databricks database
for PostgreSQL, the display of procedure text was limited to 8K bytes
for MySQL, Procedure text was sometimes displayed in hex
for DB2 > Delete Contents, the Filter did not work correctly
A few fixes to the Query Builder:
incorrect SQL was generated when a Where clause was Disabled
sometimes got an error when deleting a Where clause on the Columns tab
sometimes got error an error in "gen_sql_tabname" when the table didn't have a schema
error messages (in the status bar at the bottom) were sometimes were overwritten by other messages
Copy SQL to Clipboard sometimes did not work
on the Columns tab, a Where condition would sometimes be shown against all columns
for MySQL and MariaDB, AQT didn't get the list of related tables from the database
when Aways qualify table names is not selected, tables names were not enclosed in quotes when they should have been
For the Run SQL window
when the "Copy to Data Display window" option was chosen for a grid, the group-by box was displayed
sometimes interpreted Where as a table alias
when a Baltic character was entered, the text turned to "chinese" and AQT sometimes crashed
Replace All ignored the match Whole Word option
Replace text in the Replace dialog was sometimes jumbled with an earlier entry
the Find/Replace dialog had the Find text blanked when the dialog regained focus
the View/Edit Parameters window position/size was not remembered
Format SQL didn't correctly format a Select statement which didn't have a From clause.
when using Run using SQL*PLUS, AQT will run a Set Schema statement to set the current schema. This allows an query referencing unqualified tables to run correctly.
On the Data Display window:
the XML viewer sometimes got the error "The underlying connection was closed: The connection was closed unexpectedly".
when showing the parameters panel, changing to another parameter sometimes got error "Conversion from string to type integer is not valid."
unsigned integers were erroneously displayed as Null when the Use GetData All option was specified.
when displaying a large BLOB/CLOB value, the Detail of Data Value window would display the entire value rather than the first 100K bytes.
when exporting from a display grid, the Total row was not exported
when Exporting to Excel, an extra blank row was written at the bottom
DDL for DB2 z/OS
sometimes omitted Index columns
tablespace generation has been added as an option of table DDL
For SQL Server:
DDL for indexes did not include the column names correctly
NCHAR columns were given the wrong length
Other
Altering an Oracle Tablespace sometimes got an Arithmetic Overflow error.
Signing onto an Oracle database could be slow for some systems when Dbug Mode was active.
For Oracle, we have changed the name of Snapshots to Materialized Query Tables.
AQT sometimes got an error displaying the columns in a SAP/IQ database
AQT shutdown would hang if it waiting for an external editor to close.
Batch AQT would get an error if an Include file was empty
Restore Session didn't remember the button captions, when they had been renamed
Restore Session didn't apply a display layout, when a non-default layout was used
Didn't correctly handle the case of a parameter being set to the name of another parameter
Data Loader: when loading from one type of database to another in Create Table mode, the data types in the Create Table statement were not correct.
On the Row Update window, the Extended Edit dialog was sometimes formatted incorrectly
When creating a Batch Job, a connect statements was sometimes omitted
Scripts generated from the Compare Objects window did not handle the case of comparing a single table
There is a new option Center dialog windows which will ensure that dialog windows are not position out of sight.
New to AQT v11.0.5
New Features
You can now specify whether Format SQL places the comma prior to the column name.
on the Run SQL window, there is now a button in the Edit Toolbar for switching Intellisense off and on.
This is useful if you are typing a lot of text into your SQL and you do not want Intellisense to be active
The Run SQL window has a new function Run > Run Current Statement (Alt+F4). This will run the statement which is at your position of your cursor.
Some extra displays in the System Monitor for MySQL and MariaDB. This includes displaying Query Profiling.
When building a MySQL / MariaDB query, there is now an option to include the database name with the table name
You can now use any character for the parameter character. Previously, it was restricted to $ and :
There is a new option Save Session when Windows shuts down. This option is useful if your PC shuts down when it is unattended and you are unable to respond to the Save Session prompt.
Teradata will now show User-Defined Functions.
On the Database Explorer, when the right panel shows view/procedure text, there is now an option to show line numbers.
you can now specify multiple vertical axes (previously you could just have two - left and right). This is useful when charting multiple series, and each has a different scale.
there is now an Abort button to terminate a long data load
scaling of the horizontal axis can now be done for bubble charts
This feature has not been fully configured for DB2 for z/OS so it gives you ability to select whether you wish to use it, or the old Explain display.
queryparm and setparm now allows the syntax value=<date:from($date):endof(m)>. This allows you to set a parm to (for instance) the end of the month given by another parm.
Export now has an option to automatically append the date/time onto the end of the filename. This is useful for ensuring that the export file is unique.
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.
Bug fixes
the Query Builder truncated a large CASE statement
a number of miscellaneous problems with displays for PostregSQL
when a Session was restored, some queryparm parameters were not restored correctly on the Data Display window
in some places, auto-reconnect of the database was not being done
did not give a useful message when the user attempted to delete a license which is supplied in a license file
AQT didn't recognize some AQT v7 license keys
didn't pick up the correct cfg file for recent versions of Sybase Enterprise (ASE).
New to AQT v11.0.2
This build fixes a few problems not identified during AQT v11 testing:
AQT didn't recognize some AQT v7 license keys
When a SQL Server session was saved and restored, the database name would be (for example) SQLServer(master)(master)(master)
The Query Builder window allowed a Shared Query to be saved, despite the user not having the authority to do so
On the Generate DDL window, Select All / None didn't behave well.
On the Generate DDL window, Include commented drop will now be selected by default.
For SQL Server, DDL was not genned correctly for columns defined as nvarchar(max).
A few miscellaneous fixes and improvements to the Build Where Clause dialog.
The Open Session and Save Session dialogs now have shortcut keys for the OK / Cancel / Exit buttons.
On the Run SQL window:
Intellisense sometimes showed the wrong list (eg. list of tables rather list of columns)
there have been a few minor improvements to Intellisense.
Generate Text will now give you a message when Select All and Copy/Cut is used.
We have simplified the process of loading an AQT v11 Upgrade key when the user currently has an AQT v10 Upgrade key (AQT will automatically deactivate the AQT v10 license).
The Data Loader didn't allow you to select the database when loading from a query.
When running a non-select statement against Teradata, an error box was shown.
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
Misc
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:
--aqt options,exportdir=c:\Apps\AQT\Exports
Batch Jobs - Changes of Behaviour
There have been a significant number of changes and enhancements of batch processing.
when a Data Display grid was saved / opened, the data types were not reestablished correctly
in the Generate DDL window, when using the Write output to > Single File option, the file would be replaced without warning.
could not connect to a Datasource name which was 32 bytes long
when exporting in JSON format,
in the Run SQL window, text was sometimes not aligned when Courier New was used.
in the Run SQL window, Select All would cause the text to scroll to the bottom.
for DB2, clicking on the chart button when displaying Table Sizing would give an error
the Query Builder would behave oddly with Union > Add Statement > Cancel
the Data Loader didn't correctly process multiple Column Functions.
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.
exporting using Use bulk exporting of data took a very long time in the Formatting data phase when there were date columns. To avoid this a new option Use bulk formatting of dates has been added.
when exporting from the Data Display window, Export didn't remember the last set of options used.
when exporting from the Data Display window, date values could be formatted incorrectly.
JSON export didn't export line-feeds and other control characters correctly
creating a chart sometimes hit error "Cannot convert xxxx to Boolean".
charts sometimes had a spurious footer "1".
Other
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.
Visual Explain
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.
Visual Explain for DB2/LUW can also show the information for Explained Packages
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.
Data Loader:
we've done a number of performance enhancements; in particular Load Using Insert statements should now run considerably faster
we've added a new load option Use Bulk Inserts. This will load multiple rows in a single Insert statement. In some scenarios this provides a very fast method of loading.
there is now an option to truncate data values to the defined length of character column. This is useful when loading "untidy" data.
when the Data Loader was run from the Run SQL window, the Abort button was not active
when loading from a file, it is now easier to specify the column delimiter
There is now an Option to disable the Export function.
now has an option to remember your compare options between sessions
resync can now generate drop / create statements for objects whose definition can only be resynced if they are dropped and recreated. An example is indexes.
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.
Data Compare:
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.
Bug Fixes
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
for DB2/UDB:
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.
there was an error running a DB2 z/OS Stored Procedure with returned an XML value
when a hex value was entered for a parameter, AQT would incorrectly give a warning message that the value was too long
Other:
for some users, SQL Server gave very slow performance when the Use ExecDirect option was used.
for SQL Server and MS Access, AQT returned an error on update/delete when the Use ExecDirect option was used.
for DB2 z/OS, the Package ConToken was sometimes not displayed correctly. Depending on the data, AQT would attempt to convert the ConToken from Ecbdic to Ascii. It will now always be displayed in hex.
a number of admin functions (Compare Objects, DDL) failed for some databases when the table had more than 500 columns.
PostgreSQL had the Referenced Tables and Dependent Tables displays the wrong way around.
Batch processing sometimes hit an error that the log file was in use
for setparm, the value=$list parameter did not work if there was a blank prior to the bracket
for setparm, the value clause was not picked up if preceded by a blank
the Run SQL window no longer had File > Close. The Close item has been reinstated.
when running multiple SQL statements, the Abort button did not cancel the SQL statement which was running.
the History window sometimes gave an error when Show Saved Results was clicked
in the Create Table window, the dropdown for column data types sometimes had the same value multiple times.
when running AQT in batch, SQL was truncated to 1000 bytes when written to the log file. You can now increase this to a higher value by setting option BatchSQLLength.
the SQL didn't show the cursor position in the box at the bottom right (as it should). Also the View > Show ascii value of current character did not work.
for some users, AQT crashed when displaying a Teradata table which had timestamp columns
in the System Monitor window, you were unable to copy data from the text box in the bottom panel
for PostgreSQL, the Table Properties display failed for PostgreSQL v12
for PostgreSQL, AQT didn't generate correct DDL for functions
for Informix, AQT incorrectly included the rowid when displaying tables
for Oracle, AQT hit an error when generating the DDL for large packages
AQT hit an error when loading a BLOB of more than 2GB in size.
For Informix, there were a number of problems with the admin component and Compare Objects.
AQT took a long time to display a large Redshift table. The add Top x clause option has been amended to include Redshift.
Teradata hit an error when using the add Sample x clause option and the query had a Union / Minus or Except clause. For these statements, AQT will not add the Sample clause.
When creating a SQL Server index, it would sometimes incorrectly have the clause INCUDE().
When creating an Index, the Asc/Desc column did not have a dropdown list, as it should.
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
when SQL is generated you can choose to have commas generated prior to the column names in the column list. This option is in Options > Line Breaks
on the tables on the Gui tab, you can choose to have the correlation name appear prior to the table name. This option is in Options > Misc.
when you mouse-over a table header, you will get a tooltip with the full table name. This is useful if your table names are very long.
when specifying a column correlation name, you can use a colon to include the name of the column,
when you add a table to the query from a User Defined Relationship, the table correlation name is taken from the UDR Alias name.
there is now a Filter box to show only particular columns in a table
the Help for the Query Builder has been improved
Activation
some minor improvements to the Activation process
AQT will now contact our activation server on a regular basis (eg. every 5 days). This help some of our corporate customers who wish to know which licenses are still being used, and which are not.
Filters (in the Database Explorer and other places):
can now filter on all columns in the grid, not just the first. To specify this, use a # at the start of the filter.
filters will now be highlighted in green. It will now be more noticeable when you have a filter in effect.
File paths, such as Options > File Locations, can include Windows environment variables. Example: %APPDATA%\AQT\Queries
On the Open / Save window, the columns you have sorted by are remembered between sessions.
There is now an option to not include the schema when table names are used in the SQL and Query Builder windows.
Improved support for Microsoft Azure databases.
The Run SQL window:
there is now a combo-box on the toolbar where you can specify the number of rows to be displayed
the Select to matching bracket function was difficult to use. This has now been changed to Select within brackets. This will select the text within the brackets containing the cursor.
there is a new function Run within Brackets. This will run the SQL within the brackets containing the cursor. This is useful for running sub-select clauses.
there is now an option for the default query for a table to include the full column list (rather than using select * from table).
you can now export to multiple files. This provides a mechanism to spread a large export over a number of files.
can now export data in JSON format.
when exporting to a CSV file, you can specify the encoding of the file
AQT will now create the export directory if it doesn't exist
export to Excel did not format timestamp values correctly. Note that the decimal parts of a timestamp value will not display fully - this is a limitation of Excel.
For DB2 for z/OS
AQT now supports Archived tables.
AQT will now show the Image Copies of a Tablespace
There is a new display - Index Columns - which shows a table's indexes and index columns in a single list.
when displaying XML values, there is now an option to display the XML as Formatted XML. This will add line breaks, indenting and syntax highlighting to make the XML data more readable.
there is now a JSON viewer for displaying JSON values
if you have a lot of columns in your display, you now "find" a column with Grid > Find Column (Ctrl + Shift +F)
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.
Other:
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.
Objects were not shown for SQL Server when the Use SQLExecDirect option was used
times were displayed in 12-hour format, not 24-hour format.
the directory box was too narrow for long directory names; has been made wider
did not show Help when F1 was hit
Data Compare:
was not able to compare tables which had hidden columns. This included DB2 tables defined with Distribute by Random (these have a hidden column). Plus it included DB2 for z/OS Temporal Tables.
export results to Excel would sometimers highlight the wrong columns.
export results to Excel would not behave well when the Excel file name was invalid, or it hit an error writing to the file
Data Loader:
when selecting a table, the table-list wasn't refreshed after you had switched to another database
there is a new option Display unprintable characters as which allows you to display all unprintable characters in a readable format.
the Hide all Columns where.. function now allows you to specify multiple values. This setting is now remembered between sessions.
In the Preview dialog of the Row Update window, we have added a Copy to SQL Window function.
The result-grids on the SQL and Query Builder windows can now show the Group By box (right-click the grid for the option to show this).
When you customize a toolbar, you can specify that some items are right-aligned on the toolbar.
We have added a window for displaying and amending environment variables. This was borne out of frustration with Windows poor tools for managing these.
exporting to a Document Locator file now supports Append Mode plus exporting to an new worksheet on an existing Excel file.
DB2 z/OS now has better support for Roles.
In the Query Builder you can now clear all display-grids with View > Clear all grids.
Bug Fixes
For DB2 for LUW:
the tables in tablespace display showed all tables, rather than those just in the selected tablespace
DDL for Bufferpools did not have the Database Partition Group clause
the table Internal Detail display was very slow
DDL for partitioned tables incorrectly had DISTRIBUTE BY when it should be PARTITION BY
table DDL was not correct for columns defined with type CODEUNITSxx.
for DB2 v10.5 and above, AQT will generate the ORGANIZE BY ROW/COLUMN clause.
Index DDL was sometimes generated with a NON UNIQUE clause, which is not valid
XML values were sometimes not able to be displayed
DDL was incorrect for columns defined with TIMESTAMP WITH TIME ZONE
in some cases, XML values were not able to be displayed
For both DB2 LUW and DB2 z/OS:
DDL was incorrect for timestamp columns where the length was specified - eg. TIMESTAMP(9)
We have improved the way AQT handles tables with Hidden Columns. In particular the Row Update window sometimes generated incorrect SQL for such tables.
Timestamp values were sometimes not formatted correctly in insert / update statements
the column list for a table was not refreshed when the refresh icon (above the column list) was hit.
Format SQL truncated a statement which was longer than 32K bytes
Format SQL sometimes omitted the UNION clause, or added one erroneously
the Database dropdown box was sometimes not wide enough for very long database names. It will now resize to the width of the database names.
when the Link SQL window to Data window option was used, AQT would sometimes link to an existing data window rather than a new one
when FIPSAlgorithmPolicy was enabled, the editor would crash when pasting text
When using Run using SQL*PLUS in the Run SQL window, AQT ran the entire SQL, not just the selected SQL.
Run using SQL*PLUS gives an error when a line is 3000 bytes or more (this is a limitation of SQL*PLUS). AQT will avoid this problem by splitting long lines into lines of less than 3000 bytes.
You can now specify how many Recent Query items are to be maintained
There is a new option Run to Cursor. This will run all SQL up to the current cursor position.
The Sort Columns option above the column-list will now cycle-though no-sort, sort-ascending, sort-descending. Previously it did not have a sort-descending mode.
There is now an option for AQT to save your SQL when you close the window (even if it is not a saved-query)
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
Other:
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.
the setting for background color of the editor was ignored, if this was specified as being a different color for different databases.
multi-line text was pasted oddly.
when FIPSAlgorithmPolicy was enabled, the editor would crash when pasting text which ended in a linefeed.
copy text sometimes hung for a long time, then failed.
when using Autosave, the save message was written to the status-bar at the bottom of the window, overwriting other messages. AQT will now not give you the save message when a query is autosaved.
when hitting the backspace key, the cursor would sometimes jump to the end of the query
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 you click on Get Values, AQT can get the values from the Foreign Key defined for the column. This will be done when Options > Row Display/Update > Get Values from Foreign Keys has been selected.
once you have clicked on Get Values to get the list of values for a column, this list is retained / re-established while the window is open and doesn't have to be re-fetched.
the option Edit > Enable Sorting is now remembered.
now has an option to Show Row Detail. This allows you to see the detail of a comparison of an individual row. You can step through the compare results in this mode.
you can now use View > Only show rows which are different to only see the rows which are different.
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.
on the Database Explorer window, there is now a DDL menu item. This can be used for generating the DDL for all the objects in the database. This option is only available for some databases.
a new option Format View definitions. This will format the view text to a more readable format.
a new option Include a Commit after each object. When this is selected, AQT will include a Commit statement at the end of the DDL for each object.
Other:
Charts can now run queries that contain parameters (and queryparm commands)
You can now Activate your License using a web page. This is an alternative should online activation not be possible.
For DB2/UDB you can now generate the DDL for all objects (eg. All Tables). This is done with the DDL option on the main menu-bar of the Database Explorer window.
When Exporting to a delimited file you can specify a hex column delimiter.
In the Database Explorer window, you can now select multiple objects then click on DDL, Drop table etc. The selected objects will be selected in the new window.
when loading from Excel files, the cell formula rather than the cell value was loaded.
when loading from Excel files, date and time values were sometimes loaded as the internal numeric value rather than the string representation of the date/time.
when creating a table from a file or Excel file, AQT looked at all rows, ignoring Start From Row. This sometimes led to the columns being created with the wrong data types.
the Data Loader didn't give an error message when it hit an error opening an Excel file
when creating a new table, the DDL would be incorrect if a column name contained a comma
the load sometimes did not check whether the target table exists
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.
The Signon window has a new look, plus makes it easy to sign onto File DSN plus Access/Excel files
Compare Objects can now generate a script to resync the objects / schemas being compared.
Other New Features
The displays in the Database Explorer can now be cached to disk. This allows for a faster startup of AQT for databases which have a large number of objects.
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.
when running a script, you can now set the statement delimiter at the start of the script.
has a new function to Rewrite your SQL. This is the equialent of importing your SQL into the Query Builder and back again; the SQL will be rewritten as per the options in the Query Builder.
The Data Display window:
has an option View > Use column expressions as headers. This is useful for complex queries - the column header (title) will be taken from the column expression rather than the column name.
Miscellaneous:
The Window-list buttons now display a close button. This makes it a lot easier to close windows.
There is a new Scripting command Sleep. This will cause the script to sleep for a given number of seconds, or until a particular time of day. This can be used as a basic scheduler.
You can now specify any arguments that need to be passed when using an External Editor.
You can now specify that the display tabs in the Run SQL and Query Builder windows have a "Copy to Data Display window" button. This makes it easy to copy the results to their own Data Display window.
Changes in behaviour
AQT v10 licenses now need to be Activated. This registers the use of the license on our servers.
AQT now runs under .Net Framework v3.5. This is an inbuilt part of Windows 7. For Windows 8 you may have to install this. See Installing AQT under Windows 8 for details.
