 | |
#21
 
| |||
| |||
|
Re: CASTLESCE -
02-11-2007
, 12:28 AM
|
On Feb 10, 9:19 pm, Troels Arvin <tro... (AT) arvin (DOT) dk> wrote: On Fri, 09 Feb 2007 08:22:38 -0500, Serge Rielau wrote: My first thought was to create a UDF where the UDF catches cast-errors via a declared continue-handler. But using a continue-handler seems to be possible only in procedures(?). What's my best option? Try using a procedure, or in some other way? You can use a SQL UDF that calls a procedure.. You won't get a prize for performance though.... 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@ CREATE FUNCTION castalesce_date(strval VARCHAR(100)) RETURNS DATE DETERMINISTIC NO EXTERNAL ACTION READS SQL DATA BEGIN ATOMIC DECLARE retval INT; -- The following is needed because declaration of SQLEXECPTION handlers -- isn't allowed in UDFs: CALL castalesce_date_check_(strval); GET DIAGNOSTICS retval = DB2_RETURN_STATUS; IF retval = 1 THEN RETURN DATE(strval); ELSE RETURN NULL; END IF; END@ -- Note: The result from the stored procedure is transferred via the -- return value. First, I tried doing it via an OUT parameter, but -- setting the OUT parameter required me to declare the procedure -- MODIFIES SQL DATA. Calling such a procedure would make the caller -- (the UDF) have status MODIFIES SQL DATA, as well. And it _seems_ -- (not to be read anywhere in the DB2 docs, at least not where I -- could find it) UDFs can't be declared with MODIFIES SQL DATA -- unless it's a "table function" (what I need is a "scalar" function, -- as the above). -- ================================================== ================ If you have the opportunity, Is there any difference in performance between castalesce_date and castalesce_date2 below? I've been told that there are performance issues doing a call to a procedure as above (Serges comment indicate this as well), and I'm curious whether this is true in your case. I could generate some data on my own, but I have a feeling that you've got more than enough :-) |
solution runs faster than mine, god dam it :-)
/Lennart
[...]
#22
| |||
| |||
|
|
Tonkuma<tonkuma (AT) jp (DOT) ibm.com> 02/10/07 6:17 AM Correction to handle '9999-12-xx': |
, CASE
WHEN SUBSTR(CharDate,1,4) BETWEEN '0001' AND '9999'
AND SUBSTR(CharDate,6,2) BETWEEN '01' AND '12' THEN
CASE
WHEN CharDate
BETWEEN SUBSTR(CharDate,1,8)||'01'
AND COALESCE(CHAR(DATE(NULLIF(SUBSTR(CharDate,
1,8),'9999-12-')||'01') + 1 MONTH - 1 DAY)
,'9999-12-31') THEN
DATE(CharDate)
ELSE NULL
END
ELSE NULL
END ValidDate
FROM (VALUES '0000-00-00'
, '2006-00-15'
, '2006-13-15'
, '2006-02-45'
, '2005-02-29'
, '2008-02-29'
, '2006-01-00'
, '2006-01-01'
, '2006-04-31'
, '2006-12-00'
, '2006-12-31'
, '9999-11-15'
, '9999-12-31'
) TestData(CharDate)
#23
| |||
| |||
|
#24
| |||
| |||
|
#25
| |||
| |||
|
|
Tonkuma<tonkuma (AT) jp (DOT) ibm.com> 02/13/07 2:08 AM Character representation of DATE value is defferent by teritory code |
format name "ISO" in CHAR function like followings.
SELECT CharDate
, CASE
WHEN SUBSTR(CharDate,1,4) BETWEEN '0001' AND '9999'
AND SUBSTR(CharDate,6,2) BETWEEN '01' AND '12' THEN
CASE
WHEN CharDate
BETWEEN SUBSTR(CharDate,1,8)||'01'
AND COALESCE(CHAR(DATE(NULLIF(SUBSTR(CharDate,
1,8),'9999-12-')||'01') + 1 MONTH - 1 DAY, ISO)
,'9999-12-31') THEN
DATE(CharDate)
ELSE NULL
END
ELSE NULL
END ValidDate
FROM (VALUES '0000-00-00'
, '2006-00-15'
, '2006-13-15'
, '2006-02-45'
, '2005-02-29'
, '2008-02-29'
, '2006-01-00'
, '2006-01-01'
, '2006-04-31'
, '2006-12-00'
, '2006-12-31'
, '9999-11-15'
, '9999-12-31'
) TestData(CharDate)
;
#26
| |||
| |||
|
|
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 (AT) arvin (DOT) dk>
http://troels.arvin.dk/
#27
| |||
| |||
|
|
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 |
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.