User-Defined Functions by Examples | Intelligent Enterprises of the 21st Century

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)

Note

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)

Note

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)))

Tip

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).

Note

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.

Tip

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.

Note

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:

If you have to order the values returned by the table function, you must specify it in the calling SELECT statement as illustrated in the previous figures. ORDER BY cannot be specified inside the table function body.
The function is called in the FROM clause of a SELECT statement, and the table function must be cast to a table type by using the TABLE() function. Furthermore, you must alias the table returned by the table function (such as emp as shown previously).

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.

Note

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.

Tip

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:

It invokes the SQL procedure on Line (2).
It generates customized error code and user-friendly error message at the IF...ELSE block on Line (3).
It returns the department name to the application on Line (4).

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.

Note

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.