New to AQT v11.0.6
New Features
Bug Fixes
- The Database Explorer window:
- 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.
- 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.
- Export Data
- 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
Bug Fixes
- AQT didn't process a Rollback to Savepoint correctly
- when running a script, the Parameters prompt window was sometimes shown multiple times
- when running a script, setting the sdelim was ignored if another option was specified in the --aqt options statement
- when typing text in the SQL window, the FROM dropdown box was sometimes shown inappropriately
- in the Export dialog:
- Append mode could not be selected for Excel worksheets
- when using Export as Insert statements, AQT didn't give a warning when the insert-table was blank
- in the SQL window, when a query which contained an --export statement was run, the query was not exported
- Restore Session didn't reestablish the correct order of the window-list tabs
- For PostgreSQL, Find Columns had schema and table the wrong way around.
- For Sybase ASE, the Query Builder sometimes got an objref error
- Open CSV file got a CPU loop when a line ended with the delimiter character
- When a Version Upgrade license was entered into the Product Registration window, it was not shown in the Current Registration Keys grid
- On the Database Explorer window, the Table Contents display (in the right panel) did not give a correct message about the number of rows displayed
- On the Database Explorer window, the Table Contents display (in the right panel) did not show row numbers
- In the Query Builder window, clicking on All would add the columns multiple times
- For DB2 z/OS:
- generate DDL got an error saying that object ViewOnView not defined.
- when comparing tables, the resync script didn't generate the Default clause correctly
- Some users experienced a long delay (20 seconds) when AQT started. We have added an option so that affected users can prevent this from happening.
- When moving columns in the Data Display window, the regenerated SQL can sometimes be incorrect
- In some circumstances, the Row Update window can update the wrong column
- In the System Monitor, when a command (such as Kill Process) fails, AQT did not give the error message
- on the Charting dialog, the chart was sometimes did not redraw when a change was made to the chart
New to AQT v11.0.4
New Feature
- there is a new window Open CSV File. This is useful for displaying the contents of a CSV file without having to use the Text ODBC Driver.
- there is a new window Merge Table. This will build and run a Merge statement.
- a few improvements to Charting:
- when displaying a chart in the Database Explorer window, there is a now an option for displaying the Chart SQL in the right panel.
- improvements on how Charting deals with Null values.
- 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
- when using Transaction Mode,
- the transaction mode warning has been improved and now appears on more windows
- you are now prompted to commit changes before closing the database, rather than when closing the SQL window
- there is now an improved message warning to not close the connection without committing
- on the Data Display window, when you click on Apply new rows, you will now be shown the progress of the apply, and will have the option to cancel it
- SQL Server will now display constraints as a separate item in the Object Tree
- when generating DDL to a single file, the filename will be placed in the SQL Query history. This makes it easy to open and run the DDL file.
- when using Parameters, you can use a parameter character of @
Bug Fixes
- A few fixes to Charting:
- when displaying a chart in the Database Explorer window, the status-bar incorrectly showed the message "No objects displayed".
- on the Axes tab, the Format couldn't be specified for the vertical axis.
- on the Axes tab, the Axis Title could not be changed
- incorrectly got message "Value is not a valid Date" when changing Axis Min/Max
- null values were incorrectly charted as zero
- A few fixes to Compare Object:
- column names comparison was always case-independent, rather than being compared as per the option Ignore case when comparing.
- when using Creating a Batch Job on the Load Multiple tables window, a Connect statement wasn't generated for the source database.
- Foreign Keys were not be fetched correctly for SQL Server
- for some databases, Primary Key columns were not being compared correctly
- in some circumstances, column types were not compared correctly
- for PostgreSQL. multi-column foreign keys were not compared correctly
- when generating a resync script, would sometimes give the message Error "Primary Key" not found
- when generating a resync script to a database of a different type, the wrong data types would be used
- A few fixes for Data Compare:
- the Numeric Compare option was not saved correctly with File > View Compare Options
- Numeric Compare was not being done for the SQL Server money data type
- Compare all Tables in Schema sometimes did nothing when the Compare button was hit
- when Compare was run from the SQL window, the Abort button in the toolbar was not active, nor result message given
- for SQL Server, a unique key columns were not being being picked up from a unique index
- a few problems when running AQT in batch:
- when a Connect was inside an Include file, it was not processed correctly
- comments in the SQL were not removed, resulting in the statement following the comment being ignored
- in the .bat file, filenames containing \\ were not processed correctly
- running Generate DDL in batch didn't work well for some databases
- in the Query Builder window, de-selecting the option Always qualify table names sometimes did not work.
- in the Data Display window:
- the Parameters panel was sometimes displayed when there were no parameters
- the SQL generated when you moved or hid columns would sometimes omit quotes around column names
- when Reconnecting to the database, a number of setting were not reestablished.
- Find - Standard on the Data Display window would terminate prematurely when there were hidden rows
- There was an Object Reference error when removing a license key
- The Data Loader would fail when using Load using Insert statements and the database disconnects due to the Disconnect if Idle option.
- In the Run SQL window, the Check Syntax function was not working correctly
- When altering a table, AQT sometimes generated the same Alter statement more than once
- For SQL Server, AQT hit an error displaying a smalldatetime column when Bind all cols to strings was used
- Display of Check Constraint text didn't work for recent versions of PostgreSQL
- in the Row Update window, in rare circumstances the wrong column would be updated
- in the Load All Tables in Schema and Compare all Tables in Schema windows:
- rows would sometimes lose their highlight color after they have been click on
- table names would sometimes be shown with the wrong case
- other fixes for Load All Tables in Schema
- the grid would be reset after going to the Options tab
- loaded all rows irrespective of the Maximum Rows option
New to AQT v11.0.3
New features
Bug fixes
- the Query Builder truncated a large CASE statement
- a number of miscellaneous problems with displays for PostregSQL
- Compare Objects did not work well for PostgreSQL
- 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.
New to AQT v11
Major New Features
These are described in the section What's new in AQT v11.
Other New Features
- Exporting.
- the layout of the window has been improved.
- 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
- there is now an option to use Block Selection
- you can now specify the character used with parameters with: --aqt options,parmchar=$
- there are now buttons in the Edit Toolbar to move a line up or down
- the Data Loader
- there is a new option Ignore lines where. This allows you to specify conditions where the input data is ignored by the Data Loader .
This can be used to ignore "junk" rows when loading from a file, or to only load rows with a particular set of data.
- has several fixes with the use of Create new table mode
- has new functions:
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
- Connection Strings can now use encrypted passwords.
- Greenplum will not show Materialized Views
- the Query Builder will now display the SQL using syntax highlighting
- the Save Queries dialog now has tool-bar shortcuts for OneDrive Personal, My Documents and Google Drive.
- in some circumstances, Disconnect if Idle didn't work. Also, Auto-Reconnect sometimes wasn't being done.
- 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.
Existing users of this should read Changes to Batch Jobs with AQT v11.
Bug Fixes
- 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-Reconnect options, 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.
- User-Defined Relationships sometimes saved a relationship multiple times when the cases were different.
- For schema-less databases (such as MySQL), AQT failed to find the columns for a table when it was prefixed with the database name.
- on the Run SQL window, Check Syntax would run the query when the Use SQLExecDirect option was being used.
- when you had a very large query in the SQL window, clearing the text or closing the window would take a very, very long time.
- on the Data Display window, the columns widths were sometimes excessively large.
New to AQT v10.1.2p
New Features
- When Activation is using SSL (https), it will now also use TLS1.2
- You can now specify a connection statement in the AQT command line. This allows you to connect to a particular database when AQT starts.
- For PostgreSQL, AQT will now display the different databases within the instance. You can switch to another database.
Bug fixes
- A few fixes to Data Export:
- 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
- A few fixes for the Data Loader:
- when the user was prompted whether to Drop the target table, the Yes option was ignored
- when loading from an Excel file in non-native mode, all values came through as Null.
- when loading from an Excel file in non-native mode, timestamp values were sometimes not formatted correctly
- the Scan function (when in Create mode) sometimes didn't recognize date and timestamp values correctly
- the Scan function sometimes failed with
Object invoked has disconnected from its clients
- A couple of fixes to Charting
- 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 will 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
- The setparm and queryparm commands didn't correctly process a value contained in brackets, such as value=('A','B','C')
- Generate DDL for DB2 z/OS sometimes failed with message "get_parms has detected a possible recursive situation"
- In some circumstances, viewing a file failed when the file contained an embedded blank
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 there is now a tool for easily Explaining 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.
- A number of enhancements to Charting.
- a lot more information has been added to the Help
- Bubble Charts have been added
- the Cumulative option can now be different for different series
- AQT will now, by default, show the column names on the Axes
- you can now specify a Logarithmic scale
- you can now specify the format of the values in the Axes. For instance, numeric values can be formatted as a currency amount.
- Compare Objects:
- 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).
- A few improvements to Data Export
- 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.
To avoid this problem, AQT will switch to using the DBA views if the user has authority to use these.
The problem can also be eased by using option Use Disk Cache for Schemas.
- the Database Explorer window:
- some queries populating the window could not be canceled
- the grid headers did not have a distinct font
- in the Query Explorer display, some of the Shared Queries display showed Personal Queries
- for some users, the Queries display in the Database Explorer sometimes showed directories in random order
- in the Run SQL window:
- the fonts on the SQL Editor didn't display well for some users. To resolve this we have changed the default font from Courier New to Consolas.
- a Shared Query sometimes wasn't saved when the window was closed.
- on the Open / Save dialog, the button for Shared Home was shown when the user had not specified a Shared Query directory
- the Open / Save dialog gave numerous pop-up messages when AQT hit an error reading a directory.
- a few fixes for SQL Server:
- the Find Objects feature wasn't working
- the display of Tables Only wasn't working
- some users got message
The multi-part identified "i.column-id" could not be bound when displaying the properties or altering a table.
- foreign key columns were not correct when there were multiple foreign keys between two tables
- the length of nvarchar columns was reported incorrectly in the Database Explorer window
- a few fixes for Data Export:
- when there were no rows to export, the header was erroneously not written.
- when exporting from a grid, there were several errors when the grid contained hidden rows
- when exporting from a grid, AQT hit an error when the grid had a Total row
- when exporting from a grid, decimal values were truncated when the decimal separator was a comma
- Export to Multiple Files hit an error when exporting to Excel
- 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.
- Compare Objects
- the Filter did not process the Not, Regex or Ends With options.
- sometimes didn't show schemas when the All Tables option was selected, or the user opens the Compare window before selecting a schema
- the resync script sometimes had multiple identical statements.
- Data Compare
- sometimes didn't compare numeric values correctly on systems where the decimal separator was a comma
- hit an Overflow error when using the Write Results to excel file option, and there were a large number of differences
- Ctrl+A didn't select the text in the query text boxes
- For Stored Procedures:
- 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.
- Some queries for Teradata took a long time to complete. This was due to AQT checking to see if the query returned another result-set. This is discussed in the section Multiple result-sets for Sybase / MS SQL Server / Teradata.
New to AQT v10.1.0
Installation
- AQT v10.1 now runs under .Net Framework 4.5. This includes later releases such as .Net 4.7.
New Features
- 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
- it now supports the use of the WITH statement
- it can now build column SubSelect expressions
- it can now build Case expressions
- there is now support for the TOP statement used by SQL Server, Sybase and Teradata
- there is now a combo-box on the toolbar where you can specify the number of rows to be displayed
- a few changes with the Run Against feature
- some minor enhancements to the Build IN List dialog
- 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 3 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).
- Export Data
- 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.
- On the Data Display window:
- 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)
- improvements to the display of binary values
- improvements to the display of EBCDIC values
- the sum of selected cells was incorrect for decimal values when the decimal separator was a comma.
The SQL History window:
- 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.
Bug Fixes
- 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
- On the Data Display window:
- when a layout was Applied, it did not set the style of the Caption correctly
- when a new column was added to Grouping, sub-totals were removed
- when copying, it would sometimes erroneously give the message "No cells selected"
- AQT would get an error opening Saved Results in which more than one column had the same name
- SQL Server timestamp values were incorrectly displayed in ascii mode rather than hex mode
- The Replace x'00' option on Copy Cells was not working correctly
- When displaying binary values in hex mode, only the first 50 bytes were displayed.
- A few fixes for DDL generation for DB2/LUW:
- the NOT TRUSTED clause was omitted from the DDL for Foreign Keys
- the EXCLUDING NULL KEYS clause was omitted from DDL for Indexes
- index DDL can now include column functions
- when Foreign Key DDL was generated using the "FK on Dep Tables" option, the Enforced, Trusted and Optimize clauses were omitted.
- a few problems with the DISTRIBUTE BY and MAINTAINED BY clauses for Tables
- the ENFORCED / NOT ENFORCED clause will be included in Primary Key DDL
- Random Distribution key columns had incorrectly been included in table DDL
- added Percent Encoded to the column sizing display for DB2 v11
- DDL of Boolean types incorrectly included the length specification
- the DDL for Row Organized MDC tables was sometimes wrong
- For DB2 for z/OS:
- DDL was not generated correctly for Temporal tables
- the Database Explorer did not show ARCHIVE tables
- A few fixes for Exporting data
- when exporting multiple queries to Excel, it would sometimes run in append mode rather than replace mode
- when exporting to Excel you could get message "Worksheet already exists" even though Replace was specified
- export to Excel didn't generate hypertext links for values starting with https://
- when exporting to multiple sheets in an Excel file, numeric values were sometimes formatted as dates
- when exporting to Excel using bulk exporting mode, numeric values would sometimes be formatted as text
- The Run SQL window
- would take a long time to parse some WITH queries
- Run as Single Statement incorrectly ran all text, not the selected text
- SQL History would not include entries which had dates which Window didn't recognize as valid
- would get an error message when the window is activated, and the user is editing a query which has an invalid file date
- Wrap to 60 chars did not work well when the SQL contained comments
- query elapsed time would be displayed as negative if the query spanned midnight
- Run as DB2 Command failed if your password contained a blank or special character.
- In the Query Builder:
- Options > Table Id > offset was disabled.
- User Defined Relationships didn't correctly deal with lower case table names (in Oracle/DB2) that need to be enclosed in quotes.
- Commands preceding the SQL (such as queryparm and setparm) were removed from the SQL.
- SQL containing a correlated subquery would interpretted be incorrectly as a join
- In the Open / Save window:
- 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
- Compare Objects:
- did not correctly compare constraints bewtween DB2 for LUW and DB2 for z/OS
- sometimes did not correctly compare procedure / view text when they contain comments
- Other:
- AQT 64-bit would get an error trying to display a MySQL table containing a longblob column
- Pivot and Charting did not recognize the DB2 DECFLOAT data type as numeric
- For recent versions of MySQL, the column type was displayed incorrectly
- AQT hit an error when opening the User Defined Relationships file
- Create/Alter table failed when a column name contained the string DDL.
- Manage Authorities would take an excessive time when adding a large number of tables to the grid.
- For Teradata, the column list for Views did not include comments.
- AQT did not show tables correctly for a Tribute database.
- When creating a DB2 index, the Replace if Exists option did not work.
- AQT was using the incorrect syntax when renaming an Oracle table.
- Some users got multiple messages "Error in get_options" when AQT started. This happens when the user doesn't have a My Documents directory.
- Add Multiple Objects to Favorites hit an error for SQL Server
- When Creating a new table, the default properties of columns were not being set correctly
- When Altering a SQL Server Procedure, comments before the start of the procedure were incorrectly removed.
- In the System Monitor, the Cancel Task failed
- The Oracle Display DBMS Output window didn't allow cells to be copied. A few other miscellaneous improvements to this window.
- Oracle LONG columns were sometimes displayed incorrectly
New to AQT v10.0.4z
This is a maintenance release of AQT v10.0.4, correcting a few minor problems.
New Features
- 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.
Bug Fixes
- when a license key was entered, the user got the message Activation Code is not valid
- Compare Objects for DB2/LUW failed with an error
- 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.
- For DB2 z/OS, some XML values were not able to be displayed
- Some DB2/LUW systems did not display Modules.
- The Query Builder > Cols tab did not display any columns in the left panel.
New to AQT v10.0.4
New Features
- On the Database Explorer window:
- there is now a Filter box on the Object Tree. This can be used for filtering the schemas.
- You can specify that the schema filters are remembered between AQT sessions
- Data Compare can now :
- write results to an Excel spreadsheet. This can be done when the Compare is run in batch mode, or when the Compare all tables in Schema function is used.
- when writing the resync script to separate files, there is now as option to not also write to the main script file.
- the report file will now contain info on the number of insert/update/delete statements written to the script files.
- In the System Monitor and History windows, copying from cells has been improved.
- In the Run SQL window:
- we have added an option to highlight the current line
- for Sybase, Intellisense will show you a dropdown of tables and views, not just tables
- For DB2 for LUW:
- we have added DDL generation for Database Partition Groups
- For DB2 for z/OS:
- we have improved the display of native SQL Procedures with different versions.
- the Run Procedure window will now correctly handle SQL Procedures with different versions.
- A few improvements to the Data DIsplay window:
- the Display x'00 characters as spaces now allows you to display a particular character for the x'00'.
- 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.
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.
- For DB2 for z/OS:
- 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
- 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
- On the Run SQL window:
- 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
- A few problems with Data Compare:
- the compare would crash when running in 64-bit mode and comparing LOBs
- the Generate into Separate Files options were sometimes disabled
- Customize Shortcut keys hit an error when your PC was using German or some other non-english languages
- Values for columns with the LOGICAL data type were erroneously enclosed in quotes
- For Teradata, the NUMBER data type was not identified correctly in the column display.
- For Teradata v15, AQT was not displaying the full (long) object names.
- Export to XML hit an error when exporting to a directory that didn't exist
- On the Signon window, items in the Recent list would sometimes be displayed with the wrong icon.
- The Save dialog would sometimes not recognize that the directory had been changed, and would save the file into the original directory.
- The Data Loader
- incorrectly reset the Mapping when the table being loaded, or the load-source was changed.
- did not save the history of Load Data from Table entries
- When using the ODBC Driver for Excel, tables sometimes didn't get their column list populated in the SQL and Query Builder windows
- When a query was run from the Database Explorer window, the Cancel button was ignored on the Enter Parameter Values dialog.
- History for Search Table was not saved when Search Table was run fom the Database Explorer window.
- Manage Authorities only allowed 500 items to be added to the grid when the Add to Grid option was used.
- The Row Update window truncated text in the Preview box when it contained a x'00' character.
- If AQT was positioned at the top of the screen, it would be reopened in a slightly different position.
- In the Query Builder you can now clear all display-grids with View > Clear all grids.
- On the Database Explorer window, the Number of Objects Displayed message was incorrect when a filter was applied.
New to AQT v10.0.3
New Features
- A few enhancements to the Database Explorer window:
- when you print a grid, the grid title is included in the print
- for DB2 for iSeries, when the Use Column Headers option is specified, AQT will use the COLUMN_TEXT if the COLUMN_HEADER is blank
- for DB2/LUW, the Enforced flag is now shown on the Foreign Key display
- Generate DDL
- can now save/remember your settings
- can now be run as a script statement (e.g. in batch)
- you can now specify how data types are mapped from one database to another. This is done with a statement (typeconv) in the cfg file for the database.
- AQT now interfaces with the Document Locator document management system by ColumbiaSoft.
- On the Compare Multiple Tables and Load Multiple Tables windows, you can now copy the contents of the grids.
- A few enhancements to the Run SQL window:
- 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)
- A few enhancements to Data Compare:
- you can now export the results to Excel
- you can now generate a resync script when comparing a query, as long as it is an updateable query
- when comparing a query, you will now get a warning message if the query doesn't include an ORDER BY clause
- A few improvements to the DB2 System Monitor.
- AQT can now run Teradata Procedures. This is discussed here.
- In the Run Procedure window, you can now specify hex IN values, plus view output parameters in hex.
- The Export window:
- will now prompt you if a file/worksheet exists and you haven't specified replace/append/new. In the past this caused the export to fail.
- will now remember the recent files you have exported-to. A different list is held per export type.
- A few changes to User-Defined Parameters:
- 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.
- AQT can now read QMF query files (*.qry)
Bug Fixes
- The Database Explorer
- for some databases, it didn't show your schemas when you had a single schema
- displays did not work for Netezza v7
- for DB2/LUW, the Tables in Tablespace display didn't include tables which had this tablespace as a Long Tablespace or Index Tablespace.
- the Tablespace Sizing display for DB2 for z/OS did not include all tablespaces
- in some circumstances, when the Referenced Tables option was selected, the table count was displayed instead
- did not show any tables when using the CData ODBC Driver for XML
- A few fixes for DDL Generation:
- for DB2/LUW, Table DDL did not have the Inline Length clause
- for DB2/iSeries, you can now generate the DDL for Physicals. They will be generated as a Create Table statement.
- for DB2/iSeries, AQT will now include Column Comments in the DDL.
- Data Compare:
- hit an error when the Primary Key column was a long way down in the column-list
- did not find the Primary Keys for Sybase Anywhere tables
- Compare Multiple Objects didn't copy cells from the grid when Select All > Copy was hit
- for SQL Server, the Primary Key columns were in alphabetic order, rather than in the index order
- A few fixes for the Run SQL window:
- 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 couple of fixes for the Open / Save queries window.
- The Save file name sometimes had two back-slashes in the name
- the Rename / Delete Directory functions did not work
- A couple of fixes to the Data Loader
- a mapping function which had a comma inside quotes (such as trans(“$,”,)) did not work
- when creating a table, the scale of a decimal column wasn't calculated accurately
- A couple of fixes for Export:
- when exporting to a new Excel file, you sometimes got an error indicating your worksheet already existed
- when changing between different export modes, export would sometimes export the wrong file format
- when exporting to Excel using Native export, date values weren't correctly defined to Excel as date values
- Other:
- For PostgreSQL, AQT wasn't getting Primary Keys correctly.
- Options > Time Format was ignored when formatting Timestamp values
- In some circumstances, a query run against PostgreSQL would not return an error message.
- The DB2 System Monitor would hang when the Monitor Flags were changed.
- Foreign Key information for DB2 for iSeries was not correct.
- For Turkish users, some of the Administration Component window did not display correctly.
- Load Multiple Tables ignored the setting for Delete Table Contents when this was set to False.
- Edit Data hit an error when one of the Key values was Null.
- Compare Objects sometimes did not correctly compare Informix Default values
- for DB2 for z/OS, when the Resource Limit was exceeded, AQT did not warn the user
- the Query Builder didn't give a warnng message when importing a Unioned statement
- the batch-mode flag noappend wasn't recognised unless it was in lower case
- in batch-mode, if the log file was not specified, but noappend was specified, the log file was erroneously taken as being noappend
- the Signon window didn't display Direct Connect entries correctly in the Recent list
- in the Data Layout window, the Move Up / Move Down buttons did not work after Move To was used.
- AQT would sometimes crashes when File > Exit AQT was selected.
- for Informix, the Primary Key columns were in the incorrect order when one of the columns was in descending sequence.
New to AQT v10.0.2
New Features
- A few enhancement to the signon window:
- you can now right-click > sort the Recent list
- you can add descriptions to Recent items (as was available in AQT v9)
- Some enhancements to the Data Display window:
- 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
- there is now to ability to highlight a particular set of cells in a display grid
- Some improvements to the Row Update window:
- 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.
- Data Compare:
- 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.
- The Open/Save dialog has been improved. New features are:
- you can filter files to quickly find a file with a particular name
- you can display all recently-changed files with a single click
- you can display all files in the directory plus sub-directories in a single display
- you can delete or rename multiple files
- In the Database Explorer (and elsewhere) the Filter used for filtering objects has been enhanced:
- you can search for entries ending with some text
- you use regular expressions (regex)
- A number of enhacements for the Data Loader
- improved handling of loading from space-delimited files
- new mapping functions LPAD, RPAD and NULLIF
- 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.
- A few enhancements for the Query Builder window
- on the Columns tab, there are now options to display column types and column descriptions
- on the Columns tab, there is now an option to sort the table column list
- for DB2 for iSeries users, when Options > For DB2/400 use Column Headers is specified, column Header will be added to the query when a column is selected.
- A couple of enhancements to Generate DDL
- 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.
- In Options > Diagnostics you can now specify the name of the Debug file. This can also be specified when running AQT in batch.
- Run Procedure > Copy to SQL Window will now include the parameter name in the parms statements. This makes it easier to identify which parm is which.
- You can now specify how many searches are on the Search Table window.
- For DB2 z/OS you can now use Compare Objects with Plans and Packages.
- For SQL Server, the Database Explorer will now display Linked Servers.
Bug Fixes
- A few minor problems on the Data Display window:
- when a Font was specified, the Font Script was ignored. This is needed to display some asian characters correctly.
- group Calculations didn't recognise some columns as numeric when Options > Display Numeric Columns as Double was specified.
- crash displaying a result when Options > Max Row Height was set to a very high value.
- when customizing a Style, the settings for Group Header were ignored.
- the right-click menu sometimes didn't show when a single row was displayed.
- group-calculations > show as text row, sometimes did not display
- the XML Viewer would sometimes get error Unable to display XML Data '.' hexadecimal value 0x00 is an invalid character.
- A few fixes for the Run SQL window:
- data was sometimes exported instead of displayed
- parsing of the SQL (to get the column list) sometimes took a long time if Options > Use SQLExecDirect was specfied.
- Copy cell values to SQL did not work correctly after the grid had been sorted.
- when using Format SQL, and connected to DB2, the WITH UR clause would be removed from the query.
- Format SQL did not work correctly when there was a table expression without a correlation name at the end.
- a few functions did not work correctly when select* was specified (eg. no blank between the select and the *).
- A few fixes to the Data Loader:
- 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
- Some fixes to the Database Explorer window:
- the DB2/UDB Table Sizing display sometimes got an error with the Round function
- a few improvements to the display of DB2/UDB Stored Procedures
- for SQL Server, Index DDL didn't work when right-clicking an index in the right grid
- for DB2 z/OS some users were not shown in the User list
- Generate DDL did not work from the a list of objects displayed with Find Objects.
- A couple of fixes for Generate DDL
- the option Ensure lines are less than 72 bytes was not working correctly
- when Write DDL at end of script was selected, the DDL would incorrectly contain some internal control characters
- DDL for DB2 z/OS didn't handle Index Include columns correctly
- Some fixes for the Query Builder window:
- For Oracle, Add Related Tables was very slow
- The SQL was sometimes created as function(colname) as : rather than function(colname) as colname.
- A number of fixes for Compare Objects:
- for DB2, the resync script did not correctly handle columns defined as NOT NULL WITH DEFAULT, or correctly resync DEFAULT values
- when the Select Table button was clicked, the right grid in the Database Explorer window was cleared
- when comparing a single table, the resync script was not shown, even though it was specified to do this
- for DB2/LUW, AQT would compare table partitioning columns. This has been changed to be an optional item to be compared.
- for DB2 iSeries there were a few problems with the script for resyncing table definitions.
- the resync script includes statements to resync the definition of a table, even though this was de-selected.
- The Run Procedure window:
- would get an error when a procedure was selected from the dropdown list after a Filter was applied.
- the number of result-sets returned by a procedure was limited to 100.
- for DB2/LUW there were problems running a stored procedures when there were multiple procedures with the same name but different specific-names.
- A couple of problems with Data Export:
- when running an export to Excel, the Windows clipboard was cleared
- for a csv export, the delimiter character was not remembered when it was an Other character.
- Other:
- On the Window-List bar, the Show Close Button state was not remembered.
- Explain for DB2 z/OS didn't show the SQL text
- Find Text in Table didn't include Aliases and Synonyms.
- Find Text in Table did not work with SQL/MX numeric columns.
- For Oracle, Add Related Tables in the Query Builder was very slow.
- The option Get Related Table information from User Defined Relationships only was ignored.
- In batch mode, an error was not flagged if queryparm or setparm had an error running a query.
- In the file open/save dialog, files were sometimes not shown in file name order.
- Find in the SQL History window could give an Overflow error
- For DB2/LUW v8 the System Monitor gave error "monitor-object monlockw not found"
- Simple DDL Generator didn't generate DDL for DB2/400 Physicals and Logicals
- Auto-reconnect was not working for DB2 for z/OS.
- 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 now search multiple tables looking for a particular piece of text.
- 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.
- Compare Objects can now be run in batch.
- Data Compare has had a couple of improvements:
- 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.
- The Run SQL window:
- can now highlight column and table names.
- 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.
Bug Fixes
- In the Run SQL window:
- AQT didn't recognise that a query had been changed when the backspace or delete button were hit
- the Window-List button for the SQL window was sometimes blank
- sometimes it hit an error when running multiple --aqt commands
- Data Compare:
- did not correctly write to the resync script when Append mode was selected
- did not work well when the Primary Key was numeric
- For DB2 z/OS:
- the wrong SQL was generated when displaying a table that contained XML columns.
- Table DDL sometimes had "Generated By Default" when should have been "Generated Always"
- A few fixes for DB2 for LUW:
- for DB2/LUW v8, the Table Sizing display failed
- when generating the DDL for a DB2/LUW Stored Procedure, the text <NULL> was erroneously replaced with <1>
- the DDL was incorrect for Automatic Storage tablespaces.
- DDL was generated corretly for Column Organized tables
- DB2 for iSeries: AQT didn't include the Row Change Timestamp clause in table DDL
- For SQL Server:
- Table DDL was not working
- Improved the handling of Column Comments
- For Oracle:
- support has been added for Trigger Columns
- DDL for some tables had an invalid Storage clause
- The Database Explorer displays did not work for some versions of Netezza.
- The Data Display window did not save the Font when Save Layout was selected.
- In the Row Display window, Goto Row sometimes went to the wrong row.
