SEO

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?)

--
Regards,
Troels Arvin <troels@arvin.dk>
http://troels.arvin.dk/

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