SQL Programming Manual for TAL

Program Compilation and Execution
HP NonStop SQL Programming Manual for TAL—527887-001
5-18
Using the EXPLAIN Utility
Generating an EXPLAIN PLAN Report
The EXPLAIN PLAN report applies only to SQL DML statements. A plan shows 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 that contains subqueries
into separate query plans, one for each subquery and one for the statement itself. This
report numbers the query plans in each statement in the order they appear. Each plan
can contain these steps:
1. Scan a table
2. Join two or more tables
3. Insert into a table
4. Perform a sort operation
In the next example, the SQL compiler compiles the newprog program file using the
EXPLAIN PLAN option to generate the statement execution plans. The command also
specifies the catalog name rather than use the current default subvolume name for the
catalog. The SQL compiler writes the output to the spooler. The command is:
SQLCOMP / IN newprog, OUT $s.#explain, NOWAIT / &
... CATALOG sales, EXPLAIN PLAN
By default, the SQL compiler uses the current set of TACL DEFINEs to create the
object file.
In the next example, the SQL compiler writes an execution plan and set of DEFINEs to
the EDIT file EXOUT, but it does not recompile the OXPLAIN program file:
SQLCOMP /IN oxplain, OUT exout/ NOOBJECT STOREDDEFINES &
EXPLAIN PLAN DEFINES, OBEYFORM
The catalog name for the program unit is stored in the program file so that you do not
have to include the CATALOG option. NOOBJECT suppresses the generation of a new
object file, so the compiler does not write a record about the program file to the
catalog.
The TACL DEFINE set is the set stored with the program OXPLAIN. The execution
plans are the plans that the compiler would generate if it were generating object code;
they are not the plans stored in the object file OXPLAIN.
Generating an EXPLAIN DEFINES Report
The EXPLAIN DEFINES report shows the mapping of TACL DEFINE names used in
SQL DML, DCL, and DDL statements. This report lists:
•
Each TACL DEFINE name and its associated Guardian name
•
The default volume and default catalog used by the program (which it obtains from
the =_DEFAULTS DEFINE)