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

Using the DROP PROCEDURE Statement
To drop an SPJ, specify the name of the SPJ in the DROP PROCEDURE statement, as shown:
DROP PROCEDURE samdbcat.persnl.adjustsalary;
Do not specify the SQL parameters along with the procedure name. Each procedure name represents
a unique SPJ in the database because NonStop SQL/MX does not support the overloading of
procedure names.
For the syntax of the DROP PROCEDURE statement, see the SQL/MX Reference Manual.
Altering an SPJ and Its Java Class
To alter an SPJ, you must first drop the SPJ from system metadata by using the DROP PROCEDURE
statement and then re-create the SPJ by using the CREATE PROCEDURE statement.
NOTE: NonStop SQL/MX does not support the ALTER_JAVA_PATH procedure from SQL/JRT of
the ANSI SQL/Foundation standard.
To change the SPJ or the Java class of the SPJ method:
1. End all current SQL/MX UDR server processes that might have loaded the SPJ method that
you want to change.
For example, if you were calling the SPJ in an MXCI session, end that MXCI session. If you
were calling the SPJ from an SQL/MX application, terminate that application.
2. Drop the SPJ from system metadata by using the DROP PROCEDURE statement. See Dropping
an SPJ (page 67).
NOTE: 2 is required only if you move the Java class of the SPJ method or change its external
name or Java signature.
3. If desired, move the Java class file to another OSS directory, or change the source code of
the SPJ method and recompile the Java class.
4. Re-create the SPJ by using the CREATE PROCEDURE statement. See Creating an SPJ (page 59).
NOTE: 4 is required only if you move the Java class of the SPJ method or change its external
name or Java signature.
5. Restart the application and call the SPJ in a new SQL/MX UDR server process.
CAUTION: To prevent unpredictable and undesirable behavior of CALL statements, do not change
SPJ classes or referenced application classes while SPJ environments are active. For more
information, see Maintaining Class and JAR Files in an SPJ Environment (page 30).
Typical scenarios that require an SPJ to be altered are discussed next:
• Changes to the External Path (page 68)
• Changes to the External Name (page 69)
• Changes to the Java Signature (page 70)
Changes to the External Path
The external path is the OSS directory or JAR file path of the Java class file that contains the SPJ
method. If you change the external path of the underlying SPJ method, you must drop the associated
SPJ and re-create it.
For example, suppose that you move the class, Sales.class, which contains the SPJ method,
numMonthlyOrders(), from the /usr/mydir/myclasses directory into a JAR file, myJar.jar.
The SPJ was originally registered in system metadata as:
68 Registering SPJs in NonStop SQL/MX