Examples of scalar UDF, table UDF, and procedure invocation inside UDFs in this section will show you more DB2 UDF features.
A Simple Scalar UDF Example
The example in Figure 9.5 demonstrates a simple UDF, which will trim the blanks off a string from both sides.
Figure 9.5. A simple UDF example.
CREATE FUNCTION trim_both ( p_var VARCHAR(100) ) --(1) RETURNS VARCHAR(100) --(2) LANGUAGE SQL SPECIFIC trim RETURN LTRIM(RTRIM(p_var)) --(3)
A similar built-in function, trIM, does exist on iSeries. However, the example is provided only as an illustration.
The UDF uses two DB2 built-in functions: LTRIM and RTRIM, which trims the blanks off the string from the left and the right side, respectively.
This UDF simply takes one VARCHAR input variable on Line (1) and routes the parameter to the LTRIM and RTRIM functions on Line (3). The function is defined with RETURNS VARCHAR(100) on Line (2), which is a single value and, by definition, a scalar function.
To test the UDF in Figure 9.5, you can issue the following SQL statement:
SELECT trim_both(' abc ') FROM SYSIBM.SYSDUMMY1 1 ------------------------- abc 1 record(s) selected.
The result is that the blanks at both sides are trimmed off.
A Complex Scalar UDF Example
Now that you have a good understanding of UDFs, here is a more complex example. Consider a scenario in which your application frequently retrieves the department name to which an employee belongs. The employee table, however, only contains the department code for employees, and you don't want to write your queries to join the employee table and the department table every time the department name is needed. To simplify this task, a UDF can be created that takes the employee number as a parameter and returns the department name. Figure 9.6 presents code for a function that would satisfy these requirements.
Figure 9.6. A scalar UDF that returns a department name, given an employee number (for LUW and iSeries only).
CREATE FUNCTION deptname(p_empid VARCHAR(6)) --(1) RETURNS VARCHAR(30) --(2) LANGUAGE SQL SPECIFIC deptname --(3) d: BEGIN ATOMIC --(4) DECLARE v_department_name VARCHAR(30); DECLARE v_err VARCHAR(70); SET v_department_name = ( SELECT d.deptname FROM department d, employee e --(5) WHERE e.workdept=d.deptno AND e.empno= p_empid); SET v_err = 'Error: employee ' || p_empid || ' was not found'; IF v_department_name IS NULL THEN SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT=v_err; --(6) END IF; RETURN v_department_name; END d --(7)
The example in Figure 9.6 does not work for zSeries because compound statements (BEGIN...END) are not supported.
In Figure 9.6, the SQL UDF deptname takes an employee ID as its parameter on Line (1). If you looked at the DDL for the employee table, you would notice that the type for the EMPNO column is actually CHAR(6). The function parameter, however, is defined as VARCHAR(6) because DB2 assumes VARCHAR types to resolve a UDF call. If you defined the input parameter as CHAR(6), every time you called the function you would have to cast the input parameter to CHAR(6) as follows (which is highly inconvenient):
SELECT * FROM department d WHERE d.deptname=deptname(CAST ('000060' AS CHAR(6)))
Use VARCHAR types for character string input in UDF definitions.
On Line (2), the function is defined to return a single value of type VARCHAR(30) which, by definition, makes it a scalar function.
In our first example, the function body was a single line. To use multiple lines of code in the UDF, the body of the function is wrapped with BEGIN ATOMIC on Line (4) and END on Line (7).
On LUW, the atomic compound statements must be used in the UDF body.
To get the department name, the employee and department tables are joined on the department code (deptno and workdept columns) on Line (5), and further filtered by the employee number (which is unique) to guarantee that at most one row will be returned. Note that the SET statement is used to accomplish this rather than SELECT INTO. On LUW, SELECT INTO is currently not supported in dynamic compound statements, but using the SET statement works just as well. For more information, refer to Appendix B, "Inline SQL PL for DB2 UDB for Linux, UNIX, and Windows." On iSeries, the SELECT INTO is supported.
As a best practice, avoid using variable names that are the same as column names within queries. Within a query, DB2 always resolves column names first before evaluating variable and parameter values. Consider what would happen if the input parameter was called empno instead of p_empid and replaced on Line (5) in the previous code sample with the SELECT statement:
SELECT d.deptname FROM department d ,employee e WHERE e.workdept=d.deptno AND e.empno= empno
Because of the order in which DB2 evaluates column and variable values, the previous query is equivalent the following query, which is incorrect:
SELECT d.deptname FROM department d ,employee e WHERE e.workdept=d.deptno AND 1=1
If you use the variable naming scheme that has been used throughout the book, (the p_ prefix for parameter names and the v_ prefix for local variable names), you will not run into this problem.
Do not use the column names as variable names. DB2 always resolves column names first before evaluating variable and parameter values.
Looking at the SELECT statement on Line (5), there is a possibility that an invalid employee number could be passed as a parameter. The SELECT statement will then return no values and v_department_name will be null. To better handle the scenario, SIGNAL SQLSTATE is used on Line (6) to throw an error if the employee ID is not found. The SIGNAL SQLSTATE code is optional because, had it been left out, the function would simply return null which may also be reasonable depending on your needs.
The scalar SQL UDF can be used in a SELECT column list, in a predicate, or as part of an expression, as shown in Figures 9.7, 9.8, and 9.9.
Figure 9.7. Scalar UDF in a SELECT column.
SELECT e.empno ,e.firstnme ,e.lastname ,deptname(e.empno) department_name FROM employee e WHERE e.empno='000060' EMPNO FIRSTNME LASTNAME DEPARTMENT_NAME ------ ------------ --------------- ------------------------------------------- 000060 IRVING STERN MANUFACTURING SYSTEMS 1 record(s) selected.
Figure 9.8. Scalar UDF in a predicate.
SELECT * FROM department d WHERE d.deptname=deptname('000060') DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION ------ ----------------------------- ------ -------- ---------------- D11 MANUFACTURING SYSTEMS 000060 D01 - 1 record(s) selected.
Figure 9.9. Scalar UDF as part of an expression.
SELECT deptname('000060') || ' department' FROM SYSIBM.SYSDUMMY1 1 ----------------------------------- MANUFACTURING SYSTEMS department 1 record(s) selected.
DB2 on zSeries supports the same UDF usages. However, the examples in Figures 9.7 to 9.9 do not work on zSeries because the particular UDF is not supported.
A Table UDF Example (LUW and iSeries)
Table functions return entire tables and are used in the FROM clause of a SELECT. Suppose you want to have a table function that dynamically returns a result set of all employees in a given department. Use a table UDF that takes a single parameter representing the department ID.
To build the table function, the resulting table structure needs to be determined. In this example, the following should be returned: the employee number, last name, and first name of employees. To ensure that you don't encounter incompatible data type errors, first look at what data types are used for the employee table. From the CLP on LUW, enter the following SQL command:
DESCRIBE TABLE employee Column Type Type name schema name Length Scale Null ------------------------------ --------- ------------------ -------- ----- ---- EMPNO SYSIBM CHARACTER 6 0 No FIRSTNME SYSIBM VARCHAR 12 0 No MIDINIT SYSIBM CHARACTER 1 0 No LASTNAME SYSIBM VARCHAR 15 0 No WORKDEPT SYSIBM CHARACTER 3 0 Yes PHONENO SYSIBM CHARACTER 4 0 Yes HIREDATE SYSIBM DATE 4 0 Yes JOB SYSIBM CHARACTER 8 0 Yes EDLEVEL SYSIBM SMALLINT 2 0 No SEX SYSIBM CHARACTER 1 0 Yes BIRTHDATE SYSIBM DATE 4 0 Yes SALARY SYSIBM DECIMAL 9 2 Yes BONUS SYSIBM DECIMAL 9 2 Yes COMM SYSIBM DECIMAL 9 2 Yes 14 record(s) selected.
From this table description, you can determine that the returned table of our table function will have the following definition:
CREATE TABLE ... (empno CHAR(6), lastname VARCHAR(15), firstnme VARCHAR(20))
The table function can now be created with the following code shown in Figure 9.10.
Figure 9.10. Example of a user-defined table function.
CREATE FUNCTION getEmployee(p_dept VARCHAR(3)) RETURNS TABLE --(1) (empno VARCHAR(6), lastname VARCHAR(15), firstnme VARCHAR(12)) --(2) LANGUAGE SQL SPECIFIC getEmployee -- LUW and iSeries --DISALLOW PARALLEL -- iSeries RETURN --(3) SELECT e.empno, e.lastname, e.firstnme FROM employee e WHERE e.workdept=p_dept
In Figure 9.10, the UDF is defined to return a table on Line (1), and will have the definition previously composed using the column types found in the employee table on Line (2).
The body of this query is quite simple; the result of a SELECT statement that retrieves all employees for a given department is returned on Line (3).
After the previous table function has been created, it can be used in queries such as the following in Figure 9.11:
Figure 9.11. Query the simple table UDF example.
SELECT * FROM TABLE(getEmployee('E21')) AS emp ORDER BY lastname EMPNO LASTNAME FIRSTNME ------ --------------- ------------ 000340 GOUNOT JASON 000330 LEE WING 000320 MEHTA RAMLAL 000100 SPENSER THEODORE 4 record(s) selected.
Note the following information from Figure 9.11:
The example in Figure 9.10 shows the simplest case of a table function. It is only useful if all the data you need already exists in the table. What if you want to build some of your business logic into the table function? Assume now you received a new request to insert a default record to the employee table if the input department number does not exist. Figure 9.12 shows how you can build this business logic in an SQL table function.
Figure 9.12. Example of a complex table UDF.
CREATE FUNCTION getEmployee2(p_dept VARCHAR(3)) RETURNS TABLE (empno VARCHAR(6), lastname VARCHAR(15), firstnme VARCHAR(12)) LANGUAGE SQL SPECIFIC getEnumEmployee2 -- LUW and iSeries MODIFIES SQL DATA -- (1) --DISALLOW PARALLEL -- iSeries ge2: BEGIN ATOMIC DECLARE v_cnt int DEFAULT 0; -- (2) SET v_cnt = (SELECT COUNT(*) FROM employee WHERE workdept = p_dept); -- (3) IF (v_cnt = 0) THEN INSERT INTO employee -- (4) (empno, firstnme, midinit, lastname, workdept, edlevel) VALUES ('000000', 'No Record', 'N', 'New Department', p_dept, 0); END IF; RETURN --(5) SELECT e.empno, e.lastname, e.firstnme FROM employee e WHERE e.workdept=p_dept; END ge2
Figure 9.12 is a modified table UDF based on the function in Figure 9.10. To be able to modify the employee table as the business logic required, the MODIFIES SQL DATA clause has to be included in the function header on Line (1). Otherwise, you will receive an SQL error.
On LUW: SQL0374N The "MODIFIES SQL DATA" clause has not been specified in the CREATE FUNCTION statement for LANGUAGE SQL function "DB2ADMIN.GETENUMEMPLOYEE2" but an examination of the function body reveals that it should be specified. LINE NUMBER=26. SQLSTATE=428C2 On iSeries (shortened): SQL State: 2F002 Vendor Code: -577 Message: [SQL0577] Modifying SQL data not permitted.
A local variable v_cnt is declared on Line (2) and used on Line (3) to check for any record in the employee table for the department whose number is passed in as the input parameter. If there is no record found, a default record with dummy names will be inserted into the employee table on Line (4) before the content of the table is returned on Line (5). Note that because the SELECT statement after the RETURN statement on Line (5) is now part of the compound statement, a semicolon is required at the end of the statement as the delimiter. If you compare this code snippet with Figure 9.10, you will notice that the SELECT statement is not followed by a semicolon.
Test the table function using a similar query as shown in Figure 9.11; you should get the same result set. However, if you query with a new department number, you will see a dummy row being returned. These two sample queries are shown in Figure 9.13.
Figure 9.13. Query the complex table UDF example.
SELECT * FROM TABLE(getEmployee2('E21')) AS emp EMPNO LASTNAME FIRSTNME ------ --------------- ------------ 000100 SPENSER THEODORE 000320 MEHTA RAMLAL 000330 LEE WING 000340 GOUNOT JASON 4 record(s) selected. SELECT * FROM TABLE(getEmployee2('111')) AS emp EMPNO LASTNAME FIRSTNME ------ --------------- ------------ 000000 New Department No Record 1 record(s) selected.
If the iSeries sample database is being used, you will need to drop the red foreign key constraint on the employee table for the example in Figure 9.13 to work.
Invoking SQL Procedures in UDFs (LUW and iSeries Only)
On LUW and iSeries, it is possible to invoke SQL procedures from UDFs. This is particularly useful on LUW, because it provides a way to overcome limitations of inline SQL PL. The ability to invoke SQL procedures allows you to use any DB2 SQL PL features in your UDFs directly or indirectly.
On LUW, use SQL procedures to work around inline SQL PL limitations in SQL UDFs.
The example in Figure 9.6 only supports simple error handling. Any SQL error will force the function to stop and the standard DB2 SQL error codes return to the application. The application has to be able to analyze and handle DB2-specific SQL error codes. This could be inconvenient if your application works with multiple DBMSs.
To make this transparent to the application, a simple UDF can be used as a wrapper, as shown in Figure 9.14. It calls an SQL procedure where all business logic is implemented (demonstrated in Figure 9.15), captures errors returned from the stored procedure, and returns a user-defined error code.
Figure 9.14. Example of an SQL UDF invoking an SQL procedure.
CREATE FUNCTION deptname2 (p_empid VARCHAR(6)) RETURNS VARCHAR(30) LANGUAGE SQL SPECIFIC deptname2 READS SQL DATA -- (1) d2: BEGIN ATOMIC DECLARE v_department_name VARCHAR(30); DECLARE v_error INT; CALL deptname_sp (p_empid, v_department_name, v_error); -- (2) IF (v_error = 1) THEN -- (3) SIGNAL SQLSTATE '80001' SET MESSAGE_TEXT='The employee is not found'; ELSEIF (v_error= 2) THEN SIGNAL SQLSTATE '80002' SET MESSAGE_TEXT='The department is not found'; ELSEIF (v_error= 3) THEN SIGNAL SQLSTATE '80003' SET MESSAGE_TEXT='Duplicate department numbers'; ELSEIF (v_error= 4) THEN SIGNAL SQLSTATE '80004' SET MESSAGE_TEXT='Other fatal errors'; END IF; RETURN v_department_name; -- (4) END d2
Because the business logic is moved to the supporting SQL stored procedure, the UDF shown in Figure 9.14 is only a wrapper. The UDF serves three purposes:
The READS SQL DATA option on Line (1) is the default value. It is spelled out here to make it clear that the data access restrictions in the UDF and the procedure are at the same level. More details on the data access are discussed in the later section of this chapter.
Figure 9.15. Use an SQL procedure to support an SQL UDF.
CREATE PROCEDURE deptname_sp ( IN p_empid VARCHAR(6), OUT p_department_name VARCHAR(30), OUT p_error INT) LANGUAGE SQL SPECIFIC deptname_sp READS SQL DATA -- (1) ds: BEGIN -- Declare variables DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE v_cnt INT; -- Declare condition handlers DECLARE EXIT HANDLER FOR SQLEXCEPTION -- (2) SET p_error = 4; DECLARE EXIT HANDLER FOR SQLSTATE '21000' -- (3) SET p_error = 3; DECLARE EXIT HANDLER FOR NOT FOUND -- (4) SET p_error = 2; DECLARE EXIT HANDLER FOR SQLSTATE '99999' -- (5) SET p_error = 1; -- Procedure logic SET p_error = 0; SET p_department_name = ''; SELECT COUNT(*) -- (6) INTO v_cnt FROM employee WHERE empno = p_empid; IF (v_cnt = 0) THEN SIGNAL SQLSTATE '99999'; -- (7) END IF; SELECT d.deptname -- (8) INTO p_department_name FROM department d, employee e WHERE e.workdept=d.deptno AND e.empno= p_empid; END ds
Because a UDF with the READS SQL DATA option cannot call a procedure with the MODIFIES SQL DATA option, the READS SQL DATA option on Line (1) of Figure 9.15 is required. More details on the data access are discussed in the later section of this chapter.
The business logic is moved from the SQL UDF to the supporting SQL procedure on Line (8). Because the SQL procedures support the complete set of SQL PL, more sophisticated error handlings are implemented. Four error handlers are declared on Lines (2) to (5) to provide more details on exactly what goes wrong if an error happens.
The extra SELECT statement on Line (6) and the SIGNAL statement on Line (7) are used to check if the employee exists. If the employee does not exist, the SELECT statement on Line (6) sets the v_cnt to 0, which will raise SQLSTATE '99999' on Line (7). The raised SQLSTATE is captured by EXIT HANDLER on Line (5), which returns customized error code 1 to the calling UDF for further process.
Only if the employee exists does the SELECT statement on Line (8) execute. If the statement returns no rows, you know it is caused by the department table. The EXIT HANDLER on Line (4) for the NOT FOUND condition captures the error and returns error code 2 to the calling UDF for further process. Please note that due to the validation of the employee table on Lines (6) and (7), the NOT FOUND condition can only be caused by the department table.
DB2 does not allow more than one row returned in a SELECT ... INTO ... statement such as the statement on Line (8). If more than one department name is returned for one department number, DB2 will not simply pick the first one. DB2 will raise an SQL error instead.
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. SQLSTATE=21000
This error is captured by an exit handler on Line (3). The EXIT HANDLER on Line (2) simply captures all other SQL errors. If the error handler on Line (2) is not declared, unhandled SQL errors will cause the SQL procedure to stop and error code to be returned to the UDF. Because the UDF in Figure 9.14 cannot handle the error code either, the UDF is stopped in turn. The DB2 error code is further passed to your application by the UDF.
Invoke the deptname2 UDF with a valid employee ID, and the result is a valid department number in the sample database.
SELECT deptname2('000010') FROM SYSIBM.SYSDUMMY1 1 ------------------------------ SPIFFY COMPUTER SERVICE DIV. 1 record(s) selected.
If a nonexisting employee ID is used, a more meaningful customized error message tells you exactly what happened.
SELECT deptname2('123456') FROM SYSIBM.SYSDUMMY1 1 ------------------------------ SQL0438N Application raised error with diagnostic text: "The employee is not found". SQLSTATE=80001
Some extra invalid data are needed to see other newly implemented error messages. Invoking the UDF with the employee number of a newly inserted employee who has an invalid department number produces the following:
INSERT INTO employee (empno, firstnme, midinit, lastname, workdept, edlevel) VALUES ('010010', 'No Record', 'N', 'New Record', 'FAK', 0) DB20000I The SQL command completed successfully. SELECT deptname2('010010') FROM SYSIBM.SYSDUMMY1 1 ------------------------------ SQL0438N Application raised error with diagnostic text: "The department is not found". SQLSTATE=80002
Some data setup is required if you are interested in seeing how the new implementation works with duplicate department numbers:
INSERT INTO employee (empno, firstnme, midinit, lastname, workdept, edlevel) VALUES ('010020', 'No Record', 'N', 'New Record', 'AAA', 0) DB20000I The SQL command completed successfully. INSERT INTO department (deptno, deptname, admrdept) VALUES ('AAA', 'Duplicate Name', 'A00') DB20000I The SQL command completed successfully. INSERT INTO department (deptno, deptname, admrdept) VALUES ('AAA', 'Duplicate Name', 'A00') DB20000I The SQL command completed successfully. SELECT deptname2('010020') FROM SYSIBM.SYSDUMMY1 1 ------------------------------ SQL0438N Application raised error with diagnostic text: "Duplicate department numbers". SQLSTATE=80003
For other considerations of invoking SQL procedures in UDFs and triggers, refer to the later section of this chapter.
If the iSeries sample database is being used, in order for the series of examples above to work you will need to drop the red foreign key on the employee table, drop the q_<schema>_department_deptno_00001 primary key constraint on the department table, and drop the xdept1 unique index on the department table.