This chapter contains explanation of some of the issues that AQT must handle when it connects to different database types.

It also describes some of the more advanced functions available in AQT.

Startup Command Arguments

You can supply command arguments when you start AQT.

These can be provided by:

• in a *.bat job when you run an AQT script

• in the command string in the AQT desktop icon / shortcut

• when you invoke AQT from another program

The various command arguments are given as follows:

Connecting to a database when AQT starts

The command to do this is:

aqtv11.exe conn=connection-string,dbs=dbname

• examples of connect strings are given below. See also Doing a Direct Connect.

• dbs is the name of the database within AQT

Examples of connection strings:

The third example shows how you can connect to a database when you haven't created a datasource. This form of the connection string requires you to specify the Driver. There are two formats:

• Driver={Oracle in XE}

• Driver=Oracle

In the first of these, you need to specify the driver name exactly. If it is different by as much as a single byte, the connection will fail.

The second example is an AQT-developed variation of the Driver clause. AQT will scan all drivers defined to your Windows system looking for one containing "Oracle". It will then use this driver in your connection string. The "MS ODBC for Oracle" driver will be ignored.

Starting AQT specifying options

You can specify options when you start AQT.

Examples:

Advanced Customization

Concepts explained in this section will strongly affect the way that AQT runs, and may cause data to be displayed incorrectly in the Database Explorer, or not at all.

Warning: only users with a good understanding of their Database's System Table or Catalog should consider using the customization features described below. You must backup config files before altering them. AQT NZ accepts no responsibility for any issues that may arise as a result of changes made to the config files.

Customization

This section covers customizing Advanced Query Tool by modifying the contents of the config files. The config files contain the SQL statements that control the system-table query feature of AQT.

You might wish to do this if you want to:

• change some of the system-table queries that have been delivered with AQT. This could be because you make extensive use of a particular feature of the database that you would like reported in the Database Explorer window. Alternatively you might like to disable a feature you do not wish to use.

• use AQT against a database which AQT hasn’t yet been configured for. This would involve developing a config file for that database. If you wish to do this please contact us – we will be keen to help you do this.

While the config files can be altered, we do not recommend you do this, or at least you should do so only with caution. As a result we don’t provide full tools or documentation for helping you with this. Most of what exists (detailed below) is for use by AQT development and is not intended to be used by yourselves.

• file readme.cfg in the program directory gives comprehensive information about the entries in the config files.

• in general the config files are amended by using a text editor.

• there is an online tool for displaying the config entries. This is reached by Tools > Configure System Queries from within the Database Explorer window. See System Query Config for more information on this. This tool will not be available if you are connected to a database type that does not support system queries, such as MS Access.

Disclaimer:

When AQT reads the config files it does some checking of the contents, but does not check everything. If there is an error in your config file, or some of the entries do not match up, you may notice this only because the system behaves strangely or gives obscure error messages. Using Options > Debug can provide you with more information about what is happening under the covers.

Warning: before you change a config file, make sure you save the existing version of the file. If you create a config file for a new database type you will need to make an entry in the Options > Config Files before it will be picked up.

New versions of AQT overwrite config files

If you reinstall AQT or install a new version of AQT your config files will be overwritten. It is up to you to back them up elsewhere and reapply your changes to the new files.

DB2 - Restricting Schemas displayed by AQT

The Database Explorer window displays all your objects, grouped by Schema name.

With DB2, when you configure an ODBC Datasource, you can specify a setting (SCHEMALIST) that will restrict the list of schemas to those specified.

This setting is used by the inbuilt ODBC functions (eg. SQLTables) that display the schemas and tables. AQT does not use these ODBC functions, instead uses its own queries. As a result, AQT is not aware of and ignores the SCHEMALIST setting.

However it is possible to configure AQT to use the SCHEMALIST setting. To do this:

• edit the cfg file for the database (eg. db2udb.cfg)

• at the bottom, add a schemalist specification. Code one of these per AQT query that needs to be modified. The file readme.cfg has information on how to code this specification.

Customizing the display of particular data types

Sometimes, you need to display particular columns using a certain column function. Examples:

• Oracle Timestamps often need to be displayed with TO_CHAR(timestamp_col). If this is not done, the ODBC Driver can crash (depending on the version of the driver you are using).

• SQL Server 2008 has datatypes HierarchyID, GeometryID and GeographyID. To display these, you need to use the ToString function, eg. geometry_column.ToString.

• User-defined types, in particular object types, may have particular requirements for how they are displayed.

AQT provides a generic mechanism for dealing with columns such as this. This is done by coding a displaycol specification in the cfg file. Example:

• displaycol;timestamp;to_char(:);

• displaycol;geometry;:.ToString();

Note: where : appears, the column name will be substituted.

How this works

By default, when AQT displays a table, it builds a Default Query. In most cases this is:

select * from table

When a displaycol setting is used, the Default Query will be:

select col1, col2, to_char(timestamp_col) as timestamp_col from table

This will display the data using the function specified in the displaycol specifications (to_char in this example).

This default query will be used when you display a table, or go to the Run SQL or Query Builder windows. If you specify select * from table, the displaycol specification will not be used.

Other uses for Displaycol

This is a flexible option that can be used for a number of purposes.

To prevent the display of BLOB columns, code:

displaycol;blob;'BLOB data, not displayed';

To display only the first 1000 bytes of a CLOB column, code:

displaycol;clob;left(:,1000);

Customizing the Explain statement used

The Explain Plan feature of AQT uses a particular command for performing the Explain. For instance:

explain plan set queryno=678 for <sql statement>

The explain statement that AQT uses is hard-coded within AQT. However you can override this with the explain specification in the cfg file.

This is useful for DB2 for z/OS, for which you can specify that Explains are to be done using a stored procedure. In this case, you would code:

explain;{call DSN8.DSN8EXP (?,?,?,?,?,?,?,?)};in,char(8),<schema>;in,integer(4),<queryno>;in,varchar(32700),<sql>;in,char(1),Y;in,char(8),<schema>;out,integer(4),,<rc>;out,char(5);out,varchar(960),,<errtext>;

When this is coded, clicking on Run > Explain from the Run SQL window will invoke DSN8.DSN8EXP to perform the explain.

Note that in this example:

• the semicolon delimited options after the call statement give the parms to be passed to/from the DSN8EXP stored procedure.

• where <queryno> is specified, the unique query number will be substituted

• where <sql> is specified, the sql text will be substituted

• where <schema> is specified, the explain schema will be substituted. The explain schema is given in Options > Explain.

• where <user> is specified, your signed-on user-id will be substituted.

• <errtext> specifies the error text that will be returned to AQT.

Multiple result sets for SAP ASE/MS SQL Server/Teradata

Warning: This is a technical discussion for advanced users only

It is possible for an SQL statement to generate more than one result-set. There are two circumstances where this happens:

• stored procedure. A stored procedure can return multiple result-sets

• code-block. This is discussed in detail below.

AQT can deal with both of these cases, however there is a technical issue with the some databases that you should be aware of if you are using this feature. This problem has been seen for SAP ASE, MS SQL Server and Teradata.

Code-Block

If you wish to run multiple SQL statements you would normally code these as separate statements, delimited by a semicolon. Example:

Update dbo.Products Set UnitPrice=30.0000 where ProductID=7;

SELECT count(*) FROM Products;

AQT will interpret this as two statements, and will run them separately.

SAP ASE and MS SQL Server also allow you to run a block of Transact-SQL code. This can include multiple statements, and will be run in AQT as a single statement.

Example:

declare @CategoryId int

select @CategoryId=2

select count(*) from Products where CategoryId = @CategoryId

select * from Products where CategoryId = @CategoryId

Code Blocks such as this can return multiple result-sets.

How AQT processes code-blocks

In order to deal with statements that return more than one result-set, the logic in AQT for processing queries is as follows:

1. process result-set

2. get next result-set (which is ODBC call SQLMoreResults)

3. if there is another result-set, go to 1), else finish

This works fine on all databases except SAP ASE, MS SQL Server and Teradata.

Problem with SAP ASE, MS SQL Server and Teradata.

When AQT processes a query as described in the previous paragraph, it hits a major performance problem with SAP ASE, MS SQL Server and Teradata.

This problem happens when AQT is accessing a large table. AQT will not read the whole table, but just the number of rows specified in the Display-Limits options. For instance, if you have a million-row table, and the Display-Limit is set to 1000, AQT will read only 1000 rows from the table.

When AQT does the SQLMoreResults, SAP ASE/SQL Server/Teradata will then read all the remaining rows in table. In our example of the million-row table, when the SQLMoreResults is done, SAP ASE/SQL Server/Teradata will read the remaining 999000 rows before moving to the next result-set. This will take a very long time, and cause a high I/O load on your database.

It is unclear whether this is bug in the ODBC Drivers for SAP ASE/SQL Server/Teradata, or whether it is working as "designed".

Circumvention

To prevent this problem from happening, for SAP ASE/SQL Server/Teradata we will not issue the SQLMoreResults unless:

• AQT has read all the rows in the result-set

• a stored procedure is being run

• option Allow multiple result-sets for SAP ASE, SQL Server / Teradata has been specified (see below)

The only impact of this is that if you are running a Transact-SQL code-block in AQT, you may not see all the results-sets that are being generated.

Allow multiple result-sets for SAP ASE, SQL Server and Teradata

This is one of the Technical Parameter options. When this option is selected, AQT will always issue the SQLMoreResults in all circumstances. If you are running SAP ASE/ SQL Server / Teradata code-blocks, you may wish to set this option on.

If you access any large tables with this option set on, you will notice very long response times.

Date and time columns

Date and Time columns are a very complicated part of AQT because:

• people want the flexibility to view them in a particular format.

• most databases are very fussy about how dates are specified in insert/update statements.

• all databases have different date/time field types, and different rules about how they are handled.

Display Format versus Update Format

AQT uses two different formats for date/time columns – the Display Format and the Update Format. The Display Format is the way you wish to see the dates displayed. Whenever AQT is displaying a table, it will display the dates in this format. AQT will use the Update Format when you are specifying a date in an Update/Insert Statement or in a Where clause. This happens in two places:

• when you do a Get Values on a date/time column you will get it in the Update Format

• when you go into Update mode on a table AQT will change the date/time columns from the Display format to the Update format.

You set the Display Format with Options > Display Options > Format of Date/Time Columns. The Update Format is hard-coded within AQT, based on the database type (see the section Date data types below).

Date data types

The following discussion summarises the date / time / timestamp data-types of the various databases. The tables also include the column Literal Format which is how the date / time / timestamp value must be specified to the database.

In these, MM is the month, HH is the 24-hour, mm is the minute.

DB2

Oracle

AQT will set the NLS_DATE_FORMAT to the format you want to display the dates. This allows you to use the same format for displaying dates as for specifying in Where clauses (in other words, the Display Format and Update Format will be the same).

TO_DATE function

When specifying Oracle dates, it is generally best to use the TO_DATE function. This removes any ambiguity about how the date is specified. The TO_DATE function must be used if the time part of a datetime value is included in the literal.

Example: TO_DATE('2010-12-31 15:32:55', 'yyyy-mm-dd hh24:mi:ss')

In many places in AQT, there is an option Use TO_DATE. When is checked, AQT will automatically use the TO_DATE function when specifying the literal

TO_TIMESTAMP

Timestamp literals can be specified in the format as given in the table above. If you wish to specify the timestamp literals using another format, you do this with the TO_TIMESTAMP function.

Example: TO_TIMESTAMP('2010-31-12 15:32:55.123456', 'yyyy-dd-mm hh24:mi:ss.ff')

MS SQL Server

Sybase ASE

Informix

As AQT is unable to detect the values of DBDATE and other environment variables, you must specify in Format of Informix date/time literals the how the date and time literals are to be formatted.

MS Access

When specifying DateTime literals, MS Access requires these to be enclosed in #s (not single-quotes like every other database). AQT will do this automatically.

SQL/MX and SQL/MP

Dates in Parameters

In AQT you can use parameter markers in queries:

Select * from DEMO.EMPLOYEE WHERE HIRE_DATE = ?

When you run this you will be prompted for the value of Hire Date. You must specify a correct Date/Time value for it. This value this must be in the Update format. Functions such as TO_DATE cannot be used in this situation.

With Oracle there is a further complication with queries like these – due to limitations with the Oracle ODBC driver, AQT does not know the “type” of the parameter. You will be shown that the parameter is Char(100) or Varchar2(999) rather than Datetime.

Suppression of Time-part

For datetime or timestamp columns, when the time-part is zero, AQT will not display it. In other words, AQT will display a value of '2001-12-31 00:00:00' as '2001-12-31'. This is useful if you are holding dates (with a zero time) in these columns.

This behaviour is governed by Options > Display Options > Show Time part of timestamp when zero. When this option is selected, you will see the full value of the timestamp column.

Decimal separators

AQT behaves in the following way:

• columns defined as FLOAT or REAL are formatted by AQT. In this case the Windows Decimal Separator will be used to format the number.

• columns defined as DECIMAL or NUMBER are formatted by your ODBC driver, rather than by AQT. Whether these show the correct Decimal Separator is determined by the capabilities of your ODBC driver. Most databases do format the number correctly, however we cannot guarantee this.

• for Oracle, the display of numeric fields is determined by the NLS_NUMERIC_CHARACTERS session variable. You can set this with a statement such as: alter session set NLS_NUMERIC_CHARACTERS=',.'

Numeric literals

To further complicate matters, most databases do not recognise a comma Decimal Separator in numeric literals.

This means: if you are specifying a numeric value in a Where clause, you must specify it with a fullstop, irrespective of what you have set as your Windows Decimal Seperator. The same applies for numeric values coded in Insert and Update statements.

AQT helps you use the correct format for a numeric literals; when you use List Values to display the values of a numeric column, AQT will display it using a fullstop instead of a comma. Similarly, when you go into Update Mode, AQT will automatically change all your numeric values to have a fullstop. These features will ensure that your numeric literals will be specified in the correct format.

Rowcount for SQL Server and SAP ASE

We recommend that you allow AQT to manage the rowcount (see below), as there are a number of considerations for using it from within AQT.

For SAP ASE and SQL Server, you can use the set rowcount n statement to restrict queries to return (a maximum of) n rows. Using this clause can give significant performance benefits when dealing with large tables, however

• When you use set rowcount n, this rowcount will stay in effect for the remainder of your AQT session (for this database) (for SAP ASE, see note below). It will only change when you use another set rowcount statement.

• AQT itself runs a number of queries to populate the Database Explorer. It also runs queries to get column information for your tables in the GUI Query builder and other places. If you have set the rowcount, the rowcount will affect these internal AQT queries also. This can lead to "unexpected results", such as the Database Explorer not showing you all information, and the GUI Query Builder or Run SQL window not showing you all the columns in your tables.

Note for SAP ASE

The set rowcount will only stay in effect for the duration of your session if your ODBC Driver has been configured to use a Select Method of 1-Direct. We recommend using this setting in conjunction the the rowcount option (see next section).

Allow AQT to manage the ROWCOUNT

AQT can automatically set the rowcount for you. To enable this feature, go Options > Technical Parameters then check For SAP ASE and SQL Server, use SET ROWCOUNT. When this feature is enabled, AQT will periodically run a set rowcount n statement, where n is:

• for normal queries, the value specified in Options > Display Limits > Max Rows Displayed

• for internal AQT queries, 0 (which means no limit)

• for Table contents (first 30 rows) (this is a display in the Database Explorer), 30

AQT doesn't set the rowcount every time you run a query; instead it will do that whenever the rows-to-be-displayed are changed. This will happen, for instance, when you go from running queries to using the Database Explorer. If you manually set the rowcount, it will stay into effect for a while, but will at some point be reset by AQT

Get More Rows feature

If the Get More Rows feature is used, the Rowcount will be set to 0 (no limit).

System-table query feature

For most databases, Advanced Query Tool reads the system tables to obtain the information about the objects in the system (the system tables are also called the dictionary or system catalog, depending on the terminology of your database). With such databases AQT can give you comprehensive information about your tables, and can report on objects other than tables (indexes, tablespaces etc).

Types of databases AQT can access lists the databases that this feature is available for.

For other databases, AQT gets its information on system objects by using the ODBC API calls. These generally work fine; however the amount of information available is very limited – all that you will see are tables, and within these you will just see the table column list, primary key and the table access rights for some databases.

Even for databases for which the system-table query feature is available, you can switch off this feature and run in ODBC table-info mode. You might do this if you are getting messages indicating a problem with the system queries. This setting is controlled by Options > Table Info > Get Table Info From.

The system-table query feature is controlled by config files. Database Types and Config Files discusses how this works.

Pervasive.SQL system tables

In order for the System-Table Query feature to work, you will need access to the System Tables. If security has been enabled in your database and you are not the Master user, then you may not have access to these tables. AQT will check for your access to these tables and will only use the System Table Feature if you can read these tables.

User rights

Many organisations find that AQT is too powerful for many of their users. To address this issue, we have made it possible to configure AQT to run a more basic set of features.

We have implemented a number of options:

• the ability to run AQT in read-only mode. With this, AQT will run only queries (SELECT statements) - any other SQL statements will be rejected.

• the ability to restrict AQT access to particular database types

• the ability to prevent access to AQT's Admin Component

These options are set in Options > User Rights.

• To run AQT in read-only mode, select Read Only mode. You can change this option at any time - this could be useful, for instance, if you are accessing a Production database and do not wish to run an update inadvertently.

• To restrict access to particular databases, check Only allow signon to some databases, then select the drivers from the list of drivers.

• To prevent the user from accessing the Admin Component, select Hide the Admin Component.

Changing the options permanently

You can permanently set one of these options by clicking on the Lock this Setting buttons. The option will be permanently set and you will not be able to reset this. Do not click on this button unless you really want to do this!! Even uninstalling and reinstalling AQT will not reset this setting, so do not do this by mistake.

Technical Notes

• These settings are held in the LOCAL_MACHINE part of the Windows Registry. In order to run Lock this Setting you need write access to this part of the Registry.

• These settings will apply to all users of the PC.

• If you are doing a mass deployment of AQT throughout your company, it is possible to automate the setting of these options. Contact us if you require more information on this.

Restricting Objects Viewed

In the future we will allow you to specify the objects that a user can view in the Database Explorer. In the meantime, this result can be achieved by changing the AQT config files so that a more restricted set of objects is displayed. The amended config file can then be deployed with AQT. Advanced Customization contains more on amending the cfg files.

Interface to Document Locator

AQT has an interface to the Document Locator document management system by ColumbiaSoft.

Enabling the Interface to Document Locator

To enable the interface to DL, you need to:

• have the Document Locator client installed and configured on your PC

• set Options > General > Enable interface to Document Locator

• specify other options related to the interface at Options > Document Locator

Selecting Document Locator Files

Once the interface to DL has been specified, whenever you select files or directories you will have the option of:

• selecting a file/directory on your local disk (as per normal)

• selecting a file/directory from the Document Locator repository

You can use a DL directory for your Saved Queries directory, however may not use it for your default directory (which AQT uses for work files).

Whenever AQT needs a file located in the DL repository it will frist export the file from the repository to a directory on your PC. Similarly, whenever AQT writes to a DL file, it will first write to a file on your PC then import the file to the repository. There is some overhead associated with the export and import operations. Consequently it is not recommended that you use the DL repository for work files or other files that are read and written frequently.

Specifying Document Locator Files

You can specify that AQT is to use a DL file by specifying a filename in the following format:

(DL)repository-name\Documents\directory\filename

Examples:

(DL)demo\Documents\query1.sql

(DL)demo\Documents\Export Files\employees.csv

(DL)demo\Documents\Saved Queries\employee_summary.sql

Exporting to a Document Locator files

Some notes on this are given at Exporting to a Document Locator file

History Files

You can use a DL directory for holding History Files. This works as follows

• when an AQT session starts, it will read the existing History File from the DL repository to a local history file

• during the AQT session, AQT will write to the local history file

• when the AQT session closes, AQT will import the local history file to the DL repository

There are three complications to this:

• an AQT session crashes, or is unable to import the local history file to the DL repository

• the user is running multiple AQT sessions, each writing to the same local history file

• AQT runs over a date change when it must switch to another history file

To deal with these:

• AQT crashes. To deal with this - when AQT starts, if it finds an existing local history file, it will not fetch the history file from the repository. Instead it will continue writing to the existing local history file.

• Multiple AQT sessions are an awkward issue. All sessions will write to the same local file without problem, however the first session that finishes will import the file to the DL repository then delete the local file from the disk. The remaining session(s) will create and write-to a new local file. This will only contain the history records from that point on. When this session finishes, this truncated local file will be imported to the repository as a new version of the history file.

  History records will not be lost, as will be present in earlier versions of the history file in the repository, however this could be a confusing situation for the user. As a result it is not recommended to run multiple AQT sessions when using the DL repository for holding history files.

• This isn't supported. AQT will write to the same history file throughout the AQT session and import this to DL when the session ends.

Using Document Locator with work files

It is not recommended that you use Document Locator to hold work files, such as:

• the report and error files using by the Data Loader

• the report file, SQL files and difference file used by Data Compare

The reason for this is as follows:

• when these processes start, the work files are deleted

• the process may or may not write to the work files. This will depend, for instance, on whether any error conditions were hit. At the end of the process, the work files may still not exist.

• the empty files will not be imported into the Repository. The Repository will contain the files when they last were imported as non-empty files. This may have been from some earlier run of the Load or Compare, and different files could be for different runs.

This can result in misleading and confusing information for the user.

Using AQT with a High Contrast theme

AQT makes extensive use of colors for readibility. Many of these colors are non-standard Windows colors, so AQT may not display well when a non-standard Windows Theme is used.

In this case you may wish to select Option > General > Use System Colors. When this option is selected, AQT will use standard Windows colors for many of it's elements. AQT appearance will be slightly different, however it will display with the colors appropriate to the Theme selected.

If AQT detects that you are using a High Contrast theme, it will automatically switch this option on. You can then switch it off if you wish.