2

I need a SQL statement which turns off/on foreign key checks in a DB2 database.

All I found so far is this: SET INTEGRITY FOR <your_table> OFF but that turns off the constraints only for one specific table. I could iterate through all tables in schema and call that command but that's not very effective. Is this even posible in DB2?

user2864740
  • 60,010
  • 15
  • 145
  • 220
Jardo
  • 1,939
  • 2
  • 25
  • 45

1 Answers1

2

You can generate a dynamic query for all your tables

db2 "select 'set integrity for ' || TRIM(TABSCHEMA) || '.' || TRIM(TABNAME) || ' off;' 
  from syscat.tables
  where tabschema not like 'SYS%'
  and type = 'T'" | db2 +p -t
AngocA
  • 7,655
  • 6
  • 39
  • 55
  • Thanks. What worked for me : `db2 -m -x "select 'set integrity for ' || TRIM(TABSCHEMA) || '.' || TRIM(TABNAME) || ' off;' from syscat.tables where tabschema not like 'SYS%' and type = 'T'" | sed 's/[[:blank:]]*$//' | db2 +p -v` * The first `db2` command doesn't output the number of rows * Had to remove whitespaces at the end of each generated query – Ludovic Ronsin Jun 11 '20 at 16:50
  • How do you return it back on? The OFF works fine on its own but ON triggers an exception. The documentation is about as clear as concrete. – G_V Jun 23 '20 at 18:09
  • Never mind, this is some command line voodoo and not SQL – G_V Jun 25 '20 at 07:54