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-7
Displaying Information About SPJs
Displaying Information About SPJs
To manage SPJs in NonStop SQL/MX, you might need to gather information about the
SPJs that are already registered in the database. For more information, see:
•
Listing the SPJs in a Catalog on page 6-7
•
Showing Privileges on the SPJs on page 6-8
•
Showing the Procedure Label on page 6-11
•
Showing the Syntax of an SPJ on page 6-12
The first three topics suggest querying SQL/MX system metadata. For information
about system metadata tables, see the SQL/MX Reference Manual.
Listing the SPJs in a Catalog
To display the names of all SPJs in a user catalog of the database, query the
OBJECTS, SCHEMATA, and CATSYS system metadata tables and store the results in
a log file, if desired.
For example:
1. In an MXCI session, enter the LOG command so that MXCI writes the output of the
query to a log file:
LOG spj_list.txt;
2. In the same MXCI session, enter this query to list all the SPJs in a catalog of the
database:
SELECT SUBSTRING(cat_name,1,25) AS "CATALOG",
SUBSTRING(schema_name,1,25) AS "SCHEMA",
SUBSTRING(object_name,1,25) AS "PROCEDURE"
FROM catalog.definition_schema_version_vernum.objects ot,
nonstop_sqlmx_node.system_schema.schemata st,
nonstop_sqlmx_node.system_schema.catsys ct
WHERE ot.object_type = 'UR'
AND ot.schema_uid = st.schema_uid
AND st.cat_uid = ct.cat_uid;
The schema version number vernum for NonStop SQL/MX Releases 2.0 and 2.1
is 1200.
3. To end logging, enter the LOG command without the name of the log file:
LOG;