SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions

HP NonStop SQL/MX Reference Manual—540440-003

9-53

Considerations for EXPLAIN

Using a Statement Pattern

Using a statement pattern is analogous to using a LIKE pattern. For example, this

statement returns the EXPLAIN result for all statements prepared within the current

MXCI session:

SELECT * FROM TABLE (EXPLAIN (NULL,'%'))

This statement returns the EXPLAIN result for all statements prepared within the

embedded SQL module named MYCAT.MYSCH.MYPROG:

SELECT * FROM TABLE (EXPLAIN ('MYCAT.MYSCH.MYPROG','%'))

This statement returns the EXPLAIN result for all prepared statements whose names

begin with the uppercase letter 'S':

SELECT * FROM TABLE (EXPLAIN (NULL,'S%'))

If the statement pattern does not find any matching statement names, no rows are

returned as the result of the SELECT statement.

For more information about module names, see the SQL/MX Programming Manual for

C and COBOL.

Using EXPLAIN and DISPLAY_EXPLAIN

The result of the EXPLAIN function for a specific DML statement can be generated

either by using the EXPLAIN function or the DISPLAY_EXPLAIN command. Use the

EXPLAIN function only for prepared statements.

The DISPLAY_EXPLAIN command displays all of the columns of the result table of the

EXPLAIN function. If you want to display only some of the columns, you must use the

EXPLAIN function to return the intermediate result table that you then query with a

SELECT statement.

Result of the EXPLAIN Function

The result table of the EXPLAIN function describes the access plans for SELECT,

INSERT, DELETE, UPDATE, or CALL statements. Use the EXPLAIN function to

generate the result and the DISPLAY_EXPLAIN command to display the result.

See the EXPLAIN Function on page 9-52 and DISPLAY_EXPLAIN Command on

page 4-13.

In this description of the result of the EXPLAIN function, an operator tree is a structure

that represents operators used in an access plan as nodes, with at most one parent

node for each node in the tree, and with only one root node.