Exploring Your Database

Tools

Table of Content

The following tools are started from the Database Explorer menu or toolbar.

Generate Text

The Generate Text tool can be used to generate any text that contains table and/or column names. You specify a “skeleton text” specification – AQT will substitute (and repeat) the table and column names at the places you specify.

This can be used for generating:

lists of table or column names (for importing into reports or spread-sheets)
SQL statements or utility statements involving a list of tables
program source
HTML source for displaying / editing a table

This tool is accessed by Tools > Generate Text from the Database Explorer. The Generate Text tool operates on the contents of the Object Display and Details Display of the Database Explorer, so make sure these are populated.

Generate Text is a very powerful tool. Once you become familiar with how it works, it can save you a large amount of time.

The best way of understanding how this works is by an example. In the text window type Table name is $2 then click on Generate. The following text will be generated:

Table name is Cust_Orders

Table name is Customer_Contact

Table name is Customer_Details

Table name is Order_Details

Table name is Order_Line

Table name is Part_Details

Notice that where you coded $2 the table name has been substituted, and this line has been repeated for every table in the table list.

For other example of the use of this, click on the Examples drop-down list in the Generate Text Window, and select one of the items. These will show you the types of things you can generate with this utility (though they may not necessarily be examples of working code).

The substitutional parameters you can specify are as follows. These relate to the values displayed in the grids in the Database Explorer:

Parameter	Description	Note
$1	Schema name	value in Database Explorer
$2	Table name	value in middle grid
$3	Column name	value in right grid
$4 - $9	Type, Length, Scale etc	other columns in right grid
$-3	Column names with underscores replaced with minus signs	For COBOL users only. This will give you the Cobol host variables names.
$0	Number of table/column names to be generated.
$n	Sequential number of the table/column name
$fn	A function	see the following Functions section for options
<t>	Tab character

Text generation options

There are a couple of options for the way the text is generated. The examples demonstrate the use of this. Alternatively you can try them to see their effect.

Repeat What?

This option is only relevant when your text consists of more than one line. It specifies what to repeat when you code, say, a $3 in a line – just the line containing the $3, or the entire text.

Delimit Generated Names With

Set the delimiter to be used when a parameter is substituted and repeated. Options are:

Options	Result	Notes
newline	each item is placed on a new line
comma plus newline	when using this option you won't get a comma after the last item	This can be useful when generating a comma-delimited set of names. It is preferable to use this rather than putting a comma in your skeleton text
comma	actually comma plus blank
tab		Useful if you are generating a list of names to be pasted into Excel.
none

Functions

You can use the $fn keyword to specify functions to do simple string manipulation and conditional processing. The syntax of this is:

$fn(function, parm1, parm2, parm3, parm4)

The number of parms required depends on the function. Strings do not need to be in quotes.

The following functions have been implemented:

$fn(lcase,x)	converts x to lower case
$fn(ucase,x)	converts x to upper case
$fn(scase,x)	converts x to sentence case
$fn(trim,x)	removes leading and trailing blanks
$fn(repl,x,y,z)	replaces all occurrences of string y in x to z
$fn(min,x,i)	returns the minimum of x and i
$fn(max,x,i)	returns the maximum of x and i
$fn(left,x,i)	returns the left-most i characters of x
$fn(right,x,i)	returns the right-most i characters of x
$fn(mid,x,i,j)	returns a substring from x, starting at i, length j
$fn(instr,x,y,z,w)	returns z if x contains string y, otherwise returns w
$fn(ifblank,x,y,z)	returns y if x is blank, otherwise returns z
$fn(ifnum,x,y,z)	returns y if x is a numeric data type, otherwise returns z
$fn(ifeq,x,y,z,w)	returns z if x = y, otherwise returns w
$fn(decode,x,comp1,val1,comp2,val2,val3)	if x=comp1, returns val1. if comp2=val2, returns val2. Up to 8 sets of value can be compared. If no values matched, returns the final value. Example: $fn(decode,$4,varchar,c,integer,i,datetime,d,other)
$fn(after,x,y,spos,n)	Searches string x for the nth occurrence of string y and returns the text after this occurrence of string y. spos gives the start position for the search. If negative, the search will be done from right to left starting from the end of the string. spos is optional, if not given a value of 1 will be used. n is optional and defaults to 1. If string y is not found anywhere in string x, all of string x is returned. If string y is found, but occurs less than n times, the last position where string y is found will be used. Examples: $fn(after,$2,"_") returns text after the first "_" $fn(after,$2,"_",-1,2) returns text after 2nd "_" from right
$fn(before,x,y,spos,n)	Similar to after, except that the text before the location of the search text is returned
<delim>spec	This allows you to change the delimiter while your script is running. spec can take values <cr> (new line), <b> (blank) <tab> tab. Example: <delim><cr>

Notes:

in these examples, i and j are numeric. If a non-numeric value is passed it will be interpreted as zero.
$ifnum needs some clarification. It doesn't check whether x is numeric, but whether x equals one of: Integer, Smallint, Bigint, Long, Double, Number (eg. a string representing one of the numeric data types). Normally x would be $4.
if one of your parameters (x, y, z etc) contains either a comma or a bracket then AQT will get very confused and probably produce the wrong result. Enclose the parameter in single or double quotes.
if a parameter is omitted, it is assumed to be a null string. For example $fn(repl,$3,_) will remove all _'s from the column name.
functions can be nested - for example $fn(trim,$fn(lcase,$3))
additional functions are easily implemented – please let us know if you want any others.
Click on Examples in the Generate Text window to see examples using these functions.

Save / Retrieve

These options can be used to Save/Retrieve your text generation specification.

Font

This allows you to set the Font in the text box to non-proportional (eg. fixed width) font. This is useful if you want to line up multiple lines (such as when you are generating code for Cobol).

Generate to File

Use this if you want to output the text generation directly to a file. This is useful if your output is large (> 64 kb), as text larger than this cannot be displayed in the result text-box.

Compare Table Definitions

With AQT v7 we introduced the Compare Objects function, which is part of the Extended Edition of AQT. If you are not licensed for the Extended Edition you may still use this simpler, quicker compare.

This window is used to compare the definition of two tables. You activate this window from the Database Explorer by Tools > Simple Compare > Compare Definition to another table.

Alternatively, if you are using the Compare Schemas function, click on Compare Columns (or double-click a table) to open this window.

When you enter this feature from the Database Explorer, you will be asked to select the table you are comparing to. You can choose any table, view, or synonym in any database you are connected to.

The definitions of the tables will be displayed side by side.

You can select whether you are Comparing by Column Number or Column Name. The default is Column Number. Compare by name is useful if the tables have the same columns, but they are in a different order.

Results of table comparison

The status-bar at the bottom of the window will report on whether the tables compare OK or not. If not, your PC will also beep.

Differences between tables

If there are any differences in the column definitions, they will be highlighted in red.

The right-most column (Compare) will also give you a short description of the differences between the columns. If the columns are the same, it will have OK on a green background.

Other compare options

You can select another table to compare to by clicking on the button on the right of the To table name.

If you have gone into this Window from the Compare Schema function, the Prev and Next menu items will be active. Click on these to move through entries in the Compare Schemas result-grid.

Types of comparison

AQT only compares name, type, length, scale and nulls. Other column attributes (such as default value and description) are not compared.

The compare doesn’t just work for tables, but also Views, Synonyms and AS400 Logicals/Physicals. For these, only the definitions of the columns are compared.

It is possible to compare across database types (eg. a table defined in Oracle to a table defined in DB2). However this is generally not very successful because of the numerous differences in data types between different databases. However the side by side of the definitions makes it easy to do an eyeball comparison of the definitions.

Compare table definitions across two systems

At large sites it is common to have the same tables defined either in multiple databases (development, test, production etc) OR in multiple schemas within the same database. Often one needs to know whether these tables have the same definition, or, if not, to find out the differences.

AQT provides a powerful tool for doing this comparison. Note that this just compares the definition of the tables across two databases or schemas. If you want to compare the data between two tables see Comparing Table Data.

There are two functions for comparing table definitions:

Compare to another table. You would use this option for comparing the definition of the currently-selected table to some other table. The other table can be of any name.
Compare all tables in schema. You would use this option if you wished to compare the definition of a large number of tables (for instance all the tables in a schema) to the tables in another schema and/or database. This option requires that the tables have the same names in the two schemas.

To run these functions, from the Database Explorer, click on Tools > Compare, or click on the Compare icon.

These functions are not restricted to tables, but can also compare the column definitions of Views, Synonyms, Aliases and AS400 Logicals/Physicals.

Problem with DB2 z/OS

There is a known problem when comparing DB2 z/OS tables to tables on a non-DB2 z/OS platform. AQT obtains the list of tables to compare by running a query such as select name from systables order by 1. However if your table names contain numbers, DB2 z/OS will return this list of tables in a different order to non-z/OS systems. This is due to the difference in collating orders between mainframe and non-mainframe systems. This will cause the AQT table-compare function to think that the some tables exist on one system but not the other (and vica-versa), when in fact the tables exist on both systems.

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

Use with HP SQL/MX

There are some limitations with using Compare all tables in schema with HP SQL/MX. See Database Notes > HP SQL/MX for more on this.

Simple DDL Generator

DDL stands for Data Definition Language, and consists of the SQL statements that create your Tables and Views (and other objects).

AQT has two DDL generators:

a simple DDL generator (described in this section). This was the DDL generator available in AQT V3/4/5. It generates DDL for table and views, but will not generate DDL for indexes, primary keys or indexes.
a full generator. DDL generation was completly rewritten as part of the Administration component of AQT V6. It generates full DDL of tables, including indexes, primary keys and foreign keys. It will also generate DDL for other objects, such as tablespaces, triggers, functions and stored procedures. This DDL generator is part of the Extended Edition of AQT and is described in Generate DDL.

While the simple DDL generator has been superceded by the full DDL generator, we have retained the simple DDL generator in AQT. This is available to users of the Standard Edition of AQT.

Using the DDL Generator window

The simple DDL generator can be invoked from the Database Explorer window by clicking on Tools > Generate DDL.

Task	How	Notes
select the tables / views you want to generate DDL for	click on the Select column in the grid	Select All selects all objects Select None de-selects them all
select the type of object you wish to generate DDL for	use the Generate DDL for box	if your list of objects contains both tables and views
select whether you want to generate column comments and/or default values	in the Options box	For DB2 z/OS the system cannot generate default values successfully because of the complex way in which these are held in the catalog
as a developer you may be wanting to create a copy of a view on your own database in order to test a program you are developing.	use the Generate views as tables option	Normally AQT will generate the view DDL as in the original Create View statement – this might be a complex query involving multiple tables and views. This view can only be created by first creating all these base tables/views, which means that creating the view becomes a large job. Instead, when you select Generate views as tables, AQT will generate DDL for a table which looks just like the view. Not only can this be easily created, but test data can easily be loaded because you create a table instead of a complex view.
specify whether the columns are defined using the native data types (char, integer etc), or user-defined types (SQL Server and Sybase Enterprise only	Select native or user-defined data types
specify where the generated DDL is to go to	output to (a window or a file)	The window option is useful for quickly seeing what is being generated. The window has a limit of 32K so cannot be used for a large number of tables.
specify whether the schema/creator/owner name is to be included in the Create Table/View	include schema name	You can choose a particular schema name to be used, or none at all (in which case the objects will be created under the ID of the person running the statements). For Create View statements, the schema names of any objects referenced within the view-query are not affected by this parameter.

To generate the DDL, click on the Generate button.

Output of DDL

If output to window was selected you will see the DDL. You can copy this (to the clipboard) for pasting into a file or the Run SQL window.

If output to file was selected the DDL will go to the file. You will have the option of viewing the (first part) of this file.

Open CSV File

This dialog is opened with either:

Database Explorer: Tools > Open CSV file
Data Display Window: File > Open CSV file

This tool allows you to quickly view the contents of a CSV file. CSV files contain structured data - eg. a number of columns separated delimiters. CSV stands for comma-separated variables though the delimiter may not necessarily be a comma.

To use this window:

the data can come from a File or from the Clipboard
specify the file-format options with the options in the top pane
the bottom panes show a sample of your file and (in the grid) the file after it has been split into columns as per your options. As you change your options, the grid will change.
once you have specified the options correctly, the full file with be displayed in a Data Display window.

Options

Use correct data types for grid columns. When this option is selected, AQT will display numeric and dates values in grid columns defined as numeric and date respectively.
If any values are not valid numeric or date values, this will fail and the values will not display. In this case, de-selecting this will cause all values to be displayed as character.
Delimiter can appear multiple successive times. This option is useful if the delimiter appears multiple times but should only be interpreted as a single delimiter.
An example of this is a file which uses a tab delimiter. We have seen some files where multiple tabs are used to line the data into columns. The multiple tabs are intended to only be a single delimiter.

Updating or running SQL against a CSV File

This feature doesn't allow the CSV file to be updated. To do this, and to perform more comprehensive processing and querying of CSV files, use the Microsoft Text ODBC Driver.

Generate encrypted password

This window is invoked from the Database Explorer window (Tools > Generate Encrypted Password).

This feature was introduced in conjunction with Batch Mode operation of AQT, in which you need to specify Connect statements in your batch scripts to sign onto the database you wish to run your script against. If your database requires a user-id and password to be specified, these will need to be coded in the script. This is a security risk - passwords should never be coded in clear text in scripts.

Our solution to this is to use encrypted passwords in your batch scripts. The procedure is to:

use the Generate Encrypted Password window to generate an encrypted password.
use this encrypted password in the your batch scripts. When coding a Connect function, you specify an encrypted password with the epwd parameter.

To use the Generate Encrypted Password window

Type your password into the Password textbox
AQT will encrypt the password and show the result to you in Encrypted Password
AQT will then also decrypt the encrypted password and show this in Decrypt Check. This is used to verify that the encryption and decryption are working correctly. The Decrypt Check should be the same as Password - if it is different there is some problem with the decryption.
Hit the Copy button to copy the Encrypted Password to the windows clipboard. This can then be pasted into an AQT script.

Note that there is a randomizer built into the encrypted password. The same password can produce different encrypted passwords if run more than once (this is to prevent the cracking of password by trial and error).

Note: while AQT uses strong password encryption, we cannot state that it cannot be broken. You should not code encrypted passwords for databases which contain sensitive or critical data.

Earlier Releases of AQT

As of AQT v10.1.1, AQT will use a stronger encryption method than prior releases. Encrypted Passwords generated by AQT v10.1.1 cannot be used in prior releases of AQT.

Encrypted passwords generated by prior releases of AQT can still be used by AQT v10.1.1 and AQT v11.

Who can use this password?

Three identity options are available:

Anyone. When this is selected any AQT user can use this encrypted password.
Anyone using this Machine. When this option is selected, the encrypted password can be used by any user on the machine. The encrypted password cannot be used on another machine.
The encryption key is based on a hash of the machine name and will be invalid if the machine name changes.
Only myself. Only you are able to use the encrypted password. No other user will be able to use it. The encryption key is based on a hash of the user's SID.

Environment Variables

AQT has a window for viewing and updating Environment Variables. This was borne out of frustration with the ridiculously-bad means that Windows supplied for displaying and updating Environment Variables (though this has been improved in Windows 10 and Windows 11).

To show your environment variables, from the Database Explorer window, go to Tools > Environment Variables.

Three Sets of Environment Variables

There are three sets of environment variables:

Machine environment variables - these apply to all users on your machine
User environment variables - these apply to your userid only
Process environment variables. These are the environment variables in use for your current process. These are the combination of the machine and user environment variables.

You can use the options at the top of the window to select which of these to display.

To update a value, edit the value in the text box then click on Update or Create Value
For a multi-item value, enter the values separated on separate lines. Make sure you include the semi-colon add the end of each line - AQT will not add this for you.
To add a new environment variable, change the environment variable name to the new name then click on Update or Create Value
In general you will not be able to update or add Machine environment variables. If you wish to do this, you will need to start AQT with "Run as Administrator".