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>
