On the Mapping tab, an Auto button has been added. This will automatically match columns between the source and target if they have the same column names.
A new option to Replace commas with fullstops for numeric columns. This is useful when the input data uses a comma as the decimal separator. This needs to be converted to a fullstop before it can be loaded into the database.
There are new mapping functions before and after to extract the text before/after a given delimiter.
DB2 for LUW:
now has a function to rebind all the dependent packages of a table
now supports the use of Row IDs (RID function). These can be displayed with Options > Display Options > Show ROWID.
you can now delete, rename or change the type of columns.
Other:
The Query Builder has a new option as to whether column names are to be qualified when Table Ids are blank
In the Data Compare window, you can now type the name of a table (as opposed to using the select button)
There is now an Option for Date columns to be displayed as character. Previously the option Use Correct Data Types for Grid columns displayed all data types as character. With this new option, you can now do this for Date columns only.
You can now open a particular Favorites file for your database. This can be handy if you have developed a favorites file which you wish to use with a number of similar databases.
did not successfully reverse-engineer some queries
now has Export as a default button on the toolbar
sometimes generated incorrect SQL when columns were generated in lower case and Options > Always Quote table and column names was specified
creating a new Where clause failed for a MySQL Decimal column
generated SQL with syntax such as select 'literal, * from table which is invalid on many databases. Changed to specify the full column list instead of *.
when Options>Always Quote table and column names was specified, tables added to the query weren't always quoted.
changing the Statement Type on the Options tab had no effect
some problems with DDL generation:
the Generate DDL window now has buttons Select All and Select None on the options tab. Previously the menu-bar options were confusing as to what they applied to
didn't deal with Filter or Include clauses on SQL Server Index DDL
incorrectly included Statistics indexes on SQL Server table DDL
didn't deal with the syntax varchar(max) on SQL Server table DDL
DB2 for iSeries DDL didn't generate the Encoded Vector keyword correctly
the Row Update window sometimes did not pick up a changed value if F5 was used to run the update.
New to AQT v9.0.8
New Features
There's a new option Set pwd when select dbs on the Signon Window . This specifies whether AQT is to retrieve the saved userid / password when a database is selected and you are in Show All mode.
you can now build/edit Table Expressions in another Query Builder window
you now have an option to rename a display tab
A few enhancement to Format SQL:
this no longer removes comments that precede the statement
for Access and SQL Server, [] delimiters will no longer be replaced with double-quotes
will now format sub-select statement in columns and table-expressions
when displaying the View Text in the Database Explorer window, you can right-click and select Format SQL
You now have more flexibility with the way in which the database Name is composed. This is specified on Options > General.
The grids in the Run SQL and Query Builder window now allow you to do Show Totals (right-click the grid to see this option).
A few enhancements to the display of XML data:
you can display XML values using AQT's XML Viewer. In the Data Display window, double-click the XML value (to see the Detail of a Data Value window) then click on View > XML Viewer.
the Database Explorer window will now show Oracle XML Tables
the Database Explorer window will now show DB2/UDB XSR Objects
AQT will now successfully display Oracle XMLData values
in the Database Explorer window, when XML text is displayed in the right-grid, you can right-cick and select Show in XML Viewer.
You can now define a Schema (or other objects such as Tablespaces) as a Favorite. AQT will dynamically retrieve the list of objects when you click on the favorite Schema. This enhancement allows you to combine both the normal Favorites display and the Objects display on the Favorties display.
for DB2 z/OS Databases incorrectly specified STOP rather than STOPDB
did not work well when invoked from a User displayed in the right grid
A few problems with DDL generation:
SQL Server Foreign Keys sometimes did not include all the foreign key columns
DB2 z/OS Foreign Key definition did not include the ENFORCED clause
Table DDL for Vertica was not correct
Other
the Signon window sometimes opened with the wrong height when the Advanced panel was displayed
Get Values did not retrieve SQL/MX timestamp values correctly
the Admin system windows always used a semicolon as the statement delimiter when the Preview button was clicked. This has been changed to use the statement delimiter as per Options > Statement Delimiter.
changing the date format in batch (with --aqt options,dateformat) did not work.
the Alter Table window sometimes did not highlight the changed rows, and sometimes incorrectly allowed values to be modified
when a DB2 z/OS update / delete affected zero rows, AQT incorrectly indicated that this was a failed execution
New to AQT v9.0.7
New Features
There is now a 64-bit version of AQT. On a 64-bit machine you can run AQT as either a 32-bit or 64-bit application (which you will use will depend on whether you are accessing a 32-bit or 64-bit database client).
In the Run SQL and Run Multiple Statement windows there are now a Remove Comments function. This will remove the line (--) comments from your SQL.
When reverse-engineering SQL into the Query Builder, comments preceding your SQL will no longer be removed This is useful if you are using queryparm or setparm to set some parameters for your query; these will now be retained.
On the Data Display window, when you are doing Group Calculations, you now have the option for the summary row to be displayed when the groups are collapsed.
Stored Procedures weren't being run when they were double-clicked
for DB2 z/OS, Package Statement and Plan Statement displays were showing some garbage characters at the end of the text
did not work well for a 1010data database
could not copy data from the right-grid popup window
in some circumstances a Favorite could not be deleted
Charts now have Delete and Rename options in the Admin toolbar
The data for DB2 z/OS Synonyms could not be displayed. This has now been improved, though you will only be able to display synonyms within your own authid.
when you selected some SQL and clicked on the Run button, AQT would run the entire SQL not the highlighted SQL (this worked OK when F5 was used to run the SQL).
when a piece of text was highlighted and a keyword or column was added to the SQL, the highlighted text would not be replaced
Ctrl+B didn't show the no-listbox option
in some circumstances, got an error opening / parsing / editing SQL text
File > Edit Keywords did not work
when using Export to Table, clicking on click here fails
Intellisense showed the tables dropdown, even though this option was de-selected
Intellisense tables dropdown crashed when there were a very large number of tables
Intellisense - the value of Max Dropdown Items was sometimes corrupted
Unformat Data had a limit of 10K characters
In the Run Multiple Statements window:
the Ignore Errors on Drops option was not working if there was a comment before the DROP
the grids on the Mapping tab will now scroll in sync.
there were problems loading from an xlsx file.
when loading from an Excel file in batch, Excel was activated once the load had completed. AQT will now behave more sensibly - e.g. close the Excel file and Excel if they were not open when the load started.
You can now Insert a row into a table without first having to display the table. This is done by right-clicking a table in the Database Explorer window and selecting Add New Row.
there is now a function right-click > Remove invalid characters. This is useful if you are pasting your SQL from an email or Word document; these can both include odd characters that can prevent the SQL from running.
there is a new function right-click > Copy as RTF. This will allow you to copy/paste your SQL into a word processor (such as MS Word) retaining the text colors.
For DB2/UDB, we now display Schemas; Schemas can be created / dropped.
you can run your SQL against another database (see Query > Run Against).
you can Refresh the data on one of the display grid with right-click > Refresh.
On the Signon window, you can create a New connection when in Show Recent mode. Previously, you needed to be in Show all mode.
Generate DDL now allows you to generate the authorities for sub-objects
With Auditing, you can now include the include the user's OS Userid in the audit file name by including <osuser> as part of the file name
Changes to behaviour
If auditing is selected, AQT will not run your SQL if it cannot write to the Audit file. Previously, AQT continued without writing to the audit file.
When you get an error, AQT will now display a new message-box. This has extra diagnostic information that can be useful for helping us diagnose the problem.
Bug Fixes
A number of improvements to the behavior of the Manage Authorities window.
if a cell was being edited when you moved to another row, the value being edited would remain being displayed, rather than changing to the value in the new row.
a value would sometimes not display after being selected from the drop-down list of values
when in Insert mode, and the Keys button was hit, all columns except the key would be de-selected
You can now Export to Excel using a Native Export, which will export directly to an Excel file. The previous method involved invoking Excel, then sending commands to it. This was slow, and could be unreliable depending on state of Excel. With the Native Export:
you do not need to have Excel on your PC to export to an Excel file
this export runs faster than the other Excel export methods
Grid Printing has been improved so you can specify portrait/landscape, and can specify the printer.
On the Database Explorer window, when you right-click an object you now have an option to Add to Favorites. This makes it a lot faster to add objects to favorites. When you right-click a favorite object, you now have an option to Delete from Favorites.
The Run SQL window now has an option View > Show special characters to see the blanks, tabs and line-feeds in your text.
Options > Diagnostics has now an option Show Errors. This will show you any errors that have occurred during your AQT session. This can be useful diagnostic information for Cardett Associates should an error occur.
occasionally crashed while scrolling or moving the cursor. This may be due to a bug in the text-editor component we use. We have upgraded to the latest release of this component; hopefully this will resolve this problem.
when running a very large script, it took a long time for the Run Multiple Statements window to open
when using Format Data on selection range of text, some text was sometimes removed
Export to > SQL Window failed
the edit toolbar sometimes overlayed the text box
when your SQL used unqualified table names, the column-list for a table was sometimes wrong
sometimes copied the wrong object when Ctrl+C was hit
sometimes prompted the user to save a query when it had not been changed
Export to Table didn't handle a query which had user-defined parameters
got message "Old format or invalid type library" when loading from an Excel file
loading from an Excel file in batch failed with message "Object reference not set"
when deleting the contents of a DB2/UDB table, it erroneously displayed the message "Unable to delete contents as delete command will affect entire table"
sometimes positioned new tables out of sight behind existing tables
when there were multiple Where clauses, the order of these clauses was sometimes incorrect
on the Where Clause window, Get Values sometimes retrieved the values for the wrong column when the "Only show cols which are in the query" option was used
the Options window:
did not allow a Display Format of HH:mm:ss
had an error when changing the read-only mode setting
Misc
AQT behaved more sensibly for a user for which the AQT install had not been run, and for a non-admin user
Get Values did not work when the table name had spaces, minus signs or other special characters
there is now an option (View > Display Multiple Grids) to specify whether you want multiple Data Grids displayed.
there is now a function View > Clear all grids to clear all the existing display grids.
you can now rename a grid-tab
you can right click a display grid and select Revert to this SQL. This will change your SQL back to the SQL that was run to display the data in this grid.
there is now a shortcut key for Format and Unformat (Alt-F and Alt-U)
there is now a script statement for setting the Max Rows Displayed
the bottom part of the History window is now sizeable
there is now an option for AQT to run a commit after every statement. This is useful in some environments in which AQT doesn't fully release locks.
sometimes scroll-bars were displayed, which disappeared once they were clicked
there was a problem whereby multiple clicks were needed on the AQT window-list bar to activate a window. This was due to the tooltip sitting on top of the button. This is a known (Windows) problem with the way this tooltip is positioned. We have circumvented this by not showing tooltips when the window-list bar is positioned at the bottom of the AQT window.
after adding a new column to a table, AQT sometimes showed the old column list for the table. This is due to AQT's table cache, which is needed to avoid repeatedly getting column information for the tables. This problem can be avoided by using a new function Tools > Reset table/index cache (from the Database Explorer window) which will reset this cache.
the Create Table window sometimes did not convert data types correctly when copying the table definition from a different database type
when generating DDL for DB2 z/OS indexes, an error message was given and the index columns were omitted
the Query Builder didn't enclose table or column names in quotes when this was required
the System Monitor sometimes got an error displaying SQL text in the lower panel
New to AQT v9
AQT v9 is a significant redevelopment of AQT in the .Net environment. This gives AQT a modern platform; this provides a number of benefits plus will allow AQT to progress into the future.
As part of this change, we have implemented:
a new grid component. This has a number of differences in behaviour, which are outlined in the following section.
a new text-editing component. This provides a number of extra features, principally the ability to show a drop-down list of table names, column names, column functions or keywords when you are building a query in the Run SQL window.
Changes to Behaviour
The new grid component behaves differently than the grid used by AQT v8. For an description on how to use the grid, see Grid usability concepts. Differences with the v8 grid are:
Selection. Cells can no longer be selected by dragging the mouse over cells. Instead you must click a cell, hold down Shift then select a second cell. All the cells between the two clicked-on cells will be selected.
Hiding Columns. In the Data Display window, columns can no longer be hidden by dragging them "away". Instead, you must right-click the column and select Hide Column.
Ungrouping Columns. In the Data Display window, you can group by a column by dragging the column to the group-by area at the top of the grid. This works the same as with AQT v8. To remove a column from the group-by area, drag it to somewhere outside the group-by area. The column will have a cross, which may indicate that you are dragging it somewhere invalid; however once you drop the column it will be removed from the grouping.
Moving Rows. The grid doesn't have any in-built ability to move rows. In places where you need to do this, there will be buttons Move Up and Move Down.
Filtering. Filtering is used when you use the filter box, or in the Data Display window when you use Edit > Find. Filtering is slow, so should be used with caution when you have a large amount of data.
New features of the new grid component are:
Styles. You have flexibility of the style used for displaying your data. You can select between a number of in-built styles, or create your own.
Column Totals. On the Data Display window, you can now get totals for numeric columns by clicking on View > Totals (Ctrl+T) or the Totals toolbar button.
Printing. The grid has two different ways of printing the data - Print Data plus Generate Report. Print Data is a a basic print of the grid. The data Display window also has Generate Report, which gives more flexibility and styles of the printed data.
New Features
New Appearance. Some of the windows have a slightly new appearance. The Database Explorer window, in particular, has had a number of changes to improve readability and functionality. This is described at Customizing the appearance.
When you right-click an object in the middle grid, the dropdown now gives a more comprehensive set of options. You can go back to the v8 style of dropdown with View > Simple dropdown menus
Charting. This is a major new feature, which allows you to create charts of your data. Once a chart has been created, it can easily be saved and reapplied to your data at a later time.
Summarizing.There is a new window to make it easier to develop Summary queries on your data.
New Text Editor component. We have implemented a new Text Editor, which is used by the Run SQL window, and the Manage Procedures and Manage View window.
this is component has Intellisense, which is the ability for AQT to display a dropdown list of tables, columns or function names as you type.
when writing Stored Procedures, Begin / End blocks are marked and can be collapsed
Pause Query. When AQT is displaying the result of a large queries, you can Pause and Resume the display. This is an alternative to the Get More Rows feature.
Where Clause window. This window has a number of enhancements:
it is simpler and easier to use
it can now build sub-selects, including correlated sub-selects
it can now build an IN clause
it can be invoked from the Run SQL window with Ctrl+W
Options
We have made a lot of changes to the way Options are stored. This provides a number of benefits:
Options can be exported and imported. This allows Options to be saved and moved between machines
you can have different options for different databases and database types
it is now much easier for system administrators to set a fixed option which cannot be changed by the end user
As a result of these changes, most options are now held in a different part of the registry than the v8 options:
when you run AQT v9 for the first time, the v8 options will be migrated to the new v9 options
after this point in time the two sets of options are separate. If you change an option in AQT v8, it will not be reflected in AQT v9 (and vica-versa).
Minor Enhancements
External Editor. You can now use an external editor other than Notepad. The editor to use is specified in Options > General.
Note that, in the Run SQL window, the Edit SQL using Editor function now works slightly differently. In the past it waited until the editor program ended. Now, you click on the Abort button for AQT to get the updated-SQL back from the editor.
Query Builder. This now allows you to specify (simple) Where clauses on the Columns tab.
As with the Run SQL window, you can now display query results on this window. This feature is turned on/off with View > DIsplay query results on this window.
You can revert back to an earlier form of your query by right-clicking one of the query-results and selecting Revert to this query. This provides a basic undo capability for the Query Builder.
Some Technical Things
AQT now has:
Improved multi-threading. AQT can now run more than one query at once, plus has improved ability to cancel queries. The AQT Cancel Queries task is no longer required.
Note that AQT can only run a single query from each database connection. Most ODBC Drivers do not allow a single connection to run more than one query simultaneously.
64-bit support. AQT will have the ability to run as either a 32-bit application or as a 64-bit application. There will be separate AQT executables for each of these. This is important if you have 64-bit database clients. For more on this see Installing AQT on a 64-bit version of Windows.
Note: the 64-bit version of AQT hasn't yet been completed.
Full unicode support. Previously, there were some items (such as window captions and status bars) which were unable to display Unicode characters.
