Maxim Ness
Active Member
Dear Friends,
I’ve been struggling to increase the execution performance of the following code, which is basically a date conversion function from Julian to iSeries DB2 date .
Function itself works fine, but the cost of invoking this UDF is huge. Also upon function execution it is forcing a table scan for each function call.
I would expect a source table to be scanned ones just to locate date, but not 6 or 7 times to locate each date field.
Any suggestions are welcome. Thank you
CREATE FUNCTION VZ_GET_JDE_DATETIME (
P_DATE NUMERIC(6, 0) )
RETURNS DATE
LANGUAGE SQL
SPECIFIC VZ_DATETIME_1PAR
DETERMINISTIC
READS SQL DATA
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
NOT FENCED
BEGIN
DECLARE INVALIDDATE CONDITION FOR '22007' ;
DECLARE EXIT HANDLER FOR INVALIDDATE
BEGIN
RETURN CAST ( NULL AS DATE ) ;
SIGNAL SQLSTATE '01HDI' SET MESSAGE_TEXT = 'Invalid date' ;
END ;
RETURN DATE ( CHAR ( P_DATE + 1900000 ) ) ;
END ;
I’ve been struggling to increase the execution performance of the following code, which is basically a date conversion function from Julian to iSeries DB2 date .
Function itself works fine, but the cost of invoking this UDF is huge. Also upon function execution it is forcing a table scan for each function call.
I would expect a source table to be scanned ones just to locate date, but not 6 or 7 times to locate each date field.
Any suggestions are welcome. Thank you
CREATE FUNCTION VZ_GET_JDE_DATETIME (
P_DATE NUMERIC(6, 0) )
RETURNS DATE
LANGUAGE SQL
SPECIFIC VZ_DATETIME_1PAR
DETERMINISTIC
READS SQL DATA
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
NOT FENCED
BEGIN
DECLARE INVALIDDATE CONDITION FOR '22007' ;
DECLARE EXIT HANDLER FOR INVALIDDATE
BEGIN
RETURN CAST ( NULL AS DATE ) ;
SIGNAL SQLSTATE '01HDI' SET MESSAGE_TEXT = 'Invalid date' ;
END ;
RETURN DATE ( CHAR ( P_DATE + 1900000 ) ) ;
END ;