SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Querying SQL/MX Metadata
HP NonStop SQL/MX Installation and Management Guide—523723-004
8-44
Displaying Partition Information
Displaying Partition Information
Topics in this subsection:
•
Displaying All Partitions for a Table or Index on page 8-44
•
Displaying Attributes of a Partition on page 8-45
The PARTITIONS table, located in the schema
DEFINITION_SCHEMA_VERSION_version-number of each catalog, describes all
partitions in a catalog. For detailed information about all metadata tables, see
Table 8-1 on page 8-6 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_1200;
--- 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_figaro.system_schema.schemata
where
schema_name = 'SALES' and
cat_uid =
(select cat_uid
from nonstop_sqlmx_figaro.system_schema.catsys
where cat_name = 'SAMDBCAT'
)
)
for read uncommitted access;
OBJECT_NAME SYSTEM DATA_SOURCE FILE_SUFFIX
----------------------- -------- ----------- ------------------
CUSTOMER \FIGARO $DATA08 ZSDWB9C1.PDC9M400
CUSTOMER \FIGARO $DATA09 ZSDWB9C1.WWK9M400
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