Configuration & Settings

Specific Database Information

Table of Content

Database notes

This section contains miscellaneous notes on various databases.

Setting up a connection to a database is described in Configuring a Database Connection.

Database-specific notes for the Administration component are described in Database-specific Admin notes.

DB2

Uncommitted Read

It is recommended that you select Options > Technical Parameters > Uncommitted Read. This is particularly important if you are accessing a production DB2 environment.

When this option is set, AQT's queries will run even if a table is being updated by another DB2 process. AQT will not interfere with any other DB2 activity.

Uncommitted Read is also known as the Program Isolation.

Optimize Clause

When you run a query, the DB2 ODBC Driver will automatically add the following clause to the end of your SQL:

Fetch First x Rows Only Optimize For x Rows

where x is a very high value.

Because the value of x is high, this can result in poor performance. This is because DB2 uses the value of x to optimise the performance of the SQL and the network traffic; this unrealistically high value of x will cause DB2 to make poor optimization choices.

There appears to be no way of preventing the DB2 ODBC Driver from adding this, however AQT has a circumvention to this issue. If you specify Options > Technical Parameters > For DB2, add FETCH FIRST x ROWS clause, AQT will add its own optimize clause to your SQL. When this is done, DB2 will not add its optimize clause. The optimize clause that AQT adds is better than the DB2 one, as it has a sensible value for x (the value specified for Options > Display Limits > Max Rows Displayed). This can result in a significant performance improvement for some DB2 systems.

With UR

If you are using v7 of the DB2 Client, specifying the With UR (Uncommitted Read) clause at the end of your SQL will give you a syntax error. This is because DB2 and/or AQT will have added the optimize clause at the end of your SQL; this is invalid syntax as the With UR needs to at the end of the SQL.

To circumvent this, code your own optimize clause. When you do this, DB2/AQT will then not add their own. Example:

select * from department fetch first 100 rows only optimize for 100 rows with ur

Note that you do not actually need to code a With UR clause; specifying the Uncommitted Read option (as discussed above) will run all your queries in uncommitted read mode, which does the same thing.

This problem has been correct in v8 of the DB2 Client.

Displaying LOBs

In order to display LOBs correctly, make sure you don't have the client setting LONGDATACOMPAT set. When this option is set, LOBs are reported to AQT as LONG data types - AQT will not recognise the columns as LOBs so may not display them in full.

EBCDIC-ASCII Conversion

In general, character data held in DB2 z/OS is encoded in EBCDIC format. AQT (along with all PC-based products) use ASCII encoding.

When AQT reads data from DB2 z/OS, the conversion from EBCDIC to ASCII is done by DB2/Connect. However, if you have defined your character data as FOR BIT DATA, the column is binary data, so no conversion is done by DB2/Connect. This means that if you have any normal text in these columns, the data will come to AQT in Ebcdic format, so will be unreadable.

This is a particular problem with DB2 z/OS v8 and above, as in this version a number of columns in the catalog tables have changed from VARCHAR to VARCHAR FOR BIT DATA. To circumvent this problem:

when displaying system information (in the Database Explorer etc), AQT will check whether the column contains Ebcdic data. If so, it will convert it to Ascii. This enables View Text, Package Statement text, and other displays to show the information correctly.
if you want normal queries to also convert the data, use option Display Format > Convert EBCDIC to ASCII.

Collation Order for DB2 z/OS

Because DB2 z/OS (normally) uses EBCDIC, the collation order is different than that of non-mainframe databases (such as DB2/UDB). For example, if you run the following query:

Select name from sysibm.systables where creator = ? order by 1

Then

DB2 z/OS will return TABLE1 before TABLEA
DB2/UDB will return TABLEA before TABLE1

This is because, in EBCDIC, 1 is "lower" than A, whereas, in ASCII, A is "lower" than 1.

In most cases this is not a problem. However the exception is when you are using the Compare Objects function, and you are comparing a DB2 z/OS database to a non-mainframe system. When you use the Compare all objects in schema function, AQT runs a query against each system to get the list of tables in the schemas. Because of the difference in collation, the lists of tables will be returned to AQT in different orders. This will lead AQT to think that some of the tables are not present in the other system, when in fact they are.

At this stage, we do not have a resolution to this problem.

Resource Limit Warning for DB2 z/OS

DB2 z/OS has a feature called the Resource Limit Facility (RLF). With this, you can configure two limits:

an error threshold. When your query exceeds a this limit, it will be automatically aborted by DB2.
a warning threshold. When your query is Prepared, DB2 estimates the amount of resources the query will use. If this is greater than the warning threshold, DB2 will return sqlcode +495 to the Prepare. The user can then decide whether or not to proceed with query.

AQT supports the warning threshold. When it receives a +495 code (actually SQLState 01616) to the Prepare, AQT will prompt the user whether or not to proceed with the query. However in order to use this feature of AQT you must disable Deferred Prepare. This is done from Options > Technical Parameters:

de-select Use Deferred Prepare
ensure that Use SQLExecDirect is not checked

Limitation using the Merant ODBC Driver

If you are using this ODBC Driver, you may get the following message while using the Database Explorer against DB2 z/OS.

DSNT408I = -301, ERROR: THE VALUE OF INPUT HOST VARIABLE OR PARAMETER NUMER 001 CANNOT BE USED AS SPECIFIED BECAUSE OF ITS DATA TYPE

This is due to a limitation or bug with the way the Merant ODBC Driver processes parameterised queries.

This problem can be circumvented by setting Options > Table Information; for Parameter Usage select Use Substitution

Limitation using the NEON ODBC Driver

This ODBC Driver appears to not support Unicode. You will not be able to display any information about your database (or even run any queries) unless you switch off Unicode mode. This is done with Options > Unicode Options, deselect Enable Unicode features.

Updating DB2 for iSeries Data

When updating a DB2 for iSeries table you can get the error message: error SQL7008 -(table) in (file) not valid for the operation. This occurs because the table you are trying to update is not being journalled and AQT, by default, runs update of under commitment control.

To circumvent this problem, you need to switch off Safe Update Mode. When this is switched off, AQT will not run updates under commitment control.

Stored Procedure - Table Dependencies

The Database Explorer has functions Dependent Objects and Dependents of Dependent Packages that make it easy to see which tables are used by Stored Procedures. You can see the reverse relationship with (when viewing a table), Dependent Routines and Dependent Compiled Routines.

However, these functions do not work well if you are using temporary tables in your stored procedure. For instance, if you have the following statement in your procedure:

insert into session.tabcounts select count(*) from Employee

then you will not see Employee in the list of dependent objects for the procedure. Due to the use of the temporary table, this statement is compiled at run-time, not when the procedure is defined. Because of this, the dependency information will not be recorded in the DB2 catalog so will not be seen by AQT.

Using *SYS Naming Convention with DB2 for iSeries

AQT supports the use of the System Naming Convention (*SYS). For more on this, see Options > Table Information.

MySQL

Run Stored Procedures

The Run Procedure window cannot be used for running Stored Procedures. MySQL provides no way to get the information on procedure parameters - AQT needs this for the Run Procedure window.

Transactions

MySQL v5 supports transactions. However it's ODBC Driver says that it does not. You may get the message Your database does not support transactions. Option ignored.

Row Counts

We have noticed some odd things with row counts. For instance a table with (say) 150 rows can have a smaller value (eg. 135) in the Table Sizing and Table > Full Details displays. When you do a Truncate of the table, it will give the lower, incorrect value (135), rather than the actual number of rows removed.

These are both oddities with MySQL, rather than errors with AQT.

Column Sizes

Some versions of the MySQL ODBC Driver can misreport the sizes of columns in the query results. This has been corrected in more recent versions of the ODBC Driver.

Number of Rows Affected with Update and Delete statements

When you run an Update in MySQL. it returns the message Number of rows affected=0 if the data has not changed. For instance if you run:

UPDATE Customer_Details SET customer_type ='T' WHERE cust_code = '000001'

you will get Number of rows affected=1

If you run the same statement a second time, you will get Number of rows affected=0. This is because the second time this is run, no data is actually changed so MySQL return a row count of zero.

This is different behaviour from all other databases, which will always return a row count of 1 for this statement. A row count of zero is used to indicate that no data has been found (by the WHERE clause).

Informix

Informix Version

Informix v7.3 introduced the CASE statement. This is used in the queries used to populate the Database Explorer and in a number of places in the Admin Component.

If you are using a version of Informix prior 7.3, you will find that a few functions of the Database Explorer and Admin Component do not work correctly.

Date and Datetime Literals

This topic is important if you are dealing with date or datetime columns.

In Informix, the way in which you specify date and datetime values is determined by your environment variables DBDATE, DBTIME, GLS_DATE and GLS_TIMESTAMP. For instance, if you have DBDATE=Y4DM/ then you would need to code a query such as:

select * from order_details where date_required = '2004/25/06'

However if you had DBDATE=Y4MD- then the query will need to be:

select * from order_details where date_required = '2004-06-25'

In most places, AQT will generate the date in the correct format (for instance, if you are using the Get Values function to get a list of date values). However, in order to do this, AQT needs to know the format in which date literals are to be specified.

Unfortunately, AQT cannot determine the values of the environment variables (DBDATE etc). Instead, you must tell AQT the format to use for date and time literals. This is done in Options > Display Format > Format of Informix date/time literals.

If you are dealing with date or datetime columns, you will need to set these options.

More on Datetime Columns

In Informix you have great flexibility in defining date / time columns which a particular granularity. For instance the following defines a column containing a time:

update_time datetime hour to second

There are no literals which can be used such a data type. Instead, you must use the datetime function if you are specifying a particular value for the column. Eg. the following is not valid:

select * from order_details where update_time = '12:23:55'

Instead you must use:

select * from order_details where update_time = datetime(12:23:55) hour to second

Note that there are no quotes around the 12:23:55 value.

At this stage, AQT will not generate the datetime clause for you. Consequently you cannot update such datetime values using the AQT inline-edit. Nor can the Data Loader load datetime values such as this.

Outer Join Syntax

If you are running an early version of Informix, you will only be able to use native syntax for outer joins. In the GUI Query Builder, the syntax used for outer joins is specified on the Options tab > Join Syntax. If you have this set to the first option (Tab1 Left Outer Join Tab2) you will get a syntax error when you use an outer join. Change this to the second of the two options (Tab1, OUTER Tab2).

When you use the native join syntax you will only be able to do left outer joins; right and full joins are not possible with native syntax.

HP SQL/MX

Column Names

By default, column names will not display correctly in AQT. You need to configure your datasource entries on MXCS to specify SQL_ATTR_NO_HEADING=TRUE.

See Connecting to HP SQL/MX for more on this.

Running Multiple Queries

SQL/MX appears to have limitations when running more than one query at the same time.

This affects the use of Compare all Tables in Schema, plus the Data Compare functions. These functions require AQT to run two queries simultaneously. If both of your schemas or tables use the same connection, then these functions will fail with:

ERROR[8813] Trying to fetch from a statement that is in the closed state

The circumvention to this is to sign onto the database twice - eg. have two connections to the same database. Select the compare and compare-to from the different connections.

Get More Rows Feature

The limitation with multiple open queries also effects the use of Get More Rows. If you:

fetch some rows (so you have an open query in the Data Display window)
do some other queries
return to the Data Display window and click on More

then AQT can crash.

Uncommitted Read

SQL/MX doesn't allow you to run a non-select statement (such as an Update or Create Table) while using uncommitted read. You will need to switch off uncommitted read if you are running such statements.

MS Access

You don't need MS Access to read Access databases with AQT

To understand this, you need to realise that MS Access consists of two separate components:

a database engine. This comes standard on all Windows PCs
a query and development environment. This is what you get when you purchase MS Access

If you do not have MS Access on your machine, you still have the database engine. As a result, AQT is able to read and update your Access databases.

Access Queries

What MS Access calls a Query is called a View in AQT.
AQT is unable to show you the definition of your Access queries. These are held internally in a way that AQT is unable to get at.
Within MS Access, you can define parameters within a query. For instance you can define a query as:
select * from customers where customer_code = custcode
When you run this query, MS Access will prompt you for the value of custcode. However the use of such parameters is internal to MS Access; if you try to run a query which is defined in this way to AQT you will get the message Too few parameters.

Transaction

MS Access has limited support for transactions. If you are running in Transaction Mode, or are using Safe Update Mode you can get message Invalid Transaction State, Function Sequence Error or Attribute cannot be set now.

In the Data Loader, you will get similar errors if you use a Commit Frequency other than Every Row.

MS SQL Server

Use of Rowcount

Performance of queries, particularly those accessing large table, can be improved by having AQT set the rowcount option. This is discussed in more detail in the section Rowcount for SQL Server and Sybase.

Using Temporary Tables

In SQL Server, you can use temporary tables to store the results of queries for the duration of your session. Example:

Select * into #temp1 from dbo.sysobjects where name like 'sys%';

Select * from #temp1;

When you run these you may get the message: Invalid object name '#temp1'. This problem is due to a limitation of the SQL Server ODBC Driver (for technical types, this is described in http://msdn.microsoft.com/library/en-us/odbcsql/od_6_015_8nce.asp).

You can circumvent this limitation by going to Options > Technical Parameters and checking Use SQLExecDirect. When this option is selected, AQT will process your SQL in a slightly different way that avoids this limitation with the ODBC Driver.

Server-Side Cursors

This is a technical topic for advanced users.

By default, AQT uses client-side cursors. This works well in almost all circumstances, however there is one limitation: only one client-side cursor can be active at once

The only time you are likely to meet this limitation is if you are using the Get More Rows feature. When this feature is used, AQT will not close your query, but will keep it open so you can continue scrolling through the query results. If you try to run another query, while you have an open query, you will get message:

Connection is busy with results for another hstmt

You will only be able to run the second query once the open query has been closed.

If this limitation is a problem for you, you can go to Options > Technical Parameters and check Use SQLExecDirect. When this option is effect, AQT will use Server-Side cursors. This will allow you to have multiple queries open at the same time

Table / View / Column Descriptions

AQT supports the use of table / view / column descriptions within SQL Server. These are held as an Extended Property of the tables / views and columns. AQT uses these with an Extended Property name of MS_Description. This is compatible with the way descriptions are managed within Enterprise Manager.

If you are using a Extended Property name other than MS_Description (for instance Caption), you will need to make a change the AQT config files:

sqlserver2000.cfg
sqlserver2005.cfg
sqlserver_adm.cfg

In each of these files, make a global change of MS_Description to (for instance) Caption.

IDENTITY_INSERT

If you wish to use the statement:

set IDENTITY_INSERT tablename on

then this will only work if you have set Options > Technical Parameters > Use SQLExecDirect. If you have not set this option, the set statement will say that it has worked, though it will have been ignored.

Oracle

Cancel Queries

In order to cancel queries with Oracle, you need to set Options > Cancel Queries > AQT Cancel Task. This runs a cancel task in a separate thread, which allows an active query or update to be cancelled.

Message ORA-00933 SQL command not properly ended

You can get this message if you are running a join which uses ansi join syntax (Left Outer Join), but your database does not support this syntax for joins.

If you are building your query using the GUI Query Builder, you can avoid this problem with:

click on the Options tab of the query builder
click on Join Syntax
for Outer Join Syntax select the second option Tab1 = Tab2(+)
you may also wish to click on Save as Default Options, so that this option will be used for future queries you build.

Displaying LOBs

If you wish to view LOB columns, you need to use the Oracle ODBC Driver. The Microsoft ODBC for Oracle driver does not support LOB columns.

Insert Data into LOBs

When inserting a LOB value, you can get the following error

ORA-01461: can bind a LONG value only for insert into a LONG column

This happens because you have not enabled LOB write support in the configuration of your Oracle ODBC Datasource. To correct this, change this:

start the ODBC Data Source Administrator (from the AQT signon window > Manage Datasources)
select your database, then click on Configure
go the Oracle tab, and check the Enable LOBs checkbox

Inserting Data into CLOBs

When inserting into CLOBs, you cannot insert a literal which is more than 4000 bytes long. If you try this you may get a message ORA-01704 string literal too long. Some versions of the Oracle ODBC Driver will abend when you try this.

The solution to both these problems is to use the Load LOBs from Files feature.

Inserting Data into LONG columns

The Load LOBs from Files feature does not appear to work for Long columns. This may be a limitation of the Oracle ODBC Driver.

Timestamp and Interval Columns

AQT cannot display timestamp or interval columns, as the Oracle ODBC driver does not yet support these data types. As a workaround you can use the TO_CHAR function. eg.

select TO_CHAR(timestamp_col) from table1

When displaying a table that has timestamp or interval columns, AQT will automatically add the TO_CHAR function to any timestamp/interval columns in the table.

Displaying Package Body Text

In the Database Explorer window, you can only display the Body Text of a Package if you have CREATE ANY PROCEDURE or DEBUG ANY PROCEDURE privileges on that Package. If you do not have this privilege, the Body Text will display as blank.

An alternative is to change the query used (query 63 in the oracle.cfg file) to use DBA_SOURCE rather than ALL_SOURCE.

User-Defined Data Types

AQT cannot display any columns defined with a user-defined data type. This is due to a limitation of the Oracle ODBC Driver. If you try to display such columns, AQT will hang or crash.

You can display a table with these data types only if you exclude these columns from the column list. When you display a table in AQT, AQT will automatically exclude the user-defined columns from the table display and replace them with the literal "(user-defined type, not displayed)".

Displaying Remote Data

If you display data from a remote database by either:

select * from remote_synonym

select * from table@database

then you may get error ORA-02041: client database did not begin a transaction.

This error is due to a bug or oddity with the Oracle ODBC Driver. The problem doesn't happen if you use the Microsoft ODBC Driver for Oracle. In general the Microsoft ODBC Driver is not as good as the Oracle ODBC Driver, however in this case it is able to display remote data without problem.

For more information on setting up a connection using the Microsoft ODBC Driver for Oracle see Connecting to Oracle.

Bug with Displaying Large Numbers

There appears to be a bug with the Oracle ODBC Driver in that large numbers (eg. 17 digits or more) have their last digits replaced with zeros. There are two circumventions to this problem:

use the Microsoft ODBC Driver for Oracle. Normally we don't recommend using this driver, however in this case it works better than the Oracle driver.
use the to_char() function. When this is used, the number is displayed correctly.

No Data when using WITH

If you are using the Microsoft ODBC for Oracle Driver, you will not see any data for a query using the WITH clause.

This is a bug / limitation of that driver.

Oracle - Diagnosing Connection Problems

If you are having problems connecting to your Oracle database, then you should follow the following steps for diagnosing this:

when you fail to connect, a file sqlnet.log is often created (see below). This can contain useful information about how the Oracle Client tried to connect, and the error it received.
open a Windows command window and enter tnsping ORCL where ORCL is the name of the Oracle Service you are trying to connect to. If you are unsure of the Oracle Service name, from the AQT signon screen click on your Oracle database then click on Configure - the Oracle Service name is given in the field TNS Service Name.
tnsping will try to connect to the Oracle database, and will provide useful information about how it is doing this and the error it has received.

tnsnames.ora

The information about the Oracle service names, and how to connect to them, is given in the Oracle file tnsnames.ora. In many cases, connection problems have happened because the wrong tnsnames.ora file is being used.

Oracle looks at the following locations for tnsnames.ora:

the directory referred to in environment variable TNS_ADMIN
the directory ORACLE_HOME\network\admin. ORACLE_HOME is given in the ORACLE_HOME environment variable, or the Windows registry.

To complicate matters:

a user may have multiple ORACLE_HOMEs
Oracle products may have their own ORACLE_HOME (and thus tnsnames.ora). So SQL*PLUS may be using one tnsnames.ora file but (unknown to you), AQT is using another.

To clear up this uncertainty, it is recommended that the TNS_ADMIN environment variable is set to refer to directory where tnsnames.ora is located. All Oracle products and AQT will then use this tnsnames.ora file.

To view environment variables, open a Windows command window and enter SET. To permanently set an environment variable, go to the Windows Control Panel > System. Click on the Advanced tab and then the Environment Variables button (this is for Windows XP - other Windows versions may have these in a different location).

sqlnet.log

If you fail to connect, the Oracle client will generally write diagnostic information to sqlnet.log. Note that this does not include information on which tnsnames.ora file is being used, which is often the cause of many connection problems.

In earlier versions of Windows, sqlnet.log was written in the same directory as the AQT executable (eg. C:\Program Files\Advanced Query Tool v11). However for more recent versions of Windows, access to the Program Files directories is restricted. As a result the file can often be created in a Virtual Store directory. You may wish to look for sqlnet.log in either:

C:\Users\<username>\AppData\Local\VirtualStore\Program Files\Advanced Query Tool v11
C:\Users\<username>\AppData\Local\VirtualStore\Windows\System32

Running AQT on a 64-bit version of Windows

If you are running AQT on a 64-bit version of Windows, you may fail to connect with message:

TNS could not resolve the connect identifier

This can happen due to a bug in the Oracle client in the 64-bit environment. This is described below.

By default, AQT will be installed into C:\Program Files (x86)\Advanced Query Tool v11. The Program Files (x86) directory structure is used for 32-bit applications. However there is a bug in the Oracle client - when you run a program which has a bracket in the path, the Oracle client will fail to parse tnsnames.ora correctly, resulting in the above message.

The resolution to this problem is to install AQT into a directory that doesn't have a bracket in the name.

Note that this problem has been fixed in recent versions of the Oracle Client.

PostgreSQL

AQT has been configured for use with PostgreSQL v7.3 and above. This release introduced schemas into PostgreSQL, which is a significant change to the database, and a significant change in the way AQT displays information in the Database Explorer.

If you are using a release of PostgreSQL prior to v7.3, you will need to use a different PostgreSQL cfg file. You do this as follows:

select the Options menu item
click on the Config Files tab
find the Postrgres entry, and change the Cfg File Name entry from postgresql to postgresql2

The Admin Component will only work with PostgreSQL v7.3 and above.

Pervasive.SQL

Admin Component and X$Attrib Table

If you do not have the X$Attrib system table defined on your system, the Admin component will not be able to manage your tables.

The solution is to use an alternate admin config file (pervasive_adm2.cfg) that does not refer to the X$Attrib table. Some attributes of your tables (Default and Case Sensitivity) will not be displayed.

To use this alternate cfg file, go to Options > Config Files. Find the entry for Pervasive and change the Admin Cfg value to pervasive_adm2

This change may also be required if you are running Pervasive.SQL v7, which can misreport column types.

SAP ASE

Message "Stored procedure 'sp_odbc_tables' not found"

You will get this message if your SAP ASE server hasn't been configured fully. Part of the configuration of a SAP ASE server is the step that creates a lot of stored procedures in the sybsystemprocs database. If this step has not been done, AQT will not operate and you will get the above message.

The solution is to have your DBA run the configuration step for the SAP ASE server.

Parameterised Queries

If you wish to run queries with parameters (such as Select * from table where column=?) then your ODBC Driver must be configured for a non-default value of PrepareMethod. This is described in SAP ASE unable to display tables in Database Explorer.

Numeric Parameters in Parameterised Queries

SAP ASE has difficulty dealing with numeric parameters in parameterised queries. For instance if you run a query such as:

Select * from table where numeric_column=?

you will get the following error message:

Implicit conversion from datatype 'CHAR' to 'INT' is not allowed. Use the CONVERT function to run this query

The solution to this is to change your query to:

Select * from table where numeric_column=convert(int,?)

The datatype in the convert function must match the data type of numeric_column.

Cancel Queries

In order to cancel queries with SAP ASE, you need to set Options > Cancel Queries > AQT Cancel Task. This runs a cancel task in a separate thread, which allows an active query or update to be cancelled.

Use of Rowcount

Performance of queries, particularly those accessing large tables, can be improved by having AQT set the rowcount option. This is discussed in more detail in the section Rowcount for SQL Server and SAP ASE.

Compute Clause

If you use this, you may get the error:

COMPUTE clause can not be used in the declaration of a cursor

To solution to this is to change the ODBC Configuration of your SAP ASE database to use a Select Method of 1-Direct.

Truncate Table

This can give you message:

TRUNCATE TABLE command not allowed within multi-statement transaction

The resolution to this is to go to Options > Technical, and check Use SQLExecDirect.

SAPDB / MaxDB

Using MaxDB v7.6 or later

As with MaxDB v7.6, Schemas were introduced into MaxDB. This has a significant change to the database and has a big effect on the structure of the system tables and the data displayed by AQT. Consequently, for MaxDB, AQT has two config files (config files are used for displaying the information in the Database Explorer)

maxdb.cfg for SAPDB or for MaxDB v7.5 or earlier
maxdb_v7.cfg for MaxDB v7.6 or later

AQT should detect the version of MaxDB you are using and automatically use the correct cfg file. However if this is not working, you can change the cfg file manually. You do this as follows:

go to Options > Config Files
find the entry for MaxDB
change the cfg file name from maxdb to maxdb_v7

Message "Invalid string of buffer length"

You can get this message while displaying the information on a table. This occurs when you do not have authorization to display the table.

Teradata

AQT works well with Teradata, however some Teradata users have reported that the following has to be done in order for AQT to work in their environment:

when configuring the ODBC configuration for your Teradata database, ensure that the No Help Database option is selected
one user could not display information because they did not have access to the Teradata system tables. If this is the case you will see an error message at the bottom of the window. To correct, ensure you have access to these tables

Table name ambiguous

Some users have experienced the following message when running a query:

S0002(-3806)[NCR][ODBC Teradata Driver][Teradata RDBMS] Table/view/trigger name 'xxxxxxx' is ambiguous

This can happen if you are using a qualified table-name in your query. For example you might have:

select * from dev1.tableA

The cause of this problem is the Default Database setting in the ODBC Configuration for your database. If this is (for instance) dev2 then Teradata will look at both dev1 and dev2 for a table called tableA. If it is in both, you will get the ambiguous table-name message.

The solution is to change the Default Database to a database that doesn't have a table of that name.

Running Stored Procedures

By default, AQT is able to run Macros but not Procedures. This is because AQT runs procedures with the CALL statement, which the Teradata ODBC Driver will convert to an EXEC. Only Macros can be run with an EXEC. By default, when you try to run a procedure from AQT you will get message:

S1000(-3855)[Teradata][ODBC Driver][Teradata Database] 'procedure-name' is not a macro.

To get around this, you will need to set the ODBC Driver setting Disable CALL to EXEC Conversion.

With this option set, you will be able to run Procedures but not Macros.

Excel

Opening an Excel file in AQT

This is described in Connecting to Excel.

Using Excel Files in AQT

When you sign onto an Excel file using AQT, you will be shown

your worksheets
your named ranges

The Excel ODBC Driver will assume that the first row in your worksheet / range contains your column names. If your column names are numeric, it will give it a name such as F1 instead.

If your worksheet name contains spaces, the ODBC driver can get confused and think that it is a range instead of a worksheet. This doesn't effect the way AQT operates.

Querying Data

When querying your data, you need to enclose Worksheet names in square brackets (this isn't needed for Ranges). If you wish to select a particular range of cells, you can include this as part of the table name. Examples:

select * from Range1

select * from [Sheet1$]

select * from [Sheet1$A1:C10]

Updating Data

You can use AQT to update / insert / delete data in your Worksheets. By default, your Excel datasource will be read-only; if you try to amend amend data you will get message:

Operation must use an updateable query

The solution is to configure your ODBC Datasource to be non read-only (see Connecting to Excel for a description of this).

Other notes:

if you insert data into a range, it may be placed at the bottom of the worksheet, outside the defined area of the range. While it will have been inserted successfully into the worksheet, you will not see if when you redisplay the range.
you can create worksheets with the create table command. When you create worksheeets in this way, Excel will create both a worksheet and a range with this name (though the worksheet with have a $ on the end).
you can use drop table. The contents of the worksheet will be deleted, though the worksheet will not be removed.
Excel sometimes gets messed up if you have an Excel file open in AQT, then you open it in Excel. A better option is to open it in Excel, then open it in AQT. AQT links into the Excel session; changes you make in AQT are seen immediately in Excel.
If Excel gets into an odd state (whereby the body of the window isn't displaying) go View > Full Screen, then Close Full Screen.

Setting up named ranges

If you are using Excel frequently with AQT, you may wish to set up a number of named ranges in your Excel file. This is done as follows:

open your Excel file
select ONLY the active cells in your worksheet.
click on the Insert menu item, then Name > Define
enter a name for this range of cells

You should select a range that includes only your data. Do not select columns outside your data else you will get an error when trying to access the data.

Columns are maximum size of 255 bytes

Excel can sometimes truncate data at 255 bytes. This is a known issue with the Excel ODBC Driver that is described in http://support.microsoft.com/kb/189897/

Bug with Excel Dates

There is a bug with Excel dates, that some AQT users have noticed while viewing Excel data in AQT, or while loading Excel data into a database.

Excel incorrectly thinks that 1900 was a leap year, and therefore that 29 Feb 1900 is a valid date (when it is not). This results in every day prior to and including 28 Feb 1900 being incorrect by one day. In other words, if an Excel spreadsheet shows a date as 10 Jan 1900, it will appear in AQT as 09 Jan 1900. If you load the date into your database, it will come across as 09 Jan 1900.

This is a problem with Excel, and there is little we can do about this.

Database types and config files

The system-table feature of Advanced Query Tool reads the system tables (dictionary / catalog) for the database. These system tables differ greatly; they are:

different for different database types (Oracle / DB2),
different for database implementations (DB2/UDB for AIX is different from DB2 for z/OS), and
even different between releases of a database.

To provide a simple and customizable way of reading the system tables, the SQL statements for all system-table queries are held in config files. No SQL statements are hard-coded in the AQT code. The config files also give the structure for the Database Explorer, plus the various list-boxes and menus used for navigating through the system objects. Designing Advanced Query Tool in this way has given it tremendous flexibility:

if you want, you can amend these config files to add in your own system queries, or change the existing ones
you can distribute different config files to different categories of users
support for new database types can be implemented by building and distributing a new config file.

If you wish to amend any of the config files you should see Customizing your System.

The use of config files however provides an element of complexity as follows: when AQT connects to a database it needs to work out which config file to use. The discussion below describes how this works within AQT. You may need to know this if you are making changes to the config files, or if you are having problems with the system.

Database Type

AQT operates on the principal of auto-detecting the database type (Oracle, SQL Server, etc). When AQT connects to a database it issues the GetInfo ODBC API call to get the database type. AQT is reliant on the database returning sensible information to this call and the type being known to AQT (see the next section).

If you wish to know what name is being returned here, see Help > Database Details > Database Type or Options > Config Files.

Config File

Having found the database type, AQT then needs to determine which config file to use for this database. AQT uses a cross-reference between database type and the config file name. This cross-reference can be seen in Options > Config Files. These entries are initially populated when AQT is run for the first time, however you can amend them if you are adding a new database type or if your database identifies itself by a name different to what AQT expects.

Options > Config Files describes how these entries work.

Future releases of AQT will add extra entries to this list, as support for other databases is included.

Config File Directory

The directory where the config files are held is give by Options > File Locations > Location of Config Files. By default this is app-path\cfg where app-path is the directory where the AQT executable resides. If your config files are held elsewhere you will have to change this.

Options > File Locations has more information on this setting.

Unicode support

With AQT v7 we have added Unicode support to AQT. This allows AQT to display text from non-European languages such as Chinese, Japanese, Korean, Arabic, Maori etc.

AQT v7 can not be run on Windows 95/98/ME as those versions of Windows do not support Unicode.

This support has been enabled by the following changes to AQT:

most of the controls in AQT have been changed to new controls that support the display of Unicode characters
the database interface layer has been changed to retrieve data in unicode format
queries, and other files that AQT uses, are saved in unicode format if they contain unicode characters
the copy, cut and paste functions have been amended to use unicode clipboard format.

Unicode support

By default, Unicode support is enabled within AQT. If you do not use Unicode data in your databases it is recommended that you switch this option off for two reasons:

it may have a slight performance effect, and
files are written in Unicode format, which you may not want.

Unicode support can be enabled / disabled with Unicode Options > Enable Unicode Support.

Changing to Unicode fonts

In addition to Enabling Unicode Support, display of many unicode characters is possible only if the font you are using can display these characters. By default, AQT uses MS Sans Serif font, which displays only some Unicode characters. Courier New displays a wider range of unicode characters; to display a wider range of unicode characters you may need a specialised font.

To use a differerent font, select it in Options > Unicode Options > Font used for Unicode controls. This setting applies to the Text boxes, Combo boxes and List boxes that are used to display values from the database.

In addition to this you may need to change other fonts:

the font used in the syntax-highlighting text box used in the Run SQL window is set in Options > Syntax Highlighting
the font used in the Data Display grids is set by (from the Data Display window) Edit > Set Font.
the font used in the Database Explorer grids is set in Options > Table Information. You would only need to change this font if you have unicode characters in your table or column names.

Database interface

When Enable Unicode Support is selected, AQT will use different set of methods for interfacing to the database:

all SQL will be passed to the database in unicode (rather than ASCII) format.
all character data retreived from the database is retrieved in unicode format.
all substitutional parameters passed to the database are passed in unicode format.

So far, all databases we have tested against support this unicode interface method (even if the databases themselves don't support unicode). However if you are having trouble accessing your database, disabling unicode support might provide a more reliable interface to the database.

Saved files

When Enable Unicode Support is selected, all files saved by AQT are scanned for unicode characters. If any unicode characters are found, AQT will save the file in unicode format.

Files saved in unicode format can be read only by external editors that support unicode (such as Notepad). Unicode files are in double-byte format with the first two characters being x'FFFE'.

The Audit files are not saved in unicode format.

Exporting data

unicode data can be exported to Excel (as Excel supports unicode data).
when exporting data as HTML, any unicode characters will be written using the unicode HTML tags.
when exporting data as Insert statements, you have the option to Export Data in Unicode format. When this option is selected, the insert SQL script is saved in unicode format if it contains any unicode characters. If you are going to run the script using an SQL processor that does not support unicode text, you should de-select this option.

If you select append when exporting data, AQT will ignore the Export Data in Unicode format setting and will maintain the existing file format. For instance, if the existing file is non-unicode, all subsequent data will be appended to the file in non-unicode format, even if the data contains unicode characters. It is not possible to mix the character encoding within a file.

Writing CLOBs to files

When Unicode support is enabled, CLOBs written to files (using the Options > LOB Options > Send to files option) will be written as unicode files.

Demo Table

In the demo database AQTDEMO is a table unicode_table containining Unicode data. Displaying this table is a good test of whether unicode display is working on your PC.

Creating Unicode characters

Unless you have a specialised keyboard, unicode characters can be created with:

from the AQT Database Explorer, click on Tools > Unicode Character map
Windows supplies a very good unicode character map. It can be run by Start > Run and running charmap

Limitations with Unicode

Current limitations are:

printing the data grid does not print unicode characters. We have raised this problem with the developers of the grid. In the meantime you can print your unicode data by exporting to HTML or Excel and printing from these.
only the export methods given in the previous section will export unicode data.

Database support

Not all databases support Unicode data, and the degree to which they support unicode varies from one database to another. In the following topics we describe how particular databases support unicode.

Unicode support with DB2

This topic applies to DB2/UDB. We haven't yet investigated Unicode support with DB2 z/OS or DB2/400. If anybody has implemented Unicode with these databases we would be interested in any feedback.

Configuring the database

A DB2/UDB database can only store Unicode data if Unicode has been defined as the database character set. This is done when the database is defined, and cannot be done retrospectively.

Example of creating a Unicode database:

Create Database DB2UTF8 using codeset UTF-8 territory US

Setting the Client Codeset

If you are using a Unicode database, it is recommended that your client uses a Unicode codeset. You can set this by running the following command in a DB2 Command window:

db2set DB2CODEPAGE=1208

If this is not done, Unicode literals will not be interpretted correctly by DB2.

Data Types

In a Unicode database, all character types will hold data in Unicode. You can therefore use the standard Char and Varchar data types.

Unicode in Table and Column names

This is supported by DB2

Unicode support with Informix

ODBC Driver Version

In order to display Unicode data, you must use the IBM Informix ODBC Driver version 3.80 or later.

ODBC Driver Configuration

In order to display Unicode data, your Informix client needs to be configured to use the UTF8 codeset. On our development server, we find the following setting works well: on the Environment tab of the Informix ODBC Driver Setup, set both Client Locale and Database Locale to en_US.UTF8.

Data Types

Unicode strings are held in columns with the nchar and nvarchar data types.

Unicode Character Set Database

Any Informix database can store unicode data in nchar and nvarchar data types. However if you want to use unicode strings in the names of your objects (eg. tables and columns), you must define your database to use a Unicode character set. See your Informix documentation for more information on doing this.

You can find out the character set of your database with:

Select * from sysmaster:informix.sysdbslocale

Unicode support with MySQL

This information is accurate as of November 2020. It may change as product versions change.

Database Version

In order to use Unicode in your MySQL database, you must be using MySQL v4.1 or later.

Client Version

Support for Unicode was introduced in MySQL Connector version 5. You must be using this version, or a more recent one, to use access Unicode data with AQT.

For MySQL Connector version 8, when you install the Connector you will get two ODBC Drivers:

MySQL ODBC 8.0 ANSI Driver
MySQL ODBC 8.0 Unicode Driver

You must use the Unicode driver.

Data Types

Unicode strings are held in columns with a unicode character set. The character set is specified either when the column is defined, or from the character_set_database system variable.

Example:

Create table unicode_test (unicol varchar(20) character set utf8mb4)

You can see the character set of your columns with the "Column character sets" display on the Database Explorer.

Running SQL Statements with Unicode literals

In MySQL, Unicode literals can be specified in a number of ways:

prefixed with an N (example: N'ald').
prefixed with an underscore and the codeset name (example: _utf8 'ald').

Unicode support with Oracle

ODBC Driver Version

Unicode support is only available when using v8.1.5.5 of the Oracle ODBC Driver or later
Unicode support is not available when using the Microsoft ODBC Driver for Oracle

Data Types

If your database is using a Unicode character set, char, varchar2 and clob data types will hold Unicode values.

Irrespective of your database character set nchar, nvarchar2 and nclob data types will hold Unicode values.

Unicode Character Set Database

Full support of unicode in your Oracle database is only available if your Database Character Set has been set to a unicode character set, such as UTF8. The database character set is specified when your database is defined.

You can find out the database character set with:

Select value from SYS.NLS_DATABASE_PARAMETERS where PARAMETER = 'NLS_CHARACTERSET'

ASCII Character Set Database

If your Oracle database has been defined to use an ascii character set (as opposed to a unicode character set), then you can still store unicode data in your Oracle database. However there are some limitations on what you can do.

you can display unicode data OK
you cannot run an SQL statement containing a unicode literal
you cannot supply a unicode value to a parameter in a parameterised query.

The reason for these limitations is that, when you pass SQL or a parameter to the database, it will convert the values to the character set of the database. If the database uses an Ascii character set, the unicode values will be converted to Ascii characters.

AQT has a circumvention for the point number 2 - this is discussed below.

Beause the Data Loader uses parameterised queries by default, it cannot load unicode values unless you use the Load using Insert Statements option.

Unistr Function

Oracle has a function UNISTR which allows you to specify unicode character values. This allows you to insert or update unicode values in your database, even if your databases uses an Ascii character set.

The UNISTR function is not available in Oracle v8.

Running SQL Statements with Unicode literals

If your database uses a unicode character set, you can run SQL statements that include unicode literals.

If your database uses an Ascii character set, you cannot run SQL statements that include unicode literals. To circumvent this, AQT has an option Unicode Options > For Oracle, use UNISTR for Unicode strings. When this is option is checked, AQT will scan all SQL being processed. If it finds any unicode string values, it will replace them with a unistr function:

Example:

if you run the following:

Select * from HR.TEST1 WHERE COL1 = 'ald'

AQT will actually run:

Select * from HR.TEST1 WHERE COL1 = unistr('\03B1\03BB\03B4')

This makes it very easy to use unicode data in an Oracle database that uses an Ascii characterset.

Unicode support with SQL Server

Configuration

SQL Server has good support for Unicode data. No particular configuration is required on the server or client in order to use Unicode data in your database.

Data Types

Unicode strings are held in columns with the following data types: nchar, nvarchar and ntext.

Running SQL Statements with Unicode literals

In SQL Server, Unicode literals must be prefixed with an N (example: N'ald'). If the N is not included, SQL Server will interpret the literal as an ascii literal, and so will not be interpretted correctly.

To make this easy for you, AQT has an option Options > Unicode Options > For SQL Server, ensure Unicode strings are in N'xxx' format. When this is option is checked, AQT will scan all SQL being processed. If it finds any unicode values, it will ensure there is an N preceding the string.

Example:

if you run the following :

Select * from HR.TEST1 WHERE COL1 = 'ald'

AQT will actually run:

Select * from HR.TEST1 WHERE COL1 = N'ald'

This makes it very easy to use unicode data in an SQL Server database.

Unicode support with SAP / Sybase ASE

Version

Unicode support was introduced in Sybase ASE version 12.5. A number of improvements to Unicode support were implemented in Sybase ASE 12.5.1. For more on this, see http://www.sybase.com/detail?id=1026310

Configuring the SAP ASE Server

In order to use Unicode in your database, you need to

load the Unicode character set.
for SAP ASE 12.5 you need to change the default character set id to Unicode. This does not need to be done for SAP ASE 12.5.1 or later.

You should ask your SAP ASE DBA for both of these tasks.

Configuring the SAP ASE Client

In order to use Unicode literals in AQT, you need to specify that your SAP ASE client uses Unicode encoding. This done by setting the following environment variable:

Variable	LC_ALL
Value	en_US.utf8

This is done through the Windows Control Panel > Advanced > Environment Variables.

Data Types

Unicode strings are held in columns with the following data types: unichar, univarchar.

Running SQL Statements with Unicode literals

The use of Unicode literals in your SQL statements is supported in SAP ASE 12.5.1 (and later), but not in SAP ASE 12.5.0. If you are running SAP ASE 12.5.0 you can use Unicode literals in your SQL by selecting Options > Unicode Options > For Sybase, use TO_UNICHAR for Unicode strings. When this is option is checked, AQT will scan all SQL being processed; if it finds any unicode string values, it will replace them with to_unichar functions.

Example:

if you run the following from the SQL window:

Select * from dbo.unicode_test where col1 ='ald'

AQT will actually run:

Select * from dbo.unicode_test where col1 = to_unichar(945) || to_unichar(955) || to_unichar(948)

This makes it very easy to use unicode literals in a SAP ASE database.

Other comments

These comments are for SAP ASE 12.5.0.

Unicode values cannot be passed as parameters in parameterized queries. We do not know whether this issue has been resolved in later version of SAP ASE.
the Data Loader can be used to load Unicode values into a SAP ASE table

Unicode support with other databases

MS Access

Unicode support is available if your MS Access file is in Access 2000 format or later.

MS Access supports Unicode data in all character data types. No special configuration is needed for this.

PostgreSQL

To use unicode data in a PostgreSQL database, you must create the database with a unicode character set. An example:

createdb -E UNICODE dbname

However, beyond this we have been unable to store or retrieve Unicode data from a PostgreSQL database (using PostgreSQL v7.3.4). The Unicode database needs to be compiled with multi-byte support enabled - perhaps the build we are running does not have this. Any feedback on this would be helpful.

Interbase / Firebird

While this database supports Unicode data, we have been unable to get this to work with AQT. It could be that the ODBC Driver does not support Unicode. There are some third-party ODBC Drivers that claim Unicode support, however we have not investigated these. Any feedback on this would be helpful.

Unicode support with SAP / Sybase Anywhere

Configuring the SAP Anywhere Database

In order to use Unicode in your SAP Anywhere database, you must specify a character set of utf8 when you create the database.

Configuring the SAP Anywhere ODBC Driver

To use Unicode, you must configure the ODBC Driver to specify a client codeset of utf8. On the ODBC Driver:

select the Advanced tab
set Character set to utf8

Data Types

Once the database has been defined to use utf8 encoding, all character data types can hold unicode data.

Use of Unicode Literals

During our testing with SAP Anywhere we have have found that AQT is able to:

display unicode data
specify unicode values in parameters
use unicode data in the Data Loader

However, the one thing that does not appear to work is the specification of unicode literals in SQL statements. This means that statements such as the following cannot be run:

Insert into DBA.unicode_test (col1) Values('ald')

This makes it very difficult to manipulate unicode data in your database. This limitation is almost certainly due to limitations of the Sybase Anywhere ODBC Driver.

Replace unicode Strings with parameters

To avoid this limitation, AQT has an option Unicode Options > For SAP Anywhere, replace Unicode strings with parameters. When this option is specified, AQT will scan your SQL statements for strings containing unicode values. When if finds one, it will replace the unicode string with a parameter marker, and pass the unicode string in the parameter.

For instance, when it sees:

Insert into DBA.unicode_test (col1) Values('ald')

AQT will process this as:

--aqt parms,parmno=1,value=ald

Insert into DBA.unicode_test (col1) Values(?)

This makes it very easy to use unicode data in an SAP Anywhere database.

One limitation: if AQT finds any existing ?s (parameter markers) in your SQL, AQT will not do this string replacement.