SQL/MX Programming Manual for C and COBOL (H06.10+, J06.03+)

C/C++ Program Compilation
HP NonStop SQL/MX Programming Manual for C and COBOL—544617-003
15-69
Displaying Query Execution Plans
as integer) as stmt_index,
substring(description from position(
statement: ' in description) + 11 for 9999) as stmt
from table(explain('CAT.SCH.MYMOD','%'))
where operator = 'ROOT'
order by stmt_index;
The query displays output similar to:
MODULE_NAME STATEMENT_NAME STMT_INDEX STMT
---------------------------------------------------------------
CAT.SCH.MYMOD SQLMX_DEFAULT_STATEMENT_1 0 PROCEDURE
C1 () INSERT INTO T VALUES(1);
CAT.SCH.MYMOD SQLMX_DEFAULT_STATEMENT_2 1 PROCEDURE
SQL_DEFAULT_STATEMENT_1() COMMIT WORK
---2 row(s) selected.
Displaying the Query Execution Plan of All Statements
To display the EXPLAIN output for all DML statements 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 embedded SQL
program or the preprocessor generated module name if you did not use a MODULE
directive. For more information on the module name, see Module Management Naming
on page 17-8.
Wild Card (%)
Instead of specifying a statement pattern, use the percent sign (%) to represent all the
DML statements in the module. The percent sign (%) must be placed within single
quotes:
'%'
For information on how to interpret the output of the EXPLAIN function, see the
SQL/MX Query Guide.
SELECT * FROM TABLE(EXPLAIN('module-name', '%'));
Note. Do not confuse module files, which do not have file extensions and reside in the
application’s base directory or in the /usr/tandem/sqlmx/USERMODULES directory, with
module definition files (.m), which are optionally generated during preprocessing and are
precursors to modules. For more information, see Module Management Naming
on page 17-8.