SQL/MX Programming Manual for Java

Processing SQLJ Programs

HP NonStop SQL/MX Programming Manual for Java—523726-003

5-46

Displaying Query Execution Plans

The EXPLAIN function is an SQL/MX extension that generates a result table describing

an access plan for a DML statement, otherwise known as a query execution plan. Use

the EXPLAIN function only for customized SQLJ programs that have modules. For

more information about the EXPLAIN function, see the SQL/MX Reference Manual and

the SQL/MX Query Guide.

To display embedded module definitions, see the ProfilePrinter Tool on page 5-42.

Displaying the Query Execution Plan of One Statement

To display the EXPLAIN output for a specific DML statement in a module, issue this

statement in MXCI:

Module Name

The module-name is the full name of a module, is case-sensitive, and must be placed

within single quotes:

'CAT.SCH.GRP1^MOD1^TABLESET1^VER1'

The module name is either specified by the MODULE directive in the SQLJ source file

or by the translator if you did not use a MODULE directive. For more information about

the module name, see the MODULE Directive on page 4-12.

Statement Pattern

The statement-pattern is the name of a DML statement in the module. The

statement-pattern is case-sensitive and must be placed within single quotes:

'MX_DEFAULT_STATEMENT_0'

To determine the name of a particular SQL statement, look at the module definition,

which shows the statement name between PROCEDURE and (). To display embedded

Note. If there is no EXPLAIN output for a statically compiled application, the

GENERATE_EXPLAIN default attribute might have been turned off during compilation. If this is

the case, verify that GENERATE_EXPLAIN is on and recompile the application.

SELECT * FROM TABLE(EXPLAIN('module-name',

statement-pattern'));

Note. Do not confuse module files, which do not have file extensions and reside in the

/usr/tandem/sqlmx/USERMODULES directory, with module definition files (.m), which are

optionally generated during SQLJ processing and are precursors to modules. For more

information, see Module

on page 1-16 and Module Definition File on page 1-19.