A Few More Pointers on Pointers

by Raymond Everhart

[The code for this article is available for download.]

It's the last day of our vacation. Now I face the most unpleasant part of the entire trip: trying to fit everything we came with, and everything we bought, into the bags that were too small when I packed them the first time. I'm an RPG programmer. When I'm not sure how much space I need, I define the biggest variable or array possible. Of course, that's like packing a suitcase with empty suitcases. (Don't laugh. I've done that, too.)

It has always bothered me when I have to define a variable or an array to handle an unknown amount of data. Whenever I think, "They'll never have more than 100. . . , " they will. It just seems like such a waste to define an array with 999 elements when the average number of elements used is 13. I have always justified this by saying I'd rather use extra resources than have a program error. Using pointers to allocate and access storage gives you an alternative method.

My last article, "Using Pointer-Based Variables in RPG IV," demonstrated the use of pointers in a trigger program. The trigger program was designed to write a record to a log file for each field that was changed. A block of logic was written to compare the "before" and "after" values of each field. In my example, there were only five fields in the file. What if there had been 200 fields? I've written a number of trigger programs, and after the first one they tend to get rather tedious. Even worse, trigger programs tend to be forgotten once they are in place. I can't tell you how many times people have forgotten to consider trigger programs when planning a database change. What would be great is a program that would dynamically determine the fields in a file and then test each one for a change, before writing the data to a log file. The trigger program would not only be immune to database changes, it would also be a generic program that could be applied to any file.

The first challenge is in comparing the data stored in the trigger buffers. The data is not separated into fields. In order to compare, field by field, we need to know how the data is to be subdivided. One approach would be to use the Display File Field Definition (DSPFFD) command and build a map of starting and ending positions for each field. However, the information that we need is already stored and maintained by the DB2 database in the SYSCOLUMNS file. In this example, the embedded SQL statement shown below is used to build a record set of all the fields in the file being triggered. The host variables :LibName and :FileName contain the name of the library and file that caused the trigger program to be fired. These variables are located in the fixed portion of the Trigger data stream.

Select ORDINAL_POSITION,
       Char(COLUMN_NAME,10),
       Char(TABLE_NAME,10),
       Char(TABLE_SCHEMA,10),
       Char(DATA_TYPE,10),
       LENGTH,
       Char(IfNull(Char(NUMERIC_SCALE),' '),3),
       STORAGE,
       0
From SYSCOLUMNS
Where Table_Schema = :LibName
      and Table_Name = :FileName
Order By ORDINAL_POSITION

The number of records that are returned will be different for each file. The logical place to put this information would be in an array, so that a loop could be written that would process each field in the file. In fact, we would need multiple arrays to hold all of the information needed to calculate the starting and ending positions of each field in the trigger data structure. The following code shows how the Fetch statement is used to retrieve records from the SQL cursor and place the data into a data structure.

* This data structure is used to contain the Field information for the record
     D FieldDS         DS                   Based(FldIdxPtr)
     D   FldNumber                   10  0
     D   FldName                     10
     D   TblName                     10
     D   Schema                      10
     D   FldType                     10
     D   FldLen                      10I 0
     D   FldDecPos                    3
     D   FldBytes                    10I 0
     D   FldOffset                   10I 0

     C/EXEC SQL
     C+ Fetch from FileLayout into :FieldDS
     C/END-Exec

Notice that the data structure is based on the pointer field FldIdxPtr. By increasing and decreasing the value of FldIdxPtr by the length of the FieldDS data structure, we can simulate an array and program a loop to process each entry stored in memory. The obvious question is, where in memory? Before we can use our data structure/array, we need to allocate some memory. Since we know the length of the FieldDS, all we need to know is how many records our SQL statement will return. The code shown below will extract the number of fields (records) that exist in the triggered file.

      * Calculate Number of Fields(Columns) In the File(Table)
     C/Exec SQL
     C+ SELECT Count(*)
     C+ Into :FieldCount
     C+ FROM SYSCOLUMNS
     C+ WHERE Table_Schema= :LibName
     C+       and Table_Name = :FileName
     C/End-Exec

The amount of memory that we need to allocate can now be calculated and reserved as shown below. Now we have a block of memory that we can use that is no larger or smaller than absolutely necessary. One curious thing about this allocated memory is that it has no name. When the %Alloc function is used, it allocates the amount of storage requested and then returns a pointer to the first byte of that storage. Any access to the data stored in this area is accomplished by using a pointer-based variable. In this technique, we have created the equivalent of a dynamically sized multiple-occurrence data structure.

D FieldCount      S              9  0
D FieldDSLen      S              9  0  Inz(%Len(FieldDS))
D FieldDSPtr      S               *    Inz(*Null)

C                   Eval      FieldDSPtr = %Alloc(FieldDSLen * FieldCount)

Now that we have allocated the necessary storage, let's populate it with the field information retrieved from the system catalog. The code shown below will load our dynamic data structure with all the information we need to subdivide the data stored in the trigger data structure.

     * Read all the records from the Cursor and place the data 
      into FieldDS
0174 C                   DoU       SQLCod <> 0
0175 C/EXEC SQL
0176 C+ Fetch from FileLayout into :FieldDS
0177 C/END-Exec

     * If a record was retrieved, calculate the field offset and 
      advance the Pointer
0178 C                   If        SQLCod = 0

     * If this field name matches the Key Field Name store the 
      pointer for this Field
0179 C                   If        FldName = KeyFldName
0180 C                   Eval      KeyFldPtr = FldIdxPtr
0181 C                   EndIf

         * Determine the offset to the beginning of the field
0182 C                   Eval      FldOffset = Offset

         * If this is not the last column in the table, calculate the next offset
         * and advance the pointer to the next "occurrence" of FieldDs in the 
         * allocated storage.
0183 C                   If        FldNumber <> FieldCount
0184 C                   Eval      Offset = Offset + FldBytes
0185 C                   Eval      FldIdxPtr = FieldDSPtr +
0186 C                                         (FieldDSLen * FldNumber)
0187 C                   Clear                   FieldDS
0188 C                   Endif
0189 C                   Endif
0190 C                   EndDo

One thing that you should remember about allocating storage in this way is that the storage is not initialized. Line 0185 causes the FieldDS to be "moved and pointed" to the next the segment of our allocated memory. Line 0187 clears all the fields in the FieldDS data structure and has the same effect as initialization. It is a good practice to clear and initialize memory as soon as possible; otherwise, data decimal errors may occur. The logic to extract the field information should be conditioned to happen only one time. If this logic is executed every time that the trigger is fired, you may experience performance issues associated with repeated I/O to the SYSCOLUMNS file.

Now that we have all the information that we need to subdivide the trigger data structure, we can proceed. The code below shows the logic used to loop through all the fields in the file. The data to be compared is extracted from the trigger data structure in the GetData subroutine.

     * Set the pointer to point at the first "occurrence" of FieldDS
0216 C                   Eval      FldIdxPtr = FieldDSPtr

     * Check all the columns in the table to see if they have changed and
     *     output changed fields to the Log File
0217 C                   Do        FieldCount

     * Extract the before and after data from the Trigger Data Structure
0218 C                   Exsr      GetData

0219 C                   If        Before <> After
0220 C                   Eval      FieldName = FldName
0221 C                   Write     LogFileR
0222 C                   EndIf

     * Advance the pointer to the next "occurance" of FieldDS
0223 C                   Eval      FldIdxPtr = FldIdxPtr + FieldDSLen
0224 C                   EndDo

The code shown below is from the GetData subroutine. This subroutine uses the information stored in the FieldDS data structure to determine the location of the data in the trigger data structure and to extract it into the Before and After variables. The %STR function is used to extract data from a specified memory address. The first parameter is the starting memory address, and the second parameter is the number of bytes to extract.

      * Calculate the address of the field within the Trigger Data Structure
     C                   Eval      AfterPtr = StartBuff + NewRecOffset
     C                                       + FldOffset
     C                   Eval      BeforePtr = StartBuff + OrgRecOffset
     C                                       + FldOffset

      * Extract the data from the buffer and place into fields
      * Note that all data types have not been defined in this example!
     C                   Select
     C                   When      FldType = 'CHAR'
     C                   Eval      Before = %Str(BeforePtr:FldBytes)
     C                   Eval      After = %Str(AfterPtr:FldBytes)

Remember that we are retrieving bytes. If this is a character field, there is nothing to worry about. If this is a packed field, we have some conversion to do. The C function QXXPTOI will convert a packed value, represented as a character string, into an integer. See "Extract Packed Decimal Numbers from Character Strings" for additional information on this technique. This is a good place to mention that when a record is added, the "before" image of the record supplied to the trigger program is blank. When a record is deleted, the "after" image is blank. If you pass blanks to the QXXPTOI function, it will fail, so be aware of this when using this function. Depending on the data stored in your database, other conversions may also be required.

Because this example creates a record for each field changed, the KEY field was added to LOGFILE. The value of KeyFldName is initialized to EMPNO for this example. The KeyFldName could be extracted from the SYSKEYS file, the SYSKEYCST file, or a user file. The field name stored in the KeyFldName is used to extract the corresponding data from the trigger data structure. The contents of the KEY field should contain a unique key that allows you to get back to the original database record. Obviously, some files have multiple fields that make up the key, so you may need to extend the logic of the GetKeys subroutine to handle multiple key fields.

The complete source code for this program can be downloaded here. This program is just one example of how to harness the power of pointers. While this program isn't quite the "universal business adapter" touted in IBM's commercials, it can be adapted to fit many of your own business needs. Download it and attach it to one of your own test files to see just how universal this trigger program is.

Raymond Everhart is an independent programmer/consultant in the Dallas/Fort Worth area and has 17 years of experience with IBM midrange servers. E-mail: reverhart@raecodesign.com.

Sponsored By PROFOUND LOGIC SOFTWARE

Announcing RPGsp! RPG Smart Pages (RPGsp) is an Integrated Development Environment for creating dynamic web applications. If you are a shop with strong RPG skills, you can start developing e-Business / e-Commerce or Intranet Applications IMMEDIATELY! It's easier than traditional RPG/DDS development! RPG Smart Pages (RPGsp) integrates an RPG aware WYSIWYG HTML Designer with RPG-Alive enabled Code Editing Capabilities, allowing you to create and compile dynamic Web applications as well as traditional RPGLE programs from one sophisticated GUI development environment! For a Free Trial or more information, contact Profound Logic Software at www.ProfoundLogic.com