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

EXTERNAL PATH '/usr/mydir/myclasses'
LOCATION \KINGPIN.$TX0115.ZSDX7KT4.SL9FSB00
LANGUAGE JAVA
PARAMETER STYLE JAVA
MODIFIES SQL DATA
NOT DETERMINISTIC
ISOLATE
;
--- SQL operation complete.
For more information about the SHOWDDL command, see Showing the Syntax of an SPJ (page 92).
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, 2.1, and 2.2 is 1200.
The query returns the names of the schema, procedure, node (SYSTEM_NAME), volume
(DATA_SOURCE), and the subvolume and file (FILE_SUFFIX), as shown:
SCHEMA PROCEDURE SYSTEM_NAME DATA_SOURCE FILE_SUFFIX
-------- --------------- ----------- ----------- ------------------
PERSNL ADJUSTSALARY \KINGPIN $TX0115 ZSDPK4GV.Q85DXB00
SALES DAILYORDERS \KINGPIN $TX0115 ZSDX7KT4.N71HTB00
SALES LOWERPRICE \KINGPIN $TX0115 ZSDX7KT4.SL9FSB00
SALES MONTHLYORDERS \KINGPIN $TX0115 ZSDX7KT4.G13HVB00
SALES TOTALPRICE \KINGPIN $TX0115 ZSDX7KT4.T4BGWB00
--- 5 row(s) selected.
Showing the Syntax of an SPJ
To show the CREATE PROCEDURE syntax of an SPJ as it exists in system metadata, use the
SHOWDDL command. The SHOWDDL command generates a description of the metadata for an
SPJ and displays it in an MXCI session.
Use the SHOWDDL command to display information about SPJs that you have migrated to the
system from an earlier release of NonStop SQL/MX. For more information, see the SQL/MX
Database and Application Migration Guide.
You can also use the SHOWDDL command to create a replica of an SPJ. For more information,
see Regenerating CREATE PROCEDURE Statements (page 96).
Using the SHOWDDL Command
To display information about the SPJ named ADJUSTSALARY, for example, enter this SHOWDDL
command in an MXCI session:
SHOWDDL PROCEDURE samdbcat.persnl.adjustsalary;
For the syntax of the SHOWDDL command, see the SQL/MX Reference Manual.
92 Managing SPJs in NonStop SQL/MX