DB2 for zSeries Considerations

The differences in the support of UDFs and triggers on zSeries are highlighted in this section.


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.

Figure 9.32. The CREATE FUNCTION statement syntax for zSeries.
 >>-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.

Trigger Considerations

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

Table 9.1. SQL Statements Supported in the Trigger Body on zSeries

SQL Statement

Before Trigger

After Trigger













SET transition variable






DELETE (searched)



UPDATE (searched)






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.

Invoking UDFs and SQL Procedures from Triggers

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.

Nesting SQL Statements

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:

  • When you execute a SELECT statement on a table, you cannot execute INSERT, UPDATE, or DELETE statements on the same table at a lower level of nesting.

  • When you execute an INSERT, DELETE, or UPDATE statement on a table, you cannot access that table from a UDF or stored procedure that is at a lower level of nesting.

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.

    DB2 SQL PL. Deployment and Advanced Configuration Essential Guide for DB2 UDB on Linux., UNIX, Windows, i5. OS, z. OS
    Intelligent Enterprises of the 21st Century
    ISBN: N/A
    EAN: 2147483647
    Year: 2003
    Pages: 205

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net