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

Displaying Partition Information
Topics in this subsection:
• “Displaying all Partitions for a Table or Index” (page 135)
• “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 109) 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,
substring(p.system_name, 1, 15) as system,
p.data_source,
p.file_suffix
from partitions p, objects o
where
o.object_name_space = 'TA' and
o.object_name = 'CUSTOMER' and
o.object_uid = p.object_uid and
o.schema_uid =
(select schema_uid
from nonstop_sqlmx_<sys name>.system_schema.schemata
where
schema_name = 'SALES' and
cat_uid =
(select cat_uid
from nonstop_sqlmx_<sys name>.system_schema.catsys
where cat_name = 'SAMDBCAT'
)
)
for read uncommitted access;
FILE_SUFFIX
------------------
DATA_SOURCE
-----------
SYSTEM
--------
OBJECT_NAME
-----------------------
ZSDS53DQ.P81LGV00$SYSTEM\DMR15CUSTOMER
You can also use the MXCI SHOWDDL command to display the approximate CREATE TABLE
statement that might have created the partitions in the table. Or, you can use the MXCI SHOWLABEL
command with the DETAIL option to display a list of partitions for a table or an index. For examples
of the SHOWDDL and SHOWLABEL commands, see the SQL/MX Reference Manual.
Displaying Attributes of a Partition
This example displays attributes for the partitions for the table SAMDBCAT.SALES.CUSTOMER. To
display additional fields, select different fields from the PARTITIONS table:
>> set schema samdbcat.definition_schema_version_<schema version>;\
--- SQL operation complete.
>> select p.data_source,
p.max_ext,
substring(p.first_key, 1, 10) as first_key,
case p.partition_status
Displaying Partition Information 135