SQL/MX 3.1 Installation and Management Guide (H06.23+, J06.12+)

Querying SQL/MX Metadata
HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001
8-48
Displaying Partition Information
Displaying Partition Information
Topics in this subsection:

Displaying All Partitions for a Table or Index on page 8-48

Displaying Attributes of a Partition on page 8-49
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
Figure 8-2
on page 8-7 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;
OBJECT_NAME SYSTEM DATA_SOURCE FILE_SUFFIX
----------------------- -------- ----------- ------------------
CUSTOMER \DMR15 $SYSTEM ZSDS53DQ.P81LGV00
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