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

Managing SPJs in NonStop SQL/MX
HP NonStop SQL/MX Guide to Stored Procedures in Java—523727-004
6-11
Showing the Procedure Label
Showing the Procedure Label
The procedure label is used internally by NonStop SQL/MX to track privileges on an
SPJ. NonStop SQL/MX creates one procedure label for each SPJ. The procedure label
must exist and be available for an SPJ to be invoked successfully. Knowing the
locations of the procedure labels is particularly important when you are using SPJs in a
distributed database environment. For more information, see Using SPJs in a
Distributed Database Environment on page 6-18.
Showing the Procedure Label of One SPJ
To show the location of the procedure label for a specific SPJ, use the SHOWDDL
command. For example, enter this command in MXCI:
SHOWDDL PROCEDURE samdbcat.sales.lowerprice;
The SHOWDDL command returns information about the procedure label in the
LOCATION clause:
For more information about the SHOWDDL command, see Showing the Syntax of an
SPJ on page 6-12.
Showing the Procedure Labels of All SPJs in a Catalog
To show the locations of procedure labels for all SPJs in a user catalog of the
database, query the OBJECTS, SCHEMATA, and REPLICAS metadata tables:
SELECT SUBSTRING(schema_name,1,8) AS "SCHEMA",
SUBSTRING(object_name,1,15) AS "PROCEDURE",
system_name, data_source, file_suffix
FROM catalog.definition_schema_version_vernum.objects ot,
nonstop_sqlmx_node.system_schema.schemata st,
catalog.definition_schema_version_vernum.replicas rt
WHERE ot.object_type = 'UR'
AND ot.schema_uid = st.schema_uid
AND ot.object_uid = rt.object_uid;
The schema version number vernum for NonStop SQL/MX Releases 2.0 and 2.1 is
1200.
CREATE PROCEDURE SAMDBCAT.SALES.LOWERPRICE
(
)
EXTERNAL NAME 'Sales.lowerPrice ()'
EXTERNAL PATH '/usr/mydir/myclasses'
LOCATION \KINGPIN.$TX0115.ZSDX7KT4.SL9FSB00
LANGUAGE JAVA
PARAMETER STYLE JAVA
MODIFIES SQL DATA
NOT DETERMINISTIC
ISOLATE
;
--- SQL operation complete.