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

Querying SQL/MX Metadata
HP NonStop SQL/MX Installation and Management Guide—544536-007
8-17
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
2.0
(c) Copyright 2003 Hewlett-Packard Development Company, LP.
>> set schema
nonstop_sqlmx_figaro.definition_schema_version_1200;
--- 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_figaro.system_schema.schemata
where
schema_name = 'SYSTEM_DEFAULTS_SCHEMA' and
cat_uid =
-- get catalog uid for CAT
(select cat_uid
from nonstop_sqlmx_figaro.system_schema.catsys
where cat_name = 'NONSTOP_SQLMX_FIGARO'
)
)
for read uncommitted access;
TABLE_NAME SYSTEM DATA_SOURCE FILE_SUFFIX
------------------ ------- ----------- -----------------
SYSTEM_DEFAULTS \FIGARO $DATA08 ZSDA984F.FPJCX800
--- 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
NonStop SQL/MX MXTOOL Utility 2.0
(c) Copyright 2003 Hewlett-Packard Development Company, LP.