Use of the 'Add Physical File Trigger (ADDPFTRG)' command on your AS/400, iSeries and System i servers can be a valuable tool with a lot of possibilities. It can also be an application performance nightmare - if not done carefully.
The ability to have custom logic executed by the operating system at a database level is extremely powerful. For environments with purchased application software and no access to source code, you now have some options for providing hooks or real-time interfaces into other applications.
Maintaining data integrity and application performance that meets your business needs becomes a top concern.
Pay close attention to the definition of the word 'trigger'. A trigger should initiate an event or series of processes. It should not perform the processing.
If you add a physical file trigger to one of your Production database files, you are affecting every job on the system that updates this file. Every record that is added, updated or deleted by your interactive and batch processing is now affected by your trigger program.
This is a good thing and the whole point of writing the program. You want every database change to call your program and execute your custom logic. What you don't want is a 30 minute batch job to now take 3 hours to run.
You don't want interactive response times to now start to affect the productivity of your end-users.
Let's assume your trigger program is written in RPG. This program should not open any files. It should not call any other application programs. It should not run any imbedded SQL or OPNQRYF commands. It definitely should not SETON *INLR.
It should do the bare minimum manipulation of the parameter data via data structures to determine whether this record is one that you care about. If it is, CALL QSNDDTAQ with the key data from the data structures. Regardless, use a RETRN to exit the program.
Why does all of this matter and how is my customcode getting executed?
Use your favorite Internet search engine and you will find numerous examples of the syntax for using the QSNDDTAQ and QRCVDTAQ operating system APIs. The main point here is that you should use them in your physical file trigger programs. By limiting the logic in your trigger programs to basically just one call to an operating system API, you will be adding minimal performance impact to the jobs that update these Production database files.
You will have a 'background' process that will CALL QRCVDTAQ and perform your custom logic. This batch process will be able to open the files once at IPL time. Programs will be able to stay in memory inside of this one batch job versus hundreds of jobs calling QSNDDTAQ.
How do we find existing trigger programs anddetermine how they are affecting performance?
Use the 'Print Trigger Programs (PRTTRGPGM)' command to generate a report showing all physical file triggers on your system. This listing in conjunction with the 'Display File Description (DSPFD)' command will provide you with detailed information about the programs and database files involved.
Remember that the worst written program in the world is not a performance issue if it is only called once during a non-critical timeframe to your business. We need to identify the trigger programs that are called the most across all jobs on your system. Since these programs are called by the operating system whenever an add, update or delete occurs on these specific files - it would be appropriate to analyze the adds, updates and deletes on these files.
Using the DSPFD command with the *MBR and *OUTFILE options, you can generate a database file containing detailed information about each of the physical files on your system. The important information here is the 'Activity Statistics' for each physical file that has an attached trigger program. This data will show the number of write, update and delete operations since the last IPL. Trigger programs are executed for each of these operations. It makes sense to focus on the programs that are called the most.
Using the techniques mentioned earlier on the most heavily used physical file trigger programs can provide significant improvement in the interactive and batch processing response times across your entire system. Our Disk Navigator can facilitate collection of data for this type of analysis. Our Application Optimizer can be used to show the CPU, I/O or duration impact that a trigger program is having within a specific job on your system.
The Workload Performance Series software is a comprehensive tool-set focusing on many aspects of system and application performance monitoring and tuning. There are seven tools in this tool-set, each with their own data collection and analysis components.
Our Disk Navigator tool has evolved dramatically since 1995. It started out as a simple disk space analysis tool for helping you to better manage the DASD on your system. As the Integrated File System (IFS) has evolved over the years and its use has become more common, our software has been enhanced to include a much more comprehensive DASD analysis of your entire disk storage.
Along the way, IBM has enhanced their tracking of 'Activity Statistics' at a database file level. Each physical and logical file on your system has many statistics being captured 'since the last IPL'. While this data is tremendously valuable, many environments don't perform IPLs on a regular basis. Having the ability to capture and compare this data on a daily, weekly, monthly or yearly basis can make it much more useful. Maybe you want to collect these statistics before and after a critical batch processing cycle.
Sure you can use the IBM commands mentioned earlier. You can build your own programs or queries to collect and analyze this type of data. Your time would better be spent fixing and testing the trigger programs rather than building your own custom tools for finding and analyzing them.
The data comparison features that are integrated into each of the tools in this tool-set give you the flexibility to collect and analyze an unlimited number of data samples. Whether we are comparing activity statistics for physical file trigger performance analysis or analyzing CPU utilization within a core business batch processing cycle - features and data are available and ready for use.
In many situations, you have already identified a problem job on your system, but have no idea that a physical file trigger program is the underlying culprit. If this is a long running batch job, poorly performing interactive application or a web-based software package that just isn't meeting customer's expectations - it's worth some analysis to see whether some simple trigger program changes could make a difference. You will be stunned when our Application Optimizer shows you that 98% of the CPU consumed by your 3 hour batch job is from one physical file trigger program where the programmer simply forgot to use RETRN versus a SETON *INLR.
The IBM commands mentioned earlier and many others are still of tremendous value when doing any type of system or application analysis. Our Workload Performance Series software has recently been enhanced to include new 'User-Defined Options' features. This enhancement provides you with the ability to execute any operating system commands or custom CL programs from within our analysis tools. Use our default data collection and inquiry options for most of your analysis needs. Custom define your own data inquiry options if desired.
We invite you to go to our web site at http://www.mb-software.com, take us up on our Free 30 Day Trial offer or just make use of our extensive online Resource Center. White Papers and Webcasts are available which provide educational value to managers, administrators and software developers.