Manage Trigger Programs in Productions
Trigger programs are a data-centric technology to deploy business logic by establishing code that will run (fire) when the associated triggering event occurs.
By Dawn May08/22/2017
Trigger programs are a data-centric technology to deploy business logic by establishing code that will run (fire) when the associated triggering event occurs. Trigger programs have been around a long time and come in two forms:
1) Native Triggers – managed with the Add Physical File Trigger (ADDPFTRG), Change Physical File Trigger (CHGPFTRG), and Remove Physical File Trigger (RMVPFTRG)
2) SQL Triggers – managed with the CREATE TRIGGER, ALTER TRIGGER, and DROP TRIGGER SQL statements.
More recently, users have been able to also use CREATE OR REPLACE TRIGGER to simplify trigger deployment. In a response to customer requests, we delivered a way for the Database Engineer (DBE) to easily deploy trigger changes in a production environment. Prior to this enhancement, the DBE would need to find a time where an exclusive lock over the target file could be obtained. In today’s IT environment, it has become more and more difficult to schedule such maintenance. The enhancement allows the DBE to use the ALLOW_DDL_CHANGES_WHILE_OPEN option to direct the database to not fail the trigger operation if an exclusive lock cannot be acquired.
The database will attempt to acquire an exclusive lock, which has the effect of broadcasting a system event to tell all jobs that hold a pseudo-closed SQL cursor over the file to hard close those cursors. In this way, the database is taking measures to have the new trigger program used by as many jobs as quickly as possible. This enhancement is available on the following releases with the appropriate Db2 for i PTF Group:
Big Picture View
The following images depict how this new support works.
As shown in Figure 1 (image not available), the busy production environment will include lots of activity, both queries and Data Manipulation Language (DML). This is completely normal and the image only shows one of the DML operations that caused a trigger (LIBRARY/TRIG_PGMX) to be fired. As an aside, SQL Trigger names can be up to 128 characters long, so please use this naming support to make your trigger program easier to understand.
Figure 2 (image not available) shows the DBE’s execution of the CREATE OR REPLACE TRIGGER statement, to replace or extend the logic within the LIBRARY/TRIG_PGMX trigger program. If the new QAQQINI option is not used, the attempt to replace the trigger program could fail, if an exclusive lock cannot be acquired.
Finally, Figure 3 (image not available) shows the successful deployment of the new trigger program. The image also illustrates an important aspect that should be understood. Any jobs that have the previous version of the trigger program active will continue to work, unchanged. The database has moved the previous version of the trigger program out of context, which means that it is no longer found within a library. The database keeps track of the number of jobs using this out of context trigger program and the program is deleted when the usage count drops to zero. This ability to make changes to trigger programs does not include every possible trigger operation. Trigger operations that can be made with ALLOW_DDL_CHANGES_WHILE_OPEN:
- CREATE TRIGGER, ALTER TRIGGER, and DROP TRIGGER
- COMMENT ON TRIGGER and LABEL ON TRIGGER
- ADDPFTRG, RMVPFTRG and CHGPFTRG
For those who know me, you’re not going to be surprised that I prefer to use the SQL approach to managing QAQQINI options. The Change Query attributes (CHGQRYA) command can also be used to configure this new option. The OVERRIDE_QAQQINI() gives me a way to enable an option where only my job can possibly be affected. The procedure is documented in the IBM i Technology Updates wiki in developerWorks article OVERRIDE_QAQQINI procedure.
-- Deploy a trigger program without
-- acquiring an exclusive lock on the *FILE
call qsys2.override_qaqqini(1, '', '');
CREATE OR REPLACE TRIGGER toystore.new_hire
AFTER INSERT ON toystore.employee
FOR EACH ROW MODE DB2SQL
SET Number_of_employees = Number_of_employees + 1;
Example 1. Using the OVERRIDE_QAQQINI procedure
There you have it. Take this for a test drive. If you found this topic interesting, consider spending some time on the IBM i Technology Updates wiki. Every database enhancement since 2010 has its own fact page. Thank you, Dawn, for giving me the platform to cover a somewhat “hidden gem” of Db2 for I.
Dawn May is an IBM i consultant. She owns Dawn May Consulting, LLC in the Greater Boston area. Dawn is a former IBM senior technical staff member.