ZQL

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;

-U username

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.

-P password

The Oracle password for the specified username.

-S system_identifier

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

EXIT ;

Otherwise, the session never ends, even though the results are retrieved. Let us take a look at some more options provided with ZQL command;

-f input_file

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.

-h

Indicates that the output will not contain header information.

-q -Q

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
Advertisements

One thought on “ZQL

  1. Pingback: AutoSys Jobs, ZQL and Oracle Database | PrayogShala

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s