What's new

New to AQT v8.2.8

New Features

• A number of improvements for DB2 z/OS:
  • For Tablespaces, improved handling of Partition-by-Growth tablespaces.
  • Tablespace DDL will now always include the CLOSE clause.
  • For Tables, added support for the APPEND option, plus support for Clone tables
  • For Indexes, added support for the COMPRESSION option.
  • Improved support for XML tables.
• For DB2/UDB:
  • AQT now generate the DDL for dependent (child) tables
  • Support for Tablespace parameters AutoResize, IncreaseSize and MaxSize has been added
• Support for INSTEAD OF triggers added to DB2/UDB, DB2 for z/OS and DB2 for iSeries
• The Query Builder now allows you to build queries between multiple SQL Server/Sybase/MySQL databases. The User Defined Relationships window has also been changed to allow such relationships.
• Data Compare now allows customization of the Order By clause generated by AQT. This can be useful when comparing tables which use different collation orders.
• For Netezza, AQT nows allows you to switch between different databases in the Database Explorer window
• Tabs in the AQT Window-list Bar can now be closed with a middle-click (eg. clicking the mouse-wheel)
• When entering data for a Stored Procedure parameter, the text will turn red to warn that the data may not be valid (character field too long, or numeric value not numeric).
• For Generate Text, you can now change the delimiter during the script.
• When using setparm value=<date>, you can now specify the date format for this.
• There are some new scripting control statement:
  • you can now specify the date and time format to be used for your AQT session. This is useful for setting the date format used for exports in a batch script.
  • By default, when a batch script gets an error it attempts to set the Exit Code for the process. This causes a crash. There is now an option stop AQT setting the Exit Code.
• In the Data Display window, there is now a menu option (View > SQL Text) for viewing the SQL text behind the display.

Bug Fixes

• AQT sometimes crashed on shutdown after being connected to an Oracle database. We may have fixed this problem.
• A few fixes for Data Compare:
  • Failed for a table with more than 20 columns in the Primary Key
  • Improved error reporting during second-pass processing
  • Two-pass processing did not work well for Oracle Datetime columns
• Data Loader failed to load Oracle Timestamp columns
• Generate DDL:
  • failed when there were more than 32K objects selected
  • didn't generate correct DDL for Oracle XML columns
  • for DB2/UDB table DDL, CACHE 1 was incorrectly specified
• Took a long time to retrieve the column information for a remote MySQL table.
• Manage Authorities failed when processing more than 20K tables.
• Export sometimes generated an invalid file or worksheet name.
• When Export was invoked from the Database Explorer window, it sometimes gave the View File prompt even though it had failed.
• The --aqt connect scripting statement sometimes gave an error if there was a semicolon at the end of the statement.
• Options > Prompt to Save Query was not saved between sessions.

New to AQT v8.2.7

New Features

• Support for DB2 z/os Native SQL Stored Procedures. These were introduced in DB2 for z/OS v9.
• Data Compare now has an option to ignore differences in the decimal part of a timestamp column. This is useful when comparing timestamp data between different database types.
• When finding information on related tables, there is now an option for AQT to only look at User Defined Relationships, and not look at relationships which are defined to the database.
• A number of enhancements for Explain Plan:
  • You can now customize the statements used to run the Explain statement. For DB2 for z/OS, you can use this feature to have AQT use DSN8EXP (or similar stored procedure) for running your explains.
  • For DB2 for z/OS, you can specify the Schema of the Plan table.
• For DB2, when you drop / recreate a Function, AQT will now generate statements to rebind dependent packages.
• The Database Explorer window now has a shortcut key (Ctrl+B) for the Filter Objects window.
• AQT now provides a generic mechanism for displaying particular data types with given function. This can be useful if your databases uses user-defined or object types.
• For DB2, there is now a mechanism to have the table schemas displayed in the Database Explorer window to be restricted to the values specified in the SCHEMALIST CLI setting.
• When creating a DB2/UDB Stored Procedure, you can now use the Copy From button to copy the definition of an existing procedure.

Bug Fixes

• In the Data Display window, a customized Font was not applied to the grid header.
• Options > Run SQL > Statement Delimiter was not saved between sessions when it was not one of the values in the drop-down list.
• Error creating a DB2 for iSeries table using the Manage Table window.
• For SQL Server 2008, AQT did not display the new data types of HierarchyID, Geography and Geometry in a meaningful way.
• The Compare Results function of the Data Display window sometimes did not compare correctly.
• When attempting to save a read-only file, AQT said that the save was successful.
• A few fixes to Data Compare:
  • When the resync script was written to separate files, these did not have the statement delimiter set correctly.
  • The number of rows compared was incorrectly restricted to the value specified in Options > Max Rows Displayed.
  • In some circumstances, string values contained fullstops were incorrectly flagged as being different.
• The Run SQL window incorrectly removed multiple blank lines from the SQL being run.
• The Export function failed to correctly export x'00' values.
• stoponerror was being ignored for a batch script run with mode=online.

New to AQT v8.2.6

New Features

• For DB2 for iSeries, AQT now supports the *SYS Naming Convention. With this, tables are referenced as schema/table rather than schema.table.
• Pivot now has an option Tabulation. When this is selected, the Value of the Analysis column (rather than the Sum of this column) is displayed in the Pivot grid.
• There is now an option to exclude LOB columns when displaying a table.
• The Options window now has a button Default. This will reset (most of) AQT's settings to their default values.
• If AQT is shut down while a query is still running, AQT will attempt to cancel the query. If this is not done, the query can sometimes be left running on the database server.
• The Data Loader can now trim a particular character from a value. For instance ltrim(0) will remove all leading zeros from a value being loaded.
• In the Run SQL window:
  • there are now more functions in the right-click menu for the data grid.
  • there is a new function Wrap to 60 chars. AQT will split the SQL into multiple lines with a maximum length of 60 bytes.
• When Exporting to Excel, you can now set a password on the worksheet.
• When creating Oracle Stored Procedures, improved error messages are given and the position of the error is highlighted.
• For batch-mode AQT:
  • in the --aqt setparm statement you can now specify values of <scriptname> and <scriptpath>. These will pick up the name and path of the script file that is being run.
  • there is now an option in the batch file for AQT to start a new log file (as opposed to appending to existing one).
  • in the --aqt export statement, you can now specify BATCHONLY=YES. When this is specified, the export statement will be ignored when the script is run in online AQT. This is useful if you are running scripts both online and in batch.
• For SQLBase, there is now an option to include the ROWID when tables are displayed.
• Miscellaneous improvements for SQL Anywhere v10 and v11.
• In the Database Explorer, when two-color display was being used for the grids, it was not obvious which was the current-row. This has been improved.
• AQT can better handle the case of an unqualified table name being used in the Run SQL and Query Builder windows.
• For batch-mode AQT, there is now an option to start a new log file.

Bug Fixes

• When running Export in batch mode, and an unqualified file name is specified, the file was written to the AQT query directory. This will now be written to the working directory for the batch process.
• AQT did not display DB2/UDB XML columns.
• In the Data Compare window:
  • the Query text was truncated in the Report File.
  • in the Results grids, the wrong cell was sometimes highlighted as being different.
  • in the Columns tab, the columns were sometimes re-ordered when Insert Entry was clicked.
• In DDL generation, the numeric scale is now not shown when it is zero. In other words, DECIMAL(8,0) is now shown as DECIMAL(8).
• Simple DDL Generator did not deal with MySQL Views.
• For MySQL, AQT now correctly deals with an escape character being used with quotes. This means that the string John's house can be specified as either 'John''s house' or 'John\'s house'. There were a few places where this syntax confused AQT.

  Note that the Syntax Highlighting component used in the Run SQL window will think that \' is an end string-delimiter, and will display the text incorrectly.

• For SQL/MX, AQT did not correctly format numeric literals. This affected a number of areas of AQT; for instance Link Related Tables did not work when the tables had numeric keys.
• The Run SQL window got an overflow error when the query had a table-expression longer than 32K bytes.
• In the Run SQL and Run Multiple Statements windows, when a statement got a warning message AQT did not report on the number of rows affected.
• In the Data Display window:
  • the Only show rows with this value function did not work when the query using an alias for the column
  • when using Compare Results, the Compare Cols were reset every time the compare was run.
  • Save Results will now save/restore the window "tab" (eg. title of the button in the window-list bar)
• Row Update window had trouble updating a table when the query used alias names with the columns.
• Corrected a few problems with DDL for DB2 for z/OS.
  • added support for PADDED on indexes
  • added support for VOLATILE on tables
  • added support for STOP AFTER FAILURE for procedures
  • unable to create tables as Partition Mode was mandatory but not could not be set
  • DDL incorrect for partitioned tablespaces with a single partition
  • when creating a partitioned tablespace, NOT LOGGED was erroneously added
  • error genning the DDL for indexes on auxiliary tables
  • error genning the DDL for a large view
• The Query Builder was confused by the use of the USING keyword. This can be used in joins on some databases - example select col1 from table1 join table2 using (order_number)
• Some tables did not display in the Vertica database. This turned out to be due to a bug in the ODBC Driver for this database. We have implemented a work-around for this.
• On the Data Loader Mapping tab, the Source Columns were sometimes re-ordered when Insert Row was clicked.
• For Oracle and Sybase, some Unicode values were not specified correctly when the UNISTR or TO_UNICHAR options were specified.

New to AQT v8.2.5

New Features

• Config files have now been added for the following databases:
  • HP Nonstop SQL/MX
  • Redbrick
  • Progress
  • IDMS
• Explain Plan is now available for:
  • IDMS
  • Teradata
• A few improvement for DB2 z/OS:
  • Databases are now shown as a separate object in the Database Explorer. This allows you to generate the DDL for a Database, plus it's dependent Tablespaces and Tables.
  • You can now Manage the Authorities for Databases.
  • Support for Universal Tablespaces.
• Support for Roles in DB2/UDB V9.5.
• For Oracle, the Run SQL window now has an option Run > Run using SQL*PLUS. This will run your SQL in the SQL*PLUS window.
• There have been a number of enhancements to Data Compare.
  • Data Compare now has an option to do a two-pass compare. This will (in a second pass) re-compare any rows that are found to be different. This is useful if you are comparing tables which are being continuously updated (as would happen in an operational system). It also provides a solution to the problem of comparing tables which have different collation orders.
  • Improvements have been made with the way Data Compare compares Date, Timestamp and Datetime columns. AQT will now compare these values correctly, even across different database types.
  • Improvements have been made to the handling of errors during the compare.
• There is now a scripting statement for changing the Uncommitted Read option.
• A number of Help menus have been changed to be more consistent across the various windows.
• The Link Tables grid of the Data Display window now has a right-click menu of various options.
• In the SQL History window you can change the order of the columns; this order is now remembered between sessions.
• In the Run SQL window, you can add buttons for Transaction Mode, Commit and Rollback to the toolbar. You have to right-click the toolbar > Customize to see these.
• For MySQL, there is now an Option to add the LIMIT clause to the SQL. This can give a large improvement when accessing large MySQL tables.
• A new scripting function CloseXLFile to close an Excel file.

Bug Fixes

• A few bug with Generate DDL:
  • Stored Procedure text was truncated at 10K bytes
  • didn't generate DB2 z/OS Database Authorities correctly
  • failed when generating DDL for SQL Server 2005 indexes
  • DB2 z/OS table DDL did not correctly format a hex Default value.
  • DB2 Tablespace DDL incorrectly used a comma decimal separator for Overhead and Transferrate values. This affected users whose systems were configured to use commas as the decimal separator.
  • the simple DDL generator had the Not Null clause prior to the Default clause; this was invalid for Oracle
  • the simple DDL generator did not work correctly for Netezza views
• Some DB2 v9 systems didn't show tablespace containers (for some DB2 builds, a number of in-built functions had been deprecated and removed ).
• For Oracle, Compilation Errors for Stored Procedures were not being displayed.
• For SQL Server 2005, Role information was not being displayed.
• For some users, AQT would hang in the Enter Query Parameters window . This should now not happen.
• When Stored Procedures were run in batch, the returned values of OUT parameters were sometimes not displayed.
• The Run Multiple SQL Statements window sometimes got error Invalid Procedure Call or Argument
• On the Generate DDL window, the Abort button didn't immediately stop the processing. This has been improved.
• Get Values failed for Oracle Timestamp and Interval columns.
• The Query Builder didn't correctly remember the size/position of the tables (when the Save Table Positions with Query option was being used).
• The Signon window did not correctly remember the Oracle Privileges option.
• The Modify Join window sometimes failed when the Join Operator was changed.
• On the Database Explorer window, AQT sometimes did not display all the table columns (the Max Rows option was incorrectly in effect for this display).

New to AQT v8.2.4

New Features

• Export can now export to an XML spreadsheet
• Data Compare can now write insert/update/delete statements to separate files.
• On the Data Display window:
  • you can now switch back to the calling SQL window with Ctrl+W.
  • when you double-click a cell you get the "Detail of Data Value" window. This window now has an option to replace (Unix) linefeed characters with (Windows) carriage-return + linefeed. This makes it easier to view multi-line text in Oracle tables (these use linefeed as the newline character).
• For Oracle:
  • support for Recycle Bin.
  • improved the display of XML columns.
  • ability to define / redefine packages.
• A few miscellaneous improvements to Create Table for DB2/UDB
• For batch-mode AQT:
  • can set the LOB file directory
  • when using the include statement, you can now specify whether an error is returned if the file does not exist.

Bug Fixes

• The Create / Alter Table window:
  • sometimes got an error "object doesn't support this property or method" when clicking on a dropdown value.
  • sometimes got an error "type mismatch" after running a change.
• On the Data Display window:
  • View As failed for some external programs if the file name contained a blank.
  • Compare Data didn't recognize two windows as different when they were sorted in different orders.
• For Data Export:
  • for Export as Inserts, the column list was not refreshed if the columns in the table were changed between successive exports.
  • for Fixed Format, the Control File was not created if Export Data in Unicode format was selected.
  • for some databases, Fast Export returned a null line if any column in the row was Null.
  • Fast Export didn't give an error message if an invalid file path was specified.
• The Data Loader:
  • allowed the user to specify the Create new table option when Update mode was being used. This combination of options is now not allowed.
  • didn't remember recent Excel files used.
• The Run SQL window:
  • got an overflow error when a very large text value was specified in a statement
  • when using Run as DB2 Command, AQT will now (correctly) use the DBALIAS name rather than the Datasource name. Previously, Run as DB2 Command did not work for databases which had the ODBC Datasource name different from the DBALIAS name.
  • open/save query sometimes failed when you had files with no file extension.
  • didn't run multiple Run From File scripts.
  • didn't replace user defined parameters when these were used in script statements (such as --aqt export).
  • when running a script with a spurious tab character at the end, AQT split this out as a separate statement.
• Other:
  • On the Row Update window, when cancelling out of Update mode and going to Display mode, the changed values remained in the grid. These will now revert back to the original values.
  • For Informix, AQT did not display some Interval values correctly.
  • The closexl command in batch AQT erroneously returned an error condition.
  • The System Monitor sometimes showed large text values as <aqtfile> rather than the actual text.
  • Simple DDL Generator failed with an Overflow error.
  • Simple Compare All Tables in Schema also failed with an Overflow error.
  • In the Database Explorer window, text displayed in the right grid (eg. View text) was modifiable, when it should not have been.
  • When Data Compare was run in batch mode, the status message with the results of the compare was not displayed.
  • On the Multiple Objects admin window (used for Drop Table etc) the user could not sort a column by clicking on the header.

New to AQT v8.2.3

New Features

• Export now has an option to always create a new file or Excel worksheet. A sequence number will be added to the supplied name to ensure that the file/worksheet is new.
• In the Run SQL window (and batch AQT) you can now run a DB2 utility command by prefixing the command with (db2).
• The Where Clause window has improvements when building Between clauses.
• In the Data Display window, you can get various column functions by right-clicking the column header.
• For Oracle, queries that display the information in the Database Explorer a lot faster have been added to the cfg file. These have to be manually activated. See Database Notes > Oracle.
• DB2/UDB now displays information on range partitions and Multidimensional Cluster (MDC) tables.
• A Find button has been added to the Data Value and Extended Edit windows.
• A user-defined parameter can now reference another user-defined parameter.
• In the Data Loader mappings, keywords such as [comma] can be used with all functions.
• AQT windows sometimes get "lost" if they somehow get positioned a long way to the right or down. There is now an option Window > Reposition Windows to bring them all back to the top.
• The Run SQL window now has options to go to start / end of the SQL text (Ctrl+F1, Ctrl+F2)

Bug Fixes

• A few fixes for the Database Explorer window:
  • SQL Server sometimes got the message "Connection is busy with results for another hstmt" when switching between databases.
  • Did not display all the Body Text for large Oracle packages.
  • Did not display information correctly for recent versions of DB2 for iSeries. This is due to AQT not recognizing the database type and using the wrong config file.
  • Did not display information correctly for recent versions of Sybase Anywhere. Again, AQT did not recognize the database type and used the default config file.
  • The Query Explorer did not show files that had an upper-case suffix (eg. *.SQL)
  • For Sybase, the Columns display (at the bottom of the object-tree) was incorrect.
• In the Admin system:
  • When displaying a table in the Admin system, the view and trigger definitions did not display correctly.
  • When creating Stored Procedures, comments were removed when Options > Remove Comments from SQL was selected. Comments will no longer be removed from Procedures, irrespective of this setting.
  • Compare Oracle Triggers did not compare the trigger text correctly.
  • DDL for DB2 z/OS Partitioned Indexes was incorrect.
  • DDL for DB2 z/OS Large Tablespaces incorrectly included the DSSIZE parameter.
• A few fixes to the Data Loader:
  • Decimal places were sometimes truncated
  • Load-Update sometimes failed
  • The Mapping was sometimes lost when changing the source database or table
  • When using File > Open Load Options, the mapping was sometimes not re-established correctly.
• In the Run SQL window:
  • Format SQL window sometimes removed all text following a comment.
  • Format SQL will now replace x'A0' characters with spaces. x'A0' characters often appear in your SQL if you are pasting your SQL from an email.
  • When displaying data in the Run SQL grid, the query was not closed if the Get More Rows option was selected.
  • When running a very fast query, the elapsed time was sometimes displayed as 84600 seconds, instead of zero.
  • AQT erroneously removed blank lines from string values.
  • When read-only mode is selected, AQT did not allow a Select statement to be run when it preceded with a bracket.
  • Improved the position / size of the Function popup (when you right-click a column-name)
  • For IDMS, Explain statements did not work when the SQL contained line-breaks. This is a limitation of the IDMS ODBC Driver; a work-around has been implemented to circumvent this problem..
• On the Data Display window
  • right-clicking the grid and selecting Search Table did nothing.
  • Pivot sometimes gave odd results when using decimal values and the decimal separator is a comma
  • AQT sometimes displayed numeric values incorrectly when the decimal separator is a comma.
  • AQT sometimes got an error displaying DB2 Real, Smallint or Bigint values
  • Options > Display Limits > Max Column Size had been ignored for large columns (such as LOBs)
• In the Row Update window
  • the Get Values (and other) buttons were incorrectly displayed when in Delete mode.
  • when in Update mode, null date values were displayed incorrectly when Display Nulls as > Blank was selected.
  • The setting Options > How to display binary columns was not saved between sessions. A few minor improvements were made to Smart mode.
• Data Compare:
  • did not compare LOB values correctly.
  • when running in batch, Case=Y did not work
• When running Data Export in batch, by default export files were written in Unicode format. For compatibility with earlier releases the default has been changed to be not unicode format.
• For SQL Server, the number of rows processed in an update/delete/insert statement was restricted by the value of Options > Display Limits. This is a peculiarity of SQL Server. This has been corrected (so these statements will now process all rows).
• When using the Sybase v15 ODBC Driver, some functions (such as the System Monitor) do not work correctly. This is due to errors in this driver with the use of parameterized queries. Some work-arounds have been put in to cope with this.
• Run Stored Procedures failed for Date and Timestamp parameters.
• In batch AQT, the statement --aqt setparm,value=<date> sometimes failed.
• In the Run Multiple Statements window, Drop statements were not recognized when the Drop keyword was followed with a tab instead of a blank.
• The Query Builder did not interpret Where clauses if they had an IN statement not followed by a blank (eg. IN(1,2,3))
• In the Signon window, the Description (on the Show Recent display) was not saved for Direct Connection entries.
• The SQL History now displays multi-line Results values correctly in the bottom pane.
• AQT did not appear in the Windows task-list (Alt-Tab) when the Extended Edit window was being displayed.

New to AQT v8.2.2

New Features

• Auditing now includes the userid with the audit information
• Data Compare and Compare Objects now allow you to type the name of the table being compared.
• the Data Loader now allows you to load Identity columns into Sybase and SQL Server tables. This is controlled by a new option Use IDENTITY_INSERT.
• the Data Loader and Data Export now support Excel 2007 file formats (such as xlsx).
• AQT now supports Trigger Columns for DB2/UDB Triggers.
• in the Generate DDL window, AQT now remembers the name of the output file and directory.
• A few enhancements to the Compare Objects window:
  • you can now move the horizontal divider bar
  • there is an option View > Only Show Differences. When this is selected, the top grid in the Results window will only show rows where the objects are different. This is handy if you are comparing a large number of objects.
  • you can now copy the grid values.
• for SQL Server there is improved support for Computed Columns.

Bug Fixes

• in the Data Display window, the Find > and also contain option did not work
• in the Database Explorer the right-grid sometimes did not have a horizontal scroll-bar.
• the Query Explorer had an error displaying some directories.
• Run Procedure had an error when there was a parameter larger than 20K bytes.
• the System Monitor didn't display more than 10K bytes of an SQL statement.
• the System Monitor did not work well for Oracle v9.
• when editing User Defined Relationships, the join type wasn't always remembered.

New to AQT v8.2

System Monitor

The most significant change is the System Monitor. This allows you to display active tasks and other internal information for DB2/UDB, Oracle, Sybase and SQL Server. It can be invoked from the Database Explorer window with Tools > System Monitor.

Admin System

There have been a lot of changes to the Admin system.

• You can now have multiple admin windows open at the same time - eg. you can have a Create Table window open at the same time as displaying the Properties of another table. This wasn't possible in AQT v8.1.
• The Create Procedure window for Oracle and DB2/UDB now has the Parameters on a separate tab.
• AQT now allows you to create a partitioned table (for Oracle and DB2 z/OS).
• AQT now supports table-controlled partitioning for DB2 z/OS v8.
• The Create window now has a button Replace if exists. This allows you to create then replace an existing object. Previously, once you had created an object you had to exit the window then go into Alter mode. This feature is not available for Tables.
• When using the Copy From button on the Create Table window, AQT will ask you whether you want to copy the definition of the indexes, keys etc, or just the base table definition.
• If you decide to customize the admin component, you can hold your customizations in a separate cfg file (xxxx_adm_usr.cfg). This prevents the problem of your customizations being overwritten when we deploy new versions of the cfg files. This change is useful for the System Monitor feature, as it is expected that this would commonly be customized by users.
• You will no longer get the message "Insufficient Storage" if you try to open too many admin windows. We have redesigned the admin system so that this problem will no longer happen.

Other New Features

• Query Explorer. In the Database Explorer window, you can display all your Queries (click on View > Queries), rather than your Database Objects. This makes it easy to view and run your saved queries. The Query Explorer will also display your saved-results.
• You can now have a Saved Query or Saved Result as a Favorite.
• In the Data Display window, you can right-click a value and select Only show rows with this value or Exclude rows with this value. This makes it very quick to display the data you are interested in.
• AQT should now correctly set the maximum number of rows to be returned by a query. We have discovered that AQT was not setting this correctly. As a result, the options "For DB2, add FETCH FIRST X ROWS clause" and "For Sybase and SQL Server, use SET ROWCOUNT" options are no longer needed.
• You can now define a connection as being auto-connect when AQT starts.
• The Build Where Clause window has CONTAINS and STARTS WITH operators. This makes it easier to build string searches.
• AQT will now display up to 100,000 bytes of a large column (after this, you should use the LOB Options).
• The Row Update window will now warn you if you try to exit with saving a change.
• Data Export can now export data to Fixed and Delimited files in Unicode format.
• The Data Loader can now load from files which have Unicode encoding.
• Improved support for Sybase IQ.
• A number of improvements for PostgreSQL.

Bug Fixes

• Data Compare got an overflow error in some circumstances.
• The Data Loader got an overflow error loading some Unicode values.
• The Print icon in the Database Explorer toolbar did not work
• In the Run SQL window, Explain did not work in combination with user-defined parameters.
• The Run SQL window sometimes got an overflow error when creating a large Stored Procedure.
• When altering a DB2/UDB Stored Procedure, the label statement (prior to the Begin) was truncated.
• In the Run Procedure window, when the Parameter Type was manually changed, this did not always work.

New to AQT v8.1.7

New Features

• Safe Update Mode can now be switched off permanently
• added support for user-defined types in SQL Server
• for Oracle, AQT replaces carriage-return + linefeed characters with a linefeed. There is now an option to govern whether AQT is to do this.
• there is now a scripting command for renaming a button in the Window-list bar
• the Run SQL window now has a button for sorting the columns in alphabetic order (this is only seen when the list-boxes are on the right). Previously this could only be done with View > Show columns in alphabetic order.
• the Query Builder can now build queries involving tables in multiple MySQL Databases.
• you can no longer save changes to the cfg files using the Configure System Queries window

Bug Fixes

• the Default Directory was not set correctly for non-English installations of Windows.
• some LOBs weren't exported correctly when exporting a table with LOBs to Excel
• some fixes for DB2 z/OS:
  • compare tablespaces failed with "Insufficient Storage"
  • Drop Synonyms didn't always work
  • generate DDL for tablespace partitions was sometimes not correct
  • will now generate DDL for table-controlled partitioning.
  • procedure parameters for DB2 z/OS v8 needed to exclude the ROWTYPE='X' entry
  • didn't generate DSSIZE parameter on tablespace DDL
  • PIECESIZE was incorrect in index DDL
  • generate DDL sometimes did not generate the NULL attribute for columns correctly
• the Favorites list wasn't always refreshed after using Organize Favorites.
• Format SQL (in the Run SQL window) worked oddly with some MS Access queries.
• Data Loader and Data Compare had problems with MS Access table that had a comma as part of a column name.
• The Data Loader sometimes got message "Invalid Precision value" when loading an MS Access table
• Data Compare now gives a more sensible message when the table doesn't exist in the database.
• The Data Loader now checks that all columns being loaded have a Load Spec.
• In some circumstances, Copy Cells could inadvertently include the column header.
• Run SQL now allows you to undo past a ReOpen Query. This is useful if you have clicked on this by mistake.
• There had been some problems displaying Views in Pervasive.SQL. These were due to problems with the ODBC Driver; we have put in a work-around for this.
• error when generating the DDL for a MySQL table that has longblob columns.
• some fixes to the Query Builder:
  • the Query Builder got confused when interpretting an SQL statement that had multiple Select statements UNIONed together. AQT will now process only the first of these Select statements (and will give you a warning about this).
  • when building a query involving a Table Expression, the column list for this did not change when you changed the Table Expression.
  • a join condition involving a greater-than sign (>) could be generated with the columns in the incorrect order
• Signon window behaved oddly when it was Maximized and user clicked on Advanced.
• Pivot (in the Data Display window) gave an error if the data was grouped.

New to AQT v8.1.6

New Features

• A few changes for compatibility with Windows Vista:
  • Vista has gotten more strict about user data being held in the Program Files directory structure. As a result, we have changed the Default Directory and Saved Queries directories to be in Documents and Settings (non Vista) or Users (Vista) directory. This only affects new users, or when you click on the Default button in Options > File Locations.
  • The AQT install has been upgraded to the use the latest release of InstallShield. This installs AQT more successfully on Vista.
• A number of improvements for creating/altering procedures with DB2/UDB and Oracle.
• The Create Object window now has an option to Replace object if exists. This allows you to create then amend the object in the same window.
• Data Compare will now use the delimiter specified in Options > Run SQL > Statement Delimiter for generating the resync script.
• Data Export now allows you to include your query SQL in the start text and end text.
• A few enhancements to Link to Related Tables - columns in the display can be sorted, easier to Edit an entry.
• The Filter box in the Database Explorer window now has the text selected when it gets focus.
• The Add Multiple Objects to Favorites window has a few improvements when dealing with a large number of objects.
• The Analyze functions on the Run SQL window will now add the WITH UR clause for DB2. This only done when Options > Uncommitted Read is selected.
• Improved support for DB2 z/OS Sequences

Bug Fixes

• for Export, the Show Nulls as setting wasn't remembered beween AQT sessions
• encrypt password did not work correctly with the Anyone options.
• when read-only mode was specified, AQT didn't allow WITH statements to be run
• Export to Table in the Run SQL window always with Create New Table always created the columns as Nulls allowed.
• AQT now gives you an error message if you get an error writing to a History file
• A few fixes for SQL Server:
  • when loading data from a file or Excel file, and the Create New Table option was specified, the Create Table statement sometimes got a syntax error.
  • the column list was not displayed correctly in some circumstances.
  • data was sometimes not displayed when when a code-block was run
  • Data Compare did not compare numeric columns when the databases had different ODBC regional settings
  • error generating DDL for Indexes
  • in Data Compare, when selecting a table in a different database, the table-list was not displayed correctly.
• For DB2 z/OS:
  • the queries for displaying package information were slow.
  • when running in Compatibility mode, some Views were not displayed correctly.
• For Oracle:
  • DDL for TIMESTAMP and INTERVAL columns was not correct
  • improved the display and insert for RAW columns
• Some Admin functions failed if there were more than 32K objects in the list.
• The --aqt use scripting statement did not always work if the Show user name as part of database name option was selected.
• AQT did not display Object Types (in the Database Explorer) for Oracle v8.
• The Link to Related Tables feature sometimes got an error when grouping was used in the Data Display window.
• The Row Detail window could display the wrong row if the data was grouped, and the user clicked on a row near the bottom of the display.
• In the Join Details window, AQT crashed if the user clicked in the area below the column list.
• The Abort button did not work reliably on the Generate DDL window.
• The Run Multiple Statements window gave error Invalid string or buffer length when running a query that was blank or a comment.
• The Signon window didn't allow a Recent signon of a Direct Connection.
• The Signon window ignored the No Password option.
• The Build Where Clause window sometimes selected the wrong column.
• On the Data Compare window, when viewing cells, sometimes the wrong cell was shown.
• Running the Data Loader in batch got an error when del=y was specified.
• Hit Refresh in the Database Explorer, while the focus was on the top item in the object-tree, gave an error message.
• Generate DDL could fail for a large procedure.
• When accessing Excel files, AQT needed to use #s (rather than quotes) with date literals.
• AQT now responds to the Menu key in most windows (this is the key on the right of the keyboard, immediately left of the Ctrl key).

New to AQT v8.1.5

New features

• on the Show All mode of the Signon window, you can now apply a filter to the databases displayed
• for Oracle, there is now an Option for the Database name to be taken from the Oracle Server name, not the Datasource name
• Generate Text now has Examples for Export, Load and Compare
• The option Display x'00' characters as spaces now applies to output parameters from Stored Procedures, as well as for displayed data
• History now has an option as to whether SQL run from Linked Tables is included in the History.
• AQT will now generate DDL for Oracle Packages
• Improvements to the creating of Oracle Stored Procedures
• For DB2 z/OS:
  • support for Sequences added
  • synonyms are now included as part of the Table DDL

Bug Fixes

• Oracle column information was not been displayed for Oracle v8
• SQLServer did not display view text when the SQLServer Native ODBC Driver was used
• Format SQL in the Run SQL window did not correctly format an Insert statement which had the syntax insert into tab1 (cols) select cols from tab2
• DB2 DDL erroneously included system-generated indexes
• Export from the Data Display window sometimes did not export BLOBs
• The Query Builder misinterpretted SQL when join expressions were inside multiple levels of brackets
• Export to Excel did not set the data type correctly for date columns
• Nicknames for Server did not display for DB2/UDB v9
• Data Loader Error and Duplicate files were empty when loading from Excel
• When using the Create new table option in the Data Loader, AQT now sets the Null attribute in the Create Table statement.
• Sybase Anywhere DDL did not show table defaults or trigger text correctly
• For DB2/UDB, AQT didn't properly deal with Package versions
• In batch-mode, the database name was not being set correctly when connecting using a connection string.
• When generating a resync script in Data Compare, quotes weren't being placed aroung LONGTEXT and LONG VARCHAR columns.
• In the Data Display window, Apply Layout was excessively slow for a large result-set
• Saved Results did not display correctly when selected from the History window
• In the Row Update window, AQT did not automatically mark the key columns in some circumstances
• Pivot Data failed for a large table (> 32K rows)
• Query Builder sometimes did not remember the setting for the Inner Join syntax type.

New to AQT v8.1.4

New features

• We have improved the time taken to load schema-lists in the Object tree of the Database Explorer. Users with a large number of schemas in their database will notice a very significant reduction in the time taken to load these.
• We have redesigned the Signon window. This implements a number of new features that have been requested. In particular:
  • a new layout
  • Database list also shows the name of the ODBC Driver
  • buttons for Adding / Configuring / Deleting Datasources (though you can disable this)
  • window is resizeable
  • Recent Signons also includes the (SQL Server/Sybase) Database selected, plus Oracle Privileges used
  • you can now add a Description to a Recent Signon entry
  • you can do a Direct ("DSN-less") connection to a database. This includes the ability to connect using a connection string.
• For SQL Server 2005, we have made a number of improvements:
  • AQT now displays the Trigger status, and provides the ability to enable/disable triggers
  • AQT now displays Synonyms. The Database Explorer display has been changed to display Table, Views, Synonyms in the same list rather than as separate objects.
  • improvements to the display of Indexes.
  • improved the Rename Columns function.
• For DB2 z/OS,
  • AQT now generates the BIND PACKAGE statements for packages.
  • support for FIELDPROCs has been added
  • support for LABELs has been added
  • corrected a few problems with Auxilary Tables (and indexes on these)
  • improvements in the display of Default and Generated attributes of columns
  • had an error when Redefine Table was used
• For MS Access, AQT now displays the definition of Views / Queries. Views can be amended.

Bug Fixes

• A few fixes to the Database Explorer
  • did not display more than 50,000 obects in the table-list
  • the Database dropdown for SQL Server was limited to 500 entries
  • the object-list sometimes had the wrong captions when switching between Objects and Favorites
  • Row Height wasn't being set correctly when a non-default height was being used
• Explain SQL for Teradata did not display anything.
• SQL run with Run as DB2 Command was not recorded in the SQL History
• Run Procedure History did not correctly save multi-line text parameters.
• Sybase Anywhere / IQ Procedure DDL was truncated at 9999 bytes.
• Query Builder sometimes did not correctly parse queries with table-expressions.
• The Data Loader failed when loading decimal fields, and the decimal separator is a comma.
• When the decimal separator is a comma, Gen DDL will specify Decimal(10, 2) rather than Decimal(10,2) (which can fail with some databases).
• For Oracle:
  • columns were not displayed for Oracle v8
  • columns for Public remote synonyms sometimes were not displayed
  • Query Builder can now correctly parse / build queries involving remote tables.
• Display of History failed when there were no items in the History.
• Apply Format in the Data Display window did not correctly apply variable-row-height.

New to AQT v8.1.3

New features

• We have added a function to prevent inadvertent mass-updates when using the table editor. When a key is specified by the user, prior to every update/delete, AQT will run a select count(*) query to check that only a single row with be updated / deleted.
• the Data Loader has new mapping functions ifblank and ifempty.
• when defining user-defined relationships, you can now define a generic relationship. A generic relationship is one that applies across all schemas in the database. This is useful if you have the same tables defined in multiple schemas in your database.
• in the Query Builder, you can now add multiple Known Relationships to the query.
• Save Results from the Data Display window will also save the layout of the display.
• You can now display Saved Results without having to first run a query. This can be done from the Database Explorer window with Tools > Display Saved Results (or Ctrl+D).
• in the Run SQL window, Number of Distinct Values can now analyse multiple columns.
• a new function decode has been added to Generate Text.
• a few miscellaneous improvements to the Unicode Character map.
• improved support for the Netezza database (there is now a cfg file for this).

Bug Fixes

• DB2 z/OS Tablespace DDL was incorrect when PRIQTY was -1
• AQT didn't pick up the config file for SQLBase v9
• AQT didn't pick up the config file for Sybase Anywhere v9.0.2
• Exhibited poor performance when a large result was displayed and user had selected all cells.
• Table Editor got message "Type Mismatch" when Apply New Rows was selected.
• SQL Server default values were displayed as a hex string instead of an ascii value.
• Data Loader got a CPU loop when a mapping of rep([none]) was specified.
• When a non-numeric value was supplied to a numeric column, the Data Loader would load a zero, rather than giving an error.
• For DB2, the Optimize clause should not be added when the user has coded WITH UR.
• The Uncommitted Read option was sometimes enabled inadvertedly.
• For Oracle, the length of character columns were reported incorrectly for some database encodings.
• DDL for Informix triggers had an extra semicolon.
• In the Run SQL window, a comma was incorrect added between DISTINCT and a column name.

New to AQT v8.1.2

New features

• we have made many enhancements to the Pivot function. This is now more powerful and easier to use.
• there is now an option that governs whether AQT removes line-comments from your SQL. By default is does; you may wish to de-select this if you want the comments to remain in the text of a Stored Procedure / Trigger / Function that you are defining.
• for DB2/UDB, we have added queries for showing dependencies of Stored Procedures. These can be used to show the cross-reference (in both directions) between Tables and Stored Procedures (however, if you are using temporary tables, see note).
• for DB2/UDB, we have improved the display of Nicknames and Servers.
• the DDL generator for DB2 z/OS can now generate the DDL for dependent-table Foreign Keys (eg. FKs that refer to the specified table).
• we have improved the use of AQT with Excel files. AQT will now display Worksheets as well as Ranges.
• you can open a Access (*.mdb), Excel (*.xls), Dbase (*.dbf) or csv (*.csv) file in AQT by right-clicking the file, selecting Open With, then selecting AQT. In the future we will add file associations for these files.
• AQT will now display Unicode table names with MS Access.
• some changes to the Sybase cfg file:
  • compatibility with the Sybase v15 ODBC Driver
  • has been changed to no longer use object-ids. This means that if you drop / redefine a Table or Procedure, you no longer have to refresh the object-list in order to see the properties of the object.

Bug fixes

• AQT was removing leading-spaces from SQL run from the Run Multiple Statements window.
• Database was not being reestablised correctly after a Reconnect.
• Data Compare crashed when report file name was blank, or had a write error on the file.
• A few bugs with the Data Display window:
  • could get a repeated error message and have to be cancelled.
  • Sum Cell Values could sum the wrong column if the focus was outside the grid.
  • Get-more-rows-append did not work
  • If there were no rows in the display, Hide all null columns hid all the columns.
  • When a rows was selected by clicking in the row number, the row wasn't set as the current row.
  • When in linked-mode, and the grid was refreshed, the grid size was wrong.
  • Group calculation didn't recognise columns as numeric on some circumstances.
  • When the Find function was used to filter the rows in the display, the Row Display would sometimes show the wrong row.
• Row Display window failed when a column contained only a linefeed character.
• Extended Get Values failed when the column name contained spaces.
• Generate DDL for DB2 z/OS Tablespaces sometime behaved oddly.
• Write LOBs to Files option was not saved between AQT sessions.
• The Add table to Query dialog didn't handle switching between SQL Server / Sybase databases.
• Compare Objects did not correctly compare View columns.
• Display multiple queries in same window was not working correctly.
• If the Table Search window positioned itself outside of the display area, it could not gain focus and AQT would have to be cancelled. AQT now ensures that this window is within the visible area.
• The caption on the Database Explorer window was sometimes wrong.
• The display of DB2/UDB Foreign Keys was incorrect when the table had multiple FKs.
• Generate DDL Ensure lines are less than 72 bytes function failed for some Views.
• Row Update reset the Where columns when you moved to another row.

New to AQT v8.1.1

• The Drop Table function incorrectly dropped all tables, not just the ones selected. This problem only affected databases that use a default admin config file, such as MS Access, MaxDB and Firebird / Interbase,
• Pivot produced strange results. Some minor enhancements have been made to this function, including improved Help.

New to AQT v8

AQT v8 has a number of major new features to improve the ease-of-use and functionality of AQT.

A new look

• AQT now supports XP styles
• AQT menus now display images
• The Toolbars have been enhanced to display text plus images. This makes it much easier to see the function of the various buttons. The toolbars now have comprehensive customization options. You can control the appearance of the toolbars, and can select which buttons are visible.
• The window-list bar has been improved to look more like the Windows taskbar. The buttons will now shrink to fit the size of the window, and will give a left-right scrolling when they do not fit. There are more options governing the appearance of this bar.

Favorites

Within the Database Explorer window, you can specify various database objects as being a Favorite.

To define an obect as a Favorite, select the object then click on Favorites > Add to Favorites.

• You can organize your Favorites into folders. You can have different types of object (tables, indexes, tablespaces) in a favorites folder. This allows you to group your objects by application type or some other useful grouping. Because an object can appear in more than one folder, you can create multiple groupings of your obects.
• In the Database Explorer you can display your Favorites by clicking on the Favorites toolbar button, Return to display Objects by clicking on the Objects button.
• You can specify whether AQT is to show Favorites by defaut when it connects to a database (see Options > Table Information).
• For more on this feature, see Favorites.

Formatting Data

When displaying a table / query result, you now have more options for customizing the appearance of the display. This includes:

• formatting columns in particular ways - for instance you can display currency values with the currency indicator and correct number of decimal places. True/false values can be displayed as a checkbox.
• sorting by multiple columns.
• showing a caption for your display.
• displaying sub-totals

Once your display is formatted as you require, you can save this as a Saved Layout. This can then be reapplied to the table at a later date. This saved-layout includes information on column order, column widths, sorting, grouping, sub-totals, column formatting, display caption.

Showing information in related tables

This is a significant enhancement to the Data Display window. If your table is related to other tables, AQT can show the data from the related tables on the Data Display window. As you click on rows in your table, the rows from the related tables are displayed. This is a very useful feature when dealing with tables that are related.

See here for more information on this feature.

Important note for those upgrading from AQT v7

When upgrading from AQT v7, there are two changes you need to be aware of:

• with AQT v8, we have implemented a more secure method for encrypting passwords. This is used by the Save Password option of the Signon window, plus the epwd clause in batch scripts. Passwords saved by AQT v8 will not be recognised or decrypted correctly by AQT v7. This will be an issue for you if you are running both AQT v7 and AQT v8 on your machine, or if you downgrade from AQT v8 to AQT v7.
• as with AQT v8, the cfg files have been moved to their own directory. These wil be moved automatically by AQT, however if you notice any problems in this area you should review the settings for Options > File Locations.

Other New Features

• Display Oracle DBMS_Output. We have added a window for displaying the output produced by the Oracle dbms_output.put command.
• Improved support for SQL Server 2005. A number of changes were made to the cfg files due to significant changes to the system tables with SQL Server 2005.
• A number of changes to the use of User-Defined Parameters within AQT:
  • user-defined parameters can be used in online AQT, not just batch-mode
  • parameter values are saved between AQT sessions
  • the QueryParm command now allows you to specify a drop-down list of values for the user to select from. This drop-down list of values can be populated by a query run on the database.
  • You can display a date-picker for the user to select a date / time or timestamp value.
  • SetParm command now allows you to set a parameter to the value of an Environment Variable, or to the current date / time / timestamp.
• User-defined keys. You can now define user-defined keys on your tables and views. When editing a view, synonym or a table that doesn't have a unique index, this saves having to specify the key every time you edit the table/view.
• Unique Key. In the table-editor, data compare (and other places), if your table does not have a Primary key, AQT will see if your table has a Unique Key. If your table has multiple unique keys, AQT will use the first that it finds.
• Changes to the Database Explorer window:
  • The Database Explorer now caches the objects lists displayed in the middle grid. If you are moving around the Database Explorer, this will save AQT from having to continually fetch this information from the database. This can give a significant performance if you are dealing with large databases.
  • Refresh button will now refresh the list-of-databases (for SQL Server / Sybase).
• A number of improvements to the Run SQL window,
  • there is a new function to Export to Table. This makes it easy to save your query data to a table (behind the covers this runs the Data Loader).
  • the Format function now formats CASE statements.
  • there is a new function Create > Generate Query table DDL. This generates a statement to create a table with the columns as per your query.
  • you can specify that your query is auto-saved (see Options > Run SQL). AQT will save your query every time it runs successfully.
  • you can specify that the Query Saved message is not to be displayed (see Options > Run SQL).
  • added editing functions to add / remove block comments (such as /* comment */).
  • if you have multiple SQL statements, you can select the current statement with F4. To run the current statement, hit F4 then F5.
  • option to show the columns in alphabetic order. Once this option has been selected, it remains in effect for the remainder of your AQT session.
• a few enhancements to the Row Display / Update window:
  • if a string value is too long, or a numeric value is non-numeric, it is highlighted in red. This provides a simple warning that your data may not be valid.
  • there is now has an option to display the Primary Key columns.
  • when in Insert mode, you can easily select or de-select all columns with Edit > Include All / None. Previously this option was only available when in Update mode.
  • when editing a date, time or timestamp column, you will get a button Select Date/Time/Timestamp. Clicking on this shows you a date-picker window which makes it easy to select a date/time/timestamp value.
• The Data Loader now has:
  • a load-update mode. When a row cannot be inserted because it already exists, the existing row will be updated.
  • an update mode. With this, the target table is updated with values from the source. No new rows are added. This can be used (for instance), to populate a column in your table with values from another table or from a file.
• A number of changes to the Run Multiple SQL window:
  • the window is now resizeable, and has resizeable panes.
  • the SQL shown in this window now includes comments (previously these were stripped out)
  • if you modify the SQL, you can now save the changed script back to the SQL window, or to a file
  • there are now options to show only some of the SQL statements (eg. those that have failed, succeeded or not yet run).
  • you can now specify that some error codes are not be considered errors, so will not cause the script to stop running.
• EBCDIC Conversion. When running DB2 z/OS v8 in Compatibility Mode, a number of catalog columns have changed to VARCHAR FOR BIT DATA. This means that the data comes across to AQT in EBCDIC format, so will not display correctly. To deal with this, we have added a number of enhancements:
  • all system queries (those that display data in the Database Explorer etc), will check whether the data is in Ebcdic format, and if so, will convert it to Ascii.
  • there is a new option (Display Format > Convert Ebcdic to Ascii) to specify whether you want this conversion done when running normal queries.
  • the Base64 Encoding Tool (see the Tools menu in the Database Explorer) has been extended to also encode/decode Ebcdic text.
• Added a new Fast Export module that can export data rapidly (with a number of limitations).
• You can now specify the directory used for your History Files.

Minor New Features

• When Administering Authorities, AQT will grant multiple authorities with a single statement.
• For SQL Server, AQT now supports Table / View / Column descriptions.
• AQT now traps the Oracle password-expired message and provides information on how the password can be changed.
• The SQL History now allows you to delete entries from the history.
• In the SQL History window, you can now rearrange the columns in the display
• when the Data Loader is loading data from a query, your query can contain substitutional parameters.
• Data Compare now has an option to Filter the rows being compared (this applies a Where clause to the data being retrieved for the compare).
• The Create Table window now has options to convert your table/column names to different formats. For instance, this can convert column names from Oracle/DB2 format (with underscores) to SQL Server Sybase format (no underscores but with the first letter of each word capatilized). This is useful when copying a table definition from one type of database to another.
• the window used for editing views / triggers / procedures and functions, now has a toolbar with many editing functions.
• In the Data Display window you can automatically hide columns if they contain values which contain entirely Null or some other value. This is done with View > Hide all Null columns (Ctrl+N) or View > Hide all columns where...
• the Foreign Key / Dependent tables queries for DB2 z/OS and DB2/UDB have been improved. Previously, Foreign Keys based on a unique-constraint (rather than a primary key) were not displayed.
• added support for user-defined datasets with DB2 z/OS Tablespaces.
• DDL for SQL Server has been changed to generate index DDL prior to the primary-key DDL. This will handle the case of a primary key based on a clustered index.
• Table Search now allows multiple values to be selected.

Known Problems

• the tooltips on the toolbars sometimes displays incorrectly. This is a problem with the toolbar component which we have raised with the developers.
• when computing group-totals, the MIN of a character field is always given as blank. This is a bug with the grid component which we have raised with the developers.