iCan Blog Archive

Manage Trigger Programs in Production

by | Aug 22, 2017 | SQL, Db2 for i | 0 comments

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.

Big Picture View

The following images depict how this new support works.

Figure 1. Insert, Update, and Deletes are firing the existing trigger



As shown in Figure 1, 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. The trigger is replaced using ALLOW_DDL_CHANGES_WHILE_OPEN


Figure 2 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.

Figure 3. For a period of time, both trigger programs are used



Finally, Figure 3 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

Using ALLOW_DDL_CHANGES_WHILE_OPEN

The ALLOW_DDL_CHANGES_WHILE_OPEN may not have been our finest moment when creating a new name. Honestly, we discussed names at length and settled on this verbose name. You may have noticed that the name does not include any mention of trigger programs. This is because we architected this control to be extensible in the future to possibly cover other Data Definition Language (DDL) or Data Security Language (DSL) operations.

The following steps can be used to use this QAQQINI (Query Options) control. 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, '', '');
call qsys2.override_qaqqini(2,  
                            'ALLOW_DDL_CHANGES_WHILE_OPEN', 
                            '*YES');

CREATE OR REPLACE TRIGGER toystore.new_hire 
  AFTER INSERT ON toystore.employee
    FOR EACH ROW MODE DB2SQL
      UPDATE toystore.company_stats
        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.

This blog post was written by Scott Forstie. Scott is the Db2 for i Business Architect, SQL development leader and IBM i developerWorks content manager. 

This blog post was edited for currency on November 6, 2020.

This blog post was originally published on IBMSystemsMag.com and is reproduced here by permission of IBM Systems Media.