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-20
Locating System Defaults Schema Tables

In the following example on a node called \DMR15, a SELECT statement to access
information from the PARTITIONS, OBJECTS, SCHEMATA, and CATSYS tables to
display volume and subvolume of the SYSTEM_DEFAULTS table is used. Then it
uses the
ls command on that volume and subvolume to get a list of the default
schema table names. The additional files in the subvolume
$DATA08.ZSDA984F
are the user metadata tables that contain histogram information.
# mxci
Hewlett-Packard NonStop(TM) SQL/MX Conversational Interface
3.1
(c) Copyright 2003, 2004-2011 Hewlett-Packard Development
Company, LP.
>> set schema
nonstop_sqlmx_dmr15.definition_schema_version_3100;
--- SQL operation complete.
>>select substring(object_name, 1, 23) as table_name,
substring(p.system_name, 1, 15) as system,
p.data_source,
p.file_suffix
from partitions p, objects o
where
o.object_type = 'BT' and
o.object_name = 'SYSTEM_DEFAULTS' and
o.object_uid = p.object_uid and
o.schema_uid =
(select schema_uid
from nonstop_sqlmx_dmr15.system_schema.schemata
where
schema_name = 'SYSTEM_DEFAULTS_SCHEMA' and
cat_uid =
-- get catalog uid for CAT
(select cat_uid
from nonstop_sqlmx_dmr15.system_schema.catsys
where cat_name = 'NONSTOP_SQLMX_DMR15'
)
)
for read uncommitted access;
TABLE_NAME SYSTEM DATA_SOURCE FILE_SUFFIX
------------- -------- ----------- ------------------
SYSTEM_DEFAULTS \DMR15 $DATA08 ZSDA984F.M26FB700
--- 1 row(s) selected.
>> exit;
# ls /G/data08/zsda984f
bd2xt800 d96rs800 fpjcx800 hpz9p800 l2jnr800
q956v800 bd2xt801 d96rs801 fpjcx801 hpz9p801
l2jnr801 q956v801
# mxtool info \$data08.zsda984f.fpjcx800