DB2 for i 7.2 Functions, Functions, Functions

In this tip about new DB2 for i 7.2 features, I’m going to cover some noteworthy news about user-defined function (UDF) features. The feature list includes how a function name is now resolved based on casting rules, named parameters, parameter defaults and array parameters.

Function Name Resolution

From the earliest days of V4R4 when user-defined functions became available, I’ve had a BIG pet peeve. That pet peeve is how DB2 tries to figure out what function you want to use.

For a little background, remember that a function can be overloaded. That means scalar UDF ConvertDate can be defined several times in DB2 for i, as long as the parameter signature is unique:

CREATE OR REPLACE FUNCTION DEV.ConvertDate(CYMMDD_DATE DEC(7,0))
RETURNS DATE
SPECIFIC ConvertDate_CYMD
BEGIN ...

CREATE FUNCTION DEV.ConvertDate(ISO_DATE VARCHAR(8))
RETURNS DATE
SPECIFIC ConvertDate_ISO
BEGIN ...

The only restriction on overloading is that the number of input parameters must be different, or the parameters have incompatible data types. In the above example, the first function accepts a DEC(7,0) parameter and the other accepts VARCHAR(8). However, you could not create a third function overload with a single input parameter of type INT, because INT and DEC(7,0) are similar compatible types and DB2 would not have a good way to distinguish which function was intended to be used.

Internally, DB2 uses the specific name to make sure each function has a unique identifier. If a specific name is not given on the CREATE FUNCTION statement, DB2 will assign one automatically.

But the question is, when you have a column expression using a UDF like this:

SELECT ConvertDate(MyDate) ...

How does DB2 know which function to use? The answer is DB2 looks at the requested function name and the data type(s) of the parameter value(s) being passed to the function, and then it looks in its list of registered functions to see if there is a function with the same name and parameters with compatible data types. To say it again, DB2 doesn’t just look for the function by name, it has to look for the function by name and parameter signature (i.e., number of parameters and their data types).

So there were a few difficulties when DB2 tried to resolve a function name. For instance, consider function ACTION_NAME:

CREATE FUNCTION ACTION_NAME (ACTION_CODE CHAR(1))
RETURNS CHAR(1)
BEGIN...

When invoking this function prior to DB2 for i 7.2, DB2 would respond that it couldn’t find the function:

VALUES (ACTION_NAME('x'));

What gives? DB2 viewed the constant ‘x’ as a VARCHAR(1) data type, which doesn’t match the function’s expected data type of CHAR(1)! Possible solutions for this issue were to change the function’s data type from CHAR(1) to VARCHAR(1), or to CAST the constant value to the correct type when invoking the function:

VALUES (ACTION_NAME(CAST('x' AS CHAR(1)) ));

What a mess! Now, that 7.2 is here, DB2 will do the work to find the function by using the casting rules. In other words, it will check the parameter values supplied with all data type variations allowed by the casting rules when looking for a function. Now parameters with castable (but not exact) data types can be used to invoke a UDF without doing anything special.

The same can be demonstrated for SMALLINT parameters:

CREATE FUNCTION MONTH_TOTAL (MONTH SMALLINT,YEAR SMALLINT)
RETURNS DEC(17,4)
BEGIN...

This used to fail when invoked because the constant values 12 and 2012 were treated as INTs:

VALUES (MONTH_TOTAL(12,2012));

Now, of course, DB2 will process this request without a hiccup.

For more info on how DB2 resolves function names, see the Function Resolution, Promoting Data Types and Determining the Best Fit sections of the SQL Reference manual.

Named Parameters And Parameter Defaults

Like their stored procedure counterparts, function parameters can now be defined to have default values as shown in the following example. The GETPAYRATE UDF is used to lookup an employee’s variable pay rate based on quite a few pieces of information, however, most of the time not all of the parameter information is needed. The parameters are primarily there for special cases when determining a pay rate:

CREATE OR REPLACE FUNCTION zzz.GETPAYRATE (
EMPLOYEE_ID INT,
TRANS_DATE  DATE      DEFAULT CURRENT_DATE,
DEPT        CHAR(4)   DEFAULT 'SHOP',
SHIFT       SMALLINT  DEFAULT 1,
OPERATION   CHAR(4)   DEFAULT '0200',
HIRE_DATE   DATE      DEFAULT NULL,
QTY         INT       DEFAULT 1,
SCRAP_QTY   INT       DEFAULT 0,
LENGTH_SEC  INT       DEFAULT 30,
MACHINE_ID  INT       DEFAULT NULL)
RETURNS DEC(7,2)
DETERMINISTIC
BEGIN
   ...
END;

When invoking the function, each parameter can be passed a value or the “default” keyword that will tell the function to use the parameter’s default value. If a parameter has a default, it does not have to be supplied an explicit value:

-- Invoke the function and accept all 
-- of the defined default values
VALUES (dev.GETPAYRATE(
15,default,default,default,default,default,default,default,default));

-- Pass employee id - the missing parms are defaulted
VALUES (dev.GETPAYRATE(15));

Further, when using the function, parameters can be named on invocation using the => syntax. All parameters that do not have a default value must be passed a value when the function is called. Here are some examples of the different ways the function can be invoked using parameter names:

-- Pass employee id, shift and hire date
VALUES (dev.GETPAYRATE(
114,QTY=>3,MACHINE_ID=>201,HIRE_DATE=>'2010-03-01'));

-- Pass only an employee id and default shift
VALUES (dev.GETPAYRATE(EMPLOYEE_ID=>189,SHIFT=>DEFAULT));

Once a function starts getting five or more parameters, it’s usually a pain in the neck to track what parameter goes where in the list. When passing parameters by position, parameter list changes (such as deleting parameter number three in a list of five parameters) will often require all references to be changed as well. Referencing a parameter name can help someone understand the code easier and reduce maintenance when changes are made. Also, specifying a default value allows parameters to be omitted, making the function’s SQL less cluttered.

For more info on stored procedure parameter defaults and names, see my article Stored Procedure Parameter Defaults And Named Arguments In DB2 For i

ARRAY Parameter Support

A scalar function (but unfortunately not a table function) can now receive a parameter with an array data type. The following array type and UDF demonstrate this:

CREATE TYPE zzz.ArrMonthlyTotals AS DEC(13,2) ARRAY[12];

CREATE OR REPLACE FUNCTION zzz.MonthlyAmounts 
(MonthAmounts ArrMonthlyTotals)
RETURNS DEC(13,2)
BEGIN
    RETURN (SELECT SUM(MonthAmount)
              FROM UNNEST(MonthAmounts) Data(MonthAmount));
END;

As shown above, the function receives a 12 element array, SUMs the elements, and returns the final value. Arrays have increased utility in DB2 for i because they are allowed to be used with scalar functions.

These DB2 for i user-defined function enhancements will go a long way into making developers more effective when writing and maintaining code.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

RELATED STORIES

Stored Procedure Parameter Defaults And Named Arguments In DB2 For i

New in DB2 for i 7.1: Use Global Variables to Track Environment Settings

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot