SQL/MP Programming Manual for C

Explicit Program Compilation
HP NonStop SQL/MP Programming Manual for C—429847-008
6-27
SQL Compiler Listings
EXPLAIN PLAN Report
The EXPLAIN PLAN report, which applies only to DML statements, indicates the
strategy for executing a DML statement and includes optimized access paths, joins,
and sorts. The EXPLAIN PLAN report generates a plan for a statement containing
subqueries in separate query plans, including one for the statement itself and one for
each subquery. This report numbers the query plans in each statement in the order
they appear. Each plan can contain these steps:

Scan a table

Join two or more tables

Insert into a table

Perform a sort operation
In this example, the SQL compiler compiles a program file, sqlprog, using the
EXPLAIN PLAN option. The SQLCOMP command specifies a catalog other than the
current default catalog. The SQL compiler uses the current set of DEFINEs and writes
the output to the spooler location $s.#explain :
SQLCOMP /IN sqlprog, OUT $s.#sqlist / CATALOG $disk2.sales,
EXPLAIN PLAN
EXPLAIN DEFINES Report
The EXPLAIN DEFINES report indicates the mapping of DEFINE names used in SQL
statements with this information:

Each DEFINE name and its associated Guardian name used for SQL tables and
views

The default volume and default catalog used by the SQLCOMP process (which it
gets from the current =_DEFAULTS DEFINE)
The EXPLAIN utility can generate EXPLAIN DEFINES reports in these formats:
OBEY command file format EXPLAIN generates the ADD DEFINE commands that
add DEFINEs. You can then use a TACL OBEY
command to run these commands.
INFO DEFINE format EXPLAIN generates a report in the format used by the
TACL INFO DEFINE command.
This example shows an OBEY command file report. In an actual report, each subvol-
name, guardian-name, and define-name would be replaced by the actual name.
ALTER DEFINE =_DEFAULTS, VOLUME subvol-name
ALTER DEFINE =_DEFAULTS, CATALOG subvol-name
ADD DEFINE define-name, FILE guardian-name
ADD DEFINE define-name, FILE guardian-name
...