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

• Avoid using too many UDR_JAVA_OPTIONS settings in applications that call SPJs. For more
information, see Using Multiple UDR_JAVA_OPTIONS Settings in One Application (page 39).
• Avoid nesting CALL statements in an SPJ method, which wastes resources and diminishes
performance. For more information, see Nested Java Method Invocations (page 53).
• Use connection pooling and explicitly close each java.sql.Connection object when it
is no longer needed instead of relying on garbage collection. For more information, see Use
of java.sql.Connection Objects (page 53).
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:
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
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.
102 Performance and Troubleshooting