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

C/C++ Program Compilation
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
15-62
Displaying Query Execution Plans
You can use the following query from MXCI to determine the module’s statement
names and associated SQL queries, substituting the actual value of your module name
in place of 'CAT.SCH.MYMOD'.
select module_name, statement_name,
cast(trim(substring(description from
(position('statement_index: ' in description) + 17)
for (position(' ' in substring(
description from
(position('statement_index: ' in description) + 17))))))
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.
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.