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

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.schemat ast,
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, 2.1, and 2.2 is
1200.
3. To end logging, enter the LOG command without the name of the log file:
LOG;
The query returns the catalog, schema, and procedure names of all the SPJs in the catalog, as
shown:
CATALOG SCHEMA PROCEDURE
------------------------- ------------------------- --------------------
SAMDBCAT PERSNL ADJUSTSALARY
SAMDBCAT SALES DAILYORDERS
SAMDBCAT SALES LOWERPRICE
SAMDBCAT SALES MONTHLYORDERS
SAMDBCAT SALES TOTALPRICE
--- 5 row(s) selected.
Showing Privileges on the SPJs
To display the ownership and privileges for all the SPJs in a user catalog of the database, query
the TBL_PRIVILEGES, OBJECTS, and SCHEMATA 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_prvlgs.txt;
2. In the same MXCI session, enter this query to return ownership and grantor information for
the SPJs in a catalog of the database:
SELECT SUBSTRING(schema_name,1,8) AS "SCHEMA",
SUBSTRING(object_name,1,15) AS "PROCEDURE",
object_owner, grantor, grantor_type, grantee
FROM catalog.definition_schema_version_vernum.objects ot,
nonstop_sqlmx_node.system_schema.schemata st,
catalog.definition_schema_version_vernum.tbl_privileges pr
WHERE ot.object_type = 'UR'
AND ot.schema_uid = st.schema_uid
AND ot.object_uid = pr.table_uid;
The schema version number vernum for NonStop SQL/MX Releases 2.0, 2.1, and 2.2 is
1200.
For the output, see Interpreting Ownership and Grantor Informationntor Information (page 90).
3. Enter this query to return the privileges that grantees have on the SPJs in a catalog of the
database:
Displaying Information About SPJs 89