Overview

Extract only changed information often save time and resources. A common scenario is to periodically update a webshop with products, campaigns etc. Without any info about what has changes there is no other option than extracting the full table which can be a lot. Triggers let you extract only those records that has changed since the last extraction.

Reading changed information is done through adding a trigger to selected database tables and at the same time create a shadow table that will contain all changes done in the table with the trigger. The shadow table can contain all the fields from the original table or just selected number of fields. It may be enough to just save the key fields and then join the shadow with the original to query out the information.

The following objects are created in the same library as the original physical file. 

1. Shadow table named as the original table with an _ is appended to the end. The table always contains the columns TS=Timestamp, TP=I,D,U (insert,delete,update) ST=User field

2. Trigger called same the original table with _T appended

3. Shadow Index with the keys selected called as the original table with _X appended

4. Shadow Index with the TS as key called as the original table with _XTS appended




A link to the Triggers view can be found in the left menu.  




1. The Triggers function.


At the top a pool must be selected. The pool is currently an AS400 pool, no other pools will show up in the dropdown. After a pool is selected all tables (Physical Files) and libraries are listed. Filtering can be done at table name, library or table description.

The checkbox to the right 'Triggers only' will only list tables where triggers has been applied

Press the 'Add' button to the right of the table the trigger should be applied to. The screen bellow is displayed

2. Add a new trigger to a database file

At the top there are 2 checkboxes, insert and delete trigger. These are checked by default and when checked inserts or deleted are triggered and creates a record in the shadow table.

In the table the checkbox to the left indicate if the field should be included in the shadow table. The 'Key' checkbox indicate if it should be included in the shadow table plus that it should be part of the index created. The index is called as the original table with _X appended.

The pos indicate the order of the field in the order. E.g if there is a table with order and line, order should have pos 1 and line pos 2. 

The 'Any change' checkbox when checked in the header the update trigger is fired when any column in the table is modified. If the trigger only should be fired when some fields are modified, uncheck the header checkbox and select fields.  

Press Create at the bottom of the dialog

3. After the trigger is applied 


Now in the example above any modification to the table ZZTDMN/DMNPF will create a record in the table ZZTDMN/DMNPF_

4. The 4 objects created, the trigger DMNPF_, The shadow table DMNPF_ and to 2 indexes _X and _XTS


After running the Java code

Insert 2 records, then update 1 of them and finally delete the 2 records will create the following records in the shadow table



Only the code is included because that was the only column selected when the trigger was created. Doing the same but with also name selected. First delete the trigger which will remove the 4 objects, the trigger, shadow file and the indexes. Apply the trigger again and select also name.



Query the shadow table DMNPF_



Now the name column is included. First at line 00002 the record is inserted with the name 'Melon' and then changed to 'Water Melon' at line 000003. The new column value is placed in the shadow file.