Db2 12 for z/OS SQL Enhancements: Advanced Triggers

In this, the fourth of a set of four entries covering SQL enhancements introduced with Db2 12 for z/OS, I will describe new trigger capabilities delivered by way of what are called "advanced triggers" (the kind of trigger that you could define prior to Db2 12 -- and which you can still create in a Db2 12 system -- is now referred to as a "basic trigger"). Before getting to the details of advanced trigger functionality, I want to emphasize that there are considerably more than four SQL-related enhancements delivered through Db2 12 -- I've just selected my four favorite of these enhancements for highlighting in this blog (the previous three entries in this series covered piece-wise DELETE, result set pagination, and the much-improved MERGE). A good source of information on the other SQL enhancements provided by Db2 12 is the "Application enablement" section of the "What's new" part of the Db2 12 Knowledge Center on the Web.

Note that advanced trigger functionality is available in a Db2 12 system when the activated function level is V12R1M500 or above.

OK, advanced triggers: the most important thing to know about this new kind of trigger is that it can contain (in the CREATE TRIGGER statement) a compound SQL statement. Basically, that means that you can define a trigger using SQL PL (aka SQL procedure language -- the same language that enables the creation of native SQL procedures and "native" SQL user-defined functions).

Before getting into the implications of SQL PL in the body of a trigger, I want to do a little level-setting. A trigger, for those who don't know, is a mechanism by which a data-changing action (INSERT, UPDATE, or DELETE) targeting one table can "trigger" the automatic execution of some other SQL action. A trigger can be "fired" before or after the "triggering" SQL statement has been executed. A very simple example: an AFTER UPDATE trigger can cause an UPDATE that changes column C1 of table T1 to drive an INSERT of some information into table C2.

Suppose you want the action taken when a trigger gets fired to be somewhat involved versus really simple. In that case, prior to Db2 12 it was often necessary to have the trigger call a stored procedure. That can be kind of clunky from a coding perspective. With the ability to code SQL PL in the body of an advanced trigger, you can drive a fairly sophisticated action when a triggering SQL statement executes, without having to put a stored procedure call in the trigger body (in essence, you can use SQL PL to put the equivalent of a native SQL procedure in an advanced trigger).

An example of an advanced trigger appears below, followed by some color-coded comments (this BEFORE INSERT trigger examines start and end times for classes in records to be inserted into a Db2 table, sets the end time to one hour after the start if the end time value is NULL, and returns an error if the class end time is after 9 PM):

CREATE TRIGGER MYTRIG01
BEFORE INSERT ON MYTAB
REFERENCING NEW AS N
FOR EACH ROW
ALLOW DEBUG MODE
QUALIFIER ADMF001
WHEN(N.ending IS NULL OR n.ending > '21:00')
L1: BEGIN ATOMIC
 IF (N.ending IS NULL) THEN
    SET N.ending = N.starting + 1 HOUR;
 END IF;
 IF (N.ending > '21:00') THEN
    SIGNAL SQLSTATE '80000'
    SET MESSAGE_TEXT = 'Class ending time is beyond 9 pm';
 END IF;
 SET GLOBAL_VAR = NEW.C1;
END L1#

Things to note about this advanced trigger:

• You have some new options -- Because an advanced trigger can include SQL PL statements, you can debug it, just as you can debug a native SQL procedure or a compiled SQL scalar function (Data Studio is particularly handy for debugging SQL PL routines). Another new option for advanced triggers: you can provide a high-level qualifier to be used with unqualified objects referenced in the body of the trigger.
• You can include SQL control statements (i.e., logic flow control statements) in the body of the trigger -- IF (shown in the example) is one such statement. Among the others are ITERATE, LOOP, REPEAT, and WHILE. These SQL PL statements enable the coding of a trigger that has pretty sophisticated functionality.
• There are new possibilities for the SET statement -- With an advanced trigger, SET is not restricted to transition variables -- it can also be used with global variables and SQL variables (the latter term refers to variables declared in the body of the trigger).

And, there's something that's notable by its absence in the example CREATE TRIGGER statement -- namely, the phrase MODE DB2SQL. It is, in fact, the absence of MODE DB2SQL in a CREATE TRIGGER statement that indicates that the trigger will be an advanced trigger, as opposed to a basic trigger.

Besides providing advanced functionality versus basic triggers, advanced triggers eliminate what had been a really vexing problem encountered by many users of (what are now called basic) triggers -- a problem best illustrated by example. Suppose you create three basic triggers on a table, all of which are "fired" when a particular type of statement targets the table (e.g., an UPDATE of a certain column in the table), and all of which "fire" in the same relative time period with regard to the execution of a triggering SQL statement (e.g., all three are AFTER triggers). In that case, the order in which the triggers will fire is determined by the order in which they were created: if trigger A was created first, then trigger B and then trigger C, they will fire in that order (A then B then C) upon the execution of a triggering SQL statement. Let's say that this A-B-C trigger firing sequence is important to you. Now, suppose that that trigger A has to be modified. Only way to get that done with a basic trigger is DROP and re-CREATE. But wait! That re-CREATE will make trigger A the last of the three triggers in this example to be created, resulting in an undesirable firing order of B then C then A when a triggering SQL statement is executed. How do you change trigger A and preserve the desired A-B-C firing order? Here's how: you DROP all three triggers, then re-CREATE all three in A-B-C order. What a hassle!

Along comes advanced trigger functionality, and this problem is solved -- and not only solved, but fixed by your choice of three options. All three of these hassle-free trigger modification options are made possible by the fact that advanced triggers, like native SQL procedures and compiled SQL scalar functions, have versions. So, back to the example of the preceding paragraph: you have triggers A, B, and C, and you want to change A while maintaining the A-B-C firing sequence. If they are now advanced triggers, no problem! Here are your three -- count 'em: three -- options for changing advanced trigger A without messing up the firing sequence of the triggers (I'll assume that the current version of trigger A is V2, and I'll highlight syntax that is new with Db2 12 in red):

• CREATE OR REPLACE TRIGGER A VERSION V2…
• ALTER TRIGGER A REPLACE VERSION V2…
• ALTER TRIGGER A ADD VERSION V3… followed by ALTER TRIGGER A ACTIVATE VERSION V3

Choose any of the above options, and you successfully modify trigger A while maintaining the A-B-C firing sequence of the three triggers, without having to DROP and re-CREATE triggers B and C. And the crowd goes wild!

Well, there you have it: another incentive to get to Db2 12 for z/OS (and to activate function level V12R1M500 or later). Enjoy those Db2 12 SQL enhancements, folks!

Db2 12 for z/OS SQL Enhancements: a Better MERGE

Greetings, and welcome to the third in a series of entries covering my favorite Db2 12 for z/OS SQL enhancements. The first two posts in the series described piece-wise DELETE and result set pagination. This entry focuses on the new and very much improved MERGE functionality delivered via Db2 12 (usable at function level V12R1M500 and above).

The MERGE statement, introduced with Db2 9 for z/OS, is sometimes referred to as the "upsert" statement, because it enabled, via a combination of update and/or insert operations, the "merging" of one "table" into another (I'll explain the quotes around "table" momentarily): in a MERGE statement, you'd indicate what constitutes a match between an input "table" "row" (again, quote marks to be explained) and a target table row, and where a match exists the target table row is updated with information in the matching input "table" "row," and when a match doesn't exists the input "table" "row" is inserted into the target table. That functionality sounds pretty useful, but the initial Db2 for z/OS implementation of MERGE left a good bit to be desired (read on to see what I mean).

What's great about the new versus the old Db2 for z/OS MERGE statement is best shown by way of example. Here is what a pre-Db2 12 MERGE statement might look like, with color-coded complaints following:

MERGE INTO ACCOUNT AS A

USING (VALUES (:hv_id, :hv_amount)

FOR 3 ROWS)

AS T (ID, AMOUNT)

ON (A.ID = T.ID)

WHEN MATCHED

 THEN UPDATE SET BALANCE = A.BALANCE + T.AMOUNT

WHEN NOT MATCHED THEN INSERT (ID, BALANCE)

VALUES (T.ID, T.AMOUNT)

NOT ATOMIC CONTINUE ON SQLEXCEPTION;

Complaint: the input "table" has to be represented as a series of host variable arrays -- one for each "column" of the "table" -- Thus the quotation marks I've been putting around input "table" and "row." It's not really a Db2 table that's used for MERGE-input purposes. It's a clunky representation of a table. What a hassle.

Complaint: only a very simple row-match qualification can be specified, and only a simple pair of actions are possible: when a match is found, do this update, and when a match is not found, do this insert -- Not a lot of sophistication or flexibility here.

Complaint: the NOT ATOMIC CONTINUE ON SQLEXCEPTION clause, which indicates that input "rows" (if there are several of them) are processed separately, with processing continuing in the event that an error is encountered for a given row, is required -- You might not want that behavior.

And, on top of all that, a target table row can be operated on more than once in a single execution of a pre-Db2 12 MERGE statement (it could be inserted and subsequently updated) – Again, you might not want that behavior.

Along comes Db2 12 (at function level V12R1M500 or above, as previously noted), and boy, is MERGE ever better than it was before. I'll stick with the color-coded-example approach to show how MERGE has been enhanced. A Db2 12 MERGE might look like this:

MERGE INTO RECORDS AR

USING (SELECT ACTIVITY, DESCRIPTION, DATE, LAST_MODIFIED

FROM ACTIVITIES_GROUPA) AC

ON (AR.ACTIVITY = AC.ACTIVITY) AND AR.GROUP = ’A’

WHEN MATCHED AND AC.DATE IS NULL THEN SIGNAL SQLSTATE ’70001’

SET MESSAGE_TEXT =

AC.ACTIVITY CONCAT ’ CANNOT BE MODIFIED. REASON: DATE IS NOT KNOWN’

WHEN MATCHED AND AC.DATE < CURRENT DATE THEN DELETE

WHEN MATCHED AND AR.LAST_MODIFIED < AC.LAST_MODIFIED THEN

UPDATE SET

(DESCRIPTION, DATE, LAST_MODIFIED) = (AC.DESCRIPTION, AC.DATE, DEFAULT)

WHEN NOT MATCHED AND AC.DATE IS NULL THEN SIGNAL SQLSTATE ’70002’

SET MESSAGE_TEXT =

AC.ACTIVITY CONCAT ’ CANNOT BE INSERTED. REASON: DATE IS NOT KNOWN’

WHEN NOT MATCHED AND AC.DATE >= CURRENT DATE THEN

INSERT (GROUP, ACTIVITY, DESCRIPTION, DATE)

VALUES (’A’, AC.ACTIVITY, AC.DESCRIPTION, AC.DATE)

ELSE IGNORE;

Love it: with Db2 12's new-and-improved MERGE, you can actually merge an honest-to-goodness Db2 table with another table; or, you can merge a view with a table; or (as shown in this example), you can merge the result set of a SELECT statement with a table -- So long, host variable arrays. Can't say I'll miss you.

Love it: in one MERGE statement, you can have a multitude of “when matched” and “when not matched” clauses, differentiated through various additional predicates -- Benefit: a number of update, insert, and/or delete actions can be driven via execution of a single MERGE statement (and, yes, the ability to drive DELETE operations -- in addition to UPDATE and DELETE -- via a MERGE statement is another part of the "new-and-improved" story).

Love it: you can use the SQL statement SIGNAL to provide customized error codes and messages -- Maybe you could have some fun with that, like, SET MESSAGE TEXT = 'BETTER LUCK NEXT TIME'

Love it: there's a new IGNORE option -- MERGE input rows probably hate to be ignored, but sometimes it's just necessary.

And on top of all that, with the new-and-improved MERGE, a target table row can be operated on (via INSERT, UPDATE, or DELETE) only once -- Personally, I kind of like that.

And one more thing: if an error is encountered during execution of a new-and-improved MERGE, the whole statement is rolled back -- It's all or nuthin'.

Now, if you find yourself getting all sentimental and missing the pre-Db2 12 MERGE statement, you can always bring it back: just include in your coding of the statement the NOT ATOMIC CONTINUE ON SQLEXCEPTION clause. Do that, and MERGE behavior will be as it was prior to Db2 12, right down to the requirement that input "table" "rows" be in the form of host variable arrays.

Me? I'll take the new MERGE.