RE: SQL insert - all fields

Rob, that is very nice!

I have never understood why DB2 does not support ALTER or REPLACE for
procedures, triggers and views like other SQL databases, though.

Jim

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, June 16, 2005 4:20 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL insert - all fields

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
--
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Jeff Crosby" <jlcrosby@xxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces+rob=dekko.com@xxxxxxxxxxxx
06/16/2005 04:06 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: SQL insert - all fields






> A couple of improvements.  You don't really need this:
>         Declare InvalidDate Condition For '22007' ;
>         Declare Continue HANDLER for InvalidDate
>                        Set CvtDate = 00010101;

Did that.  When trying to recreate it, it said it was a duplicate.  I
had 
to
delete the old one first.

I created it via the RUNSQLSTM command.  Is that the expected way?  Is 
there
something I can put into the source to autoreplace?  I searched the SQL
reference manual and got 2942 hits which is a bit much to look through. 
<g>