Jan 28, 2010 - No one here really understands triggers. Can you give me an example of an RPG IV trigger program that will work? A trigger is a program. Whatever may be your reason it always handy to have that called program name without having to modify all the programs that are calling it by adding parms to the call list. It could be a database trigger program that may want to find out which program just did the update or insert. August 22, 2017 This week’s blog was written by Scott Forstie. Scott is the Db2 for i Business Architect, SQL development leader and IBM i developerWorks content manager. 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, 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. Insert, Update and Deletes are firing the existing trigger 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. 300 circuite electronice pdf. If the new QAQQINI option is not used, the attempt to replace the trigger program could fail, if an exclusive lock cannot be acquired. The trigger is replaced using ALLOW_DDL_CHANGES_WHILE_OPEN 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |