Re: Changing an SQL table (Best practices)

Keith,

Your last section reminded me of something.  Someone on the list wrote me 
a stored procedure that has an embedded existence check.  Instead of DROP 
TABLE you could use that.  Here's the code.  It was written for procedures 
and functions, but it could be modified for tables and views.

This way you wouldn't have to rely on ignoring potentially hazardous 
severity levels.

create procedure ROUTINES/DRPCODE ( in lib char(10), in name char(10), in
type char(1) )

 LANGUAGE SQL MODIFIES SQL DATA

 S: BEGIN

   Declare ct      Int Default 0;

   Declare tsql     char(45);

   select count(*) into ct from sysibm/ROUTINES

    where trim(SPECIFIC_SCHEMA)=trim(lib)

      and trim(SPECIFIC_NAME)=trim(name);

   IF (ct > 0 and type = 'P') then

     set tsql = 'drop Procedure ' || trim(lib) || '/' || trim(name);

   END IF;

   IF (ct > 0 and type = 'F') then

     set tsql = 'drop Function ' || trim(lib) || '/' || trim(name);

   END IF;

   IF (ct > 0) then

     PREPARE drp from tsql;

     EXECUTE drp;
  END IF;
END S; 

Rob Berendt