iSeries Trigger Techniques Re-Visited

IBM Manual: Stored Procedures, Triggers and User Defined Functions on DB2 Universal Database for iSeries

by Alex Jayasundara
and Thibault Dambrine

Think of a hypothetical situation where a system has been running for many years, with some "known bugs, but nothing too critical". Consequences? Minor - One of the senior programmers simply runs a quick scan program and fixes a few records once in a while. It is a problem, a "known bug", but the company can survive without too much of a hiccup. Sounds familiar?

What if the consequences were MAJOR? What if individual bank accounts were at stake? Actual people's paychecks could be affected? How can one make an application bug-proof? Bug-proofing any application, especially complex and sensitive programs such as ones affecting payrolls, medical records or banking records is not a trivial task. One giant step towards increasing reliability by killing obvious bugs can be achieved with triggers.

Trigger programs typically are simple and automatically activated. They enforce simple rules, such as "don't allow creation of a detail record without an existing header" or "don't allow a money transfer for an amount greater than what is available in the source account". They can also be more sophisticated, look at related records in a number of other files before deciding to take action, which itself can be a number of things, from writing a log record to preventing an action or sending a message.

The point here is that simply said, bigger bugs tend to occur less often if the more obvious ones are kept in check.

While the general principle of triggers has not changed much, over time the AS/400 has become the iSeries, languages used on this machine have evolved, and so have trigger techniques. So, get a cup of coffee, have a seat, and we will show you with a few examples how you too can use triggers to make your systems more reliable.

Trigger Basics

The official, IBM definition of a database trigger is as follows:

Triggers are user-written programs that are associated with database tables. You can define a trigger for update, delete, and insert operations. Whenever the operation takes place, regardless of the interface that is changing the data, the trigger program is automatically activated by DB2 database and executes its logic. In this way, complex rules can be implemented at the database level, totally independent from the application layer.

Triggers are mainly intended for monitoring database changes and taking appropriate actions. The main advantage of using triggers, instead of calling the program from within an application, is that triggers are activated automatically, regardless of the process at the origin of the data change.

In addition, once a trigger is in place, application programmers and end users cannot circumvent it. When a trigger is activated, the control shifts from the application program to the database manager. The operating system executes the trigger program to perform the actions you designed. The application has no choice but to wait until the trigger program has finished its job and only then gets control again.

Triggers may cause other triggers to be called. This is a consideration one has to understand when designing triggers, which affect files that are themselves equipped with triggers.

Type of Triggers

There are two types of triggers available in iSeries database tables. They are SQL triggers and External triggers.

SQL triggers can be created using the SQL CREATE TRIGGER statement. Part of the task when using SQL to create a trigger involves specifying the name of the trigger, what table it is attached to, when it should be activated (BEFORE INSERT, AFTER UPDATE etc), and finally what actions it should perform. Using the SQL statement provided, the operating system will create a C program with your SQL statements embedded within. In effect, the system will do a combination of CRTSQLCI and CRTPGM commands to produce the final trigger program. What will be visible to the user will be an ILE C *PGM object.

External triggers are more conventional user written programs. They may or may not contain SQL statements. External triggers can be created using any high level language capable of generating *PGM objects. Each programming language has its own strengths and weaknesses. It is up to the programmer to select the optimal programming language to begin with. Since external triggers start life as regular programs, they have to be manually attached to a file to be implemented as triggers. This can be done with either ADDPFTRG command or with the iSeries Navigator.

On the topic of iSeries languages in particular, SQL is emerging as the more universal database access language. The iSeries has seen its SQL abilities grow in the last few years. C is emerging as the language of choice for applications that demand more portability. Finally, RPG did evolve in the last 8 years. In this article we will discuss triggers using pure SQL (SQL Triggers) and External triggers using RPG and C. On that particular point, we will use both SQL and conventional OS/400 terminology interchangeably in this article. Appendix 1 gives the equivalent terminology for both.

Benefit of Triggers

Typical use of triggers include:

Ø Provide consistent auditing

Ø Prevent invalid transactions

Ø Enforce complex business rules

Ø Enforce complex security authorizations

Ø Provide automatic event logging

Ø Automatically generate derived column values Preserving data consistency across different database tables

Ø Data validation and audit trail

Regardless of the application and the use one can make of the trigger, the principles remain the same. The trigger fires on a given condition, say, a deletion or the addition of a new record. When this happens, the contents of the old and the new record are passed to the trigger program and the same decision, the same rules and the same actions are taken every time, regardless of what process modified the content of the file. Note that because trigger programs will be fired for a number of circumstances, they should be very well tested, as buggy trigger programs can create giant size messes, depending on what they do.

Automated Teller Machine transaction logging system Example

To illustrate triggers in the real world, we will use an every-day life example: depositing or withdrawing money through ATM. Using that example, we will explore the following scenarios:

The iSeries is writing Automated Teller Machine transactions by writing add/change/delete row(s) to a transaction file named ATMTXN. The table ATMTXN is equipped with a trigger. For each operation on ATMTXN the trigger will log the transaction to a file named ATMTXNLOG.

While our example will only do logging, this solution can be easily enhanced to update account balances, maintain ATM totals or perform other outside operations.

The base of our example resides in two files: The transaction file, ATMTXN, and the logging file, ATMTXNLOG. Here are the DDS maps for these two files:

* ATM Transactions

UNIQUE

R ATMTRANR

ATMID 5A COLHDG('ATM ID')

ACCTID 5A COLHDG('Account #')

TCODE 1A COLHDG('Txn Code')

AMOUNT 7S 2 COLHDG('Txn Amount')

DESC 10A COLHDG('Txn Description')

ALWNULL

K ATMID

K ACCTID

DDS for ATMTXN

The fields in ATMTXN are self-explanatory. Note the ALWNULL keyword on field DESC, which will allow this field to contain NULL's. We made DESC field as null-capable to be able to explore the topic of NULL maps in relationship with their purpose when using triggers.

ATMTXNLOG

* ATM Transactions log

R TXNLOGR

LOPER 1A COLHDG('Operation')

LDATE L COLHDG('Date')

LTIME T COLHDG('Time')

LIMGB 28A COLHDG('Before Image')

LNMAPB 5A COLHDG('Null Map - Before')

LIMGA 28A COLHDG('After Image')

LNMAPA 5A COLHDG('Null Map - After')

DDS for ATMTXNLOG

IN ATMTXNLOG,

- Field LOPER will contain a transaction code (I – insert, U – update, D – delete) to identify the type of operation.

- LDATE and LTIME will hold the date and time of the transaction.

- LIMGB and LNMAPB will hold the record before the operation and null map before the operation respectively

- LIMGA and LNMAPA are the corresponding fields for after the operation.

- LNMAPB and LNMAPA fields will contain the NULL MAPS for each of the record images. NULL maps are sequences of ones and zeros used to indicate the presence of null values in the field they represent. There are as many bytes in the NULL map as there are fields. Encountering a NULL value in a field can make a high level program crash. The trigger program can use the NULL Map to know in advance if a field contains NULLs. The idea is to enable program logic based on the value of the NULL indicator rather than attempting to use a NULL-filled field which would make it crash.

Overview of the Design

The diagram above shows that when any modification is made to ATMTXN, the system will invoke the trigger program named ATMTXTRG. ATMTXTRG will capture the old record, the old null map, the new record and the new null map. It will then write these fields in the TXNLOGGER, along with the NULL maps. This design was selected to facilitate the discussion of ATMTXTRG using different languages (RPG and C), that have comparable features. Later, we will discuss the same trigger example, using SQL this time, to do the job.

Basic information for trigger programming

When a trigger is fired, the system provides two key elements for trigger program. They are the Trigger Buffer and the Trigger Buffer Length. An important point is that the Trigger buffer contains both a static area and a dynamic area. More details on the trigger buffer and its sub-fields can be found in the DB2 UDB for iSeries Database Programming V5R1.

Here is a graphic picture of what this buffer looks like:

10 20 30 31 32 33 36 40 44 48

Physical

File Name

Library

Name

Member

Name

Trig.

Event

Trig.

Time

Cmt

Level

CCSID

Relative

Rec. #

64 80 96

Old Record Information

New Record Information

Offset

Length

Null Map

Offset

Null Map

Length

Offset

Length

Null Map

Offset

Null Map

Length

Old Record

Null Map

New Record

Null Map

Trigger Buffer – This buffer will be passed to the external trigger program

Reserved by IBM for future use

In the trigger buffer, position 1 to 44 contains physical file information. This information may be useful in some circumstances, for example if the trigger monitors for the size of the file or if the trigger program has a decision to make based on the member being used. Position 49 to 80 however tends to be the bread and butter information for most trigger programs. Starting at position 49, you will find four fields, which can be better qualified as pointers. They contain offsets and lengths for "old" or before image of the record being modified and its corresponding null map. The same pointers for new record can be found at position 65. This whole area of the trigger buffer (position 0 thru 96) is called as static area.

Following the static area, in a variable position (you will notice the diagram does not specify a position), is the dynamic area. This area will contain the before and after records and their corresponding NULL maps. This is most commonly the actual data that any trigger program will use to make a decision.

Before V5R1 the before and after record images were placed soon after the position 96. While this sounds ambiguous, there is a reason. Here is how it used to work: . So if you had a file with F number of fields and a record length of L, then the total length of the trigger buffer would be 96 + 2 * (L + F). In effect, the static and dynamic parts of the trigger buffer were contiguous.

After V5R1, the dynamic portion of the trigger buffer may not come immediately after the static portion of the buffer. Note that in potentially, these fields can be change on every execution of the trigger for the same file even if the field definitions did not change.

Based on above facts there is no way to access the dynamic area correctly unless we use the information on position 49 thru 80 to find where the dynamic portion of the data is located.

Because of this change, most trigger programs written prior to V5R1 are not working well after V5R1. In this article we will assume that all four dynamic data is placed in the different locations and will use the pointers from position 65 thru 80 to determine the starting positions as follows. Another notable V5R1 change is that IBM has removed the previous limit of 6 triggers per table since V5R1. Now one database table can have maximum of 300 triggers.

Starting position of old record = Starting position of trigger buffer + Old record offset

(value from position 49 – 52)

Starting position of new record = Starting position of trigger buffer + New record offset

(value from position 64 – 67)

Starting position of old null map = Starting position of trigger buffer + Old null map offset

(value from position 57 – 60)

Starting position of new null map = Starting position of trigger buffer + new null map offset

(value from position 73 – 76)

Trigger Example using RPG ILE

When writing any trigger program, the first order of the day is defining the trigger buffer. Here below is the definition of the trigger buffer for RPG. Here is the definition for this example:

D* Data structures for Trigger Buffer

D* and Trigger Buffer Length

D* Trigger Buffer

DTrgBuffer DS

D TFileName 10

D TLibName 10

D TMemName 10

D TTrgEvent 1

D TTrgTime 1

D TCommitLock 1

D TFiller1 3

D TCCSID 10I 0

D TRelRecNbr 10I 0

D TFiller2 10I 0

D TOldRecOff 10I 0

D TOldRecLen 10I 0

D TOldNullOff 10I 0

D TOldNullLen 10I 0

D TNewRecOff 10I 0

D TNewRecLen 10I 0

D TNewNullOff 10I 0

D TNewNullLen 10I 0

D* Trigger Buffer Length

DTrgBufferLen S 10I 0

Trigger Buffer for RPG

Note the use of I (integer) data type for all the binary data types. This is because integer data type is more efficient and supports full range of values possible. One useful technique is to define the trigger buffer data structure as a separate source member. It can then be used with a COPY statement in any trigger program. We saved the above as TRGBUFFER with the type DS (for Data Structure) and it will be included in our ATMTXTRG RPG version.

Here is the RPG portion of ATMTXTRG. For clearer understanding, we will break it down in five parts:

Part 1 – Defining the trigger buffer and other variables.

*. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...

*************** Beginning of data *************************

D/COPY QRPGLESRC,TRGBUFFER

The only line in the code above will copy our trigger buffer to the program at time of compilation.

Part 2 – Declaring data storage for old and new records

To declare structures describing the before and after images of the records monitored by the trigger program, there is a good shortcut available in ILE RPG: Use the file name to serve as an externally defined data structure. We are going to use Extname keyword to define the external file name and Prefix keyword for global renaming of all fields in the database file. It is important to distinguish fields from new and old records if you are using your driver program to initiate specific tasks based on specific information (eg. Send a notification for a withdrawal over certain amount).

*. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7

D* Record format for New and Old Records.

D OldRecord E DS ExtName(ATMTXN)

D Prefix(O_)

D Based(OldRecPtr)

D NewRecord E DS ExtName(ATMTXN)

D Prefix(N_)

D Based(NewRecPtr)

By combining Prefix and ExtName keywords what program does is copy the fields of ATMTXN with prefix O_ to the data structure “OldRecord” and copy the fields of ATMTXN with prefix N_ to the data structure “NewRecord”. You will see this reflected in the compilation listing.

The keywords "Based" are used to identify which pointer is used to access the data for a given data structure. As soon as the pointers are populated with the required data, the record information will be made available in the data structure.

Part 3 – Declaring data storage for old and new null maps

As a reminder, the NULL maps contain a series of one-byte flags. There is one NULL map byte per field and they are grouped in a data structure. Access to the NULL map data structure will be done with the same method we used for the record data. The length of the null map is always equal to number of fields in the data base file (one byte per field). In this example, we declare an array with the dimension of 5 one-byte elements since our file only has five data fields.

DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords+++++++++++++++++++++++++

D* Record layout for New and Old Null Maps.

D OldNullMap DS Based(OldNullPtr)

D ONullFld 1 DIM(5)

D NewNullMap DS Based(NewNullPtr)

D NNullFld 1 DIM(5)

Here we have defined two pointers named “OldNullPtr” and “NewNullPtr” to hold the starting positions of the null maps and by setting the values for these pointers data will be available on “OldNullMap” and “NewNullMap”.

Part 4 – Declaring the parameter list

The trigger buffer and trigger buffer length will be passed to the program by the system as parameters each time the trigger is fired. These parameters are written in the most standard RPG.

CL0N01Factor1+++++++Opcode&ExtFactor2+++++++Result++++++++Len++D+HiLoEq

C *ENTRY PLIST

C PARM TrgBuffer

C PARM TrgBufferLen

Part 5 – Setting pointers

We will use the RPG built in function %ADDR to supply the values to the pointers as shown below. This will effectively populate the pointers that will tell the program where to locate the data structures described in the sections above.

CL0N01Factor1+++++++Opcode&ExtExtended-factor2+++++++++++++++++++++++++++

C Eval OldRecPtr = %ADDR(TrgBuffer) + TOldRecOff

C Eval NewRecPtr = %ADDR(TrgBuffer) + TNewRecOff

C Eval OldNullPtr = %ADDR(TrgBuffer) + TOldNullOff

C Eval NewNullPtr = %ADDR(TrgBuffer) + TNewNullOff

As soon as the “OldRecPointer” is retrieved, data will be available in “OldRecord”. The same principle will apply to the other combinations of pointers and data structures.

Having retrieved the pointers and thus the associated data structures, the program now has access to the before and after image of the record, broken up by field that can be distinguished. By this, we mean that any of the old record fields can be individually compared to its corresponding new version. The program is now equipped with all the information necessary to write the logic within the program.

The example we have chosen to write here is a simple logging function. We could have references to other, outside files, but for now, on with this example. The trigger program will log the before and after images of the records modified, along with their NULL maps and pass this information as parameters to a separate program named TXNLOGGER.

C* Call TXNLOGGER

C CALL 'TXNLOGGER'

C PARM TTrgEvent

C PARM OldRecord

C PARM OldNullMap

C PARM NewRecord

C PARM NewNullMap

C* End the program

C Eval *InLR = *On

Code for TXNLOGGER

Using the parameters passed earlier on, TXNLOGGER will write the actual log file record.

FATMTXNLOG O A E DISK

DInsert C CONST('1')

DDelete C CONST('2')

DUpdate C CONST('3')

DTrgEvent S 1A

DOldRecord S 28A

DOldNullMap S 5A

DNewRecord S 28A

DNewNullMap S 5A

DCurTime S T

DCurDate S D

C *ENTRY PLIST

C PARM TrgEvent

C PARM OldRecord

C PARM OldNullMap

C PARM NewRecord

C PARM NewNullMap

C TIME CurTime

C TIME CurDate

C MOVE CurDate LDATE

C MOVE CurTime LTIME

C Select

C When TrgEvent = Insert

C Move 'I' LOPER