Db2 – set integrity not enforced in DB2

db2referential-integrity

I am a bit confused with the SET INTEGRITY concept in DB2, why we can OFF the set integrity check to put the table in PENDING state ? does that mean that we can neglect the constraint definitions on the table and insert invalid rows into table arbitrarily ? if that is the case why we create the constraint at the first place ?

further more, there is options to turn on and off access. I am further confused, does that mean even before the integrity of table data being checked, we can allow the data to be read accessed ? is that same as saying we allow dirty read here?

+another question is, if that integrity check is put off, how to bring the table out of pending state ? does this always requires manual intervention?

Best Answer

Simple answer do not mix with your other assumption. I hope you know primary key and foreign key relation for eg:

  • Primary key in a table is nothing but a column that is unique + not null where as foreign key is referring to that primary key table column.

  • If data inserting into foreign key table column that not matches to primary key it is nothing but duplicate record and it'll not insert with error throw.

  • In DB2 Load it'll not check duplicate/dependencies between two tables (primary key and foreign key table) bypass records and load data.

DB2 recommends checking dependency/duplicate record in terms of set integrity check after Load, if no duplicate/dependency found it'll take out your table from check integrity and if found you can create another table to put duplicate records into that and bring out from check integrity.

Query to check tables in check integrity after Load:

db2 "select 'set integrity for '||tabschema||'.'||tabname||' immediate checked ;' 
from syscat.tables where tabschema='<schemaname for eg:HEDW' and not (status='N'
and access_mode='F')"

Command to bring out from check integrity:

db2 set integrity for HEDW    .EMPLY_BON_TIME immediate checked
DB20000I  The SQL command completed successfully.
Related Question