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-31
Displaying the Attributes of a Schema
MVS_USED_UMD_343845152_51 CN PK OTHER
ODETAIL TA BT User Table
ODETAIL_545675672_5188 CN PK User Table
ODETAIL_793765672_5188 CN NN User Table
ORDERS TA BT User Table
ORDERS_347711272_5188 CN NN User Table
ORDERS_917521272_5188 CN PK User Table
ORDREP TA VI User Table
PARTS TA BT User Table
PARTS_386351772_5188 CN NN User Table
PARTS_836161772_5188 CN PK User Table
XCUSTNAM IX IX User Table
XORDCUS IX IX User Table
XORDREP IX IX User Table
XPARTDES IX IX User Table
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_<system name>.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 3000 ZSDS53DQ NONE
Displaying the Owner of a Schema
This example queries the SCHEMATA system schema table and returns the owner ID:
>> set schema nonstop_sqlmx_<system name>.system_schema;