Julian date conversion function for iSeries DB2

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 ;
 
Table scans is weird. Let me know how this function works for you.

Code:
drop function myuser.JToDate;

CREATE function myuser.JToDate (JDEDate decimal(8,0))
    returns date
    language sql
    deterministic
    no external action
begin atomic
   if JDEDate = 0 then
      return null;
   else
      return date(char(1900000 + Round(JDEDate, -3) + (JDEDate - Round(JDEDate, -3))));
   end if;
end;

select JToDate(108098), JToDate(0), JToDate(imupmj)
from F4101;
 
Back
Top