Troels Arvin wrote:
> Hello,
>
> Picking up on an old thread available at http://groups.google.com/group/
> comp.databases.ibm-db2/browse_frm/thread/b2d4cc44f3e4e734 concerning how
> to create functionality to return a DATE-typed value from a valid date-
> string, but NULL for invalid date strings:
>
> On Feb 10, 10:19 pm, I wrote:
>> I finally managed to get things working this way:
>>
>> -- ================================================== ================
>> -- Called by the "castalesce_date" function; not intented to
>> -- be called directly
>> CREATE PROCEDURE castalesce_date_check_(IN strval VARCHAR(100))
>> LANGUAGE SQL
>> DETERMINISTIC
>> NO EXTERNAL ACTION
>> READS SQL DATA
>> BEGIN
>> DECLARE retval INT DEFAULT 1;
>> DECLARE dateval DATE;
>> DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET retval=-1;
>> SET dateval=DATE(strval);
>> RETURN retval;
>> END@
> [... cut function which calls the above procedure ...]
>
> It turned out that this procedure could misbehave in certain situations:
> When I upgraded a DB2 LUW generation 8.1 from 32 bit FP 14 to 64 bit FP
> 15, is seems that our STMTHEAP was suddenly too low, and the DATE(strval)
> could throw a "SQL0101N The statement is too long or too complex".
> However, the SQL01010N was masked by the declared CONTINUE HANDLER, and
> the procedure returned an innocently looking 0, even if the input string
> was actually a perfectly fine date string. Not good.
>
> I've created a safer version which seems to work well. It's part of
> http://troels.arvin.dk/db/db2/code/db2-date-massage.sql and returns 0
> only if specific string-to-date related exceptions happened; it re-throws
> all other exceptions.
>
> Now, if only DB2 LUW had an ALTER PROCEDURE which were capable of
> altering an SQL/PSM procedure definition, I'd be happy. However, since I
> have numerous (mostly indirectly) dependent functions/triggers/views, so
> I have much work ahead of me :-(
> (Will Viper 2 for LUW get DB2-for-z/OS'es ALTER PROCEDURE which seems to
> be able to change a procedure's definition?)
>
No. The problem you are describing is not limited to procedures
therefore a procedure specific solution would be too narrow.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab