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 (SPECIFIC VZ_DATETIME_1PAR) and a timestamp (SPECIFIC VZ_DATETIME_2PAR).

Function itself works fine, but the cost of invoking these UDF’s are huge. Also, if I am not mistaken, it is forcing a table scan for each function execution.

I've been working on the ETL processes where I need to use such a function 5 or 6 times per statement, and it is killing our servers. I cannot index source JDE data.

Any suggestions are welcome. Thank you


DROP SPECIFIC FUNCTION VZ_DATETIME_1PAR
;


CREATE FUNCTION VZ_GET_JDE_DATETIME (
P_DATE NUMERIC(8, 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 X_DATE DATE ;
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 ;
SET X_DATE = CAST ( NULL AS DATE ) ;
IF P_DATE > 0 THEN
SET X_DATE = DATE ( CHAR ( P_DATE + 1900000 ) ) ;
END IF ;
RETURN X_DATE ;
END ;


DROP SPECIFIC FUNCTION VZ_DATETIME_2PAR
;

CREATE FUNCTION VZ_GET_JDE_DATETIME (
P_DATE NUMERIC(8, 0) ,
P_TIME DECIMAL(8, 0) )
RETURNS TIMESTAMP
LANGUAGE SQL
SPECIFIC VZ_DATETIME_2PAR
DETERMINISTIC
READS SQL DATA
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
NOT FENCED
BEGIN
DECLARE L_SECONDS DECIMAL ( 8 , 0 ) ;
DECLARE L_TIME CHAR ( 6 ) ;
DECLARE L_TEMP CHAR ( 6 ) ;
DECLARE X_DATE TIMESTAMP ;
DECLARE INVALIDDATE CONDITION FOR '22007' ;
DECLARE EXIT HANDLER FOR INVALIDDATE
BEGIN
RETURN CAST ( NULL AS TIMESTAMP ) ;
SIGNAL SQLSTATE '01HDI' SET MESSAGE_TEXT = 'Invalid date' ;
END ;
SET X_DATE = CAST ( NULL AS TIMESTAMP ) ;
IF P_DATE IS NOT NULL THEN
IF P_DATE > 0 THEN
SET X_DATE = CAST ( DATE ( CHAR ( P_DATE + 1900000 ) ) AS TIMESTAMP ) ;
SET L_SECONDS = 0 ;
IF P_TIME IS NOT NULL THEN
IF P_TIME > 0 THEN
SET L_TIME = SUBSTR ( TRIM ( CONCAT ( '000000' , CHAR ( INT ( P_TIME ) ) ) ) , LENGTH ( TRIM ( CONCAT ( '000000' , CHAR ( INT ( P_TIME ) ) ) ) ) - 5 , 6 ) ;
SET L_TEMP = SUBSTR ( L_TIME , 1 , 2 ) ;
SET L_SECONDS = CAST ( L_TEMP AS DECIMAL ) * 3600 ;
SET L_TEMP = SUBSTR ( L_TIME , 3 , 2 ) ;
SET L_SECONDS = L_SECONDS + CAST ( L_TEMP AS DECIMAL ) * 60 ;
SET L_TEMP = SUBSTR ( L_TIME , 5 , 2 ) ;
SET L_SECONDS = L_SECONDS + CAST ( L_TEMP AS DECIMAL ) ;
END IF ;
END IF ;
SET X_DATE = X_DATE + L_SECONDS SECONDS ;
END IF ;
END IF ;
RETURN X_DATE ;
END ;
 
Back
Top