DB2 UDF...need to eliminate IF statement

[chigasakigaijin]

I have a UDF I would like setup on a DB2 mainframe server. We have a test version of the database located on a windows server. I have been testing my UDF with this. So far, it works exactly as it should:

CREATE FUNCTION DATE2SQL (DATEI DATE)
            RETURNS DATE
            LANGUAGE SQL
BEGIN atomic
           DECLARE RESULT DATE;
           IF CAST(DATEI AS VARCHAR(10)) < '1753-01-01'
                           THEN SET RESULT =  '1753-01-01' ;
           ELSE SET RESULT = DATEI;
           END IF;
           RETURN RESULT;
END@

What it does: Pull a date from DB2, if it's pre-Jan. 1, 1753, return Jan. 1, 1753, or else return the date as is (be it null, July 3, 2005, whatever).

Why it does it: Because I'm importing the db2 database into MS SQL server, where dates pre-1753 are not accepted.

What's the problem: The problem is I have been told this UDF, while it works on the windows test box, will not function when it's rolled out to the mainframe, because of the IF statement.

What can I do to replace the IF statement so that the UDF functions on the mainframe, and will still do what I need it to do.

Any help would be greatly appreciated!

chigasakigaijin

[docdiesel]

Hi,

I guess you are using this UDF to export your data, in something like

SELECT date2sql(mydate) FROM ...

before importing it to M$-SQL? Then you easily could forget about the UDF and use a CASE in your SELECT statement:

SELECT
  CASE WHEN mydate < '1753-01-01'
  THEN '1753-01-01'
  ELSE mydate
  END
FROM
  mytable ...

Is this some help for you?

Regards, Bernd

[chigasakigaijin]

Thanks for the reply!

I'm just trying to do this efficiently, because of the size of this project (about 200 tables, about 2000 fields), so

SELECT 
date2sql(fieldname1) as fieldname1, 
date2sql(fieldname2) as fieldname2, 
date2sql(fieldname3) as fieldname3
FROM tablename

is a lot less hassle than

SELECT
CASE WHEN CAST(fieldname1 as varchar(10)) < '1753-01-01'
THEN '1753-01-01'
ELSE fieldname1
END as fieldname1
CASE WHEN CAST(fieldname2 as varchar(10)) < '1753-01-01'
THEN '1753-01-01'
ELSE fieldname2
END as fieldname2
CASE WHEN CAST(fieldname3 as varchar(10)) < '1753-01-01'
THEN '1753-01-01'
ELSE fieldname3
END as fieldname3
FROM tablename

So I'd like to get the UDF functioning on the mainframe (by replacing the IF statement.) I just don't know anything about UDF programming in DB2, and I don't have access to anyone in my organization who can help me....the big question is, how can I replace an IF statement in a DB2 UDF?

Thanks again for your reply!

[docdiesel]

Hi,

sorry, I'm not a big expert in UDFs. Nevertheless, I think that the CASE way is faster than using the UDF. (Imho this should be worth a test.) If you want your export calls to be just a "SELECT * FROM ...", you could create some views on base of the "SELECT ... CASE ..." statements.

Regards,

Bernd

[chigasakigaijin]

After doing some research, I found that you can use CASE statements in DB2 UDF's without any corresponding SELECT statement, so it works just like an IF statement.

Original Code (IF statement):

CREATE FUNCTION DATE2SQL (DATEI DATE)
 RETURNS DATE
 LANGUAGE SQL
BEGIN atomic
 DECLARE RESULT DATE;
 IF CAST(DATEI AS VARCHAR(10)) < '1753-01-01'
 THEN SET RESULT =  '1753-01-01' ;
 ELSE SET RESULT = DATEI;
 END IF;
RETURN RESULT;
END@
 

Updated Code (CASE statement):

CREATE FUNCTION DATE2SQL (DATEI DATE)
 RETURNS DATE
 LANGUAGE SQL
BEGIN atomic
 DECLARE RESULT DATE;
 SET RESULT =  (CASE
 WHEN (CAST(DATEI AS VARCHAR(10)) < '1753-01-01') 
 THEN '1753-01-01'
 ELSE DATEI
 END); 
RETURN RESULT;
END@

This solves my problem, whew! Thanks for the suggestions!/

chigasakigaijin