1  
   2  -- All FKs will be enabled/disabled, PKs not affected
   3  -- usage: 
   4  --        enforce -> db2 call SET_CONSTRAINTS('YES')
   5  --        not enforced -> db2 call SET_CONSTRAINTS('NO')
   6  
   7  -- To check if referential integrity check is enforced:
   8  -- select * from syscat.tabconst where tabschema='DB2ADMIN' and TYPE='F'
   9  
  10  drop procedure SET_CONSTRAINTS!
  11  create procedure SET_CONSTRAINTS (ENFORCE VARCHAR(3))
  12  LANGUAGE SQL
  13  BEGIN
  14  declare v_tabname varchar(128);
  15  declare v_constname varchar(128);
  16  declare v_rows integer;
  17  declare v_alter_table_sql varchar(256);
  18  declare tmp_cursor cursor for SELECT tabname, constname FROM syscat.references WHERE TABSCHEMA = CURRENT SCHEMA ORDER BY CONSTNAME,TABNAME, REFTABNAME;
  19  select count(*) into v_rows from syscat.references WHERE TABSCHEMA = CURRENT SCHEMA;
  20  open tmp_cursor;
  21  while (v_rows > 0) DO
  22  FETCH tmp_cursor INTO v_tabname, v_constname;
  23  SET v_alter_table_sql = 'alter table ' || current SCHEMA || '.' || v_tabname ||  ' alter foreign key ' || v_constname;
  24  IF (ENFORCE = 'NO') THEN 
  25      SET v_alter_table_sql = v_alter_table_sql || ' NOT ENFORCED';
  26  ELSE
  27      SET v_alter_table_sql = v_alter_table_sql || ' ENFORCED';
  28  END IF;
  29  execute immediate v_alter_table_sql;
  30  SET v_rows = v_rows - 1;
  31  end while;
  32  close tmp_cursor;
  33  END!