SQL/MX 3.x Installation and Management Guide (H06.22+, J06.11+)

Querying SQL/MX Metadata
HP NonStop SQL/MX Installation and Management Guide—640325-001
8-19
Locating System Defaults Schema Tables
•
This example is more complex. It uses a SELECT statement to access information
from the PARTITIONS, OBJECTS, SCHEMATA, and CATSYS tables to display the
partition that contains the SYSTEM_DEFAULTS table. 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.0
(c) Copyright 2003, 2004-2010 Hewlett-Packard Development
Company, LP.
>> set schema
nonstop_sqlmx_dmr15.definition_schema_version_3000;
--- 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 $DATA02 ZSD5VXWZ.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