SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

from nonstop_sqlmx_<sys name>.system_schema.catsys
where cat_name = 'SAMDBCAT'
)
)
order by column_number
for read uncommitted access;
COLUMN_NUMBER
-------------
COLUMN_NAME
-------------------------
0
1
CUSTNUM
CUSTNAME
2STREET
3CITY
4
5
STATE
POSTCODE
Displaying all Columns in an Index
This example displays all columns in the index SAMDBCAT.SALES.XCUSTNAM in the schema
SAMDBCAT.SALES:
>> set schema samdbcat.definition_schema_version_<schema version>;
--- SQL operation complete.
>> select substring(c.column_name, 1, 25) as column_name,
c.column_number as base_table_column,
ac.system_added_column
from access_paths ap, access_path_cols ac, cols c, objects o
where
ap.access_path_uid = o.object_uid
and ap.access_path_uid <> ap.table_uid
and c.object_uid = ap.table_uid
and o.object_name = 'XCUSTNAM'
and ac.column_number = c.column_number
and ac.access_path_uid = ap.access_path_uid
and o.schema_uid =
(select schema_uid
from nonstop_sqlmx_<sys name>.system_schema.schemata
where
schema_name = 'SALES' and
cat_uid =
(select cat_uid
from nonstop_sqlmx_<sys name>.system_schema.catsys
where cat_name = 'SAMDBCAT'
)
)
for read uncommitted access;
SYSTEM_ADDED_COLUMN
-------------------
BASE_TABLE_COLUMN
-----------------
COLUMN_NAME
-----------------------
Y
N
0
1
CUSTNUM
CUSTNAME
Displaying all Columns in a Primary Key or Unique Constraint
This example displays all columns with primary key or unique constraints for the table
SAMDBCAT.SALES.ODETAIL; two columns of this table make up the primary key:
>> set schema samdbcat.definition_schema_version_<schema version>;
>> select substring(c.column_name, 1, 15) as column_name,
c.column_number as col_num,
case t.constraint_type
when 'C' then 'CHECK'
140 Querying SQL/MX Metadata