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

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
when 'AV' then 'AVAILABLE'
when 'UO' then 'OFFLINE'
when 'UC' then 'CORRUPT'
when 'UD' then 'DROPPED'
when 'UR' then 'RECREATED'
end as status
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'
)
Displaying Partition Information 135