V5 and Trigger Programs

Hey, Ted:

At my shop, we upgraded from V4R5 to V5R2. Now when anyone accesses a database file that has a trigger attached to it, we get a data decimal error in the trigger program. Do you have any suggestions on how to solve this problem?

-- Duane

This is a well-known problem, Duane. I should take this opportunity to publicize this problem once again. Maybe another warning will help someone else.

A trigger program receives two parameters, the first of which is a data structure that contains, among other things, before and after images of the record and before and after maps of null-column indicators. You have hard-coded the locations of the before and after record images in your data structure. With V5R1, IBM moved the location of the record images and null maps to other places in the data structure.

You could update the data structure with the new record image locations, but I think that would be a mistake. IBM may move the record images again some day.

Instead, you need to change the way you are accessing the record images. The data structure that contains the record images and null maps also includes subfields that indicate the locations of the record images and null maps. You need to modify your trigger programs so they use the offsets in these subfields.

Here is an example from which you can begin. This program contains three pointers. If you're not used to pointers, don't let them scare you. The first eval equates the data structure that breaks down the trigger buffer to the first parameter. This gives you access to the subfields. The second eval equates the data structure that contains the before (old) record image to the before image in the parameter. This gives you access to the before image. The last eval equates the data structure that contains the after (new) record image to the after image in the parameter.

 * Trigger program for purchase order master file POMASTR

 *entry plist                                              
D Trigger01       pr                  extpgm('POTRG02')    
D                             1024                         
D                               10i 0                      
D Trigger01       pi                                       
D  TriggerBuffer              1024                         
D  TriggerBufLen                10i 0                      
                                                           
 * Subfields from trigger buffer                           
D pTriggerBuffer  s               *                        
D WorkBuffer      ds          1024    based(pTriggerBuffer)
D   OldRecOffset         49     52i 0                      
D   OldRecLen            53     56i 0                      
D   NewRecOffset         65     68i 0                      
D   NewRecLen            69     72i 0                      

* old & new record images                                           
D pOld            s               *                                  
D OldRec        e ds                  extname(POMASTR) prefix(o)     
D                                     based(pOld)                    
D pNew            s               *                                  
D NewRec        e ds                  extname(POMASTR) prefix(n)     
D                                     based(pNew)                    
                                                                     
C                   eval      pTriggerBuffer = %addr(TriggerBuffer)  
C                   eval      pOld = %addr(WorkBuffer) + OldRecOffset
C                   eval      pNew = %addr(WorkBuffer) + NewRecOffset

-- Ted

Sponsored By CLIENT SERVER DEVELOPMENT

LEARN SQL BY EXAMPLE! Reader Feedback: A great book! Interestingly, it could have two completely different audiences. One is the audience of traditional AS/400 people like myself, that need to learn how SQL works. And the other is the experienced SQL person, who needs to know how SQL on the AS/400 is different. -- Jerry K. Buy your copy of iSeries and AS/400 SQL at Work direct from the author, Howard F. Arner, Jr. This is THE book to teach you SQL on the iSeries. The book is chock full of information that is useful for beginners and experts alike. The book comes with a CD that has all of the sample data and queries used in the book and a FREE copy of SQLThing so you can work through the examples in the book. You learn by doing! With Howard's expert guidance, you'll learn and understand many topics, including the following: Relational Concepts and Terms SQL Select Basics Manipulating Data with SQL Advanced Select Statements for Powerful Reports Cursors, Transactions, Journals, and Logging Debugging SQL Statements and Enhancing Performance Stored Procedures/Using Legacy Programs in SQL Embedded SQL in RPG Howard F. Arner, Jr., is a programmer who has published extensively on AS/400 integration and database topics. In his book, iSeries and AS/400 SQL at Work, Howard provides detailed coverage of SQL for business environments by using practical examples--available on the CD--to be performed by the reader. More advanced chapters teach how to become proficient in industry-standard SQL to manipulate AS/400 data, use scalar functions to summarize data, and integration of SQL into RPG and COBOL. This is the perfect guide for beginners and advanced SQL users. Here's what readers are saying about iSeries and AS/400 SQL at Work by Howard F. Arner: Anyone working with AS/400 query should look into buying this book. Now using AS/400 query seems a little odd and very cumbersome. His writing is laid out very well and is easy to read. While reading this, I felt that he was actually there explaining the chapters to me as a one-on-one instructor. -- Jonathan Tripp, Deerfield Beach, Florida If you wondered how to call AS/400 programs from your Visual Basic program correctly, this book is your one source for how to do so. It's the first book on the market that answered all of my client/server questions when I deal with PC to AS/400 programming tasks. Not only does Howard do a good job of explaining how stored procedures work, but he gives some great coding examples on how to get you up and running correctly. Howard goes the extra mile and talks about performance, too--one thing that normally is lacking in many other books. -- Bob Butcher, Sidney, New York Learn how SQL can make your job easier and you more productive. Order your copy at SQLThing.com today!