Hi all,

maybe someone can shed some light on this:

Env: DB2 9.7 FP2

I'd like to automatically remove entries from the EXPLAIN Tables after
a couple of days.
My idea was - whenever a new EXPLAIN is run it does INSERTs into the
EXPLAIN_* tables so I created a trigger:

CREATE TRIGGER prune_explain AFTER INSERT ON EXPLAIN_INSTANCE
FOR EACH STATEMENT
MODE DB2SQL
BEGIN ATOMIC
DELETE FROM EXPLAIN_INSTANCE WHERE EXPLAIN_TIME < (timestamp(current
timestamp - 30 days)); --
END
;

Due to RI rules all dependant EXPLAIN_* tables should be pruned as
well.

But it does not have any effect. When I do a manual insert into
EXPLAIN_INSTANCE the trigger fires as expected, but not when using
EXPLAIN command. Just to be sure - does EXPLAIN not use INSERT to fill
EXPLAIN_* tables ?

TIA

Joachim

On 2010-08-18 11:35, Joachim Klassen wrote:
I don't know the answer to your question, but I did a little experiment
with another trigger:

create table EXPLAIN_INSTANCE_LOG like EXPLAIN_INSTANCE;

CREATE TRIGGER log_explain
AFTER INSERT ON EXPLAIN_INSTANCE
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
insert into EXPLAIN_INSTANCE_LOG
values (
N.EXPLAIN_REQUESTER,
N.EXPLAIN_TIME,
N.SOURCE_NAME,
N.SOURCE_SCHEMA,
N.SOURCE_VERSION,
N.EXPLAIN_OPTION,
N.SNAPSHOT_TAKEN,
N.DB2_VERSION,
N.SQL_TYPE,
N.QUERYOPT,
N.BLOCK,
N.ISOLATION,
N.BUFFPAGE,
N.AVG_APPLS,
N.SORTHEAP,
N.LOCKLIST,
N.MAXLOCKS,
N.LOCKS_AVAIL,
N.CPU_SPEED,
N.REMARKS,
N.DBHEAP,
N.COMM_SPEED,
N.PARALLELISM,
N.DATAJOINER); --

END
;

After I run explain plan for ... explain_instance is populated, but
explain_instance_log is empty. So it appears as if explain_instance is
not populated via insert.

One alternative might be to schedule a job that once a day delete rows
older than 30 days.

/Lennart