SQL adaptation guide For IBM DB2 UDB 8.x, 9x / Connecting to DB2 OS/400 |
With DB2® on OS/400®, triggers need to be external programs written in a high level language such as C, COBOL, RPG, or PL/I.
To create a trigger, use the following steps:
Create a source physical file on your AS/400® for the trigger programs. Each trigger program will be stored in a separate member within this source file.
Type:
CRTSRCPF FILE(library/file)
The file name should be ten characters or fewer.
Create a source file member for each trigger program. After the creation of trigger programs (in the next step), the programs will be forwarded to these members.
Type:
ADDPFM
Enter field values:
FILE = name of the source file you just created
LIBRARY = name of the library you created for your database
MEMBER = name you want to give the trigger source member
Repeat this operation for each trigger.
The OS/400-compatible languages include: ILE C/400®, ILE COBOL, ILE RPG, COBOL, PL/I, and RPG.
The script creation file of the trigger should be send via FTP into library/sourcefile.member, where sourcefile and member are the values specified in the previous step.
Once the trigger programs are in AS/400 members, you can compile them. Use whichever compiler is appropriate for the language you used to create the trigger program.
After you compile the trigger programs, "bind" each compiled program file. Binding will establish a relationship between the program and any tables or views the program specifies.
Type:
CRTPGM PGM (library/program) ACTGRP(*CALLER)
where:
library is the name of the library you created for your new database
program is the name of the compiled trigger program
Repeat this operation for each trigger.
The final step for migrating triggers is to add each program to a physical file. This will tie the trigger program to the table that calls it.
Type:
ADDPFTRG
Enter field values:
PHYSICAL FILE = name of the table you want to attach the trigger to
PHYSICAL FILE LIBRARY = name of the database library
TRIGGER TIME = either *BEFORE or *AFTER.
TRIGGER EVENT = *INSERT, *DELETE, or *UPDATE.
PROGRAM = name of the compiled program file
PROGRAM LIBRARY = name of the database library.
REPLACE TRIGGER = *YES.
ALLOW REPEATED CHANGES = *YES.
The trigger program is now tied to the table specified in the Physical File field and will be called each time the database action you specified occurs. The trigger program may be called from interactive SQL, another AS/400 program, or an ODBC insert, delete, update, or procedure call.