Constraints information in the database catalog in DB2

Table 1. Constraints information in the database catalog

Catalog viewView columnDescriptionQuery example
SYSCAT.CHECKS
Contains a row for each table check constraintdb2 select constname, tabname, text from syscat.checks
SYSCAT.COLCHECKS
Contains a row for each column that is referenced by a table check constraintdb2 select constname, tabname, colname, usage from syscat.colchecks
SYSCAT.COLUMNSNULLSIndicates whether a column is nullable (Y) or not nullable (N)db2 select tabname, colname, nulls from syscat.columns where tabschema = 'DELSVT' and nulls = 'N'
SYSCAT.CONSTDEP
Contains a row for each dependency of a constraint on some other objectdb2 select constname, tabname, btype, bname from syscat.constdep
SYSCAT.INDEXES
Contains a row for each index.db2 select tabname, uniquerule, made_unique, system_required from syscat.indexes where tabschema = 'DELSVT'
SYSCAT.KEYCOLUSE
Contains a row for each column that participates in a key defined by a unique, primary key, or foreign key constraintdb2 select constname, tabname, colname, colseq from syscat.keycoluse
SYSCAT.REFERENCES
Contains a row for each referential constraintdb2 select constname, tabname, refkeyname, reftabname, colcount, deleterule, updaterule from syscat.references
SYSCAT.TABCONST
Contains a row for each unique (U), primary key (P), foreign key (F), or table check (K) constraintdb2 select constname, tabname, type from syscat.tabconst
SYSCAT.TABLESPARENTSNumber of parent tables of this table (the number of referential constraints in which this table is a dependent)db2 "select tabname, parents from syscat.tables where parents > 0"
SYSCAT.TABLESCHILDRENNumber of dependent tables of this table (the number of referential constraints in which this table is a parent)db2 "select tabname, children from syscat.tables where children > 0"
SYSCAT.TABLESSELFREFSNumber of self-referencing referential constraints for this table (the number of referential constraints in which this table is both a parent and a dependent)db2 "select tabname, selfrefs from syscat.tables where selfrefs > 0"
SYSCAT.TABLESKEYUNIQUENumber of unique constraints (other than primary key) defined on this tabledb2 "select tabname, keyunique from syscat.tables where keyunique > 0"
SYSCAT.TABLESCHECKCOUNTNumber of check constraints defined on this tabledb2 "select tabname, checkcount from syscat.tables where checkcount > 0"

Comments

Popular posts from this blog

Read Images from a xlsx file using Apache POI

Read Excel using Apache POI - Exception in thread "main" org.apache.poi.poifs.filesystem.OfficeXmlFileException:

Struts 2 : Warning :No configuration found for the specified action: 'Login.action' in namespace: '/'