SQL/MP Programming Manual for COBOL

ManualsBrandsHP ManualsServerHP NonStop G-Series

161

162

163

164

165

166

167

168

169

170

Explicit Program Compilation

HP NonStop SQL/MP Programming Manual for COBOL—529758-003

6-41

Using the EXPLAIN Utility

The EXPLAIN utility generates reports about execution plans for each SQL statement.

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 other than the

SQL - SLT Index = 4, Run-Unit = NEWPART

121 COMMIT WORK

SQL - SLT Index = 5, Run-Unit = NEWPART

137 ROLLBACK WORK

BINDER - OBJECT FILE BINDER - T9621D30 - (17JUL95) SYSTEM \NEWYORK

Object file \NEWYORK.$SQL.SQLPGMS.COBOBJ

TIMESTAMP 1996-06-17 14:47:05

0 Binder Warnings

0 Binder Errors

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 of a COBOL Program (page 2 of 2)