Program to make Trigger Output file

Several people have asked if I would give the source code for the program I wrote to generate the Trigger Output file I mentioned in my earlier post about writing a Trigger program. When I looked at the source code I was a bit embarrassed. By the looks of it I had written it as one of my first free form RPG programs when it was released, 2001, and I used the output file from the Display Field File Description command, DSPFFD. This request gave me an excuse to rewrite it using modern RPG.

The purpose of the program is to build a Trigger Output file. When the trigger, I talked about in my prior post, executes I want to write the before and after images of the data to an output file. Therefore, the output file must contain every field that is found in the original file and I always include these extra fields:

• Trigger event flag, was this an insert, delete or update operation
• The time the trigger happened. This way I can sort the output file in chronological order
• Job name, user, and number allows me to track who made the change

If the original file has only a few fields I can build the output file in a couple of minutes. If the original file contains many fields it can take some time to code it, and there is always the danger I have missed a field. This is the reason why I wrote the program in the first place. It takes no time to run and creates the perfect DDS source code every time.

Let me start by showing the first little bit of my test file, PARTMST.

A R PARTMSTR A PARTNBR 15A COLHDG('Part' 'number') A PARTDESC 30A COLHDG('Part' 'description')

I know it is poor form to code the field sizes in the file. If this was a real DDS database I was creating I would be using a field reference file to define all the fields used in all the files that are part of that database.

The program to generate the output file is going to create a source file in QTEMP that will contain the source member generated. What I am showing below is a "stripped down" version of my program, which shows you the interesting/juicy parts not the parts to do with how the file and library name are passed to the program.

To make it easier to understand, and for me to explain, I am going to "chop" the source code into parts. The first part is the definitions.

01 ctl-opt dftactgrp(*no) ; 02 dcl-f @SRCFILE usage(*output) extfile('QTEMP/@SRCFILE') extdesc('QTEMP/@SRCFILE') rename(@SRCFILE:OUTPUT) usropn ; 03 dcl-s File char(10) ; 04 dcl-s Library char(10) ; 05 dcl-ds Array qualified dim(8000) ; //Max No. cols in table 06 Column char(10) ; 07 end-ds ; 08 dcl-s Elements packed(4) inz(%elem(Array)) ; 09 dcl-s Retrieved like(Elements) ; 10 dcl-c AddMember 'ADDPFM FILE(QTEMP/@SRCFILE) MBR(' ; 11 dcl-s Text char(256) ; 12 Text = AddMember + %trimr(File) + ') TEXT(''Copy of ' + %trimr(File) + ''') SRCTYPE(PF)' ;

Line 1: As this program contains a procedure I need DFTACTGRP(*NO).

Line 2: This is the definition for the source file that the generated DDS source will be written to. I am using it for output only so I need to have USAGE(*OUTPUT). EXTFILE tells the compiler where to find the file. EXTDESC tells the compiler where to find the definition of the file. RENAME renames the record format, as I cannot have a record format and file with the same name this is where I "rename" the record format within this program. USROPN I will be controlling when the file is opened and closed.

Lines 3 and 4: The definitions for the variables for my original file's name and the library it is found in. I could get these as a parameters passed to this program or from a display file. I will leave you to determine how you would want to populate these variables.

Line 5 – 7: If I do a multiple row Fetch using SQL I need to receive the data into a data structure array. So this data structure has one subfield, line 6, as all I want is the field name. The array has 8,000 elements as that is maximum number of columns you can have in a table.

Line 8: Rather than hard code the number of rows to retrieve in a Fetch I am using this variable, initialized to the number of elements in the data structure array. If I decide at a later data to reduce the number of elements in the data structure array I can just change it, and not this have to remember to change this one too.

Line 9: I am going to be using this variable to contain the number of rows I fetched using the SQL Fetch.

Line 10 - 11: I am defining the first part of the Add Physical File Member command, ADDPFM, in a constant on line 13. Line 14 defines a variable I will be using to contain the command string. Line 11 creates the command string and places it in the variable.

The next part of the program I am going to call the "SQL part".

13 exec sql CALL QSYS2.QCMDEXC('DLTF FILE(QTEMP/@SRCFILE)') ; 14 exec sql CALL QSYS2.QCMDEXC('CRTSRCPF FILE(QTEMP/@SRCFILE)') ; 15 exec sql CALL QSYS2.QCMDEXC(:Text) ; 16 exec sql DECLARE C0 CURSOR FOR SELECT SYSTEM_COLUMN_NAME FROM QSYS2.SYSCOLUMNS WHERE SYSTEM_TABLE_NAME = :File AND SYSTEM_TABLE_SCHEMA = :Library FOR READ ONLY ; 17 exec sql OPEN C0 ; 18 exec sql FETCH C0 FOR :Elements ROWS INTO :Array ; 19 if (SQLCOD <> 0) ; 20 return ; 21 endif ; 22 exec sql GET DIAGNOSTICS :Retrieved = ROW_COUNT ; 23 exec sql CLOSE C0 ;

Line 13 - 15: SQL has its own QCMDEXC function, I believe it was introduced as part of IBM i 7.2 . Unlike the API we all know and love that has to be defined as an external procedure in the program, this can just be called with a EXEC SQL statement. Notice that there is only one parameter, the string to execute, I do not pass the length.

Line 13: Deletes the source file.

Line 14: Creates the source file.

Line 15: Executes the ADDPFM statement I created earlier. As this is a variable there has to be a colon ( : ) before the variable's name.

Line 16: I am defining the cursor for what I want to retrieve out of the SYSCOLUMNS view. This is a very useful as it contains a list of every field in every file/table, except those in QTEMP. For more information about this view see Getting field definitions using SYSCOLUMNS. I am only interested in retrieving the system name of the fields in the file I want. And I only want to open the cursor for input (read only).

Line 17: As I have a cursor I need to open it before I can use it.

Line 18: This is a multiple row Fetch, which I describe in detail in SQL blocking fetches. This allows me to fetch all the eligible rows in one input operation. Which is faster than doing multiple input operations with each Fetch retrieving one row at a time.

Line 19 – 21: If there was an error when I fetched I will exit this program, as there is no information to build the file from.

Line 22: In this line I am retrieving the number of rows I fetched, and placing it in the variable Retrieved. I will be using this later.

Line 23: I am finished with the cursor so I can close it.

The last part of the program writes the creates the source records and writes them to the source file.

24 open @SRCFILE ; 25 SRCSEQ = 0 ; 26 %subst(SRCDTA:6:1) = 'A' ; 27 %subst(SRCDTA:45) = 'REF(' + %trimr(Library) + '/' + %trimr(File) + ')' ; 28 WriteRecord() ; 29 %subst(SRCDTA:17) = 'R RCDFORMAT' ; 30 WriteRecord() ; 31 %subst(SRCDTA:17:11) = ' TRGTIME' ; 32 %subst(SRCDTA:35:1) = 'Z' ; 33 %subst(SRCDTA:45:35) = 'COLHDG(''Trigger'' ''time'')' ; 34 WriteRecord() ; 35 %subst(SRCDTA:19:10) = 'JOBNAME' ; 36 %subst(SRCDTA:33:3) = '10A' ; 37 %subst(SRCDTA:45:35) = 'COLHDG(''Job'' ''name'')' ; 38 WriteRecord() ; 39 %subst(SRCDTA:19:10) = 'JOBUSER' ; 40 %subst(SRCDTA:45:35) = 'COLHDG(''Job'' ''user'')' ; 41 WriteRecord() ; 42 %subst(SRCDTA:19:10) = 'JOBNBR' ; 43 %subst(SRCDTA:33:5) = ' 6S 0' ; 44 %subst(SRCDTA:45:35) = 'COLHDG(''Job'' ''number'')' ; 45 WriteRecord() ; 46 %subst(SRCDTA:19:10) = 'TRGTYPE' ; 47 %subst(SRCDTA:34:4) = '2A ' ; 48 %subst(SRCDTA:45:35) = 'COLHDG(''Trigger'' ''type'')' ; 49 WriteRecord() ; 50 %subst(SRCDTA:29) = 'R' ; 51 for Elements = 1 to Retrieved ; 52 %subst(SRCDTA:19:10) = Array(Elements).Column ; 53 WriteRecord() ; 54 endfor ; 55 close @SRCFILE ;

Line 24: As I defined the output file as USROPN I need to open it before I can write to it.

All source files have three fields:

1. SRCSEQ - source sequence number, has to be unique
2. SRCDAT - source date, which I am going to leave as it default, zero
3. SRCDTA - source data, the place for the code

Line 25: I am initializing the source sequence number here, and will be incrementing just before the write.

Line 26: As this is a DDS source member I need a 'A' in the sixth position.

Line 27: I am just going to define all the fields from the original file in the output file by using Reference fields. Therefore, I need to have the name of the file to which I am going to be referring as the first record.

Line 28: I am calling the WriteRecord procedure passing the data in SRCDTA to it. I will describe later what this procedure does.

Line 29: This substring only has two parameters instead of more customary three. What this means is that the string is moved into the variable starting at the position given and is as long as the rest of the variable. I am doing this to "blank out" the data I wrote into SRCDTA on line 27.

Line 30: The value in SRCDTA is passed to WriteRecord. I am not going to bother to repeat descriptions of the copies of this line elsewhere in the program.

Line 31: Even though the field name column starts in position 19 this substring starts at position 17 to "blank out" the data remaining from line 29.

Line 32: As this is a timestamp field I need to have 'Z' in the field type column.

Line 33: I do not need to have column headings, but I have added them to this program.

Line 35: The name of the next field.

Line 36: This field is 10 characters, which means the substring needs to start at position 33.

Line 39 – 41: I do not have to define the length and type of the field as it is same as the previous one.

Line 43: The field size and type has to start in position 33 so it "blanks" out what was there from before.

Line 50: This substring puts the 'R' in column 29 to denote that the following fields' definitions are referenced from the reference file, line 27. There is no "to" value in the %SUBST as I want this to "blank" out the rest of the line.

Lines 51 – 54: Now I can make the source lines for the fields in the original file, that are held in the data structure array.

Line 51: I am going to perform this FOR group the same number of times as rows I retrieved from my SQL fetch. I am "reusing" the variable Elements just for somewhere to contain the count of the number of times this For is performed. If you are not familiar with the For operation code see FOR replaces DO.

Line 52: Here is where I move the value from the array element into the field name position of SRCDTA.

Line 55: After exiting the For loop all the source records have been written to the source file, therefore, I can close it.

I created the WriteRecord rather than repeating the same two lines of code before the write to the source file. The procedure starts on line 56 and ends on line 59.

56 dcl-proc WriteRecord ; 57 SRCSEQ += 1 ; 58 write OUTPUT ; 59 end-proc ;

Line 57: Add one to the SRCSEQ, to make sure this value is unique.

Line 58: Writes to the source file.

When finished and I go to the source member, PARTMST, in the source file, QTEMP/@SRCFILE, my source code looks like:

A REF(MYLIB/PARTMST) A R RCDFORMAT A TRGTIME Z COLHDG('Trigger' 'time') A JOBNAME 10A COLHDG('Job' 'name') A JOBUSER 10A COLHDG('Job' 'user') A JOBNBR 6S 0 COLHDG('Job' 'number') A TRGTYPE 2A COLHDG('Trigger' 'type') A PARTNBR R A PARTDESC R

I would rename the source member to a different name from the original file, I will leave you to devise you own naming convention for trigger output files. Then copy this source member to my production source file. When this source member in the production source file is compiled I am ready to create my trigger program for the original file to write all changes to this file.

 

You can learn more about SQL's QCMDSRC from the IBM website here.

 

This article was written for IBM i 7.3, and should work for 7.2.