On May 9, 1:04 pm, Serge Rielau <srie...@[EMAIL PROTECTED]
> wrote:
> JohnO wrote:
> > On May 9, 11:45 am, JohnO <johno1...@[EMAIL PROTECTED]
> wrote:
> >> Hi All,
>
> >> This question is related to iSeries V5R4 and db2.
>
> >> I want to implement an AFTER DELETE trigger to save the deleted rows
> >> to an archive table, I initially defined it as a FOR EACH STATEMENT
> >> trigger that would insert all the deleted rows in one operation like
> >> this:
>
> >> CREATE TRIGGER MyTable_TD
> >> AFTER DELETE ON MyTable
> >> REFERENCING OLD TABLE AS Deleted
> >> FOR EACH STATEMENT
> >> BEGIN
> >> INSERT INTO MyTableA SELECT * from Deleted
> >> END
>
> >> This worked pretty well, but if I was bulk deleting millions of
> >> records, the delete would run for a long time without writing any
> >> archive records. Only once all the deletes completed would any
archive
> >> records get written. If the operation was interrupted, the delete
> >> would end incomplete, and no archive records are written at all - the
> >> records are lost forever. The archive table is not journaled.
>
> >> I can see a performance benefit in this approach as the inserts are
> >> done as a single operation (although there may be a penalty of
> >> individual inserts into a temp table).
>
> >> I am now attempting to rewrite this as a FOR EACH ROW based trigger,
> >> on the expectation that one row insert will occur immediately after
> >> each row deleted, so if the operation is interrupted, at most one row
> >> is lost.
>
> >> This is what I am trying to do:
> >> CREATE TRIGGER MyTable_TD
> >> AFTER DELETE ON MyTable
> >> REFERENCING OLD ROW AS Deleted
> >> FOR EACH ROW
> >> BEGIN
> >> INSERT INTO MyTableA SELECT Deleted.* from SYSIBM.SYSDUMMY1
> >> END
>
> >> But of course the trigger payload is invalid as Deleted is a row
> >> reference not a table reference.
>
> >> So my question is this: in a row based trigger, how can I reference
> >> the old deleted row in my insert statement. I cannot use a list of
> >> individually named columns as a) I want this to be generic and b) I
> >> want it to be low maintenance and c) the underlying table has
hundreds
> >> of columns and referencing them individually would be a royal PITA!
>
> >> Thanks in advance for any advice,
> >> JohnO
>
> > I found this thread:
>
>http://groups.google.co.nz/group/comp.databases.ibm-db2/browse_frm/th...
>
> > In which Serge replied in the negative. Serge, as I said, the
> > statement based trigger works, but it has a problem when I am deleting
> > millions of rows.
>
> As always convenience has its price....
> In some future version of DB2 you may be able to do:
> INSERT INTO T VALUES ROW newrow
> (or something like that). But that'll be a while...
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
Thanks Serge, I knew you would confirm my suspicions.
Can you think of a way to improve the statement based trigger? It
works, but I am not comfortable with using it when deleting millions
of rows as the archive rows are lost if the delete is interrupted yet
the deleted records stay deleted. If I enable journaling on the
archive table would that make it safer? Would there be a performance
and disk space penalty?
What I would really like would be for the delete to run for a while,
and then be able to interrupt the delete, with the corresponding
inserts completing. Then I could restart the delete another time.
Thanks
johnO
|
