SQL/MX Guide to Stored Procedures in Java (G06.24+, H06.03+)

HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
7-1
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 on page 7-1
•
Performance Tips on page 7-2
•
Displaying an Execution Plan for a CALL Statement on page 7-3
•
Displaying the Shape of a CALL Statement on page 7-4
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 on page 4-1, Required Privileges for
Calling an SPJ on page 5-1, and Showing Privileges on the SPJs on page 6-8.
•
Check the code of the SPJ method. See Section 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 on page 3-2.
•
Check the syntax of the CREATE PROCEDURE statement by using the
SHOWDDL command. See Showing the Syntax of an SPJ
on page 6-12. Compare
the CREATE PROCEDURE syntax with the SPJ method. See Using the CREATE
PROCEDURE Statement on page 4-2. Fix any problems.
•
Check the syntax of the CALL statement in the application. See Section 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 on page 2-11.
•
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 on page 2-26.