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

If DDL_DEFAULT_LOCATIONS default is not set, then the volume name of the SPJ is retrieved from
the =_DEFAULTS define.
If you want to control the location of the procedure label, particularly in a distributed database
environment, specify the LOCATION clause, as shown:
LOCATION \REMOTENODE.$DISKVOL.SUBVOL.PROCLABEL
You can create the procedure label only on a node where the catalog of the SPJ is visible. For
information about registering a catalog on a remote node, see the SQL/MX Installation and
Upgrade Guide andSQL/MX Management Manual.
You can also use the LOCATION clause to specify only the volume of the procedure label:
LOCATION $DISKVOL
In this case, the node is the same node where the SPJ is registered, the subvolume is the same as
the schema of the SPJ, and the file name is system generated.
For information about naming rules for the LOCATION clause, see the SQL/MX Reference Manual.
Specifying an SQL Access Mode
The SQL access mode indicates whether an SPJ performs SQL operations. Specifying the SQL
access mode for an SPJ is entirely optional. If you do not specify the access mode in the CREATE
PROCEDURE statement, the default is CONTAINS SQL, and the capability of the SPJ is MODIFIES
SQL DATA.
If the SPJ does not perform SQL operations, specify the optional NO SQL clause. Even if the SPJ
method does not perform SQL operations and the access mode is CONTAINS SQL, the SPJ still
executes successfully. However, if you specify NO SQL for an SPJ method that performs SQL
operations, NonStop SQL/MX returns an error when you try to invoke the SPJ.
To describe the types of SQL operations that an SPJ performs, specify one of these optional clauses:
CONTAINS SQL, MODIFIES SQL DATA, or READS SQL DATA. All these options have the same
capabilities as MODIFIES SQL DATA. Specify one of these options when registering a method that
contains SQL statements and that is part of the class file of a JDBC/MX program. For information
about JDBC/MX, see the JDBC Driver for SQL/MX Programmer's Reference.
Dropping an SPJ
The DROP PROCEDURE statement removes an SPJ from NonStop SQL/MX. You can issue a DROP
PROCEDURE statement from any application or interface that calls NonStop SQL/MX, such as:
• SQL/MX conversational interface (MXCI)
• Embedded SQL programs in C, C++ or COBOL
• JDBC/MX programs
• NonStop ODBC/MX clients
• NSM/web
Required Privileges for Dropping an SPJ
To issue a DROP PROCEDURE statement, you must own the SPJ or be the super ID. To determine
the ownership of an SPJ, see Showing Privileges on the SPJs (page 89).
Effect of Dropping an SPJ
Successful execution of the DROP PROCEDURE statement removes the SPJ attributes from system
metadata tables and drops the procedure label of the SPJ. The underlying Java method and any
dependent SPJ that refers to this SPJ are not affected by the DROP PROCEDURE statement. If the
DROP PROCEDURE statement fails to execute, NonStop SQL/MX does not modify system metadata
tables and does not drop the procedure label.
Dropping an SPJ 67