Scripting & Automation
AQT Scripting
The scripting component of AQT allows you to run a set of SQL statements (and other AQT functions) without any prompting by AQT. This feature is useful for automating a series of queries and other functions.
AQT scripting is available for many AQT functions: these are detailed in Scripting Commands.
Scripting commands can be run from either:
the Run SQL window.
a batch file. This is explained in Unattended Batch Mode.
Scripting syntax
The general syntax of an AQT scripting command is:
--aqt command,parameter=value,parameter=value
Example:
--aqt export,file=output.txt,type=csv,filemode=replace,prompt=no
All AQT scripting command start with -- (two minus signs), which is SQL syntax for a comment. This means that you can run your script using some other script processor (eg. SQLPlus, DB2 CLP etc) and the AQT script command will be ignored.
If your parameter value contains a comma, blank, or some other special character, you should enclose the value in double-quotes. The double-quote is used by the AQT batch processor to delimit values. Example:
--aqt export,file="customer data.txt",type=csv,filemode=replace,prompt=no
Functions and parameters are NOT case-sensitive.
Script Delimiter
By default, the SQL statements in your script are split into individual statements as per Options > Statement Delimiter.
You can specify another delimiter by used with the sdelim option. Eg.
--aqt options,sdelim=@
You would generally place this command at the start of the script
Startup script
You can use the scripting capabilities of AQT to specify some processing that AQT will do when it starts. You would typically use this to connect to the databases you commonly deal with, and set some session parameters (for those databases that have this concept).
This method is useful for automatically running standard queries for non-confident users.
To do this set up an AQT script file (an example startup.sql
is provided with AQT) containing the statements you want to run when AQT starts. The script must contain the statement:
--aqt startup,mode=online
This tells AQT that you are starting AQT in online mode, rather than running a batch script.
The start-up script can contain SQL statements, including queries. A Run SQL window will be automatically opened and these queries will be run; the results will be shown in data display windows.
Setting up the startup script
You activate the startup script by passing the script-name to AQT when it starts. The best way of doing this is to set up a batch file which does this (an example startup.bat
is provided with AQT). Once you have set this up, we suggest you also:
right-click
startup.bat
and select Send To > Desktop. This will place an icon for the startup script on your desktop.right-click the desktop icon and select Properties, then:
in the General tab, give the startup script a suitable name
in the Shortcut tab, set Run to Minimized (this stops you seeing the DOS window popping up when you run this script).
in the Shortcut tab, select change icon then Browse to the AQT executable.This will give the startup script the AQT icon.
You can use this method to have different AQT Desktop icons (or startup scripts), each of which starts AQT with different database connections.
Any errors encountered during the startup-script will be written to file aqt_output.log.
Opening AQT with a Session
Much of the functionality of the Startup Script can also be done by starting AQT with a particular Session.
The valid values for function are as follows:
Export the results of the following SQL statement. In batch mode, this applies to all subsequent SQL statement until another Export statement. | |
Parameters to be used in the following SQL statement. Applies only to the next SQL statement. If you are running a script with many SQL statements, you will need to precede each statement with an Export or Parms statement. | |
Queryinfo | System-generated value that records the positions of tables in the GUI Query Builder. |
Connects you to a database. This is mainly used when running Batch Queries. | |
When running Batch Queries, this specified the database to use for the subsequent SQL statements. You would use this if you have more than one database connection active. | |
Disconnects you from a database. | |
Runs the Compare Tables in Schema function in batch mode. | |
Datacomp | Runs the Data Compare function in batch mode |
Specify some options for the batch session. | |
Allows you to import some SQL from another file. | |
Prompts the user to enter parameter values. | |
Set a parameter to a particular value. | |
Run the Data Loader. | |
Allows you to run statements under transactional control | |
Closes Excel if it is open | |
Applied a layout to the following query. | |
Renames the button of the most recent data display window displayed. |
Examples of parameter usage
AQT provides a number of examples of parameter usage. The following files are deployed with AQT:
Batch_sample.bat
. This is a Windows command file that runs AQT in batch mode. In this file you specify the name of the AQT batch script to run. The batch script will be one of the following files.
Batch_sample.sql
. Script that demonstrates most of the capabilities of AQT batch processing.Batch_copy.sql
. Script that shows how you can copy data from one database to another.Batch_parms.sql
. Script that demonstrates the prompting for parameters.Batch_proc.sql
. Script that demonstrates the running of stored procedures.
In addition:
startup.bat
and startup.sql
demonstrate specifying a startup script, which runs when AQT is started.
Connect function
This function is used to sign on to a database. It is mainly used when running Batch Queries – you must Connect to a database before any queries can be run. However this function can also be used when you are in “normal” (interactive) AQT – you can use this to sign onto a number of databases without going through the normal signon dialogs.
The Connect
function takes the following parameters. Most of these correspond to parameters specified on the Signon window.
Parameter | Description | Notes |
---|---|---|
| The name of the database you wish to connect to. | This is the same as the database name you would use in the Signon window. If you have specified Conn, the Dbs parameter has a slightly different meaning. It will be used as (an arbitrary) name you give this database (you may need to refer to this database if you code a use function). |
| User-id to sign on with. | If not specified, you will be signed on without a user-id or password. |
| Password to sign on with. | This password is specified in clear text. This is a security exposure - it is preferable to use the epwd parameter instead. If user is specified, and pwd is not specified, you will be signed on with a user-id only. This would be the equivalent of checking the No Password option in the Signon window. |
| Encrypted Password. | The encrypted password. It is recommended that this is used rather than pwd. Encrypted passwords are discussed later in this topic. |
| The name of the SQL Server / Sybase / Informix database to sign onto. | This is the same as option Database Name in the signon window. |
| Specifies the level of prompting you wish for the signon | Values are:
This parameter is ignored if you are running AQT in batch mode. |
| Specifies the name of the Excel Worksheet to be written to | This is used for Excel only. If you code This parameter is optional – if not coded, a new Worksheet is always created (and |
|
| Used only for Excel, and only if AQT supports appending data to worksheets. Use |
| Specifies whether you are signing on with sysoper or sysdba privileges. | Only used when signing onto Oracle. Requires Oracle ODBC Driver v9.2 or above. |
BatchOnly |
| When Yes, the Connect is ignored when AQT is running in interactive mode. This is useful when you have a script which you run in both batch and interactive mode - and you don't want the Connects to run when in interactive mode. |
| Specifies a full connection string | Use the conn parameter to specify a full connection string. You may use this to connect without having to create an ODBC Datasource. They also allow you to specify a full set of connection parameters. Connection strings are different for every ODBC Driver. A reference to them is given at https://www.connectionstrings.com/ AQT has an extension to to connection strings that allow you specify passwords in an encrypted format, rather than clear text. If this parameter is specified, no other parameter is needed except Dbs. |
Parameters marked * are mandatory
Examples:
--aqt connect,dbs=sample
--aqt connect,dbs=ora_sample,user=system,epwd=JkAlDkEEkGg,connectas=sysdba,batchonly=yes
--aqt connect,conn=”Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\aqtdemo.mdb”,dbs=Access
Prompting for the Userid and Password
You can can prompt the user to enter the userid and password by using the queryparm statement. An example of this is:
--aqt queryparm,parm=uid,desc=Userid,parm=pwd,desc=Password,pwd=y
--aqt connect,dbs=dbs-name,user=$uid,pwd=$pwd
Using Encrypted Passwords
To avoid specifying passwords in clear text, AQT allows you to use encrypted passwords.
in the
--aqt connectstatement, rather than specify a password with the
pwdparameter, specify the encrypted password with
epwdwhen you use a connection string, passwords are specified with either pwd or password parameters, depending on the syntax supported by the ODBC Driver. To use an encrypted password, replace these parms with either epwd or epassword respectively. Example:
Driver={IBM DB2 ODBC DRIVER};HOSTNAME=SERV01;PORT=50000;DATABASE=SAMPLE;PROTOCOL=TCPIP;UID=scott;EPWD=^PuLvprDmYeEEzU5AwaU=KJ;
Encrypted passwords are a feature of AQT only. Connection strings containing encrypted passwords cannot be used in other product.
Encrypted passwords can be generated using the Generate Encrypted password tool.
Testing your --aqt connect statement
You can test your connect statement by running it in an SQL window. If successful, you will be connected to the database and another Database Explorer window will be opened.
Use Function
This function is only used when you are running AQT in batch mode. It cannot be used in interactive AQT. It specifies the name of the database to use for subsequent SQL statements. This is only necessary if you are connected to more than one database.
The Use
function takes one parameter:
Dbs - the name of the database to use for subsequent SQL statements. This must be a database you have connected to using a Connect function
Example
This batch script connects to two databases and runs queries against both:
--aqt connect,user=ora_sample,user=system,epwd=JkAlDkElDkBaBkHjHjEkEkGg
--aqt connect,user=sample
--aqt output,file=output.txt,type=csv,filemode=replace
select * from db2admin.employee;
--aqt use,dbs=ora_sample,batchonly=yes
--aqt output,file=output2.txt,type=csv,filemode=replace
select * from scott.emp;
Disconnect function
This function is used to disconnect from a database. It should only be used when running Batch Scripts.
The Disconnect
function takes two parameters:
Dbs - the name of the database to disconnect from. This must be a database you have connected to using a Connect function
BatchOnly - Optional. When Yes, the Disconnect is ignored when AQT is running in interactive mode
Example:
--aqt disconnect,dbs=aqtdemo
If you are using transactions in your batch script, AQT will do a Commit before disconnecting.
In the normal course of events you do not need to do this. Prior to terminating the batch script, AQT will disconnect from all databases. You would only use Disconnect if you wished to manually control your database connections.
Tran function
The Tran function allows you to implement transaction control in batch.
--aqt tran,mode=on | Starts a transaction |
--aqt tran,mode=off | Stops a transaction. Prior to doing this it is recommended that you do a commit or a rollback |
commit; | Does a commit. Note than this is not an AQT scripting function but a normal SQL statement. |
rollback | Does a rollback. Note than this is not an AQT scripting function but a normal SQL statement. |
Example:
Parms Function
The Parms function specifies substitutional parameters to be used in the subsequent SQL statement.
This function is used internally by AQT to load LOB values. Within the Update/Insert Row window, if you specify Load from File for a LOB, you can click on Preview SQL to see how this is used.
If you wish to run a stored procedure in batch mode, you should do this by coding a Parms statement for each procedure parameter. An example of this is given below.
The Parms function takes the following parameters:
Parmno. The parameter number
Parmname. This is an optional parameter which you can use to identify the parameter. It is ignored by AQT.
Value. The value of a parameter. Values do not need to be enclosed in quotes, even if they are string values. However if the string value contains a comma you should enclose it in double-quotes (the double-quote is used by AQT to delimit a batch parameter value). Value must be specified for In and Inout parameters.
The value can be set to the result of a previous query, to an environment variable or to the current date/time. Full details of how to specify these are given in Setparm.
Ptype. The parameter type. This is only used when running Stored Procedures. Ptype can take values In, Out and Inout. The default is In.
Dtype. The data type of the parameter. This is only needed when running Stored Procedures, and only for some databases.
Multiple parameter / value pairs can be specified on a Parms statement.
Examples:
Basic |
|
Two parameters |
|
Inserting a Blob |
|
Running a Stored Procedure |
|
You can generate the SQL (and control statements) for running a stored procedure by clicking on the Copy to SQL Window button from the Run Procedure window.
You can also use queryparm and setparm to specify parameters which are used through the entire script. These functions can do much of what the Parms function does, however you must use Parms if you are doing a BLOB Insert or running a stored procedure.
Dtype
This is the data type of the parameter. This is discussed in more detail in Running a Stored Procedure. It is recommended that you do not code this directly yourself, but generate this from the Copy to SQL Window button of the Run Procedure window.
if the data type contains a comma, you should enclose the value in double-quotes
the length should always be included, even for data types that do not normally have a length specified. Example: INTEGER(4).
when you use Copy to SQL Window, you can sometimes see a different datatype being specified. This happens if the data type is one that is not registered to the ODBC interface - AQT will use a character data type instead.
Setting parms to values returned by queries and stored procedures
You can set a parameter to a value returned by a query, or to an OUT parameter from a stored procedure. This is discussed in more detail in the section on setparm.
Export Function
The Export
function specifies that the SQL statements which follows are to have their output exported to a file.
The Export
function takes the following parameters, most of which correspond to parameters specified on the Export Data window.
It is recommended that you generate an Export function by (on the Export Data window) clicking on File > View Export options.
Note in earlier releases of AQT, the Export function was called Output. Either Export or Output can be used.
Parameter | Description | Notes |
File* | The name of the output file | It is recommended that a fully qualified name is used. If the name is not fully qualified, the directory used will be
For Excel, it is strongly recommended that a fully-qualified file name is used. If not, Excel may place the file in a default location that is not “known” to AQT. If you refer to this file a second time, AQT will not find it and will create a new one. You can include the keywords <schema> or <table> in your file name. These will be replaced with the schema and name of the table you are exporting. |
Type* | The type / format of the output file | Valid values are: fixed, csv, tab, html, insert, excel, delim, xml. Delim is used if you have specified an "other delimiter". In this case the delimiter is specified with a delim clause. |
Delim* | The character used to delimit values. | This is only used if type=delim is specified. If your delimiter character is a single-quote, double-quote, comma or semicolon, use the keywords squote, dquote, comma, semi respectively. If type=delim is specified and delim is not specified, a comma will be used. |
Filemode |
| By default, AQT will not overwrite an existing file. If your file already exists, you must specify a Filemode of either Append (add the data to the existing file) or Replace (replace the output file). Specify New for AQT to always create a new file. |
Prompt |
| Specifies (Yes or No) whether you want to be prompted with the message "Do you wish to view a sample of the file?”. You are prompted for this at the end of the script (after all the data has been written) so only works on the last file written, plus the last Excel file written. The default is No. Do not code this as Yes if you are running AQT in unattended batch mode. |
Header |
| Specifies (Yes or No) whether the output file is to include a header line with the column names Defaults to values last specified for it. |
Maxrows | The maximum number of rows to output | This cannot be specified if you are running the evaluation version of AQT. Defaults to the value last specified for it. |
Nulls | How nulls are to be displayed. | Options are: Null, Blank, (null) Defaults to Null (for Type of insert or xml), Blank (for Type of html or Excel), otherwise (null). If specifying this parameter, you should code it after you have coded the Type. |
Sdelim | The character used to enclose string values. | If your delimiter character is a single-quote, double-quote, comma or semicolon, use the keywords squote, dquote, comma, semi respectively. Specify sdelim=none for no string delimiter. |
remdel | Whether to remove the delimiter character from strings | When this is specified, the sdelim character will be removed from string values. This can prevent a problem should this value appear in the text. By default this option is set. Code remdel=no to switch off. |
remlf | Whether to remove linefeed characters from strings | When this is specified, linefeeds will be removed from string values. Linefeeds can cause problems when appearing in a delimited file. By default this option is set. Code remlf=no to switch off. |
Insert_Tab | Specifies the Table Name to be used in the Insert statements. | This option is only used when Type is Insert. You can include the keywords <schema> or <table> in your insert table name. These will be replaced with the schema and name of the table you are exporting. |
Sheet | Specifies the name of the Excel Worksheet to be written to | This is used for Excel only. If you code Sheet it is recommended that you also code Filemode, which specifies what to do if the Worksheet already exists. This parameter is optional – if not coded a new Worksheet is always created (and Filemode is ignored). AQT supports appending data to worksheets (to do this, set Filemode=append). You can include the keywords <schema> or <table> in your Sheet name. These will be replaced with the schema and name of the table you are exporting. |
Stext | Specifies text that is to be written before the query results. | Within this text you can code keywords <cr> (newline, this allows you to specify multi-line text) or <blank> (blank character, which would not normally be accepted as a valid parameter). Example: stext=Customer Report<cr>Run on 23Feb2003. You cannot have a comma in this text. |
Etext | Specifies text that is to be written after the query results. |
|
Startrow, Startcol | Specify the position in the Worksheet where AQT will start to write the results. | If not specified: startcol is 1 and startrow is 1 (for Replace mode) or the row past the current data (for Append mode). |
genctl | Whether to generate a control file | Specify Yes or No. This option is only relevant for Fixed File format. |
ctlfile | Name of control file | This option is only relevant for Fixed File format and when genctl is yes. |
incblank | Specifies whether a blank is to be written between columns in the output file | Specify Yes or No. This option is only relevant for Fixed File format. |
userdate | Formats date columns as specified in Options > Display Format | If this is not specified, date columns will be formatted in yyyy-mm-dd format, which is the format most databases require for data being loaded. If this is specified as Y, date columns will be formated as per Options > Display Format > Date Format |
todate | Whether the TODATE function is added to date values. | Specify Yes or No. Oracle only. This option is only used when Type is Insert. |
maxlen | Maximum length of a line in the output file. | This option is only used when Type is Insert. |
isrtcols | Whether a column list is to be included in the generated insert statements | This option is only used when Type is Insert. The column names are given in colnames. If colnames is not given, the column names are taken from the table/query being exported. |
colnames | The column names to be used in the generated insert statements. | Must be comma delimited and enclosed in brackets. Example: (columna, columnb, columnc). This option is only used when Type is Insert. |
insert_delim | The delimiter used to delimit the Insert statements. | The default is the semicolon; other options are go or / This option is only used when Type is Insert. |
uniwrite | Whether the output file is to be in Unicode format. | Specify Yes or No. By default, the file will be written in Unicode format if Unicode is enabled. This option overrrides this setting. This option is only used when Type is Insert. |
batchonly | When set to YES, the export statement will only be active in batch mode. | This is useful if you run scripts both online and in batch. When this is specified, the export statement is ignored in online AQT; results from queries will go to display grids. |
xlpwd | The worksheet password for exporting to Excel | You can specify this as either a clear-text password, or as an encrypted password (see Generate Encrypted Password). If using a clear-text password, this cannot start with either *, #, @ or $ (AQT uses these to recognize encrypted passwords). |
batchonly | When set to YES the --aqt export statement will be ignored when run in interactive AQT. | This is useful when scripts are run in both batch and interactive AQT. When run in batch, query results will go to the specified export file. When run in interactive AQT, the --export statement will be ignored, so the query results will go to a Data Display window. |
xlnative | Specifies whether the Native export to Excel is used |
|
xlfiletype | Specifies the Excel File Format | Only used when xlnative=yes is specified. Specify one of: 2003, 2007, 2010 |
replacex00 | Replace x'00' characters with spaces |
|
Parameters marked * are mandatory
Examples:
--aqt export,file=customer_details.csv,type=csv,filemode=replace,prompt=no
select * from Customer_Details;
--aqt export,file=output.xls,type=excel,nulls=blank,sheet=Customer_Contact,filemode=replace
Select * from Customer_Contact;
--aqt export,file=order_details.csv,type=csv,filemode=replace,prompt=no
Select * from Order_Details;
Note on Filemode=New
When this is specified, AQT will can write to a file different to the one specified in File. AQT will add a sequence number to the end of file name so that it is a unique file.
One must be aware of this if you use the file in a subsequent step. For instance, the following would be very dangerous:
--aqt export,file="C:\AQT\customer_details.sql",type=insert,filemode=new
Select * from Customer_Details;
--aqt include,file="C:\AQT\customer_details.sql"
The export may write to a file other than "C:\AQT\customer_details.sql". This means that the include statement may not pick up the file that was created during the export.
Multiple Select statements
If you have multiple select statements you only need to code one Export statement. The specified options will apply at all subsequent select statements; except that Filemode
will be changed to Append
after the first statement has been processed. The result will be that the data from all the select statements will be appended to the same file.
If you have multiple statements, and are writing to Excel, then a slightly different rule applies. If you code a Worksheet name, all the data will be written to this Worksheet. If you do not specify a Worksheet, every set of data will be written to a different Worksheet.
Generating an Export statement
In the Export Data window, the option Tools >Generate Batch Control Stmt (or Ctrl+G) will generate an Export statement according to the options specified on the window.
Exporting Multiple Tables
If you wish to export data from multiple tables (for instance, all tables in a schema), you can use the Generate Text feature of AQT to generate multiple --aqt export statements.
For instance, to export all the tables in a schema to separate files:
In the Database Explorer, make sure you are displaying the list of tables you wish to export. You can apply a filter to limit the list to the tables you are interested in.
Click on Tools > Generate Text.
Enter the following into the text-box:
--aqt export,file=$2.csv,type=csv,filemode=replace,prompt=no
select * from $1.$2;
Make sure you have the Repeat What? option to Entire text
Click on Generate.
Copy the generated text to the Run SQL window, or include it in a batch script
Click on Run.
Note that the same result can be achieved by have a single Export statement:
--aqt export,file=<table>.csv,type=csv,filemode=replace,prompt=no
followed by a number of select statements.
Schema Compare
This runs the Compare Schemas function.
The parameters required by this function are similar to the options on the Compare Schemas Options tab.
The Compare Schemas window has a function Tools > Genenerate Batch Control Statement which will generate this script control statement for you, based on the options specified. It is recommended that this is used, rather then coding the Compschema function from scratch.
When this is function is run, a tab-delimited report is sent to the file specified by the parameter File
.
Parameter | Description | Note |
| The schema you are comparing. |
|
| Schema you are comparing to. |
|
| The name of the database containing Schema1 | You must already have signed onto this database. |
| The name of the database containing Schema2. |
|
| The type of object you are comparing. | This must correspond to the For many databases this can only be one value: For some other databases (such as Oracle), this can be one of several values such as: This parameter is optional. If not given, or the name supplied is not a valid object-name, the first object in the Database Explorer will be used. |
| The type of object you are comparing to. | If not given, the same as Object1. |
| A filter string for comparing a subset of the tables in the Schema. |
|
|
| You would use this if the Schema you are comparing to is in a different Sybase/SQL Server database to one signed onto. |
| The compare method. | Values:
|
| When the compare method is FULL, this specifies how the columns in the tables will be compared. | Options are
|
| Name of the file the Compare Schema report is written to. |
|
| Whether the report is to | Default is |
| Specifies whether the report is to include ALL tables ( | Default is |
Parameters marked * are mandatory
Example:
--aqt compschema,dbs1=ora_tst,object1=Tables,schema1=DEMO,dbs2=ora_prod,
object2=Tables,schema2=DEMO,method=fast,file=compschema.txt,filemode=replace
Include
This option allows you to run an SQL script that is in another file. This statement cannot be used when running AQT control statements in the interactive mode of AQT.
File. The name of the file containing the SQL script.
Warning: AQT does not have any controls to prevent a recursive specification of Include statements.
Example 1:
--aqt include,file=create_table1.sql
--aqt include,file=create_table2.sql
--aqt include,file=create_table3.sql,notfounderror=n
Example 2:
This feature can be used to copy data from one database to another. The following example copies data from Table1 in Database1 to Table2 in Database2.
--aqt connect,dbs=database1
--aqt options,stoponerror=y
--aqt output,file=C:\AQT\batch_insert.sql,type=insert,filemode=replace,insert_tab=table2
select * from table1;
--aqt connect,dbs=database2
delete from table2;
--aqt include,file=C:\AQT\batch_insert.sql
Note on Filemode=New
When this is specified in an Export, AQT will can write to a file different to the one specified in File. AQT will add a sequence number to the end of file name so that it is a unique file.
One must be aware of this if you use the file in a subsequent include step. For instance, the following would be very dangerous:
--aqt export,file="C:\AQT\customer_details.sql",type=insert,filemode=new
Select * from Customer_Details;
--aqt include,file="C:\AQT\customer_details.sql"
The export may write to a file other than "C:\AQT\customer_details.sql". This means that the include statement may not pick up the file that was created during the export.
notfounderror
This specifies whether or not AQT is to return an error condition if the file is not found.
By default, an error condition will be returned. If you have specified:
--aqt options,stoponerror=y
then the script will cease processing.
Queryparm
This function prompts the user for the values of User-Defined Parameters. The following parameters are available:
Parm
The name of the parameter.
Desc
The display-name for the parameter. When the user is queried for the parameter-value, Desc is used to describe the parameter. Desc is optional and, if not specified, the same value as Parm will be used.
Pwd
If the parameter is non-display field, code
pwd=y
. When the parameter is entered by the user it is displayed as asterisks. You would use this option when prompting for a password to be entered.The Pwd parameter is not supported when a dropdown list is used (see the next section).
Select
This allows you to specify either:
a date-picker, for easy selection of a date / time or timestamp value
a dropdown list of values to user is able to select from
a file for the user to select
The Select parameter is discussed later.
Freetext
When a dropdown box is displayed, whether the user can enter text into the textbox.
When False, the user can only select items from the dropdown list.
The default is True.
Value
The value the parameter will be set to. If not specified, it will take the existing value of the parameter.
The values you can code here are the same as for Setparm. In addition, if you have specified a dropdown list, you can specify a value of <n>. The parameter will be set to the nth value from the dropdown list.
IDParm
The name of a hidden parameter. Descripted later.
FileDir
When the user is being prompted for a file, this gives the start directory for the file dialog.
FileType
When the user is being prompted for a file, this gives the file types of the file being prompted for.
FileType is specified as a series of entries seperated by a pipe (eg. vertical bar). The format is: description | filemask | description | file mask.
Examples:
All files (*.*)|*.*
csv files|*.csv|Text Files|*.txt
customer exports|cust*.csv
Once you define parameters (with either queryparm
or setparm
), AQT will scan all subsequent statements for reference to the parameters. Parameters are referred to by preceding the parameter name with a $
.
Example 1:
--aqt queryparm,parm=sdate,desc=Start Date
select * from order_details where order_date > '$sdate'
Example 2:
--aqt queryparm,parm=user-id,desc=user-id,parm=pwd,desc=Password,pwd=y
--aqt connect,dbs=sample,user=$user-id,pwd=$pwd
See the file batch_parms.sql
, which is
deployed with AQT, for more examples of the use of parameters.
Restrictions
parameters are case sensitive. You must refer to the parameters with the same case as you define them (in the above example it will find $user-id but not $user-id) .
up to 10 parameters can be specified.
parameter replacement is fairly dumb. If you have parameters cust (=SMITH) and custcode (=JONES); coding $custcode will give you SMITHcode rather than JONES.
queryparm requires screen input from the user. It should not be used for unattended operation.
Select
This allows you to specify:
a date picker field where the user can select a date / time or timestamp value.
a dropdown list of values; the user can select one of these values.
To specify a date picker, code one of:
select=date
select=time
select=timestamp
To prompt the user for a file with the File Open or File Save dialog boxes, specify either:
select=fileopen
select=filesave
To specify a drop down list, specify either:
select=(
comma-separated list of values)
The values should be enclosed in double-quotes if they contain any special characters such as commas, spaces or brackets.
select=(
select statement)
The select statement is run to populate the drop-down list.
Examples:
--aqt queryparm,parm=sdate,desc="Start Date",select=date,value=<date>
--aqt queryparm,parm=type,desc=Type,select=(T,V,S),value=T
--aqt queryparm,parm=order,desc="Order Number",select=(select distinct order_number from order_details),value=<1>
--aqt queryparm,parm=loadfile,desc="Enter file to load",select=fileopen,filedir="s:\AQT\loadfiles",filetype="Load files|*.csv"
Having a Dropdown list depending on the value of a previous parameter
When your Select statement contains a query, that query can reference a previous parameter.
Example:
--aqt queryparm,parm=Country,desc=Country,select=(Select distinct Country from dbo.DimGeography order by 1)
--aqt queryparm,parm=City,select=(Select distinct City from dbo.DimGeography where Country = '$country' order by 1)
Note that in second parm has $country in the query which populates the dropdown list. $country will be set to the value the user has selected for the Country parm. If a different value of Country is select, the query is rerun and the second dropdown list repopulated.
IDParm
Use this when you want the user to be prompted with one set of values, but pass another ("hidden") value to the query.
For instance, a user may be familiar with the names of their customers, but not the customer ids. In which case you could use:
--aqt queryparm,parm=custname,desc="Customer",idparm=custid,select=(select distinct customer_name, customer_id from customers)
select * from customers where customer_id ='$custid'
Here the select query returns two columns:
the first column (customer_name) is the set of values the user will be shown
the second column (customer_id ) is a "hidden" value
When the user selects a value from the dropdown list:
the main parameter (custname) is set to the value the user selected
the idparm parameter (custno) is set to the value of the hidden value
If your select clause is a list of values, you use a colon to delimit the main and hidden values:
--aqt queryparm,parm=type,desc=Type,idparm=typeid,select=(Table:T,View:V,Synonym:S)
Using idparm
The idparm parameter of queryparm allows you to set a parameter to a hidden value.
This is best explained with an example:
--aqt queryparm,parm=custname,idparm=custno,select=(select customer_name, customer_number from customers)
select * from orders where customer_number=$custno
The orders table needs to be queried by customer_number. However the user may not be familiar with customer numbers but only knows customers by their customer name. We wish to prompt the user by customer namer, but pass customer number to the query.
To acheive this, the select clause in queryparm returns two columns:
the first column (customer_name) is the set of values the user will be shown
the second column (customer_number) is a "hidden" value
When the user selects a value from the dropdown list:
the main parameter (custname) is set to the value the user selected
the idparm parameter (custno) is set to the value of the hidden value
Specifying a list of values
If your select clause is a list of values, you use a colon to delimit the main and hidden values:
--aqt queryparm,parm=typename,desc=Type,idparm=typeid,select=(Table:T,View:V,Synonym:S)
Note
This feature only works if you select values from the dropdown list. If you type a value, AQT will not be able to set the idparm value and it will retain its existing value.
SetParm
This function names a User-Defined Parameter and sets a value for it.
Parm
. The name of the parameter.Value
The value of the parameter.
Example:
--aqt setparm,parm=schema,value=SYSCAT
--aqt output,file=batch_sample.csv,type=csv,filemode=append
select * from syscat.tables where tabschema ='$schema';
Setting a parameter to a result of a query
You can set the parm to a value returned by a query. Example:
--aqt setparm,parm=yesterday,value=(select current date - 1 day from sysibm.sysdummy1)
If the query returns multiple rows/columns, only the first column of the first row will be used.
Setting a parameter to a value returned by a prior query or stored procedure
You can set a parameter to a value returned by a query, or to an OUT parameter from a stored procedure. This can be useful if you are passing data between a series of SQL statements.
Coding value=$result(3) will set the parm to the 3rd column of the last query run. If the query returned more than one row, the value will come from the last row. If there was no previous query, or the query returned zero rows, the results can be unpredictable.
Coding value=$parm(2) will set the parm to 2nd query parameter (as specified with a Parm function). You would typically use this when the Parm function has specified an Out or Inout parameter, and you have run a stored procedure.
You can embed these functions inside other characters. Example: value=EMP$result(1)00
will return EMPSMITH00 if $result(1) is SMITH.
Setting a parameter to a list of values from an external source
You can set a parameter to a list of values contained in a file (either a csv file or Excel file) or a from a query.
Thiis is described in Scripting - SetParm - $list
Setting a parameter to an Environment Variable
You can set a parameter to an environment variable by coding:
value=$env(CUSTCODE)
In this example, CUSTCODE is an environment variable. When running batch-mode AQT, you can set this in the batch file that invokes AQT.
Setting a parameter to a System value
You can set a parameter Value to one of the following system values.
<date> | The current date |
<date(dformat)> | The current date in the format specified by dformat. Has been superseded by <date:format(dformat)> |
<time> | The current time in hh:mm:ss format |
<timestamp> | The current timestamp in yyyy-mm-dd hh:mm:ss format |
<scriptname> | This is only used when running AQT in batch. It gives the name of the script that is being run. For instance if your *.bat file has: |
<scriptpath> | Returns the script directory. In the above example, <scriptpath> wiill return |
Additional options for <date>
You can apply a number of functions to <date> to generate a related date. These are specified as:
<date:function1:function2:function3>
There is no limit on how many functions can be used.
The functions that can be specified are as follows:
startof(datetype) | Start of the date period. datetype is one of h (hour), d (day), w (week), m (month) or y (year). The start of a week is the Sunday. Example: startof(m) will be the first day of the month. |
endof(datetype) | End of the date period. dateperiod is as above. The end of a week is Saturday. Example: endof(m) will be the last day of the month. |
add(datetype,number) | Adds to the date.
Example: add(m,1) adds a month to the date |
sub(datetype,number) | Similar to add, however the number is subtracted. |
format(dformat) | Specifies how the date is to be formatted. If this is not specified yyyy-MM-dd will be used. Note that MM is month, mm is minute. For hour, use HH (24-hour) rather than hh (12-hour) Examples: <date> gives 2021-08-30 <date:format(MMddyyyy)> gives 08302021 <date:format(HHmmss)> gives 204515. <date:format(yyyyMMddHHmmss)> gives 20210830204515 |
from(datestring) | Converts the datestring to a date. |
from(datestring,format) | Converts a datestring to a date, specifying the format of the datestring. |
Example 1
<date>
This returns the current date in yyyy-MM-dd format (2021-10-21)
Example 2
<date:startof(y):add(m,3):sub(d,1):format(yyyyMMdd)>
This:
gets the current date (2021-10-21)
goes to the start of the year (2021-01-01)
adds 3 months (2021-04-01)
subtracts one day (2021-03-31)
formats the date as specified (20210331)
Example 3
This shows the use of two connected parms, plus the use of the from function.
--aqt queryparm,parm=sdate,desc=Start Date,select=date
--aqt setparm,parm=edate,value=<date:from($sdate,yyyy-MM-dd):endof(m)>
select * from sales where sale_date between '$sdate' and '$edate'
In this:
queryparm prompts the user for the date, and sets this in parameter sdate
setparm will set parameter edate to the end of the month given by sdate
Date and Time
<date> includes both the current date and time.
startof will return the date with zero hour/minute/second. The exception is startof(h) which will be the current hour with zero minute/second.
endof will return the date with time 1 second before midnight (23:59:59). end(h) will be the last second of the current hour.
Example: endof(m) returns 2021-10-31 23:59:59
To have endof return the date with zero time, follow it with a startof(d).
Example: endof(m):startof(d) returns 2021-10-31 00:00:00
add and sub return the date with the current time.
Example: if the date is currently 2021-10-21 09:44:34 then <date:add(m,1)> returns 2021-11-21 09:44:34
Use the format function to specify whether you want the date / time or timestamp value returned.
<date:format(HH:mm:ss)> does the same as <time>
Setting a Parameter to an AQT Option
You can set the parameter to an AQT option with:
$option(optionname)
Any AQT option can be used, however the most useful ones are likely to be exportdir (the default export directory) and querydir (the query directory). Examples:
$option(exportdir)
$option(querydir)
Using $list
n,Walker,
The $list function of SetParm allows you to set the parameter to a list of values. The list of values can be read from either:
a delimited text file
an Excel file
a database
You can then use this parameter in an IN statement. Example:
--aqt setparm,parm=custlist,value=$list(xlfile="C:\AQT\Data\testdata.xlsx",sheet=customers,startrow=2)
select * from customers where custcode in ($custlist)
The format of the $list function is:
$list(parm=value,parm=value,parm=value)
Where the following parms can be specified:
Parm | Description |
sql | The query to be run to get the list of values |
db | The database the query is to be run against. You need to be signed onto this database. This is optional - if not specified it is the current database. |
file | The name of the file to read to get the list of values. This must be a delimited file |
filedelim | This is the delimiter used to delim variables in the file. Optional - if not given will default to comma. |
xlfile | The name of the Excel file to read to get the list of values. |
sheet | The name of the Excel sheet name |
colno | The column number in the query/file/spreadsheet of the data for this list. Optional - if not given will default to 1. |
quote | Whether (y or n) the values in the list will be enclosed in quotes. You would specify this as y if your list of character values, n if it of numeric values. Optional - if not given will default to y. |
delim | Delimiter used to delimit values in the list. Generally will be a comma. Optional - if nopt give will default to comma. |
startrow | The row in the query/file/worksheet to start to get the values from. If your file / worksheet has a header row, you should specify startrow of 2. Optional - if not given will default to 1. |
maxrows | The maximum number of values to get for the list. Optional - if not specified will default to 0 (no limit). |
vals | Supplies a list of value. The values are delimited with the delimiter specified with valdelim. |
valdelim | The delimiter used with the vals list. |
Parameter values should be enclosed in double-quotes if they contain any special characters such as comma or brackets.
One of sql, file or xlfile must be specified.
Delim
Both delim and filedelim can take the following values:
comma | , | comma |
tab |
| tab character |
squote | ' | single quote |
dquote | " | double quote |
lbrak | ( | left bracket |
rbrak | ) | right bracket |
dot | . | fullstop / period |
semi | ; | semicolon |
colon | : | colon |
Examples
The following statement gets a list of values from a query.
By coding the db parameter this query can run against another database. This provides a way of combining data from multiple databases.
--aqt setparm,parm=custlist,value=$list(sql="select cust_code from order_details",db=testdb)
select * from Customer_Details where cust_code in ($custlist)
This example gets the data from a csv file:
$list(file="C:\AQT\Data\customer_details.csv",filedelim=comma,colno=2,startrow=2,quote=y,maxrows=100)
The following example shows the use of the
vals parm.
$list(vals="Smith;Jones;Thompson;Walker;Brown;Spenser",valdelim=semi)
The result is:
'Smith','Jones','Thompson','Walker','Brown,'Spenser'
Caption
This statement specifies captions for the subsequent query.
Specify the following:
GridCaption
This specifies the caption for the grid displayed by the subsequent query. This caption is displayed at the top of the grid.
This caption may include parameters.
WindowCaption
When the data is displayed in a separate window, this gives the caption of the window as shown in the window-list bar.
When the data is displayed on the SQL window, this gives the caption of the tab of the grid in the bottom part of the window.
Example
--aqt caption,gridcaption="Orders for customer $custcode"
select * from order_details where cust_code = '$custcode'
ColFormat
This statement is used to specify the display format of a column in the subsequent query.
Specify the following:
colname
the name of the column you are formatting
colno
the number of the column you are formatting.
You must specify either colno or colname but not both.
format
the format to be applied to the column. The values you can specify are given in Formatting Data
Example:
--aqt colformat,colname=salary,format=currency
Select * from test.employee
If you wish to format more than one column, you can do this either on separate format lines, or on the same line:
--aqt format,colno=5,format=$###,##0.00,colno=6,format=yyyy-MM-dd
Select * from test.employee
GridOptions
The GridOptions statement specifies options about how a grid is to be displayed. This is useful for embedding in a query.
Specify the following:
GroupBy. Whether (Y or N) the grid is to show the Group-By box
RowNum. Whether (Y or N) the grid is to show Row Number.
VarHeight. Whether (Y or N) the grid rows are to be variable-row height.
Totals. Whether (Y or N) the grid is to show Totals at the bottom of the grid.
Example:
--aqt gridoptions,rownum=n,totals=y
select * from test.employees;
delparm
This function can be used to delete a user-defined parameter.
Example:
--aqt delparm,parm=custcode
You can delete a number of parameters by using the * wildcard.
Example:
--aqt delparm,parm=cust*
This will delete all parameters starting with cust.
The following statement can be used to delete all parameters:
--aqt delparm,parm=*
Options
Sdelim
Specifies the delimiter to be used for delimiting SQL statements. You can specify go, / or some other character. Use semi to specify a semicolon.
If this is not specified, AQT will use the value you have specified in Options > Run SQL > Statement Delimiter.
StopOnError
Specifies whether AQT is to halt the batch processing if it hits an error. Any error (either in the processing of an SQL statement, or an error in an
–aqt
statement) will stop the processing.Debug
Specify this as Yes or No. Switches debug mode on or off.
DbugFile
This specifies the name of the debug file. If an unqualified name is specified, (example: aqtdebug_batch.txt) it will be placed in the workfiles directory.
This debug file will be in use for this AQT session only. It will not be permanently changed.
Lobdir
Specify the directory used for writing LOB files. This can be useful if you are running multiple AQT sessions in batch and want to ensure that each session uses a separate directory for their LOB files.
UR
Specifies (Yes or No) whether Uncommitted Read is in effect for the current database.
DateFormat
Specify the date format to be used for subsequent exports. Note that the Export statement must specify userdate=y to use this format.
TimeFormat
Specify the time format to be used for subsequent exports. Note that the Export statement must specify userdate=y to use this format.
SetExitCode
This specifies whether AQT is to set the ExitCode when the batch process terminates. When AQT does this, AQT will crash - this is a problem we haven't been able to resolve. The crash does however notify you that the AQT batch process has hit an error, so may be useful.
If SetExitCode is set to No, AQT will not set the exit code so will not crash. By default, SetExitCode is Yes.
maxrows
This specifies the maximum number of rows returned by a query. This is the same limit as specified on Options > Display Limits.
This is mainly used in online-AQT. When running exports in batch-mode AQT, use the maxrows parameter of the export command.
other
You can specify any option that is present on the Options window. You refer to the option by the internal name of the option. The internal option name is described in Technical Notes on Options.
Examples:
--aqt options,stoponerror=yes,setexitcode=no
--aqt options,sdelim=go,lobdir="c:\aqt\workfiles\lobs",dateformat=mm-dd-yyyy
--aqt options,maxrows=200
Notes
When you change an option it is only in effect for your AQT session. If you go to the Options window you will see the changed value of the option. If you then click on OK in the Options window, the new value will be saved and will be permanent (e.g. until you next change it).
AQT can be configured so that some options are different for different database types (this is done with Options > Database Profiles). In this circumstance, the value in the Database Profile will take preference over the value you set here.
Load
This runs the Data Loader in unattended mode.
The syntax of the LOAD script function is not given. This is a complicated function. It is suggested that you:
use the Data Loader window to set up (and test) the load as per your requirements
click on the File > View Load Options, or Save Load Options. AQT will generate a LOAD function that will do the load as per the options you have specified on this window. You can incorporate this function in your batch script, or run it from the Run SQL window.
Return Code in Batch Mode
When running in batch mode, the Data Loader will give a return code of 8 when it encounters a critical error. A crticial error is when one of the following happens:
an error prevents the load control statement from running (eg. syntax error, table not found etc).
Duplicate Row Errors is set to Stop after x Errors, and more than x duplicate errors were encountered.
Other Errors is set to Stop after x Errors, and more than x other errors were encountered.
When running the Data Loader in online mode, these are all errors that will cause a Critical error message to be displayed (this has a large red button with a cross in it).
Running Multiple Loads
If you wish to load many multiple tables (such as all tables in a schema), this can be done as follows:
in the Database Explorer, display all the tables in the schema you wish to load.
click on Tools > Generate Text, and enter a line such as the following:
--aqt load,tab=$1.$2,dbs=<dbs1>,source=table,fromtab=$1.$2,fromdbs=<dbs2>
In this, replace <dbs1> and <dbs2> with the databases you are loading between.
The above is an example of a load script that load tables in one database with tables of the same name in another database. You may need to change this load script if you wish to do a different type of load. The way to do this is to set up the load as required in the Data Loader window, then click on File > View Load Options
Click on Generate. This will generate a script to load all the tables in the schema.
Copy the script to your batch script or Run SQL window.
Datacomp
This runs the Data Compare function in unattended mode.
The syntax of the Datacomp script function is not given. This is a complicated function. It is suggested that you:
use the Data Compare window to set up (and test) the compare as per your requirements
click on the File > View Compare Options, or Save Compare Options. AQT will generate a Datacomp function that will do the load as per the options you have specified on this window. You can incorporate this function in your batch script, or run it from the Run SQL window.
Specifying Primary Key and Columns
When running the datacomp function in batch, there are some parameters that do not need to be specified:
you do not need to specify the Primary Key (pk) parameter if the tables have primary keys defined for them. AQT will automatically determine the primary key for the tables.
you do not need to specify the columns to be compared (cols) if you are wishing to compare all columns in the tables.
CloseXL function
This closes Excel if it is open.
CloseXLFile function
This function closes an open Excel file / workbook.
Example:
--aqt CloseXLFile,file=d:\AQT\queries\output.xls
This function will always return a successful return code, irrespective of whether or not the file is open in Excel.
Layout
This function applies a layout to the query which follows. Display layouts are described in Saving / retrieving the display Layout.
The format of this function is:
--aqt layout,file=<filename>
<filename> is the name of the layout file. If an unqualified name is specified, it is assumed to be in the the Location of saved queries directory.
Example:
--aqt layout,file=layout_cust.txt
select * from CMS.CUSTOMERS;
RenameButt
This function renames the button (in the Window-list bar) of the most recent data display window created. This is useful when running a script in online AQT.
The format of this function is:
--aqt renamebutt,name="caption"
caption is the text the button is to be renamed to.
Example:
select * from CMS.CUSTOMERS;
--aqt renamebutt,name="Cust Info"
Objcomp
This runs the Compare Objects in unattended mode.
The syntax of objcomp script function is not given. This is a complicated function. It is suggested that you:
use the Compare Objects window to set up (and test) the load as per your requirements
click on the File > View Script Statement, or Save Options. AQT will generate a objcomp function that will do the compare as per the options you have specified on this window. You can incorporate this function in your batch script, or run it from the Run SQL window.
Sleep
This statement causes the script to go into a wait state for a given length of time. This can be used to run some particular commands at a given time of day.
This command can only be used when running a batch script. It cannot be used in interactive AQT.
Sleep For
This specified that AQT sleeps for a given number of seconds.
--aqt sleep,for=300
AQT will sleep for 300 seconds.
Sleep Until
This specified that AQT sleeps until a given time day.
--aqt sleep,until=23:30:00
AQT will sleep until 11:30pm.
The seconds part of the time can be omitted if it is zero. The minute part cannot be omitted.
If the time is earlier than the current time, AQT will sleep until that time the following day. This allows you to specify until=04:00
for the script to run at 4am the following morning.
WriteMsg
This function will write a message to:
the AQT Debug Log
the Batch Log, when AQT is being run in batch
The format of this command is:
--aqt writemsg,msg=About to run the Export
GenDDL
This script statement will generate the DDL for a number of tables in a schema.
To create the GenDDL script statement:
go to the Generate DDL window
select options as you require
click on File > View Script Statement
copy the text into the Run SQL window or your batch script
Schema
One parameter in the GenDDL script is schema. AQT will generate the DDL for all objects in this schema.
You can use the filter parameter to specify a subset of tables to be generated.