RE: SQL insert - all fields

[rob@xxxxxxxxx]

Summary:
1 - First create this one procedure.
2 - Then use this procedure in all your RUNSQLSTM's.

Detail:
1
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; 


2 - Sample usage:
CALL ROUTINES/DRPCODE('ROUTINES', 'STRIPEMAIL', 'F');
 
CREATE FUNCTION ROUTINES/STRIPEMAIL (CHAR (512)) 
RETURNS CHAR (100) 
DETERMINISTIC 
NO SQL 
RETURNS NULL ON NULL INPUT 
NO EXTERNAL ACTION ALLOW PARALLEL 
SIMPLE CALL 
EXTERNAL NAME 'ROUTINES/SRVEMAIL(STRIPEMAIL)'; 


Rob Berendt