I am trying to query the unique indices from SYSCAT
tables for some user tables using the query below which uses 4 catalog tables (INDEXES
, INDEXCOLUSE
, TABCONST
, CONSTDEP
). I realized all indices are there in INDEXES
and INDEXCOLUSE
tables but many of them are missing in TABCONST
and CONSTDEP
tables.
Is there any reason for having data missing in TABCONST
and CONSTDEP
tables? Is there some procedure to refresh the data from those catalog tables?
SELECT
T.TABSCHEMA AS TABLE_SCHEMA,
T.TABNAME AS TABLE_NAME,
CASE T.TYPE
WHEN 'F' THEN 'Foreign Key'
WHEN 'I' THEN 'Functional Dependency'
WHEN 'K' THEN 'Check'
WHEN 'P' THEN 'Primary Key'
WHEN 'U' THEN 'Unique'
END AS TYPE,
I.INDSCHEMA AS INDEX_SCHEMA,
I.INDNAME AS INDEX_NAME,
U.COLNAME AS COLUMN_NAME,
U.COLSEQ AS COLUMN_ORDINAL,
CASE U.COLORDER
WHEN 'A' THEN 'Ascending'
WHEN 'D' THEN 'Descending'
WHEN 'I' THEN 'Included (unordered)'
END AS COLUMN_SORRING
FROM
SYSCAT.TABCONST T
INNER JOIN
SYSCAT.CONSTDEP C
ON T.CONSTNAME = C.CONSTNAME
INNER JOIN
SYSCAT.INDEXES I
ON
C.BSCHEMA = I.INDSCHEMA AND
C.BNAME = I.INDNAME
INNER JOIN
SYSCAT.INDEXCOLUSE U
ON
I.INDSCHEMA = U.INDSCHEMA AND
I.INDNAME = U.INDNAME
WHERE
TRIM(UPPER(T.TABSCHEMA)) = 'MYSCHEMA' AND
TRIM(UPPER(T.TABNAME)) = 'MYTABLE'AND
C.BTYPE = 'I'
--INDICES ONLY
ORDER BY
T.TABSCHEMA,
T.TABNAME,
I.INDSCHEMA,
I.INDNAME
;
Edited question's title: Forgot to mention this is all about UNIQUE index instead of just index!
When you create an index directly it is not a constraint:
CREATE UNIQUE INDEX myindex on mytab(mycol)
However, if you create or alter a table and use UNIQUE
to declare a single column or several as such, then it is a constraint.