Technical Notes on Batch Jobs

An AQT batch job consists of two files:

• a *.bat file. This invokes the AQT executable and passes to it the SQL file containing the statements to be run
• an *.sql file. This contains the SQL statements and commands that AQT is to run

These files can be in any location on your PC. However to be seen by the Manage Batch Jobs dialog they should be placed in the Batch Job directory. This is specified in Options > File Locations > Location of Batch Jobs. By default this is C:\Users\<username>\AppData\Roaming\Advanced Query Tool\batch

*.bat File

An example of a *.bat file is as follows:

Each of these statements is discussed below:

• set aqtcmd=%cmdcmdline% This statement is required for the batch job to be handled correctly by the Manage Batch Job dialog, and also for AQT to qualify any unqualifed file names. This statement allows the name of the batch job to be passed to AQT.
• rem desc. This is optional. It supplies a description of the batch job. This is shown in the Manage Batch Job dialog.
• aqtv11.exe. This runs AQT.
• pause. This is optional. When it has finished the batch script will pause for you to check whether it has worked successfully. Do not code if running as a Scheduler Task.

You can amend your bat file as you wish. For instance, you can set environment variables for AQT to read (which it can do with --aqt setparm)

Log File

When AQT is run in batch, it writes information to a log file about the running of the job. This is useful for determining whether the job ran successfully, and diagnosing any problems.

Running AQT in batch - Old Format

The command for running AQT in batch takes 2 formats. The format described here is used by AQT v10 and prior versions. For backward compatibility this is still an acceptable format with AQT v11.

aqtv11.exe sqlfile logfile

Example:

aqtv11.exe UpdateSalary.sql UpdateSalary.log

For clarity these filenames are shown as unqualified.

Unqualified file names

If sqlfile and logfile filenames are unqualified, they will be qualified by Windows using the current working directory for the process.

Running AQT in batch - New Format

With AQT v11 there is a new format for running batch jobs.

aqtv11 sqlfile=<sqlfile>,logfile=<logfile>,jobdir=<jobdir>,append=y,desc=<description>

where:

• sqlfile is the name of the sql file. This is mandatory.
• logfile is the name of the log file. This is optional.

  If not specified, it will be file $jobname_$datetime,log in the logs sub-directory. This will ensure that a different log file is created every time the job is run.

  If you wish to use the same log file for all runs of the job, you should use the append option: logfile=$jobname.log,append=y

• jobdir specifies the Job Directory. This is used to qualify the sql and log file locations.
• append. Specifies (y or n) whether log entries are appended to the log file. The default is n.
• desc. Provides a description of the job run. It can include parameters which are set within the sql file.

Example:

aqtv11.exe sqlfile="SalesReport.sql",desc="Weekly sales report for $weekdate"

• It is recommended that logfile, jobdir and append are not specified and are allowed to be taken as their default values.
• In this example, $weekdate is a parameter set in the sql file with either setparm or queryparm. This description will appear in the Batch History, and will enable you to distinguish between the various runs of the batch job.
• Parameters should be enclosed in double-quotes if they contain spaces or commas.

Unqualified file names

If sqlfile is an unqualified filename, AQT will look in the following locations:

• if jobdir is specified, AQT will look for this file there.
• otherwise, AQT will look in the same directory as the *.bat file. However, this can only be done if the set aqtcmd command has been included.
• otherwise, AQT will use an unqualified name. When AQT reads an unqualified file, Windows will use the current working directory for the process. This will generally be the same directory as the *.bat file, however in some circumstances may be different.

  Because there can be some uncertainly as to what sqlfile is read and where the logs are written, it is recommended that either fully-qualified filenames are used, or the set aqtcmd command be included.

If logfile is an unqualified filename, the log file will be written to the logs subdirectory of the sqlfile directory.

Jobname

When the job is run, it is given a job name. The jobname will be shown in the Job History. It also forms part of the name of the log file.

This comes from the name of the bat file. If set aqtcmd is not given, AQT will not know the name of the bat file. In this case the job name will come from the name of the sql file.

*.sql File

The SQL file consists of a series of AQT scripting commands and SQL statements which AQT is to run.

A reference to the AQT scripting statements is given at Scripting Statements.

• your script should have at least one Connect command to sign onto the database you wish to run your SQL against.
• you can sign onto multiple databases; the Use command specifies which database a particular SQL statement runs against.
• you can run both action SQL statements and Select statements.

  If you are running a Select statement you also need an --export command to specify how the data is to be exported.

• most AQT script commands can be run from the Run SQL window. You can test a command in this window before putting it in your SQL file.
• many AQT functions have an option for generating a script command.

  For instance, in the Export Data window you can click on File > View Export Command. This will show you the command to export the data in the format specified by the options on the dialog. This command can be included in your SQL file.

Sample Batch Jobs

AQT supplied a number of sample batch jobs. These are in the following directory:

C:\Users\<username>\AppData\Roaming\Advanced Query Tool\batch

They will be seen when you start the Manage Batch Jobs dialog.