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

7 Performance and Troubleshooting
This section describes how to improve and monitor the performance of SPJs in NonStop SQL/MX,
provides guidelines for troubleshooting common problems, and covers these topics:
• Troubleshooting SPJ Problems (page 101)
• Performance Tips (page 101)
• Displaying an Execution Plan for a CALL Statement (page 102)
• Displaying the Shape of a CALL Statement (page 103)
Troubleshooting SPJ Problems
To resolve problems that occur when you register or invoke an SPJ, follow these guidelines:
• Note the SQLCODE or SQLSTATE value of the error messages and locate the information in
the SQL/MX Messages Manual, which provides cause, effect, and recovery information for
all SQL/MX errors.
• Check that the user has the appropriate permissions to create or call the SPJ. See Required
Privileges for Creating an SPJ (page 59), Required Privileges for Calling an SPJ (page 71),
and Showing Privileges on the SPJs (page 89).
• Check the code of the SPJ method. See Chapter 3: Writing SPJ Methods. Fix any problems.
• If you successfully compiled 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 50).
• Check the syntax of the CREATE PROCEDURE statement by using the SHOWDDL command.
See Showing the Syntax of an SPJ (page 92). Compare the CREATE PROCEDURE syntax with
the SPJ method. See Using the CREATE PROCEDURE Statement (page 59). Fix any problems.
• Check the syntax of the CALL statement in the application. See Chapter 5: Invoking SPJs in
NonStop SQL/MX. Fix any problems.
• If the SQL/MX syntax and SPJ code are correct, check the configuration of the SPJ environment.
Verify the JREHOME, JDBC/MX location, and class path settings. Reconfigure the JVM startup
options (that is, UDR_JAVA_OPTIONS settings) to determine if the problem is related to the
SPJ environment. See Controlling JVM Startup Options (page 36).
• If you receive an error describing a Java security-related problem, check the
UDR_JAVA_OPTIONS setting and the permissions in the specified policy file. Verify that all
the Java classes have required permissions. See Establishing Java Security (page 46).
• 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 the command line. For more
information, see the NonStop Server for Java Programmer's Reference.
Performance Tips
To ensure the optimal performance of SPJs in NonStop SQL/MX:
• Design your Java classes and directory structure so that fewer SPJ methods in different external
paths rely on the same application class. This approach conserves system resources and
memory. For more information, see Updating the Java Class Files of a Referenced Application
Class (page 30).
• Use UDR_JAVA_OPTIONS settings to manage multiple SPJ environments to support parallelism
of CALL statements issued from a multithreaded application. For more information, see
Multithreading in an SPJ Environment (page 27).
Troubleshooting SPJ Problems 101