Scripting & Automation
Running AQT as an unattended batch job
AQT allows you to run your SQL as an unattended batch job. You may do this for a number of reasons:
you have a query or other function which you run on a regular basis
you have a query or other function which you need to run out of hours
you have a long-running query or other function which you wish to run outside your AQT session
Setting up a new Batch Job
You can set up a new Batch Job :
from the SQL window with File > Create Batch Job
from the Data Compare window with File > Create Batch Job
from the Data Loader window with File > Create Batch Job
For more information on this see Creating a new Batch Job.
Sample Jobs
AQT provides a number of sample batch jobs which you can use to test this feature and to understand it's capabilities.
These sample jobs are located at: C:\Users\<username>\AppData\Roaming\Advanced Query Tool\batch.
You can see / manage then with the Manage Batch Jobs window.
Managing Batch Jobs
AQT provides a window for managing your batch jobs. This allows you to:
View / edit the batch jobs in your system
Set up batch job to run on a regular basis using the Windows Task Scheduler
See a history of the running of your jobs
Viewing the export files created by your batch jobs
Viewing Export History
In addition to the Manage Batch Jobs window, AQT maintains a history of all data exports. This is a quick way of viewing all export files created by your batch jobs.
The export history can be viewed with the View Export History window.
Creating a new Batch Job
You can create a Batch Job from a number of windows in AQT with File > Create Batch Job.
This will show you the Create Batch Job dialog.
Job Name is mandatory. It cannot be the name of an existing job.
Description is optional.
Click on Next to go the next step in the dialog.
Editing your SQL
The next step in the dialog shows you the SQL in your batch job. You can amend this if required.
Specifying how your data is to be exported
If your SQL runs a query which produces data, you will need to specify how the data is to be exported.
If you have already specified this with an --aqt export statement, this step will not be shown.
This dialog shows a limited set of options for exporting data. To see the full set of options, click on Advanced.
Export File
By default, the export will go a file named $jobname_$datetime.
$jobname is the name of the batch job (UpdateSalaries in this example)
$datetime is the date/time the batch job is run (for example 20210605123457)
if the job exports multiple files, $runtime will be the same for all of them. However File Mode of Create New will ensure that each export will go to a new file
the export files will be created in the Default directory for Exports, specified in Options > File Locations.
Viewing the Export File
Once the batch job has run, you can view the export file/s in several ways:
from the Manage Batch Jobs dialog, click on the batch job > History > Show Exported Files
from the Export History window
if you have exported the results as a saved-grid, it can be seen in the Query Explorer.
Specifying when to run your Batch Job
This step of the dialog allows you to specify when your Batch Job is to be run. You have the choices of:
Manually. The job will be run when you explicitly run it.
Scheduled. You can schedule your job to be run at a particular time, or on a regular basis. When you specify this, AQT will create a Scheduled Task with the Windows Task Scheduler.
You will need to have the authority within Windows to create a Scheduled Task
This dialog shows a basic set of options for scheduling your job. You can create more advanced options on either the Manage Jobs dialog, or using the Windows Task Scheduler.
You will not be able to create a Scheduled Task if a task already exists with that name, whether created by yourself or someone else. If you machine is used by multiple people (such as a virtual / Citrix / Terminal server) then you may wish to specify a different scheduler folder for each person. This specified in Options > General.
Manage Batch Jobs
On the Database Explorer window, click on Tools > Manage Batch Jobs or Ctrl+J to see the Manage Batch Jobs window.
In this window you can:
see all your batch jobs, including when they were last run
run a batch job
edit a job or set it up as a Scheduler Task
click on the History tab to see the history of all runs of the job
click on the Schedule tab to view edit the details of the how the job has been scheduled to run.
Batch Job directory
This dialog will show to the batch jobs in your Batch Job directory - this is specified in Options > File Locations. Batch Jobs held in other locations will not be shown in this display.
You can show the Batch Jobs in a different directory with File > Open Batch Directory.
Viewing the Details of a Job
The bottom panel of the Manage Batch Jobs window shows the details of the batch job:
you can Run the job
you can edit the details of the Batch File. This specifies which SQL script to run, plus the name of the log file
you can edit the details of the SQL script
History for a Batch Job
When you click on the History tab, you will be shown the history of all runs of the batch job.
you can easily see whether or not a run was successful
you can view the log file for the run, either in a lower pane, or in AQT's file viewer.
clicking on Show Export Files will show you the export files created by a batch job.
Viewing the Export Files creating by a job
Clicking on Show Export Files will show a lower panel with the files created by the run of the job.
Scheduling the Batch Job
You can run the Batch Job on a regular basis. You can do this in either of the following ways:
when viewing the Job Details tab, click on Schedule Job
when viewing the Job Details, go to the Schedule tab
when viewing all Scheduler Tasks, click on Add New Task
On this window:
Task Name. This is name of the task in the Windows Task Scheduler. We generally recommend that this is the same name as the job, however a different name can be used if required.
The Task Name must be unique within the Task Scheduler folder. If there are other users on your machine creating tasks, you won't be able to create task with the same name as their tasks. To avoid this problem you may wish to give all users a different Task Scheduler folder. This is specified in Options > General.
Batch File to Run.This is the AQT Batch Job to run.
How often? This specifies how frequently you want to run the job. For Weekly and Monthly schedules, you will get boxes where you can specify the days-of-week or days-of-month as appropriate.
Run if schedule missed? When this option is specified, Windows will run the task even if the scheduled time for the task has passed.
if Run when not logged on is specified, the task time may be missed because of a machine reboot or power off. In this case the task will be run when Windows is next running.
if Run when not logged on is not specified, any missed tasks will be run when you next log on.
Userid and Password. By default the task will run under your own userid. Only specify userid/password if:
you are running the task under a different userid than your own.
you have selected option Run when not logged on. In this case you need to specify userid / password, even if the task is running under your own userid.
If you enter a userid. but no password, the userid will be ignored and the task will run under your userid.
AQT doesn't provide a mechanism to run the task under Local Administrator or other inbuilt account. To do this, you will need to create / edit the report using the Windows Task Scheduler dialog.
Run when not logged on? Select this when you wish to run the task even when you are not logged onto the machine. For tasks run out of hours (eg. overnight), it is recommended that this option is selected.
When specifying this, you must enter both userid and password.
For critical jobs, it is recommended that both Run if schedule missed and Run when not logged on are selected. If this is not done, there is a chance that the task will not be run.
Job History
AQT maintains a history of batch jobs you have run. This is held in file job_history.txt in your History Directory.
You can view this log in Manage Batch Jobs > History.
This:
shows you the history of all Batch Jobs run
allows you to easily view the log for any job
allows you to see the files created by a job
Notes
You will only see jobs run by AQT v11. Jobs run by AQT v10 and earlier versions will not be included in this.
The Job column gives the name of the job - this comes from the filename of the *.bat file which runs the script.
Job will be blank if your *.bat file doesn't contain the set aqtcmd=%cmdcmdline% statement at the start. When this statement is not present, AQT is unable to determine the name of the batch file which is running the job.
Scheduler Tasks
To see all Scheduler Tasks, click on the Scheduler button in the toolbar of the Manage Batch Jobs window.
this will show you all the Scheduler Tasks which you have created.
to see tasks created by all users you need to run AQT in administrator mode
Status and Last Run Status sometimes have Running (when the task is not running) or other strange status. This is status as reported by the Windows Task Scheduler.
for fuller information on your tasks, use the Windows Task Scheduler dialog.
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=yjobdir 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.
Changes to Batch Jobs with AQT v11
Users who have been running batch jobs with AQT v10 or prior versions should read this topic
There have been a number of changes to the running of batch jobs; these are outlined here.
Backward Compatibility
AQT maintains backward compatibility - your existing batch jobs will continue to run without any changes. However, it is recommended that you make a number of changes, as outlined below.
Batch Job Directory
It is recommended that your batch jobs be placed in a single directory, and this directory is specified in Options > File Locations > Location of Batch Jobs. Having all the batch jobs in a single directory enables them to be managed using the Manage Batch Jobs system.
AQT provides a number of sample batch files in C:\Users\<username>\AppData\Roaming\Advanced Query Tool\batch and Location of Batch Jobs will default to this location. If you are running batch jobs frequently, you may wish to use another directory.
Note that the default directory can only be used by yourself, and will not be found if you have scheduled a batch job to run under a different userid,.
Adding the set aqtcmd command to your *.bat files
When running your script, it is recommended that the *.bat file contains the following command at the top:
set aqtcmd=%cmdcmdline%
This sets the name of the *.bat file to the aqtcmd environment variable; this is then picked up by AQT. This allows AQT to include the name of the *.bat file in the Job History.
The Manage Batch Jobs window will warn you if this set command is not present in a script, and will allow you to add it easily.
Adding a description to your *.bat files
This is an optional but useful step. You can add a description to your batch by including the following line near the top of your *.bat file
rem desc=Job description
The description will appear in the Manage Batch Jobs window plus Job History.
Move AQT run command to new format
This an optional step. As with AQT v11, the command which runs AQT in the batch script has changed to a new format. This is (for example):
aqtv11.exe sqlfile=RunReport.sql,logfile=RunReport.log,append=y,desc="Overdue Invoices for $month"
This new command format is detailed in Technical Notes on Batch Jobs.