SQL/MX 3.2.1 Guide to Stored Procedures in Java (H06.26+, J06.15+)

Using the EXPLAIN function with SPJ RS
You can also prepare the CALL statement and return result sets from the result table of the EXPLAIN
function, as shown:
PREPARE S
FROM CALL samdbcat.sales.order_summary(?, ?);
SELECT DESCRIPTION FROM TABLE(EXPLAIN(NULL, 'S')) WHERE OPERATOR
= 'CALL';
The SELECT statement displays the following output:
DESCRIPTION
--------------------------------------------------------------------------
--
parameter_modes: I O routine_name: SAMDBCAT.SALES.ORDER_SUMMARY
routine_label: \ALPINE.$SYSTEM.ZSDCR2C6.L1Z7NW00
sql_access_mode: READS SQL DATA external_name: orderSummary2
external_path: /usr/mydir/myclasses external_file: rs
signature:
(Ljava/lang/String;[J[Ljava/sql/ResultSet;[Ljava/sql/ResultSet;)V
language: Java runtime_options: OFF runtime_option_delimiters: ' '
max_results: 2
--- 1 row(s) selected.
The output includes a new entry max_results, which displays the maximum number of result sets
that can be returned by this procedure. The value for max-results is set by the DYNAMIC RESULTS
option in the CREATE PROCEDURE statement.
NOTE: The max_results option is displayed only on systems running J06.05 and later J-series
RVUs or H06.16 and later H-series RVUs.
Displaying the Shape of a CALL Statement
The SHOWSHAPE command displays the shape for a given DML statement, such as a CALL
statement, in an MXCI session. The result can be used at a later time to force the SQL/MX compiler
to choose a particular execution plan (or to force the same execution plan for the statement).
CALL Statements Without a Shape
A CALL statement that does not contain a subquery as one of its SQL parameter arguments does
not have a shape. For example, this SHOWSHAPE command is for a CALL statement without
subquery parameters:
SHOWSHAPE CALL samdbcat.persnl.adjustsalary(202,5.5,?);
Because this CALL statement does not have a shape, the SHOWSHAPE command returns this
output:
control query shape anything;
--- SQL operation complete.
CALL Statements With a Shape
If a CALL statement contains one or more subqueries as its SQL parameter arguments, the
SHOWSHAPE command generates a shape that describes the entire SQL statement. For example,
this SHOWSHAPE command is for a CALL statement with a subquery parameter:
SHOWSHAPE CALL samdbcat.sales.totalprice((SELECT qty_ordered
FROM samdbcat.sales.odetail
WHERE ordernum = 100210
Displaying the Shape of a CALL Statement 103