Example of a DB2 insert trigger program written in RPG ILE.
This is a very easy example, however, it clarifies several things.
Whenever the system calls this program , it(System) passes basically two parameters. The first parameter contains all information we need in the program. e.g. the file, library and member names, Relative record number of the inserted record, commitment lock type, trigger event (Insert, Update etc), trigger time(Before/After), the offset of the old and new records and before and after(Trigger event) images of the database record etc.
The second parameter contains the length of the first parameter. This is because the system passes a variable length parameter(first entry parameter). Though it will be fixed for a specific file.
About this example
In the current example I want the backup of a records of a file to be written to a separate file. For this, I write a trigger program which is to be invoked after a new record has been written to the main file (EMPDATA1). In this example, I am interested in only the offset length after which the newly inserted record begins in the first parameter passed to this program.
Before seeing the code I think it will be very appropriate to give the source of the DDS of the EMPDATA1 and EMPDATA2.
EMPDATA1 A R EMPDTA01 A NAME 10 A ADDRESS 10 EMPDATA2 A R EMPDTA02 A NAME 10 A ADDRESS 10
0001.00 FEMPDATA2 IF A E Disk
0002.00 *
0002.01 * Define the prototype of the program
0002.02 *
0002.03 D DB2TRG PR
0002.04 D @Parm3 4096 Options(*VarSize)
0002.05 D @Parm4 4B 0
0002.06 *
0002.07 * Define the procedure interface of the program
0002.08 *
0002.09 D DB2TRG PI
0002.10 D @Parm1 4096 Options(*VarSize)
0002.11 D @Parm2 4B 0
0002.12 *
0002.13 * Define the data structure which will be used to retrieve the database
0002.14 * properties.
0002.15 *
0002.16 D @DBProps DS
0002.17 D File 1 10
0002.18 D Lib 11 20
0002.19 D Member 21 30
0002.20 D TrgEvent 31 31
0002.21 D TrgTime 31 31
0002.22 D CmtLockType 31 31
0002.23 D CCSID 37 40B 0
D RRN 41 44B 0 //Added later
0002.24 D OrigOffSet 49 52B 0
0002.25 D OrigLength 53 56B 0
0002.26 D OffSetMap 57 60B 0
0002.27 D LengthOfMap 61 64B 0
0002.28 * New records offset length
0002.29 D NewOffSet 65 68B 0
0002.30 D NewLength 69 72B 0
0002.31 D NewSetMap 73 76B 0
0002.32 D LengthOfNMap 77 80B 0
0002.33 D OldRcdNull 81 96
0002.34 D OldRcd 97 116
0002.35 D OldRcdMap 117 120
0002.36 D NewRcd 121 140
0002.37 D NewRcdNull 141 144
0002.38 *
0002.39 * This parameter tells the exact length of the buffer
0002.40 *
0002.41 D @Buffer DS
0002.42 D LengthOfParms 1 4B 0
0005.01 C Eval @DBProps = @Parm1
0005.03 C Eval NAME = %SubSt(@Parm1: NewOffSet+1 : 10)
0005.04 C Eval ADDRESS = %SubSt(@Parm1: NewOffset + 11:
0005.05 C 10)
0006.00 C Write EMPDTA02
0007.00 C Eval *InLr = *On
0008.00 C ReturnPlease note that this program uses a procedure interface hence compile this program with 15 option from wrkmbrpdm (command CRTRPGMOD) and then create program using the CRTPGM command. Alternatively, specify the keyword EXTPGM at the prototype level and compile with 14 option (Command CRTBNDRPG).
The steps we followed
The sequence of operations performed in the example can be given as below.
- Define two entry parameters, one of variable length second of 4B 0.
- Define a Data structure to retrieve the offset values. Receive the first entry parameter into this Data structure.
- Get different fields values by using the substring function. The first field value begins from the offset +1 position of the first entry parameter of the program.
- Once you have got your fields' values you are free to do whatever you wish to do with them!
- Test your program by inserting a record using the SQL utility.
The above program has been tested and it works absolutely as expected. May be I will post a video tutorial on this website in coming future to illustrate exactly how it worked.
Anyways, Now that we have written the program we need to associate this program (as a trigger program) to the physical file EMPDATA1. How do we do that? We do this using the ADDPFTRG command. More about this and other commands relevant to AS400 DB2 Triggers will be discussed on the next page of this article.
- 7306 reads