SQL/MX Guide to Stored Procedures in Java (G06.24+, H06.03+)

Performance and Troubleshooting
HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
7-3
Displaying an Execution Plan for a CALL Statement
Displaying an Execution Plan for a CALL
Statement
An execution plan reveals how a CALL statement was optimized. You can display all or
part of the execution plan for a CALL statement by using the DISPLAY_EXPLAIN
command or the EXPLAIN function.
Using the DISPLAY_EXPLAIN Command
Suppose that you want to display the execution plan for this CALL statement:
CALL samdbcat.persnl.adjustsalary(202, 5.5, ?);
Enter this DISPLAY_EXPLAIN command in an MXCI session:
DISPLAY_EXPLAIN CALL samdbcat.persnl.adjustsalary(202, 5.5, ?);
The DISPLAY_EXPLAIN command generates and displays all the columns of the
result table of the EXPLAIN function. For more information, see the SQL/MX Query
Guide.
Using the EXPLAIN Function
You can also prepare the CALL statement and select specific columns from the result
table of the EXPLAIN function, as shown:
PREPARE call_spj
FROM CALL samdbcat.persnl.adjustsalary(202, 5.5, ?);
SELECT SUBSTRING(OPERATOR,1,8) AS "OPERATOR", OPERATOR_COST,
SUBSTRING(DESCRIPTION,1,500) AS "DESCRIPTION"
FROM TABLE (EXPLAIN(NULL, 'CALL_SPJ'));
The SELECT statement displays this output:
For a CALL statement, the OPERATOR column of the result table contains a row
named CALL. The DESCRIPTION column contains special token pairs for the CALL
operator. For more information, see the SQL/MX Query Guide.
OPERATOR OPERATOR_COST DESCRIPTION
-------- --------------- -----------------------------------------------
CALL 1.3930000E-005 input_values: cast(202), cast(cast((cast(5.5) /
cast(10)))) parameter_modes: I I O routine_name:
SAMDBCAT.PERSNL.ADJUSTSALARY routine_label:
\KINGPIN.$TX0115.ZSDPK4GV.Q85DXB00 sql_access_mode: MODIFIES SQL DATA
external_name: adjustSalary external_path: /usr/mydir/myclasses
external_file: Payroll signature:
(Ljava/math/BigDecimal;D[Ljava/math/BigDecimal;)V language: Java
runtime_options: OFF runtime_option_delimiters: ' '
ROOT 5.8506000E-008 select_list: NUMERIC(8,2) SIGNED input_variables:
? statement_index: 0 statement: CALL samdbcat.persnl.adjustsalary(202,
5.5, ?); return