Neoview Guide to Stored Procedures in Java (R2.3, R2.4)

8 Performance and Troubleshooting
This chapter describes how to improve and monitor the performance of SPJs on the Neoview
platform and provides guidelines for troubleshooting common problems:
• “Troubleshooting Common Problems”
• “Performance Tip”
• “Displaying an Execution Plan of a CALL Statement” (page 73)
Troubleshooting Common Problems
To resolve problems that occur when you register or execute an SPJ, follow these guidelines:
• Note the SQLCODE or SQLSTATE value of the error messages and locate the information
in the Neoview Messages Manual, which provides cause, effect, and recovery information for
all SQL errors.
• Check that the user role has the appropriate permissions to create or call the SPJ. See
“Required Privileges for Creating an SPJ” (page 43), “Required Privileges for Calling an
SPJ” (page 63), and “Showing Privileges on SPJs” (page 60).
• Check the code of the SPJ method. See Chapter 3 (page 25). Fix any problems.
• If you successfully compiled, deployed, and registered the SPJ but are receiving errors when
calling the SPJ, check that the output parameters in the Java method are specified as arrays.
See “Returning Output Values From the Java Method” (page 26).
• Verify that the SPJ method and its JAR file exist on the Neoview platform and that someone
did not delete or change the SPJ bytecode without first dropping the SPJ from the Neoview
database. See “Altering an SPJ and Its Java Bytecode” (page 50).
• Check the characteristics of the stored procedure in DB Admin and compare them with the
SPJ method that resides on the Neoview platform. See “Using DB Admin to Display SPJs
in a Schema” (page 47) and “Downloading SPJ JAR Files to a Client Workstation” (page 38).
Fix any discrepancies.
• Check the syntax of the CALL statement in the application. See Chapter 7 (page 63). Fix any
problems.
• To identify Java-related errors, execute the SPJ method outside the database by invoking
the Java method directly in a Java application that you run on a development workstation
using the Neoview JDBC Type 4 driver.
• If the SPJ is supposed to return result sets but the result sets are not being returned to the
calling application, check that the SPJ method does not explicitly close a
java.sql.Connection object. See “Use of java.sql.Connection Objects” (page 30).
• If a java.lang.ArrayIndexOutOfBoundsException occurs, check that the SPJ method
is not trying to insert more than one array element into a java.sql.ResultSets[] array.
For more information, see “Returning Stored Procedure Result Sets” (page 27).
Performance Tip
To ensure the optimal performance of SPJs in on the Neoview platform, avoid nesting CALL
statements in an SPJ method, which wastes resources and might diminish performance. For more
information, see “Nested Java Method Invocations” (page 29).
Displaying an Execution Plan of 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 EXPLAIN statement or function.
Troubleshooting Common Problems 73