Considerations for Invoking SQL Procedures from UDFs and Triggers | Intelligent Enterprises of the 21st Century

To preserve the data integrity in the database, certain rules are implemented for SQL procedures that are to be invoked by UDFs and triggers. The same rules apply when the SQL procedures are invoked in DB2 stand-alone code for LUW, which is discussed in Appendix B, "Inline SQL PL for DB2 UDB for Linux, UNIX, and Windows."

Data Access Restrictions

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.

UDFs or SQL procedures with the MODIFIES SQL DATA option can invoke any other valid UDFs and SQL procedures.
UDFs and SQL procedures with the READS SQL DATA option can only invoke UDFs and SQL procedures with either the READS SQL DATA or CONTAINS SQL option.
UDFs and SQL procedures with the CONTAINS SQL option can only invoke UDFs and SQL procedures with the CONTAINS SQL option.

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.

Tip

Use an explicit data access option in both UDFs and SQL procedures for readability. It is easier to find UDFs and procedures with incompatible data access levels.

Transaction Control in SQL Procedures

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.

Note

On iSeries, in addition to ROLLBACK and COMMIT, a procedure invoked by a UDF or trigger cannot contain the CONNECT, SET CONNECTION, RELEASE, DISCONNECT, or SET TRANSACTION statements, either.

On zSeries, the examples in Figures 9.27, 9.28, and 9.29 only work for the stored procedures and triggers, but not for the table UDFs. Table UDFs are not supported on zSeries. For an illustration of transaction control on zSeries, replace the table UDFs with scalar UDFs.

Figure 9.27. Examples of transaction control in procedures invoked by UDFs and triggers.

 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

Figure 9.28. A modified version of Figure 9.27.

 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

Figure 9.29. Examples of save point support.

 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.

Table Read or Write Conflict in SQL Procedures on LUW

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:

Within the same table access context, different statements can both read from and write to the same table without causing a conflict.
When a table is being read within a given table access context, contexts can also read the table. If any other context attempts to write to the table, however, a conflict occurs.
When a table is being written within a table access context, then no other context can read or write to the table without causing a conflict.

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.

Figure 9.30. Examples illustrating read and write conflict (LUW only).

 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 contextcalling it context1 for easy referralis created. The three INSERT statements inside the procedure body are to be executed within context1. Another table access contextcontext2is 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.