SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

>> 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
s.cat_uid =
(select cat_uid from
nonstop_sqlmx_<sys name>.system_schema.catsys c
where c.cat_name = 'SAMDBCAT' and
schema_version = <schema version>
)
)
)
for read uncommitted access;
NUM_INDEXES
--------------------
1
Displaying all Indexes for a Table
This example (when run on the node on which the metadata is located) displays all indexes for a
given table:
>> set schema samdbcat.definition_schema_version_<schema version>;
--- SQL operation complete.
>> select substring (o1.object_name, 1, 40) as index_name
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
s.cat_uid =
(select cat_uid from
nonstop_sqlmx_<sys name>.system_schema.catsys c
where c.cat_name = 'SAMDBCAT' and
schema_version = <schema version>
)
)
)
for read uncommitted access;
132 Querying SQL/MX Metadata