IBM released DB2 for LUW 9.7.2 recently and, along with numerous quality improvements, there were a number of interesting new features. In today’s post, I will talk about the “default parameters in UDFs” feature and how that helped me shrink the amount of code I needed to write for one of my UDFs.
First, I want to explain why I was writing this particular UDF. I needed a function to take the comparison functions that I described previously in my post on DB2 Stored Procedures and Defaults and apply more than one of them at a time. I wanted to create the equivalent of a boolean OR operation using UDFs.
Say you have a function named EQ and it takes two integer arguments and compares the first argument to the second and returns a 1 if they match and a 0 if they don’t. Further, say you want to perform two such comparisons, that is, make two calls to EQ, and return a 1 if either comparison succeeded and return a 0 if both comparisons failed. You could call this function ANY_OF and use it like this:
ANY_OF(EQ(1, x), EQ(2, x))
I admit that this seems like a useless function when it is just as easy to write:
EQ(1, x) OR EQ(2, x)
or even:
x = 1 OR x = 2
I do, however, have a few reasons for turning boolean operators and comparison operators into UDFs. First of all, you can make these EQ functions take any data type as their arguments, including ROW types, and second, you can make it so that instead of the EQ functions comparing the first argument to the second, they take only the first argument and produce a special object that, at a later time, you can use to perform comparisons, allowing you to do something like this:
SET special_object = ANY_OF(EQ(1), EQ(2));
CALL PERFORM_COMPARISON(special_object, x);
As odd as the above code may seem, having the ability to declare arbitrary comparison operations and use them at a later time is useful in a project I am working on, mostly because it offers type safety advantages over manipulating strings directly to do the same thing. However, we’re going to keep things simple for now and, without loss of generality, have the EQ functions take two integer arguments and return a 1 or a 0.
I would like the ANY_OF function to take between 2 and 5 arguments, so that I can write:
ANY_OF(EQ(1, x), EQ(2, x), EQ(3, x), EQ(4, x), EQ(5, x))
just as easily as I can write:
ANY_OF(EQ(1,x), EQ(2,x))
The problem is that we now need to write four different versions of the ANY_OF function, each taking a different number of arguments:
<!--start_raw-->CREATE OR REPLACE FUNCTION ANY_OF(ARG1 INTEGER,
ARG2 INTEGER)
RETURNS INTEGER
BEGIN
IF (ARG1 = 1 OR ARG2 = 1) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END@
...
CREATE OR REPLACE FUNCTION ANY_OF(ARG1 INTEGER,
ARG2 INTEGER,
ARG3 INTEGER,
ARG4 INTEGER,
ARG5 INTEGER)
RETURNS INTEGER
BEGIN
IF (ARG1 = 1 OR ARG2 = 1 OR ARG3 = 1 OR ARG4 = 1 OR ARG5 = 1) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END@<!--end_raw-->
With the introduction of default arguments in DB2 9.7.2, we need only write one version of ANY_OF:
<!--start_raw-->CREATE OR REPLACE FUNCTION ANY_OF(ARG1 INTEGER,
ARG2 INTEGER,
ARG3 INTEGER DEFAULT NULL,
ARG4 INTEGER DEFAULT NULL,
ARG5 INTEGER DEFAULT NULL)
RETURNS INTEGER
BEGIN
IF (ARG1 = 1 OR ARG2 = 1 OR
(ARG3 IS NOT NULL AND ARG3 = 1) OR
(ARG4 IS NOT NULL AND ARG4 = 1) OR
(ARG5 IS NOT NULL AND ARG5 = 1)) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END@<!--end_raw-->
We specify that all arguments after the second argument default to NULL. If an argument is NULL, we will ignore that argument. This saved me a lot of code. Hopefully, UDF default arguments can save you some code as well.