DB2 UDF Exception Handling

Question

I am struggeling with exception handling of User Defined Functions (UDF) in DB2.

The starting postion of my problem is, that I want to SELECT over a table with varchar-timestamps in a specific format and convert them into timestamp-datatype.

The following SQL-Statements shows a positiv szenario. All varchar-timestamps are valid. The SELECT will succeed:

DROP TABLE TMP_TSP;
CREATE TABLE TMP_TSP (TSP_VARCHAR VARCHAR(100) NOT NULL UNIQUE);
INSERT INTO TMP_TSP VALUES ('2017-02-27');
INSERT INTO TMP_TSP VALUES ('2017-02-28');
SELECT TSP_VARCHAR, timestamp_format(TSP_VARCHAR, 'YYYY-MM-DD') TSP FROM tmp_tsp;

So far so good. But if the table contains an invalid varchar-timestamp the SELECT-Statement fails:

DROP TABLE TMP_TSP;
CREATE TABLE TMP_TSP (TSP_VARCHAR VARCHAR(100) NOT NULL UNIQUE);
INSERT INTO TMP_TSP VALUES ('2017-02-27');
INSERT INTO TMP_TSP VALUES ('2017-02-28');
INSERT INTO TMP_TSP VALUES ('2017-02-29'); -- Invalid Date!
select TSP_VARCHAR, timestamp_format(TSP_VARCHAR, 'YYYY-MM-DD') TSP from tmp_tsp;

The execution of the SELECT-Statement is interupted with the following errormessage:

[Error Code: -20448, SQL State: 22007] "2017-02-29" cannot be interpreted using format string "YYYY-MM-DD" for the TIMESTAMP_FORMAT function.. SQLCODE=-20448, SQLSTATE=22007

I am searching for a solution to SELECT those timestamps exception-safe, that means, if a varchar-timestamp is valid, it should be converted into timestamp, and if it is invalid, like '2017-02-29', null should be returned. The SELECT-Statement should have the following result:

2017-02-27 2017-02-27 00:00:00

2017-02-28 2017-02-28 00:00:00

2017-02-29 null

I tried to create an UDF which encapsulates the function timestamp_format and adds some logic for exception handling, e.g. via DECLARE CONTINUE HANDLER. Unfortunately I did not succeed on this way until now.

I am using DB2/LINUXX8664 10.5.7.

May be You can help?! Many thanks in advance!

Answer 1

I don't understand why you think you need to convert a VARCHAR column TSP_VARCHAR to a VARCHAR using TIMESTAMP_FORMAT, and I can't tell you what's wrong with your UDF, since you chose not to post either its source or errors you get, but it should look something like this:

create or replace function to_timestamp_safe (
  str varchar(100),
  fmt varchar(100)
) 
returns timestamp 
deterministic 
no external action contains sql 
begin 
  declare continue handler for sqlstate '22018' -- on conversion error
    return null; 
  return to_timestamp(str, fmt); 
end

You then need to use it in your query:

select 
  TSP_VARCHAR, 
  timestamp_format(to_timestamp_safe(TSP_VARCHAR, 'YYYY-MM-DD'), 'YYYY-MM-DD') TSP 
from tmp_tsp;

Answer 2

Thanks to mustaccio! My core-problem was that I wasn't able to execute this create-function-statement. But now I found a solution: Let the file tmp.sql contain the following statement

create or replace function to_timestamp_safe (
  str varchar(100),
  fmt varchar(100)
) 
returns timestamp 
deterministic 
no external action contains sql 
begin 
  declare continue handler for sqlstate '22007' -- on conversion error
    return null;
  return to_timestamp(str, fmt);
end
@

Then I can execute it in DB2 CLP with the following command:

db2 -td@ -vf  tmp.sql

Answer 3

This is my solution, which does not require an UDF

SELECT 
 XMLCAST(XMLQUERY('if (. castable as xs:date) then . else ()'
  PASSING '2017-02-29') AS DATE)
FROM SYSIBM.SYSDUMMY1

Using an XQUERY to test for a valid date, returning the value passed else return an empty element which converts to NULL