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

Displaying the State of Indexes for a Table
This example displays the state of the indexes for the table SAMDBCAT.SALES.CUSTOMER:
>> set schema samdbcat.definition_schema_version_<schema version>;
--- SQL operation complete.
>> select
substring (o1.object_name, 1, 40) as index_name,
case a.valid_data
when 'Y' then 'populated'
else 'unpopulated'
end as index_status
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;
INDEX_STATUS
------------
INDEX_NAME
----------------------------------------
populatedXCUSTNAM
Displaying Partition Information
Topics in this subsection:
• “Displaying all Partitions for a Table or Index” (page 134)
• “Displaying Attributes of a Partition” (page 135)
The PARTITIONS table, located in the schema DEFINITION_SCHEMA_VERSION_version-number
of each catalog, describes all partitions in a catalog. 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 partition must access information from the CATSYS,
SCHEMATA, and OBJECTS tables, and from the PARTITIONS table.
Displaying all Partitions for a Table or Index
This example displays a list of the partitions for the table SAMDBCAT.SALES.CUSTOMER:
>> set schema samdbcat.definition_schema_version_<schema version>;
--- SQL operation complete.
>> select substring(o.object_name, 1, 23) as object_name,
134 Querying SQL/MX Metadata