The following are performance tips to consider when using triggers support:
y
Triggers are activated by an external call. The user needs to weigh the benefit of the trigger against
the cost of the external call.
y
If a trigger is going to be used, leave as much validation to the trigger program as possible.
y
Avoid opening files in a trigger program under commitment control if the trigger program does not
cause changes to commitable resources.
y
Since trigger programs are called repeatedly, minimize the cost of program initialization and
unneeded repeated actions. For example, the trigger program should not have to open and close a file
every time it is called. If possible, design the trigger program so that the files are opened during the
first call and stay open throughout. To accomplish this, avoid SETON LR in RPG, STOP RUN in
COBOL and exit() in C.
y
If the trigger program opens a file multiple times (perhaps in a program which it calls), make use of
shared opens whenever possible.
y
If the trigger program is written for the Integrated Language Environment (ILE), make sure it uses the
caller's activation group. Having to start a new activation group every time the time the trigger
program is called is very costly.
y
If the trigger program uses SQL statements, it should be optimized such that SQL makes use of
reusable ODPs.
In conclusion, the use of triggers can help enforce business rules for user applications and can possibly
help improve overall system performance, particularly in the case of applying changes to remote systems.
However, some care needs to be used in designing triggers for good performance, particularly in the cases
where commitment control is involved. For more information see the redbook
Stored Procedures,
Triggers and User Defined Functions on DB2 Universal Database for System i
.
4.12 Variable Length Fields
Variable length field support allows a user to define any number of fields in a file as variable length, thus
potentially reducing the number of bytes that need to be stored for a particular field.
Description
Variable length field support on i5/OS has been implemented with a spill area, thus creating two possible
situations: the non-spill case and the spill case. With this implementation, when the data overflows, all of
the data is stored in the spill portion. An example would be a variable length field that is defined as
having a maximum length of 50 bytes and an allocated length of 20 bytes. In other words, it is expected
that the majority of entries in this field will be 20 bytes or less and occasionally there will be a longer
entry up to 50 bytes in length. When inserting an entry that has a length of 20 bytes or less that entry will
be inserted into the allocated part of the field. This is an example of a non-spill case. However, if an entry
is inserted that is, for example, 35 bytes long, all 35 bytes will go into the spill area.
IBM i 6.1 Performance Capabilities Reference - January/April/October 2008
©
Copyright IBM Corp. 2008
Chapter 4 - DB2 Performance
59
