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

Querying SQL/MX Metadata
HP NonStop SQL/MX Installation and Management Guide—523723-004
8-29
Displaying the Attributes of a Schema
Displaying the Attributes of a Schema
This example displays most of the attributes (in other words, SCHEMA_OWNER,
SCHEMA_VERSION, SCHEMA_SUBVOLUME, and others) for the schema SAMDBCAT.
SALES:
>> set schema nonstop_sqlmx_figaro.system_schema;
--- SQL operation complete.
>> select
substring(s.schema_name, 1, 15) as name,
s.schema_owner as owner,
s.schema_version as version,
s.schema_subvolume as subvol,
case s.current_operation
when ' ' then 'NONE'
when 'UG' then 'UPGRADE'
when 'DG' then 'DOWNGRADE'
when 'RP' then 'REPLICATE'
when 'UR' then 'UNREPLICATE'
else 'UNKNOWN'
end as CUR_OP
from schemata s
where
s.schema_name = 'SALES' and
cat_uid =
(select cat_uid from catsys where cat_name='SAMDBCAT')
for read uncommitted access;
NAME OWNER VERSION SUBVOL CUR_OP
--------------- ----------- ----------- -------- -----------
SALES 65535 1200 ZSDWB9C1 NONE
Displaying the Owner of a Schema
This example queries the SCHEMATA system schema table and returns the owner ID:
>> set schema nonstop_sqlmx_figaro.system_schema;
--- SQL operation complete.
>> select substring(schema_name, 1, 30) as schema_name,
schema_owner
from schemata s, catsys c
where cat_name = 'SAMDBCAT'
and s.cat_uid = c.cat_uid
for read uncommitted access;
SCHEMA_NAME SCHEMA_OWNER
------------------------------ ------------
DEFINITION_SCHEMA_VERSION_1200 65535
INVENT 65535
PERSNL 65535
SALES 65535