SQL/MP Programming Manual for COBOL85

Explicit Program Compilation
HP NonStop SQL/MP Programming Manual for COBOL85—429326-004
6-38
Using the EXPLAIN Utility
Using the EXPLAIN Utility
The EXPLAIN utility generates reports about the execution plans for each SQL
statement. You can use EXPLAIN reports to determine the tables and indexes used by
a program and whether creating other indexes or modifying a query would improve the
performance of the program. The EXPLAIN utility has these report options:
•
EXPLAIN PLAN report
•
EXPLAIN DEFINES report
EXPLAIN PLAN Report
The EXPLAIN PLAN report, which applies only to DML statements, 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 containing subqueries in
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:
•
Scan a table
•
Join two or more tables
•
Insert into a table
•
Perform a sort operation
In this example, the SQL compiler compiles the SQLPROG program file using the
EXPLAIN PLAN option. The SQLCOMP command specifies a catalog and does not
use the current default catalog. The SQL compiler uses the current set of DEFINEs
and writes the output to the location $S.#EXPLAIN.
SQLCOMP / IN SQLPROG, OUT $S.#EXPLAIN /
CATALOG $DISK2.SALES, EXPLAIN PLAN
SQL **************************************************
SQL - Summary of SQL Compiling
SQL - Number of SQL Statements = 6
SQL - Number of SQL Errors = 0
SQL - Number of SQL Warnings = 0
SQL - Number of other Errors = 0
SQL - Compile Time = 00:00:00.249
SQL - Elapsed Time = 00:00:22.915
SQL - Program file is \NEWYORK.$SQL.SQLPGMS.COBOBJ
SQL - >>> SQL COMPILATION STORED IN PROGRAM FILE <<<
SQL **************************************************
Example 6-1. Sample SQL Compiler Listing (page 2 of 2)