Using DB2/400 effectively

1. Using DB2/400 effectively

2. Traditional iSeries database usage Applications are responsible for data integrity Back door access possible Defensive coding required Data integrity built in Application may assume data integrity Code is smaller Data cannot be corrupted ... assuming correct database design Data integrity facilities

3. Changes to database files Visual Basic program using ODBC RPG program in interactive job RPG program in batch job Database file YWRKF DFU

4. Data integrity facilities • Referential integrity forces internal consistency of the database • Order present implies customer details present • Triggers force the database to comply with local company rules • Field/record validation before database change • Additional processing after database change

5. Integrated Language Environment • Develop code in most suitable language(s), and in small modules, without traditional performance trade-off • Multiple activation groups within job • Commitment control in one activation group is independent of that in another

6. Original Program Model • Completely dynamic • Convenient • Maintainable • Slow • One entry point per program

7. Integrated Language Environment • Static (*PGM) or dynamic (*SRVPGM) • Calls to OPM programs supported but slow • OPM may call ILE *PGM’s main entry point only • Much, much faster than OPM

8. Referential constraints Customer file CUSNBR CUSNAM . . . Parent file Parent key Foreign key Dependent file Order headers ORHNBR CUSNBR PK Parent file FK Dependent file Order details ORHNBR PRDNBR Compare Synon/2 OWNED BY and REFERS TO relationships

9. Referential constraints: types • RESTRICT (update or delete) • CASCADE (delete) • NO ACTION (update or delete: *BEFORE trigger is invoked, but database is not updated) • SET DEFAULT (delete) • SET NULL (delete)

10. Referential constraints:when the checks are made • Delete from parent file • Update to parent file (where constrained key is being updated) or to dependent file • Insert into dependent file

11. Referential constraints: journalling and access paths • RESTRICT does not require journalling • For all other rules • parent and dependent files must be journalled to the same journal • implicit commitment control is started • Constraint access paths are created • May share existing access paths

12. Referential constraints: primary and unique keys • Physical file with UNIQUE access path specified in its DDS has a primary key (Synon/2 KNOWN BY) for use as a parent key • All other parent keys are called unique keys • For consistency with other DBMS

13. Referential constraints: implementation • ADDPFCST or SQL ALTER TABLE • Not in DDS, so change control implications • May sometimes want to disable constraints temporarily (CHGPFCST) • integrity is automatically verified when constraint is re-enabled • Display via DSPFD, DSPDBR

14. Referential constraints:re-establishing • Verification of constraints follows e.g. any restore of a constrained file • Verification failure results in check pending status • EDTCPCST (also appears at manual IPL) to display check pending constraints across the system • Disable constraint, fix records, re-enable

15. Triggers • ADDPFTRG command (or via SQL), not in DDS • Program written in any language and passed a trigger buffer • No data may be returned by the trigger, only ‘OK’ or ‘Error’ • Runs synchronously within your job: *LIBL, QTEMP, may share existing open data paths • Not invoked by APYJRNCHG • Record level, not field level

16. Triggers: types • *INSERT, *UPDATE, *DELETE • *BEFORE, *AFTER • Update trigger may be *ALWAYS or *CHANGE

17. File name Library name Member name ‘1’=insert, ‘2’=delete, ‘3’=update ‘1’=before, ‘2’=after commitment control status CCSID Old record offset Old record length Old record null map offset Old record null map length New record offset New record length New record null map offset New record null map length Old record image Old record null map New record image New record null map Trigger buffer

18. Trigger feedback • *BEFORE triggers indicate problems by sending escape messages (QMHSNDPM API). I/O operation then fails, which sets activating program’s error indicator • The same happens if the trigger itself fails • Specific exception returned cannot be picked up by activating program - need standard for e.g. use of *LDA to communicate details of problem • Exceptions returned by *AFTER triggers are ignored

19. Triggers and data integrity • Trigger may not change the record that activated it • Either • share existing commitment control definition and do not COMMIT or ROLLBACK, or • start trigger’s own commitment control definition and be sure to COMMIT or ROLLBACK • If only trigger has commitment control, trigger must be run in a separate ILE activation group, so that immediate rollback occurs if the trigger fails

20. Additional SQL capability • ALTER TABLE • System-wide catalog: • SYSTABLES, SYSCOLUMNS • Describes all PFs, whether or not in SQL collections • Auxiliary storage implications

21. Two-phase commit System B System A, running application • Commitment control with DDM • SQL: Distributed Unit of Work System C