Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Unanswered: How to iterate over Transition Tables in FOR EACH STATEMENT Trigger

    Hi everyone,
    I am new to triggers. I have created a FOR EACH STATEMENT update trigger. This is for DB2 iSeries V6R1 database.
    I need to iterate over the NEW_TABLE transition table and perform some tasks. Looks like I can't use the DECLARE csr CURSOR FOR statement in the trigger. I also tried using the FOR v1 AS c1 CURSOR FOR.... statement and I am noticing some weird behavior. It seems to work sometimes. Both these constructs are not in the list of valid clauses I can use in a trigger according to the CREATE TRIGGER documentation. So my question is, how can I iterate over the NEW_TABLE or OLD_TABLE transition tables?

    Thanks for your help!
    Henok
    P.S. I have have pasted my sample below. See the sections labeled Example 1 and Example 2.

    CREATE TRIGGER update_pack_unpack_item_tr
    AFTER UPDATE on pack_unpack_item
    REFERENCING OLD_TABLE AS otable NEW_TABLE AS ntable
    FOR EACH STATEMENT MODE DB2SQL

    p1: BEGIN ATOMIC

    DECLARE no_data SMALLINT DEFAULT 0;

    -----------Example 1------------
    DECLARE csr CURSOR FOR
    SELECT pack_unpack_item_id, item_id
    FROM ntable
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_data = 1;

    OPEN csr;
    WHILE no_data = 0
    DO
    FETCH csr INTO p1.n_pack_unpack_item_id, p1.n_item_id;
    --Do some work in here with current row
    END WHILE;
    CLOSE csr;

    ---------Example 2-----------
    for_1:FOR v1 AS c1 CURSOR FOR
    SELECT pack_unpack_item_id, item_id
    FROM otable
    DO
    --Do some work in here with current row
    END FOR for_1;

    END;

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    what is it you are wanting this trigger to do? I really can't understand why you would be iterating through the table when a row getts updated.

    Dave

  3. #3
    Join Date
    Nov 2010
    Posts
    4
    This trigger needs to generate a report of the fields that were modified by an update SQL statement. I need build this change summary and store it in a table. This is the client's requirement. Then a user will go on some web page and view this change history.

    So here's what I need to do in this trigger:

    *Iterate over NEW_TABLE
    *For each row in new table, fetch corresponding row in OLD_TABLE
    *Do a field-by-field comparison of these two rows and log the changes to my history table.

    I am using a FOR EACH STATEMENT trigger because I need to be able to group the updates made by one sql query together. That way, if the sql update modified ten rows, I treat that group of modifications as one group, in my history table.

    Thanks,

    Henok

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can use a FOR loop in the trigger body and a query to iterate over the transition table(s).

    Have a look at "Example 3" in the manual: http://publib.boulder.ibm.com/infoce.../r0000931.html
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    There is no need to iterate through the table, though. Its just do something with the updated rows. The easiest way to do this would be to copy the entire contents of each row to your history table. Otherwise, it gets very difficult only putting out what has changed, because you would have to put the column name and value out to your history table and how do you accomodate all data types in the history table, also, how do you take care of multiple columns being updated?
    The easiest way I have seen it done in the past is to create a history table that matches your current table add a column to this history table that tracks whether it is old/updated row. Here is a quick overview of the statement.

    Code:
    CREATE TRIGGER update_pack_unpack_item_tr
    AFTER UPDATE on pack_unpack_item
    REFERENCING OLD_TABLE AS otable NEW_TABLE AS ntable
    FOR EACH STATEMENT MODE DB2SQL
    
    insert into history_table (new_col,cols.....)
       values('U', ntable.cols.....);
    insert into history_table (new_col,cols.....)
       values('O', otable.cols.....);
    end~
    Dave Nance

  6. #6
    Join Date
    Nov 2010
    Posts
    4
    Thank you all for the replies,

    stolze, the link you sent me is for DB2 Version 9.5 for Linux, UNIX, and Windows. I have DB2 for iSeries 6.1. I couldn't find the CREATE TRIGGER documentation for iSeries 6.1 but I found it for iSeries 5.4 and they don't have the FOR LOOP in the list of allowed statements in a trigger. But, someone else just told me that he was able to use a FOR loop on their DB2 iSeries 6.1. So he suggested that I make sure we have all the latest PTFs installed and try again. I am going to do that. It turns out we are behind on PTFs on that machine.

    dav1mo, your idea is a good one. However we have many tables to audit (about 30) and it means we would need to create one history table for each table we are auditing. That's why we have decided to create one generic history table that just stores strings (TABLE_NAME, FIELD_NAME, OLD_VALUE, NEW_VALUE...ETC.) The client also wants summarized and formatted descriptions of changes...so just dumping transition table into history table as is won't work. Thank you for the suggestion!

    Thank you all! I will update you as soon as I find out if having the latest PTFs takes care of my problem.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Here is another suggestion:
    • you copy the changes as-is to a staging table as dave suggested
    • then you run a hourly/daily/weekly/whenever job to do the describe aggregation and compaction of the data to reduce the data volume

    An advantage would be that the triggers are much simpler and, thus, reduce the performance impact to DML statements because you don't have to do the aggregation in each statement and can delay it. But you will still reduce the volume of data.

    Quote Originally Posted by dav1mo View Post
    There is no need to iterate through the table, though. Its just do something with the updated rows.
    I meant to iterate over the transition tables, which contain only the new/old updated rows - not the whole tables.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Nov 2010
    Posts
    4
    Knut, I think that's a good idea. I have already noticed some delay in my DML operations when my trigger does a lot of work. So I will need to simplify the trigger and move the bulk of the processing to a scheduled job.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •