Search code examples
db2oledbibm-midrangedb2-400

DB2 AS400/IBM ISeries Triggers/On File Change

Looking for best practices to get DELTAs of data over time.

No timestamps available, cannot program timestamps!

GOAL: To get differences in all files for all fields over time. Only need primary key as output. Also I need this for 15 minute intervals of data changes

Example: Customer file has 50 columns/fields, if any field changes I want another file to record the primary key. Or anything to record the occurrence of a change in the customer file.

Issue: I am not sure if triggers are the way to go since there is a lot of overhead associated with triggers.

Can anyone suggest best practices for DB2 deltas over time with consideration to overhead and performance?

Solution

  • I'm not sure why you think there is a lot of overhead associated with triggers, they are very fast in my experience, but as David suggested, you can journal the files you want to track, then analyze the journal receivers.

    To turn on Journaling you need to perform three steps:

    1. Create a receiver using CRTJRNRCV
    2. Create a journal for the receiver using CRTJRN
    3. Start journaling on the files using STRJRNPF. You will need to keep *BEFORE and *AFTER images to detect a change on update, but you can omit *OPNCLS records to save some space.

    Once you do this, you can also use commitment control to manage transactions! But, you will now have to manage those receivers as they use a lot of space. You can do that by using MNGRCV(*SYSTEM) on the CRTJRN command. I suspect that you will want to prevent the system from deleting the old receivers automatically as that could cause you to miss some changes when the system changes receivers. But that means you will have to delete old receivers on your own when you are done with them. I suggest waiting a day or two to delete old receivers. That can be an overnight process.

    To read the journal receiver, you will need to use RTVJRNE (Retreive Journal Entries) which lets you retrieve journal entries into variables, or DSPJRN (Display Journal) which lets you return journal entries to the display, a printer file, or an *OUTFILE. The *OUTFILE can then be read using ODBC, or SQL or however you want to process it. You can filter the journal entries that you want to receive by file, and by type.