Styjun

In DB2 for IBM i, is it possible to create a trigger which runs once per statement, but able to loop through all affected rows to determine if any values actually changed?

Use case is to update a Materialized Query Table (MQT) with a trigger on the underlying tables. But we only want to refresh the MQT if values have actually changed. If we create the trigger to run once per row, we are able to get the desired functionality. But if we Insert, Update and/or Delete multiple rows at a time, the "Refresh" statement runs for each row. And currently that takes about 10 seconds for this MQT on our system.

Just hoping there was a way to use a trigger set to run once per statement, yet still have access to the before and after values of each individual row. I haven't been able to find anything which indicates whether it is or isn't possible. Though I suspect it is not.

If there is some other way to automatically refresh MQTs in an efficient manner, I am open to that as well.

Example:

create table test (id int not null primary key, a int)@
create table test_mqt (cnt) as (select sum(a) from test) data initially deferred refresh deferred maintained by user@

insert into test values (1, 1), (2, 1), (3, 1) with nc@

create or replace trigger test_aus 
after update on test
referencing 
new table as n
old table as o
for each statement
mode db2sql
begin atomic
 if (exists (select 1 from n,o where n.id=o.id and n.a<>o.a)) then
 refresh table test_mqt;
 end if;
end@

-- trigger IS NOT fired after the following update
update test set a=1 with nc@
-- the following select returns 0
select cnt from test_mqt@

-- trigger IS fired after the following update
update test set a=2 with nc@
-- the following select returns 6
select cnt from test_mqt@

Yes, the documentation is here. However, depending on how you are trying to do this, there are some differences between the IBM i command ADDPFTRG and the SQL CREATE TRIGGER.

ADDPFTRG does not appear to support statement level triggers but CREATE TRIGGER does.

When using the statement level trigger, you can reference the affected rows with

REFERENCING OLD TABLE AS ___
 NEW TABLE AS ___

You can process the old and new table references with SQL statements just as if they were regular tables.

Just to be clear, OLD TABLE references affected rows as they were before the SQL statement ran, and NEW TABLE references affected rows as they will be after the SQL statment completes.

I don't think it makes sense to have a trigger run the refresh...

The whole point of the trigger + MQT would be to have the trigger directly update the MQT table. That way you only have to deal with the exact rows that have changed; rather than force the DB to read the entire base table again.

create or replace trigger test_aus 
after update on test
referencing 
new row as n
old row as o
for each row
mode db2sql
begin atomic
 if n.a <> o.a then
 update test_mqt set cnt = cnt + n.a - o.a;
 end if;
end@

But note that if a change in a is all you are interested in, then you could define the trigger as

after update of a on test 
for each row 

that way the trigger will only fire if a is actually updated.

If you've got more SQL set at a time updates being done than RPG (or SQL) single row updates, then maybe a statement trigger would be worthwhile...

Something like so:

create or replace trigger test_aus 
after update on test
referencing 
new table as n
old table as o
for each statement
mode db2sql
begin atomic
 update test_mqt 
 set cnt = cnt + (select sum(n.a - o.a) 
 from N join O using(id)
 where n.a <> o.a
 );

end@

Edit: Alternative to MQT

I've never actually used an MQT in production, since the IBM i doesn't support system maintained MQTs.

In the scenario where you're interested in an updated aggregate, Encoded Vector Indexes (EVI) with included aggregates may provide a better solution; as they are maintained automatically.

CREATE ENCODED VECTOR INDEX sales_fact_location_id_evi 
ON sales_fact(sale_location_id ASC) 
INCLUDE(SUM(sale_amount_measure))

The example above is from the article Accelerated analytics - faster aggregations using the IBM DB2 for i encoded vector index (EVI) technology . It a good article and compares EVI to MQT for storing aggregates.