SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

Displaying Information About SPJs
The ROUTINES table contains information about stored procedures in Java (SPJs). All queries for
information about a specific SPJs must access information from the CATSYS, SCHEMATA, and
OBJECTS tables, and from the ROUTINES table.
For more information about all metadata tables, see Figure 3 (page 108) in this manual or the
SQL/MX Reference Manual.
For more information about displaying all SPJs in a catalog and displaying the syntax of SPJs, see
the SQL/MX Guide to Stored Procedures in Java.
Displaying Index Information
Topics in this subsection:
• “Determining Whether a Table has Indexes” (page 131)
• “Displaying all Indexes for a Table” (page 132)
• “Displaying all DDL Locks on an Index” (page 132)
• “Displaying the Attributes of an Index” (page 133)
• “Displaying the State of Indexes for a Table” (page 134)
• “Displaying all Partitions for a Table or Index” (page 134)
• “Displaying all Columns in an Index” (page 139)
The OBJECTS table is the definition schema table that lists all indexes in a catalog. The
ACCESS_PATHS table, located in the same schema for each catalog, describes the physical
instances of indexes. For more information about all metadata tables, see Figure 3 (page 108) in
this manual or the SQL/MX Reference Manual.
All queries for information about a specific index must access information from the CATSYS,
SCHEMATA, and OBJECTS tables.
Determining Whether a Table has Indexes
Use the MXCI SHOWDDL command to display the approximate CREATE TABLE statement that
might have created that table, including the CREATE INDEX statements for all indexes on the table.
To obtain information about indexes, use the MXCI SHOWLABEL INDEX command. For examples
of the SHOWDDL and SHOWLABEL commands, see the SQL/MX Reference Manual.
This example (when run on the node on which the metadata is located) uses SELECT statements to
help determine whether the table SAMDBCAT.SALES.CUSTOMER has indexes:
>> set schema samdbcat.definition_schema_version_<schema version>;
--- SQL operation complete.
>> select count(*) as num_indexes
from samdbcat.definition_schema_version_<schema version>.access_paths a,
samdbcat.definition_schema_version_<schema
version>.objects o1
where
a.access_path_uid = o1.object_uid and
a.access_path_uid <> a.table_uid and
a.table_uid =
(select object_uid from
samdbcat.definition_schema_version_<schema
version>.objects o
where
o.object_name = 'CUSTOMER' and
o.schema_uid =
(select schema_uid from
nonstop_sqlmx_<sys name>.system_schema.schemata s
where s.schema_name = 'SALES' and
Displaying Information About SPJs 131