The function name specified in the CREATE FUNCTION statement is limited to 18 characters on LUW and 128 characters on iSeries and zSeries. It must begin with a letter, followed by zero or more letters, digits, or underscore characters (_).

A function is said to be qualified if it is referenced by a two-part name that is made up of a schema and a function name. The restrictions for schema name on each platform include the following:

Unqualified functions are resolved in the same manner as the unqualified procedures. Refer to Chapter 2, “Basic SQL Procedure Structure,” for details.

To uniquely identify a function, DB2 relies on the function signature. A function signature is a combination of a schema name, a function name, a list of parameters, and the data types of the parameters. DB2 allows all data types to be used in SQL UDFs, with the exception of LONG VARCHAR and LONG VARGRAPHIC on LUW.

If any parameter is to be used for a UDF, you need to specify the parameter name and its data type inside the parentheses. Use a comma to separate multiple parameters. Figure 9.2 shows an example of functions with zero or more parameters.

CREATE FUNCTION functest(). . .
CREATE FUNCTION prod.sum_abc(p_type INTEGER, p_value DECIMAL). . .
CREATE FUNCTION test.getsalary(p_name VARCHAR(10), p_age SMALLINT). . .

It is valid to define more than one function with the same name in a database as long as they have unique function signatures. For example, two functions with the same name in the same schema are unique if the number of parameters or the data types of the parameters are different. These functions are known as overloaded functions. Figure 9.3 shows some examples of overloaded functions.

CREATE FUNCTION db2admin.functest(v_int INT, v_vc VARCHAR(100)) ...
CREATE FUNCTION db2admin.functest(v_double DOUBLE, v_vc VARCHAR(100)) ...
CREATE FUNCTION db2admin.functest(v_vc VARCHAR(100), v_ts TIMESTAMPS) ...
CREATE FUNCTION db2admin.functest(v_vc VARCHAR(100), v_ts TIMESTAMPS, v_si SMALLINT) ...

Functions with the same number of parameters and the same data types of the parameters are considered to have the same signature, even if the length or the precision of the parameters are different:

CREATE FUNCTION DB2ADMIN.FUNCTEST(V_VC VARCHAR(100))...
CREATE FUNCTION DB2ADMIN.FUNCTEST(V_VC VARCHAR(200))...

These functions are considered to have the same signature, even though one uses VARCHAR(100) and the other uses VARCHAR(200). They cannot be overloaded.

As you may have noticed, the functions are overloaded differently from the procedures. While functions with different number of parameters or with the same number of parameters but different parameter data types can both be overloaded, only procedures with different number of parameters can be overloaded. Refer to Chapter 2, “Basic SQL Procedure Structure,” for more details on overloaded procedures.

The type of data a UDF can return is not restricted only to a single value but also a row (LUW only) or a table of data (LUW and iSeries only). If you want to return a scalar value, simply specify its data type in the RETURNS clause, like this:

CREATE FUNCTION functest () RETURNS INTEGER . . .

For row and table functions specify the ROW or TABLE keyword in the RETURNS clause. Following that, you need to provide the name and data type of each column to be returned. Here are some, examples:

CREATE FUNCTION functest () RETURNS ROW (name VARCHAR(10), age INTEGER) . . .
CREATE FUNCTION functest () RETURNS TABLE (prod_id SMALLINT, price DECIMAL) . . .

A specific name is used to uniquely identify a UDF, and it is particularly useful when using overloaded functions. The specific name can be used with UDFs in the same manner as with the SQL procedures. Refer to Chapter 2, “Basic SQL Procedure Structure,” for more details.

On LUW and zSeries, the LANGUAGE SQL clause is optional, and if omitted it is assumed to be an SQL UDF. On iSeries, LANGUAGE SQL must be specified as the first clause following the parameter list and RETURNS clause.

This clause allows you to specify if the function is DETERMINISTIC or NOT DETERMINISTIC. A UDF is deterministic if it returns the same results for each invocation of identical input parameters. On the other hand, a UDF is not deterministic if the results depend on the input values and/or other values that may change, such as the current date or time. Whenever possible, identifying a function as DETERMINISTIC allows DB2 to perform additional optimizations to improve performance.

The default is NOT DETERMINISTIC, which is typically the case for most UDFs.

This optional clause specifies whether or not the function takes some action that changes the state of an object not managed by the database manager. By specifying NO EXTERNAL ACTION, the system can use certain optimizations that assume functions have no external impacts. The default is EXTERNAL ACTION.

This clause restricts the type of SQL statements that can be executed by the function. On top of the standard DB2 security model on database objects, this clause provides extra security control over the function body.

When CONTAINS SQL is specified, only statements that do not read or modify data are allowed in the function. Examples of such statements are DECLARE variables, SET variables, and SQL control statements.

READS SQL DATA is the default option. It can be specified if the function contains only statements that do not modify SQL data. There is one exception case where tables might not be accessible from a function even if READS SQL DATA is specified. Consider this statement:

UPDATE EMPLOYEE SET SALARY = SALARY + BONUS (EMPNO)

BONUS is a UDF defined with READS SQL DATA. SQL statements that read from the EMPLOYEE table are not allowed in the UDF. This is due to conflict operations on the table where the statement invoking the function is trying to update EMPLOYEE and BONUS only allows READ access.

For nested UDFs (and in fact nested UDFs, triggers, and procedures), data access in the nested object is allowed to be more restrictive, but will fail if the nested object is defined with a less restrictive level.

On LUW, MODIFIES SQL DATA is supported for SQL table functions only. All SQL statements supported in the dynamic compound SQL statement block in the function body are allowed. Refer to the section later in this chapter for an introduction to dynamic compound SQL statements.

On iSeries, MODIFIES SQL DATA is supported for all SQL function types.

On zSeries, MODIFIES SQL DATA is not supported.

This optional clause indicates that at function resolution time, DB2 chooses a function based on the static types (declared types) of the parameters of the function. This behavior is the default and the only value that can be specified. This clause is optional.

This clause indicates that the function will always be invoked even if its input parameters are null. This behavior is the default and is the only value that can be specified. This clause is optional.

The INHERIT SPECIAL REGISTERS option works the same with UDFs as with the SQL procedures. Refer to Chapter 2, “Basic SQL Procedure Structure,” for more details.

PREDICATES is an optional clause that is valid for scalar functions only. It allows the predicates of an SQL statement that will be using the function to exploit index extensions. An index extension is an index object for use with indexes that have structured type or user-defined type columns.

The function body contains the logic of the SQL function. It is made up of a RETURN statement or a dynamic compound SQL statement. The RETURN statement is used to return the result of a function. The syntax of the statement is shown in Figure 9.4.

>>-RETURN--+---------------------------------------------------+-><
           +-expression----------------------------------------+
           +-NULL----------------------------------------------+
           '-+-----------------------------------+--fullselect-'
             |      .-,-----------------------.  |
             |      V                          | |
             '-WITH----common-table-expression-+-'

Depending on whether the function output type is a scalar value, a row, or a table of data, different options are supported in the body of the RETURN statement.

An expression specifies a value which can be a constant, a special register, CASE expression, or another function invocation. Data type of the expression result must match what is specified in the RETURNS clause of the CREATE FUNCTION statement. An expression is supported for all three UDF types.

Unlike SQL procedures, you can return a NULL value from SQL functions. Because both the expression and NULL clauses return only a single value, they can only be specified in scalar SQL functions.

A compound statement combines one or more other SQL statements into one execution block. It is supported on LUW and iSeries only. For more information on compound statement support on LUW, refer to Appendix B, “Inline SQL PL for DB2 UDB for Linux, UNIX, and Windows.”

The example in Figure 9.5 demonstrates a simple UDF, which will trim the blanks off a string from both sides.

CREATE FUNCTION trim_both ( p_var VARCHAR(100) ) --(1)
   RETURNS VARCHAR(100)                          --(2)
   LANGUAGE SQL
   SPECIFIC trim
   RETURN LTRIM(RTRIM(p_var))                    --(3)

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.

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.

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)

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

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

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.

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.

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.

SELECT *
  FROM department d
 WHERE d.deptname=deptname('000060')


DEPTNO DEPTNAME                      MGRNO  ADMRDEPT LOCATION
------ ----------------------------- ------ -------- ----------------
D11    MANUFACTURING SYSTEMS         000060 D01      -


  1 record(s) selected.

SELECT deptname('000060') || ' department'
  FROM SYSIBM.SYSDUMMY1


1
-----------------------------------
MANUFACTURING SYSTEMS department


  1 record(s) selected.

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.

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:

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.

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.

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.

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.

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.

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.

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.

The unqualified trigger name specified in the CREATE TRIGGER statement is limited to 18 characters for LUW and 128 characters for iSeries and zSeries. It must begin with a letter, followed by zero or more letters, digits, or underscore characters (_).

A trigger is said to be qualified if it is referenced by a two-part name that is made up of a schema and a trigger name. The schema name limitation of triggers is the same as that of SQL procedures. The qualified trigger name, including the implicit or explicit schema name, must be unique.

DB2 supports triggers defined on tables on all platforms, and triggers defined on views for LUW only. The view trigger is also known as the INSTEAD OF trigger. There are two types of table triggers: BEFORE and AFTER triggers. When a trigger begins its execution because of a triggering SQL statement, the trigger is said to be activated.

A BEFORE trigger will be activated before any table data is affected by the triggering SQL statement. You would use BEFORE triggers to intercept data provided by the application to validate or supply missing values, for example. If the triggering SQL statement affects more than one row, the BEFORE trigger will be activated for every row that is affected.

A BEFORE trigger is always defined with the NO CASCADE clause. This means that operations performed within this trigger do not activate other triggers in the database. This has the implicit restriction that no INSERT, UPDATE, or DELETE statements are allowed in the body of BEFORE triggers. If you want to perform INSERT, UPDATE, or DELETE statements in a trigger body, you must define them as AFTER triggers.

AFTER triggers are activated after the triggering SQL statement has executed to completion successfully. In general, you would use AFTER triggers to post-process data. You would also use AFTER triggers if your logic required you to perform any INSERT, UPDATE, or DELETE operations because these statements are not supported in BEFORE triggers. AFTER triggers also offer additional options in how they can behave. With AFTER triggers, you can optionally define them to activate on a per-statement basis rather than on a per-row basis. This topic is discussed further in the upcoming sections in this chapter.

On LUW, an INSTEAD OF trigger is activated after the triggering SQL statement has been issued to the base view. A fundamental difference exists between a table trigger and a view trigger. For a table trigger, regardless of a BEFORE or an AFTER trigger, both the trigger actions and the triggering statements are executed eventually. For a view trigger, also known as an INSTEAD OF trigger, the trigger action is used to replace the triggering statement. In other words, the trigger action is executed instead of the triggering statement. The triggering statement is only attempted on the base view and is only used to fire the INSTEAD OF trigger. The triggering statement will never be executed.

Triggers can be defined for INSERT, DELETE, or UPDATE triggering statements. On LUW and zSeries, there is no limit to how many triggers you can define on a table. On iSeries, the limit is 300. However, it is not a good idea to define too many triggers. When more than one trigger is defined for one type of triggering statement—for example, for an INSERT statement—they are fired in the order of their creation.

On iSeries, a DELETE trigger cannot be created on a table that has a referential constraint of ON DELETE SET NULL. Similarly, an UPDATE trigger cannot be created on a table that has a referential constraint of ON UPDATE SET NULL or ON UPDATE SET DEFAULT.

On LUW, you can only define one INSTEAD OF trigger for each type of triggering statements on each view. In other words, you can only define one INSTEAD OF delete trigger, one INSTEAD OF insert trigger, and one INSTEAD OF update trigger for a view.

You should always try to specify the column name list for UPDATE triggers. If the optional column name list is not specified in an UPDATE trigger, every column of the table is implied. Omission of the column-name list implies that the trigger will be activated by the update of any column of the table. The column-name list is not supported in INSTEAD OF triggers.

REFERENCING NEW is used to define a qualifier to reference transition values supplied by INSERT and UPDATE statements. REFERENCING OLD is used to define a qualifier to reference transition data that will be discarded by UPDATE and DELETE statements. REFERENCING OLD_TABLE specifies a transition table name that identifies the set of affected rows prior to the triggering SQL operation. REFERENCING NEW_TABLE specifies a transition table name that identifies the affected rows as modified by the triggering SQL operation and by any SET statement in a BEFORE trigger that has already executed. This option can be referred to in AFTER triggers only.

Triggers defined with FOR EACH ROW will fire once for each row. Both BEFORE and AFTER triggers support activation FOR EACH ROW.

Triggers defined with FOR EACH STATEMENT will fire once for each statement only, no matter how many rows are affected in that statement. Only AFTER triggers can be defined to activate FOR EACH STATEMENT. For update and delete triggers defined with FOR EACH STATEMENT, they will be activated even if no rows are affected by the triggering UPDATE or DELETE statement. For example, if a single delete statement results in 1,000 rows deleted, an AFTER/FOR EACH STATEMENT trigger defined on that table will activate just once at the end of the entire delete operation. On the other hand, if the trigger was defined as AFTER/FOR EACH ROW, it will activate 1,000 times, once for each row affected by the triggering SQL statement.

MODE DB2SQL is simply a clause that must be included as part of the CREATE TRIGGER syntax. DB2SQL is the only mode currently supported on LUW and zSeries. For iSeries, mode DB2ROW is also supported. Triggers on iSeries defined with mode DB2ROW are activated on each row operation whereas triggers defined with mode DB2SQL are activated after all of the row operations have been activated.

On LUW, the SQL procedure statements in the body of the triggered action are implemented by inline SQL PL. The previous sections in this chapter on user-defined SQL functions have already explained the inline SQL PL and its limitations. For more information, refer to Appendix B, “Inline SQL PL for DB2 UDB for Linux, UNIX, and Windows.”

On iSeries, you can specify processing options to be used to create the trigger by using the SET OPTION clause. This clause can be specified as part of the triggered action, just before the WHEN clause.

The optional WHEN clause defines the conditions for trigger activation. You could, for example, define the trigger to activate only if certain data existed in another table. For better performance, it is recommended that you specify trigger activation conditions with the WHEN clause if it is reasonable to do so. The WHEN clause is not supported in INSTEAD OF triggers.

BEFORE triggers are activated before the triggering SQL statement executes. By using a BEFORE trigger, you have the opportunity to supply values, validate data, and even reject the triggering SQL statement according to user-defined rules. In this section, a trigger will be created to activate before an INSERT into a table. Using similar code, you can create triggers to activate before UPDATE and DELETE operations.

In the SAMPLE database, a table called cl_sched is used to store data for class schedules. Two columns in cl_sched—starting and ending—define when the class starts and ends, respectively.

A simple rule might be to assume that a class ends one hour after it begins if the ending time is not provided upon INSERT into this table. The trigger in Figure 9.17 will enforce this.

CREATE TRIGGER default_class_end
NO CASCADE BEFORE INSERT ON cl_sched      --(1)
REFERENCING NEW AS n                      --(2)
FOR EACH ROW                              --(3)
MODE DB2SQL
WHEN (n.ending IS NULL)                   --(4)
    SET n.ending = n.starting + 1 HOUR    --(5)

Figure 9.17 shows how to create a trigger called default_class_end which activates before an insert on table cl_sched on Line (1). To intercept and supply a default-ending time, the trigger needs to make reference to values provided by the triggering INSERT statement. The REFERENCING NEW AS n clause on Line (2) associates the transition values provided by the INSERT statement with the qualifier n. The ending time of the INSERT statement can then be checked and/or supplied by referring to n. ending on Lines (4) and (5).

On Line (3), FOR EACH ROW means that this trigger will activate for every row that is inserted. Therefore, if you had executed the following INSERT statement:

INSERT INTO cl_sched (class_code, day, starting)
VALUES ('DB20002', 1, '12:00'), ('DB20003', 3, '9:00')

The trigger would be activated twice, once for each row inserted, even though only a single INSERT statement was issued.

The trigger is defined to activate only when the ending time is null using the optional WHEN clause on Line (4), which ensures that the trigger only activates if a value has not been supplied.

Finally, if the trigger is activated, the ending time is supplied automatically and is set to one hour after the starting time on Line (5).

To test this trigger, execute the following SQL statement:

INSERT INTO cl_sched (class_code, day, starting)
VALUES ('DB20001', 1, '10:00')

Now, if you select all rows from cl_sched, you’ll see that the class has been automatically set to have an ending time of 11:00.

SELECT * FROM cl_sched

CLASS_CODE DAY    STARTING ENDING
---------- ------ -------- --------
DB20001         1 10:00:00 11:00:00
  1 record(s) selected.

Similarly, the trigger does not execute if an ending time is supplied:

INSERT INTO cl_sched (class_code, day, starting, ending)
VALUES ('DB20002', 2, '12:00', '15:00')

Now, selecting from the cl_sched will show that the ending time remains at 15:00.

SELECT * FROM cl_sched

CLASS_CODE DAY    STARTING ENDING
---------- ------ -------- --------
DB20001         1 10:00:00 11:00:00
DB20002         2 12:00:00 15:00:00
  2 record(s) selected.

As stated earlier, unlike BEFORE triggers, AFTER triggers allow you to use INSERT, UPDATE, or DELETE statements inside the trigger body. This would be useful if you wanted to transparently keep an audit trail of when certain events occurred.

To support the following example, connect to the sample database and execute the following DDL to create a table called audit:

CREATE TABLE AUDIT (event_time TIMESTAMP, desc VARCHAR(100))

Figure 9.18 includes a trigger that can be used to keep an audit trail of salary changes with related information such as date and time of the change, as well as the person who made the change.

CREATE TRIGGER audit_emp_sal
AFTER UPDATE OF salary ON employee
REFERENCING OLD AS o NEW AS n                         --(1)
FOR EACH ROW
MODE DB2SQL
INSERT INTO audit VALUES                              --(2)
(CURRENT TIMESTAMP, ' Employee ' || o.empno ||
' salary changed from ' || CHAR(o.salary) || ' to ' ||
CHAR(n.salary) || ' by ' || USER)

In Figure 9.18, the trigger is able to reference to both old and new values because it is defined to activate upon table updates on Line (1). Upon any change in salary for any employee, the trigger will insert into the audit table a record of when the update occurred, what the old and new values are, and who executed the UPDATE statement on Line (2). USER is a DB2 special register that holds the connection ID of the application. Refer to Chapter 2, “Basic SQL Procedure Structure,” for more information. Also, the WHEN clause in this example has been left out so that this trigger will activate unconditionally.

To test this trigger, update Theodore Spenser’s salary because he seems to be underpaid relative to other managers. To see the salaries of current managers, issue the following query:

SELECT empno, firstnme, lastname, salary FROM employee
WHERE job='MANAGER'

EMPNO  FIRSTNME     LASTNAME        SALARY
------ ------------ --------------- -----------
000020 MICHAEL      THOMPSON           41250.00
000030 SALLY        KWAN               38250.00
000050 JOHN         GEYER              40175.00
000060 IRVING       STERN              32250.00
000070 EVA          PULASKI            36170.00
000090 EILEEN       HENDERSON          29750.00
000100 THEODORE     SPENSER            26150.00

  7 record(s) selected.

To give Theodore a 15 percent raise, issue the following UPDATE statement:

UPDATE employee e SET salary=salary*1.15 WHERE e.empno= '000100';

Now you can check the status of the employee table to see Theodore’s new salary.

SELECT empno, firstnme, lastname, salary FROM employee e
WHERE e.empno='000100'

EMPNO  FIRSTNME     LASTNAME        SALARY
------ ------------ --------------- -----------
000100 THEODORE     SPENSER            30072.50

  1 record(s) selected.

Finally, verify that the salary update has been logged in the AUDIT table:

SELECT * FROM AUDIT

EVENT_TIME                 DESC
-------------------------- ----------------------------------------------------
2002-04-21-21.26.07.665000 employee 000100 salary changed from 0026150.00 to
0030072.50  by DB2ADMIN

  1 record(s) selected.

In the examples presented thus far, the trigger code bodies have only contained single SQL statements. In this section, previous examples will be extended to show you how to incorporate more complex logic using the SQL PL elements you’ve already seen in previous chapters.

Returning to the example in Figure 9.17, with the default class time suppose that some restrictions have been added with respect to when a class can be scheduled:

A trigger could be defined to disallow the INSERT, UPDATE, or DELETE on table cl_sched that violates the aforementioned rules and returns a descriptive error to the application.

If you created the trigger from the previous example, drop it before continuing with this example. Note that an ALTER TRIGGER statement does not exist. Therefore, to modify a trigger you must drop and re-create it:

DROP TRIGGER default_class_end

Figure 9.19 contains the trigger code to enforce the new rules.

CREATE TRIGGER validate_sched
NO CASCADE BEFORE INSERT ON cl_sched
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
vs: BEGIN ATOMIC                                                 --(1)
   -- supply default value for ending time if null
   IF (n.ending IS NULL) THEN                                    --(2)
      SET n.ending = n.starting + 1 HOUR;
   END IF;

   -- ensure that class does not end beyond 9PM
   IF (n.ending > '21:00') THEN                                  --(3)
      SIGNAL SQLSTATE '80000'
         SET MESSAGE_TEXT='class ending time is beyond 9pm';     --(4)
   ELSEIF (n.DAY=1 or n.DAY=7) THEN                              --(5)
      SIGNAL SQLSTATE '80001' SET MESSAGE_TEXT='class cannot
      be scheduled on a weekend';                                --(6)
   END IF;
END vs                                                           --(7)

CREATE TRIGGER validate_sched_1
NO CASCADE BEFORE INSERT ON cl_sched
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
-- supply default value for ending time if null
WHEN (n.ending IS NULL)
BEGIN ATOMIC
    SET n.ending = n.starting + 1 HOUR;
END


CREATE TRIGGER validate_sched_2
NO CASCADE BEFORE INSERT ON cl_sched
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
-- ensure that class does not end beyond 9pm
WHEN (n.ending > '21:00')
BEGIN ATOMIC
    SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT='class ending time is beyond 9pm';
END


CREATE TRIGGER validate_sched_3
NO CASCADE BEFORE INSERT ON cl_sched
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
-- supply default value for ending time if null
WHEN (n.DAY=1 or n.DAY=7)
BEGIN ATOMIC
SIGNAL SQLSTATE '80001' SET MESSAGE_TEXT='class cannot be scheduled on a weekend';
END

The trigger works by first supplying a default ending time, if it has not already been provided on Line (2). Then, it ensures that the ending time does not exceed 9PM on Line (3) and has not been scheduled for a weekend on Line (5).

Here are the highlights of the example in Figure 9.19:

To test the trigger, execute the following SQL statements:

INSERT INTO CL_SCHED (class_code, day, starting, ending)
    VALUES ('DB20005', 5, '13:00', '15:00')

By selecting from the cl_sched table, you will see the row that was just inserted. (You may see another row, as shown next, if you attempted the previous example.)

SELECT * FROM cl_sched

CLASS_CODE DAY    STARTING ENDING
---------- ------ -------- --------
DB20001         1 10:00:00 11:00:00
DB20002         2 12:00:00 15:00:00
DB20005         5 13:00:00 15:00:00
  3 record(s) selected.

Figure 9.20 displays a view based on table org in the sample database. It gives you the department count by divisions.

CREATE VIEW org_by_division
  (division, number_of_dept)
AS
  SELECT division, count(*)
  FROM org
  GROUP BY division

A simple SELECT query

SELECT * FROM org_by_division

shows the content of the view as following:

DIVISION   NUMBER_OF_DEPT
---------- --------------
Corporate               1
Eastern                 3
Midwest                 2
Western                 2

  4 record(s) selected.

In DB2, if a view contains the GROUP BY clause, the view is not updatable, which means you can not issue an UPDATE statement against the view. Try the following UPDATE statement, which attempts to change the Midwest division name to Southern division:

UPDATE org_by_division SET division='Southern' WHERE division='Midwest'

You will receive the following SQL error indicating that the operation is not supported:

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0150N The target fullselect, view, typed table, materialized query table,
or staging table in the INSERT, DELETE, UPDATE, or MERGE statement is a target
for which the requested operation is not permitted. SQLSTATE=42807

As discussed earlier, an INSTEAD OF trigger can be used to implement the UPDATE operation you want to perform on the view which is not updatable. Figure 9.21 is such an example.

CREATE TRIGGER upd_org
INSTEAD OF UPDATE
ON org_by_division
REFERENCING OLD AS o NEW AS n
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
  IF (o.number_of_dept != n.number_of_dept) THEN      -- (1)
    SIGNAL SQLSTATE '80001' SET MESSAGE_TEXT =
        'The number of department is not updatable.';
  END IF;


  UPDATE org                                          -- (2)
  SET division = n.division
  WHERE division = o.division;
END

After the trigger is created, when an UPDATE statement is issued against the view the trigger body will be executed instead. If the query is to update the number of department, it will be rejected on Line (1). It is reasonable as you cannot update the counts without adding rows into the base table. If the query is to update the division name, the UPDATE statement on Line (2) will be executed to update all corresponding rows in the org table from the old division name to the new division name.

A simple SELECT query shows the content of the base table:

SELECT * FROM org

DEPTNUMB DEPTNAME       MANAGER DIVISION   LOCATION
-------- -------------- ------- ---------- -------------
      10 Head Office    160     Corporate  New York
      15 New England    50      Eastern    Boston
      20 Mid Atlantic   10      Eastern    Washington
      38 South Atlantic 30      Eastern    Atlanta
      42 Great Lakes    100     Midwest    Chicago
      51 Plains         140     Midwest    Dallas
      66 Pacific        270     Western    San Francisco
      84 Mountain       290     Western    Denver

  8 record(s) selected.

To see the effect of the trigger, issue an UPDATE query that attempts to update the number of department. The customized error message defined in the trigger body is returned:

UPDATE org_by_division SET number_of_dept=2 WHERE division='Corporate'

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0438N Application raised error with diagnostic text: "The number of
department is not updatable.". SQLSTATE=80001

Issue the previously failed UPDATE query again to update the division name:

UPDATE org_by_division SET division='Southern' WHERE division='Midwest'

DB20000I The SQL command completed successfully.

The query was successful because of the INSTEAD OF trigger. All the query did was to activate the trigger. It was the body of the trigger that really went through. Issue the SELECT query again to exam the content of the org table:

SELECT * FROM org

DEPTNUMB DEPTNAME       MANAGER DIVISION   LOCATION
-------- -------------- ------- ---------- -------------
      10 Head Office    160     Corporate  New York
      15 New England    50      Eastern    Boston
      20 Mid Atlantic   10      Eastern    Washington
      38 South Atlantic 30      Eastern    Atlanta
      42 Great Lakes    100     Southern   Chicago
      51 Plains         140     Southern   Dallas
      66 Pacific        270     Western    San Francisco
      84 Mountain       290     Western    Denver

   8 record(s) selected.

Comparing with the previous content, you may notice that all Midwest division rows are renamed into the Southern division.

The IF...ELSE... block in the trigger body in Figure 9.18 cannot be replaced by the UPDATE OF or the WHEN clause because they are not supported in INSTEAD OF triggers.

The view trigger is a relatively new concept for DBMSs. A fundamental difference exists between the table triggers and the view triggers. Figure 9.22 is used to further illustrate the difference. It consists of a simple base table, a trivial view (which is defined the exactly the same as the base table), a simple table insert trigger, and a view insert trigger.

CREATE TABLE t_airport
( airport_code char(3) not null,
  airport_name char(50) );


CREATE VIEW v_airport
AS
  SELECT * FROM t_airport;


CREATE TRIGGER insert_t_airport
AFTER INSERT
ON t_airport
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
END;


CREATE TRIGGER insert_v_airport
INSTEAD OF INSERT
ON v_airport
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
END;

Both triggers are very similar. They both have an empty trigger body. There is no triggered action defined. Without the triggers, the following INSERT statements through the view or to the table generates the same results:

INSERT INTO t_airport VALUES ('YYZ', 'TORONTO'),
INSERT INTO v_airport VALUES ('YYZ', 'TORONTO'),

After the triggers are defined, however, the results of the INSERT statements become different. Execute the INSERT statement on the empty table:

INSERT INTO t_airport VALUES ('YYZ', 'TORONTO'),

You will see the content of the table t_airport as the following:

AIRPORT_CODE AIRPORT_NAME
------------ --------------------------------------------------
YYZ          TORONTO


  1 record(s) selected.

This result is expected. The values in the INSERT statement are all valid. The INSERT statement completed without a problem. Then the AFTER trigger is activated. Because the trigger has no action defined, it did nothing as expected.

What if you execute the INSERT statement through a view now?

INSERT INTO v_airport VALUES ('SFO', 'SAN FRANCISCO'),

You can probably expect the same behavior. After all, the two triggers look very similar. Query the table t_airport again, and you should see the following:

AIRPORT_CODE AIRPORT_NAME
------------ --------------------------------------------------
YYZ          TORONTO


  1 record(s) selected.

Nothing changed. There is no new row. Where does the record go? Is there anything wrong? Not really. Everything worked as designed. Remember if an INSTEAD OF trigger is defined, the triggering statement will only serve the pupose of activating the trigger. It will not be executed at all. Instead, the trigger body, the triggered actions, will be executed. In this example, the triggered action is not defined, to the trigger did nothing, as designed.

A slight modification of the INSTEAD OF trigger in Figure 9.21 is needed to make it work the same away as the table trigger. The result is shown in Figure 9.23.

CREATE TRIGGER insert_v_airport
INSTEAD OF INSERT
ON v_airport
REFERENCING NEW AS n                         -- (1)
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
    INSERT INTO t_airport                    -- (2)
    VALUES (n.airport_code, n.airport_name);
END

In order to behave the same as the table trigger in Figure 9.22, the INSERT statement to the base table has to be explicitly implemented on Line (2), and the REFERENCING clause on Line (1) has to be included in the header.

Both UDFs and SQL procedures can be invoked from triggers. The ability to invoke SQL procedures is particularly useful on LUW if you need to use DB2 SQL PL features that are not yet supported in the inline SQL PL subset.

An example of how triggers invoke functions and procedures is illustrated in Figures 9.24, 9.25, and 9.26.

CREATE TRIGGER insert_employee
NO CASCADE BEFORE INSERT
ON employee
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
ie: BEGIN ATOMIC
  DECLARE v_valid CHAR(1) DEFAULT 'N';

  IF (n.empno = '') THEN
    SET n.empno = new_empno();                           -- (1)
  END IF;


  CALL validate_dept(n.workdept, v_valid);               -- (2)


  IF (v_valid = 'N') THEN
    SIGNAL SQLSTATE '80001'
        SET MESSAGE_TEXT= 'Incorrect department number'; -- (3)
  END IF;
END ie

CREATE FUNCTION new_empno ( )
RETURNS CHAR(6)
LANGUAGE SQL
SPECIFIC new_empno
ne: BEGIN ATOMIC
  DECLARE v_max_empno INT;
  DECLARE v_next_empno VARCHAR(12);


  SET v_max_empno =                                           -- (1)
    (SELECT INT(MAX(empno)) FROM employee);


  SET v_next_empno =                                          -- (2)
    ( '000000' || RTRIM(CHAR(v_max_empno + 10)) );


  RETURN SUBSTR(v_next_empno, LENGTH(v_next_empno)-5, 6);     -- (3)
END ne

CREATE PROCEDURE validate_dept
  ( IN p_deptno VARCHAR(3),
    OUT p_valid CHAR(1) )
   LANGUAGE SQL
   SPECIFIC validate_dept                     -- Applies to LUW and iSeries
-- WLM ENVIRONMENT <env>                      -- Applies to zSeries
   READS SQL DATA
vd: BEGIN
  -- Declare variables
  DECLARE v_deptno CHAR(3);
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';


  -- Procedure logic
  DECLARE c_dept CURSOR FOR
    SELECT deptno FROM department;


  OPEN c_dept;

  SET p_valid = 'N';


  FETCH FROM c_dept INTO v_deptno;


  w1: WHILE ( SQLSTATE = '00000' ) DO
    IF (v_deptno = p_deptno) THEN
      SET p_valid = 'Y';
      LEAVE w1;
    END IF;


    FETCH FROM c_dept INTO v_deptno;
  END WHILE w1;


  CLOSE c_dept;
END vd

Figure 9.24 shows a BEFORE INSERT trigger that performs data validation on the input data. It creates a new employee number if the input employee number is an empty string. It validates the department number, raises an SQL error, and stops the INSERT statement if the department number is not validated. A business decision has been made to implement the logic to produce a new employee number by a function so that the function can be used by other triggers, procedures, and functions if needed. The trigger only invokes the UDF on Line (1).

Another business decision is also made to use a cursor to implement the logic of department number validation. The example only illustrates how to invoke procedures in triggers as a method to use cursors indirectly. The cursor is used in a simple example. In real life, you should only use cursors when necessary; for example, you may consider using cursors because the department table is very large and because the business logic is too complex for a query to handle. For more information, refer to Chapter 5, “Understanding and Using Cursors and Result Sets.” The trigger only invokes the SQL procedure on Line (2).

The only logic implemented in the trigger is to raise an SQL error if the department number is not valid on Line (3).

The UDF in Figure 9.25 generates a new employee number by adding 10 to the largest employee number that currently exists. The employee number is defined as CHAR(6) in the employee table. However, the contents are numeric strings. Because DB2 SQL PL is a strong typed language, explicit casting is needed. The maximum employee number on Line (1) needs to be cast into an integer for the next employee number calculation. The result is cast back to characters on Line (2). The RTRIM function is used on Line (2) to remove the trailing blanks after the conversion from an integer to a character string. Concatenating leading zeros on Line (2) and taking the rightmost six characters on Line (3) formats the employee number in the proper style.

The body of the UDF can be implemented in one line of the RETURN statement. It is implemented as shown in Figure 9.25 for better readability. In your own project, it is recommended that a numeric column is used as the IDs and that a DB2 SEQUENCE object or a DB2 IDENTITY column is used for auto-incremental IDs. The implementation in Figure 9.25 illustrates using UDFs with triggers. Using maximum value to calculate the next ID may cause concurrent problems in a real-life application. For more information on the SEQUENCE object or the IDENTITY column, refer to Chapter 3, “Overview of SQL PL Language Elements.”

The validate_dept procedure checks to see whether the input department number is correct. A 'Y' is returned for a department number found in the department table; otherwise, an 'N' is returned. A cursor is used in this simple example to illustrate how to use SQL procedures that implement SQL PL features that are not currently supported in triggers.

If an 'N' is returned to the trigger in Figure 9.24, the IF...ELSE block on Line (3) will raise an SQL error and pass a customized error message to the application.

The use of a UDF in this example allows the trigger to reuse the new employee number-generating code, which might be shared by many database objects. Implementing SQL procedures allows the trigger to use the cursor indirectly.

To test the trigger, use a new employee record with a valid employee number but an invalid department number as shown in the following code. The shown customized error message indicates that the procedure is invoked properly, because the business logic of department number checking is only implemented in the SQL procedure.

INSERT INTO employee (empno, firstnme, midinit, lastname, workdept, edlevel)
VALUES('000400', 'Firstname',  'T', 'Lastname', 'ABC', 1)

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0438N Application raised error with diagnostic text: "Incorrect department
number". SQLSTATE=80001

Now test again with a valid department number but without an employee number. A new record is successfully created. The employee number is created from the maximum existing employee number as implemented in the UDF.

INSERT INTO employee (empno, firstnme, midinit, lastname, workdept, edlevel)
VALUES('', 'Firstname', 'T',  'Lastname', 'A00', 1)
DB20000I  The SQL command completed successfully.

SELECT empno, firstnme, midinit, lastname, workdept, edlevel FROM employee

EMPNO  FIRSTNME     MIDINIT LASTNAME        WORKDEPT EDLEVEL
------ ------------ ------- --------------- -------- -------
000010 CHRISTINE    I       HAAS            A00           18
...
000340 JASON        R       GOUNOT          E21           16
000350 Firstname    T       Lastname        A00            1

  33 record(s) selected.

For other considerations of invoking SQL procedures in UDFs and triggers, refer to the next section of this chapter.

Both UDFs and SQL procedures have three data access options: CONTAINS SQL, READ SQL DATA, and MODIFIES SQL DATA, as discussed in Chapter 2, “Basic SQL Procedure Structure,” and in earlier sections within this chapter. AFTER and INSTEAD OF triggers can always modify data. BEFORE triggers cannot UPDATE, INSERT, or DELETE. For practical purposes, you can think of these two types of triggers as having a MODIFIES SQL DATA access level and BEFORE triggers as having a READ SQL DATA access level.

DB2 does not allow UDFs or SQL procedures with lower data access levels to invoke UDFs or SQL procedures with higher data access levels.

Because READS SQL DATA is the default option for UDFs but MODIFIES SQL DATA is the default option for SQL procedures, you need to use the explicit data access options if you need to invoke SQL procedures in your UDFs. This was demonstrated in Figures 9.14 and 9.15.

The MODIFIES SQL DATA option is not supported in row functions. An SQL procedure that modifies tables cannot be invoked in row functions. It, however, can be invoked in table functions and triggers.

If a procedure is invoked by either a UDF or a trigger, the ROLLBACK and COMMIT statements are not allowed in the body of the procedure, unless it is rolled back to a save point that is defined in the same procedure.

CREATE PROCEDURE show_trans_sp()
   LANGUAGE SQL
   SPECIFIC show_trans_sp                        -- applies to LUW and iSeries
-- WLM ENVIRONMENT <env>                         -- applies to zSeries
   MODIFIES SQL DATA
sts: BEGIN
  INSERT INTO cl_sched (class_code) VALUES ('AAA'),
  INSERT INTO cl_sched (class_code) VALUES ('BBB'),
  INSERT INTO cl_sched (class_code) VALUES ('CCC'),


  COMMIT;                                    -- (1)
END sts

CREATE FUNCTION show_trans_func()
RETURNS TABLE (subject CHAR(64))
LANGUAGE SQL
SPECIFIC show_trans_func
MODIFIES SQL DATA
-- DISALLOW PARALLEL                             -- applies to iSeries
stf: BEGIN ATOMIC
  CALL show_trans_sp();
  RETURN
    SELECT subject FROM in_tray;
END stf


CREATE TRIGGER show_trans_trig
AFTER INSERT ON in_tray
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
  CALL show_trans_sp();
END

CREATE PROCEDURE show_trans_sp2()
   LANGUAGE SQL
   SPECIFIC show_trans_sp2                    -- applies to LUW and iSeries
-- WLM ENVIRONMENT <env>                      -- applies to zSeries
   MODIFIES SQL DATA
sts2: BEGIN
  INSERT INTO cl_sched (class_code) VALUES ('AAA'),
  INSERT INTO cl_sched (class_code) VALUES ('BBB'),
  INSERT INTO cl_sched (class_code) VALUES ('CCC'),


  IF (1=0) THEN
    COMMIT;
  END IF;
END sts2


CREATE FUNCTION show_trans_func2()
RETURNS TABLE (subject CHAR(64))
LANGUAGE SQL
SPECIFIC show_trans_func2
-- DISALLOW PARALLEL                          -- applies to iSeries
MODIFIES SQL DATA
stf2: BEGIN ATOMIC
  CALL show_trans_sp2();
  RETURN
    SELECT subject FROM in_tray;
END stf2


CREATE TRIGGER show_trans_trig2
AFTER INSERT ON in_tray
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
  CALL show_trans_sp2();
END

CREATE PROCEDURE show_svpt_sp()
   LANGUAGE SQL
   SPECIFIC show_svpt_sp -- Applies to LUW and iSeries
-- WLM ENVIRONMENT <env> -- Applies to zSeries
   MODIFIES SQL DATA
sss: BEGIN
  SAVEPOINT svpt1 ON ROLLBACK RETAIN CURSORS;
  INSERT INTO cl_sched (class_code) VALUES ('AAA'),
  INSERT INTO cl_sched (class_code) VALUES ('BBB'),


  ROLLBACK TO SAVEPOINT svpt1;
    INSERT INTO cl_sched (class_code) VALUES ('CCC'),
END sss



CREATE FUNCTION show_svpt_func()
RETURNS TABLE (subject CHAR(64))
LANGUAGE SQL
SPECIFIC show_svpt_func
MODIFIES SQL DATA
-- DISALLOW PARALLEL -- applies to iSeries
ssf: BEGIN ATOMIC
  CALL show_svpt_sp();
  RETURN
    SELECT subject FROM in_tray;
END ssf


CREATE TRIGGER show_svpt_trig
AFTER INSERT ON in_tray
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
  CALL show_svpt_sp();
END

In Figure 9.27, the show_trans_sp procedure has a COMMIT statement at the end. A table UDF and a trigger are created to invoke the procedure.

When executed, the function and trigger produce the following error messages because of the COMMIT statement on Line (1).

SELECT * FROM TABLE(show_trans_func()) AS t

SQL0751N  Routine "DB2ADMIN.SHOW_TRANS_SP" (specific name "SQL040222031129103")
attempted to execute a statement that is not allowed.

INSERT INTO in_tray (subject) VALUES ('ABCD')

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0723N  An error occurred in a triggered SQL statement in trigger
"DB2ADMIN.SHOW_TRANS_TRI". Information returned for the error includes SQLCODE
"-751", SQLSTATE " " and message tokens
"DB2ADMIN.SHOW_TRANS_SP|SQL040222031129103". SQLSTATE=09000

Both the UDF and the trigger work if the COMMIT statement is removed. Replacing the COMMIT statement on Line (1) with a ROLLBACK statement produces the same error message.

As you might already notice, the errors produced are runtime errors. As long as the COMMIT or ROLLBACK statement is not executed, both the UDF and the trigger will work. Figure 9.28 shows the modified version of Figure 9.27, where the COMMIT statement will never be executed.

Both the UDF and the trigger work in this example. The following INSERT statement invokes the trigger, which in turn invokes the modified procedure

INSERT INTO in_tray (subject) VALUES ('ABCD')

produces the following results:

SELECT class_code FROM cl_sched


CLASS_CODE
----------
AAA
BBB
CCC


 3 record(s) selected.


SELECT subject FROM in_tray


SUBJECT
----------------------------------------------------------------
ABCD


  1 record(s) selected.

The execution of the function in Figure 9.28 will produce the same result in table cl_sched.

The examples in Figures 9.27 and 9.28 can be modified further to demonstrate how save points are supported in such SQL procedures. The modified code is shown in Figure 9.29.

Compare the result of the execution of the trigger in the following code snippet with the result of the execution of the trigger in Figure 9.28.

INSERT INTO in_tray (subject) VALUES ('ABCD'),
DB20000I The SQL command completed successfully.

SELECT class_code FROM cl_sched

CLASS_CODE
----------
CCC


 1 record(s) selected.


SELECT subject from in_tray


SUBJECT
----------------------------------------------------------------
ABCD


  1 record(s) selected.

There is now only one row left in table cl_sched as the first two rows are rolled back.

DB2 enforces a set of rules to avoid data integrity violation when an SQL procedure is invoked by a UDF or trigger. For simplicity, triggers are used in this section as examples. Please note that all discussions in this section apply to both UDFs and triggers as well as stand-alone code unless otherwise specified.

When an SQL procedure is invoked by a trigger, if both the trigger and the SQL procedure are working with the same table at the same time, the result could be unpredictable. Because the statements in a trigger and the statements in an SQL procedure invoked by that trigger can access a table at the same time, the status of the table is unknown from the perspective of the SQL procedure. Some rows of the table might have been updated by the trigger action; some might have not. Actions taken based on the partially modified rows can be incorrect. To avoid the potential read or write conflict in this type of situation, DB2 does not allow statements that conflict on any table.

The restrictions are implemented in DB2 using the concept of table access contexts. A table access context is created when the trigger invokes an SQL procedure. A separate table access context is created when the same trigger issues other SQL statements.

The following rules are enforced by DB2:

If a conflict occurs, an error (SQLCODE -746, SQLSTATE 57053) is returned to the statement that caused the conflict at the runtime.

Please do not mistake the read and write conflict restrictions by the regular row and table level data access control. DB2 controls the data access and guarantees the data integrity by isolation levels and database locking mechanism, which is beyond the scope of this book. For this section, all you need to remember is that statements that cause a conflict are not allowed. Statements that do not cause a conflict will still need to wait to receive the proper locks before they can actually read from or write to a table.

The concept of read and write conflict is illustrated by the examples in Figure 9.30.

CREATE PROCEDURE show_conflict_sp()
LANGUAGE SQL
SPECIFIC show_conflict_sp
MODIFIES SQL DATA
scs: BEGIN
  INSERT INTO cl_sched (class_code) VALUES ('AAA'), -- (1)
  INSERT INTO cl_sched (class_code) VALUES ('BBB'),
  INSERT INTO cl_sched (class_code) VALUES ('CCC'),  -- (2)
END scs


CREATE TRIGGER show_conflict_trig
AFTER INSERT ON in_tray
FOR EACH ROW MODE DB2SQL
sct: BEGIN ATOMIC
  INSERT INTO cl_sched (class_code) VALUES ('DDD'), -- (3)
  CALL show_conflict_sp();    -- (4)
END sct

In Figure 9.30, the procedure show_conflict_sp is to be called from the trigger show_conflict_trig. Both the procedure and the trigger contain SQL statements that write to the cl_sched table. When the SQL procedure is invoked on Line (4), a table access context—calling it context1 for easy referral—is created. The three INSERT statements inside the procedure body are to be executed within context1. Another table access context—context2—is created when the trigger issues the INSERT statement on Line (3). The INSERT statement on Line (3) is to be executed within context2.

Recall the three rules enforced by DB2; the third rule is violated in this example. Both context1 and context2 are trying to write to the same table cl_sched. This is not allowed.

When the trigger is executed, you will see the following error message:

INSERT INTO in_tray (subject) VALUES ('ABCD')

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0723N An error occurred in a triggered SQL statement in trigger
"DB2ADMIN.SHOW_CONFL". Information returned for the error includes SQLCODE
"-746", SQLSTATE "57053" and message tokens
"DB2ADMIN.SHOW_CONFLICT_SP|SQL040222032202703|". SQLSTATE=09000

Because the conflicts are only reported at the runtime, it is important that you test all your conditions to catch this type of error.

For completeness, the syntax diagram for the zSeries CREATE FUNCTION statement is shown in Figure 9.32, followed by an explanation of differences from DB2 on LUW.

>>-CREATE FUNCTION--function-name------------------------------->


>--(--+--------------------------------+--)--*------------------>
      | .-,--------------------------. |
      | V                            | |
      '---parameter-name--data-type--+-'


>--RETURNS--+-data-type------------------+--*------------------->


                                   .-LANGUAGE SQL-.
>--+-------------------------+--*--+--------------+--*---------->
   '-SPECIFIC--specific-name-'


>--+------------------------------+-----------------+----------->
   +-PARAMETER CCSID -+--ASCII----+
                      |- EBCDIC  -|
                      '- UNICODE -'

   .-NOT DETERMINISTIC-.     .-EXTERNAL ACTION----.
>--+-------------------+--*--+--------------------+--*---------->
   '-DETERMINISTIC-----'     '-NO EXTERNAL ACTION-'


   .-READS SQL DATA---------.     .-STATIC DISPATCH-.
>--+------------------------+--*--+-----------------+--*-------->
   +-CONTAINS SQL-----------+


   .-CALLED ON NULL INPUT-.
>--+----------------------+-----+---------------------------+--->




>--| SQL-function-body |---------------------------------------><


SQL-function-body:
>>-RETURN--+---------------------------------------------------+-><
           +-expression----------------------------------------+
           +-NULL----------------------------------------------+

Only SQL scalar UDFs are supported.

The PARAMETER CCSID clause indicates the encoding scheme for string parameters as ASCII, EBCDIC, or UNICODE.

Compound statements (identified by BEGIN ... END blocks) are not supported in the SQL function body. Only the RETURN clause followed by an expression or NULL is supported. This implies that a stored procedure cannot be called from a function on zSeries because the CALL statement cannot be specified anywhere in the function body.

Table 9.1 shows the subset of SQL statements supported in the trigger body on zSeries.

Because the IF statement is not supported in the trigger body, the example in Figure 9.19 does not work on zSeries. One possible workaround is to split the trigger into three separate triggers as shown in Figure 9.33.

The triggers in Figure 9.33 need to be created in the order shown so that they are fired in the proper sequence to maintain the original logic.

To invoke UDFs from a trigger, use SELECT or VALUE statements. The SET statement can also be used to assign the value returned by a function to a variable.

To invoke SQL procedures from a trigger, use CALL statements. The parameters of this stored procedure call must be literals, transition variables, table locators, or expressions.

The trigger example in Figure 9.24 does not work on zSeries because it contains unsupported statements, such as the IF and DECLARE statements. A possible workaround is to move all the logic other than the function call into the procedure.

The function example in Figure 9.25 needs to be implemented as an external UDF because the BEGIN...END compound statements are not supported on zSeries.

An SQL statement can explicitly invoke UDFs or stored procedures, or can implicitly activate triggers that invoke UDFs or stored procedures. This is known as nesting SQL statements. DB2 for zSeries supports up to 16 levels of nesting.

Restrictions on nested SQL statements include the following:

Although trigger activations count in the levels of SQL statement nesting, the previous restrictions on SQL statements do not apply to SQL statements that are executed in the trigger body.