Last week, we had a look at how to extract AutoSys Job Information from AutoSys Relational Database using ZQL. Let us understand how to execute ZQL script and do it smartly.. In the following code snippet, we would be executing using standard input as ZQL script.
zql -U <username> -P <password> -S <system_identifier> <<EOF SET HEADING OFF SET FEEDBACK OFF SET WRAP OFF SET LINESIZE 2000 /* Your ZQL Query*/ EOF
This is one of the most simplest of the examples to get started with. So, here is a quick overview on the options provided by zql, as mentioned in the man pages;
The name of the Oracle user to log in as, and can be
any valid Oracle user. This is typically autosys for
the AutoSys user, or SYS for the system administrator.
The Oracle password for the specified username.
The name of the Oracle system identifier as defined in
the tnsnames.ora file.
There’s another way to execute ZQL script, through an input file. In this code snippet,
zql -U <username> -P <password> -S <system_identifier> -f <zql-script>.zql
Just make sure to keep the last statement in the supplied ZQL script as
Otherwise, the session never ends, even though the results are retrieved. Let us take a look at some more options provided with ZQL command;
A text file containing SQL statements to be passed to
Oracle. zql sends this file of commands to the
dataserver, and sends the results to standard output.
Indicates that the output will not contain header information.
Specifies the level of quietness in the output. For
example, -Q discards all messages and column headings
from sqlplus, leaving only data in the output.
In this code snippet, the column headers would be suppressed in the results;
zql -U <username> -P <password> -S <system_identifier> -h -f <zql-script>.zql
In this code snippet, the column headers along with messages sent by sqlplus will be suppressed in the results;
zql -U <username> -P <password> -S <system_identifier> -h -q <zql-script>.zql
Alternatively, one can use -Q in place of -h -q, to get similar results, with no blank lines in output.
zql -U <username> -P <password> -S <system_identifier> -Q -f <zql-script>.zql