SQL/MX 3.2 Guide to Stored Procedures in Java (H06.25+, J06.14+)

Performance and Troubleshooting

HP NonStop SQL/MX Release 3.2 Guide to Stored Procedures in Java—691166-001

7-4

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:

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.

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.

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.