How to drop constraints in DB2

In my opinion, the quickest and easiest way to learn DB2 is through its control center. Over the number of years, Control Center has come a long way and it provides an easy way to learn DB2.

I do not want to invite a flame war on my personal view about the Control Center but to me this is a great learning tool. Why I say so is due to the fact that the Control Center wizards at the end give a button “Show Command” and when you click this button, DB2 tells you the script that it is going to run to execute a task. To me, this is a great feature and I like it very much.

For example if I have to do a redirected restore, I will go to the Control Center and follow the restore steps. At the end of wizard, I can hit “Show Command” button and copy the script, customize it and use it in my automation scripts. I do not have to scramble to find out the sequence of commands through help as it is already available to me through the Control Center. The experienced DBAs seldom use the Control Center but it does not hurt to use it especially if you are looking for quick scripts and you do not have the patience to go through the online help or manuals.

So what is the easiest way to find objects in DB2 is through the Control Center. But, a DBA is incomplete without scripts or a DBA feels powerless if there is nothing in terms of the scripts that can be run through a SSH connection to your database.

Here is my attempt to give few Stored Procedures that I have used in past. You can use it to learn about DB2 Stored Procedure language and have the power that you need quickly and easily.

Stored Procedure to drop all constraints from a schema

--#SET TERMINATOR @
DROP SPECIFIC PROCEDURE DROP_ALL_CONSTRAINTS
@
CREATE PROCEDURE DROP_ALL_CONSTRAINTS
(
   IN schemaName VARCHAR(128)
)
SPECIFIC DROP_ALL_CONSTRAINTS
MODIFIES SQL DATA NO EXTERNAL ACTION DETERMINISTIC
BEGIN
  -- Drop all foreign key constraints
  FOR theseFKeys
  AS
  SELECT 'ALTER TABLE '||TRIM(schemaName)||'.'||TRIM(tabname)||
         ' DROP CONSTRAINT '||constname AS FKConst
          FROM SYSCAT.TABCONST
         WHERE tabschema = schemaName
           AND type = 'F' 
  DO
      EXECUTE IMMEDIATE theseFKeys.FKConst;
  END FOR;
  -- Drop all DEFAULT constraints
  FOR theseDefs
  AS
  SELECT 'ALTER TABLE '||TRIM(schemaName)||'.'||TRIM(tabname)||
         ' ALTER COLUMN '||colname||' DROP DEFAULT' AS defs
         FROM SYSCAT.COLUMNS
         WHERE tabschema = schemaName
         AND   "DEFAULT" IS NOT NULL 
  DO
     EXECUTE IMMEDIATE theseDefs.defs;
  END FOR;
  -- Drop all check constraints
  FOR theseChecks
  AS
  SELECT 'ALTER TABLE '||TRIM(schemaName)||'.'||TRIM(T.tabname)||
         ' DROP CHECK '|| T.constname AS checks
        FROM SYSCAT.TABCONST T, SYSCAT.CHECKS C 
       WHERE T.tabschema = 'VIKRAM'
         AND C.type = 'C'
         AND T.constname = C.constname
         AND T.tabschema = C.tabschema
         AND T.tabschema = schemaName
  DO
     EXECUTE IMMEDIATE theseChecks.checks;
  END FOR;
  -- Drop all primary and unique keys 
  FOR theseKeys
  AS
  SELECT 'ALTER TABLE '||TRIM(schemaName)||'.'||TRIM(tabname)||
         ' DROP CONSTRAINT '||constname AS constkey
          FROM SYSCAT.TABCONST
         WHERE tabschema = schemaName
           AND type IN ('U', 'P')
  DO
     EXECUTE IMMEDIATE theseKeys.constkey;
  END FOR;
END  
@

Notes:

• If you need the script, you can download it from here.
• The statement terminator has already been defined in the script as @.
  
--#SET TERMINATOR @


  If you did not define the statement terminator, you would have to specify it specifically either from your Command Editor or from the command line script.

  For example, without above SET TERMINATOR statement, you would compile the above stored procedure as:

  
db2 connect to sample
db2 -td@ -f DROP_ALL_CONSTRAINTS.SQL
db2 connect reset


  With SET TERMINATOR command, you can compile the above SP by above command or by this simple command.

  
db2 connect to sample
db2 -f DROP_ALL_CONSTRAINTS.SQL
db2 connect reset