SQL Programming Nugget: User-Defined Functions and Data Types

Earlier this week, I posted an entry to this blog that described a "lesson learned" experience in SQL programming (having to do with the use of a WHILE loop in a SQL stored procedure to process rows in a result set generated and returned by a nested stored procedure). Today, I'll relate another such experience to you. This time, it has to do with user-defined functions (UDFs) and associated data types. As with my previous "SQL programming nugget" post (see the link above), this one describes a situation that I encountered in a DB2 for Linux/UNIX/Windows (LUW) environment, but the same would have occurred in a DB2 for z/OS system.

OK, here's the deal: I was working on the development of a UDF that would take as an input argument a fixed-length character string of 3 bytes. Accordingly, the first part of the CREATE FUNCTION statement looked like this:

CREATE FUNCTION MYFUNC(ARG CHAR(3))

I filled in the rest (RETURNS information, variable declarations, function logic, etc.), submitted the statement, and got a message indicating successful creation of the UDF. Then, to test the UDF, I invoked it via the Command Editor, passing a 3-byte character string constant as the input argument, as follows:

SELECT MYFUNC('A01') ...
FROM ...

Execution of this SELECT statement resulted in an error message indicating that MYFUNC could not be found (SQLSTATE 42884). Huh? Not found? What do you mean, "not found?" I just created it! I checked the SYSCAT.ROUTINES catalog view, and there was my UDF. If I could find it, why couldn't DB2?

After I'd racked my brain for a while, it finally occurred to me that I might have introduced a data type mismatch when I passed to my UDF a character string constant. See, to be found by DB2 when invoked, a UDF has to be named properly in the SELECT statement (I did that), and it has to be passed the right number of input arguments (I did that), and the data type of the input argument(s) has to match, or be promotable to, the data type of the corresponding function parameter(s) (oops). I was thinking "matched data type" when I passed the character string constant 'A01', because that's a 3-byte character string and my UDF was defined as taking a CHAR(3) value as input. What I failed to consider is this: a character string constant is treated by DB2 as a varying-length value (i.e., a VARCHAR data type). That did not match the CHAR data type for the input parameter specified in the definition of my UDF. Furthermore, VARCHAR is not promotable to CHAR (to see what data types are promotable to others, refer to "Promotion of Data Types" in the DB2 for LUW SQL Reference, Volume 1, or the DB2 for z/OS SQL Reference).

To verify my understanding of what had gone wrong, I tried invoking my UDF in this manner:

SELECT MYFUNC(CHAR('A01')) ...
FROM ...

Sure enough, this worked, because the CHAR function returns a fixed-length representation of a character string. I also successfully invoked my UDF by passing to it a value from a table column that was created with a CHAR(3) data type specification (in other words, supposing that COL1 of table ABC was defined as CHAR(3), this invocation of my UDF worked: SELECT MYFUNC(COL1) FROM ABC). Finally, I created a UDF that was exactly like MYFUNC, except that the input parameter was defined as VARCHAR(3) instead of CHAR(3). When this modified UDF was invoked with a 3-character string constant passed as the input argument, it was found by DB2 and it worked just fine.

So, when you are creating a UDF, make sure that the data types of any input arguments will match - or can be promoted to - the data type as specified in the corresponding input parameter in the CREATE FUNCTION statement. Important exception to this rule: if you are using DB2 9.7 for LUW (the current release, which became generally available just last month), be aware of an enhancement with respect to function resolution (i.e., the process of finding the right function when a UDF is invoked). In a DB2 9.7 environment, if the regular function resolution process (the one I've described) fails to find an "exact match" for an invoked UDF, and if there is a function with the right name and the right number of parameters but with an inexact data type match in terms of input arguments and corresponding input parameters (as specified in the CREATE FUNCTION statement), the arguments will be converted to the data types of the parameters using the rules that apply to assignment of data values to columns. From a value-assignment perspective, VARCHAR is compatible with CHAR (refer to "Assignments and Comparisons" in the DB2 for LUW SQL Reference, Volume 1, or "Assignment and Comparison" in the DB2 for z/OS SQL Reference). That being the case, the "not found" situation that I encountered in a DB2 9.1 for LUW system when I passed a VARCHAR argument to a UDF defined with a CHAR input parameter (and the same thing would have happened in a DB2 9.5 system or a DB2 9 for z/OS environment) would NOT have occurred in a DB2 9.7 system. DB2 9.7 would have found and utilized my UDF.

Isn't that nice? One less thing to worry about. Keep those DB2 enhancements coming, IBM.