Solving cyclic dependency for DB2 SET INTEGRITY

sqlcode

Solving cyclic dependency for DB2 SET INTEGRITY

By TY → Wednesday, October 14, 2015

While I am trying to resolve -668 error for DB2, the solution is to set integrity on the related tables.

However, I ended up trying to set integrity on a set of tables with cyclical dependencies. So the command 

SET INTEGRITY for <table> immediate checked

does not work.

Searching more, I found that the solution is actually the same command, but all the dependent table should be comma separated. Something like the below.

SET INTEGRITY for table_a, table_b, table_c immediate checked

Hope this helps!

DB2 SQL Error: SQLCODE=-668. Fixed with SET INTEGRITY

sqlcode

DB2 SQL Error: SQLCODE=-668. Fixed with SET INTEGRITY

By TY → Tuesday, October 13, 2015

After I migrated my DB2 data to a new machine, I encountered "DB2 SQL Error: SQLCODE=-668".

At first, I thought that I can do a table reorg to fix it.  However, when I issue the reorg command, the same error code -668 was returned.

After some searching, the solution is to set integrity for the affected tables.

1. To find all the affected tables.

select 'set integrity for '||rtrim(tabschema)||'.'||tabname||' immediate checked' from syscat.tables where status = 'C'

2. Copy and paste the generated statements into db2 console.

3. Repeat a few times to clear all the tables so that the SQL from step 1 returns no more row.

Note, you may experience cyclic dependency on your tables that you are not able to clear. You can check out this post on cyclical dependencies and set integrity for db2.