we have a table with jobs and a table with job_history information.
Users can define jobs and let them run every X minutes/hours , like a
cronjob.

The jobs table has the following trigger:
CREATE TRIGGER JOBS_AFTER_DELETE
AFTER DELETE ON JOBS
REFERENCING OLD AS o
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DELETE FROM JOB_HISTORY WHERE JOB = o.JOB;
END



When the job_history table contains very much rows for a given job
(say a job that has run every 30 min for the past 2 years), the log
file gets full (error DIA8309C). This gives an error with inconsistent
information, job_history entries for a job that doesn't exist anymore.

Is there an easy way to prevent this error? I was thinking of
deleting the rows in blocks of 100.000 and looping as long as rows
exist for the given job. Is this possible with a trigger, or is there
some better solution?

regards,
Jan831

The logfile is becoming full, as the transaction size of the delete
performed by the trigger is greater than the log space available
(logfilsiz * (logprimary + logsecond).

There are several approaches you could take:
1) Increase the logspace available, by increasing logfilsiz,
logprimary and/or logsecond - are nay combination there of.
2) Enable infinite logging. The database must be enabled for
rollforward recovery for this and to enable set logsecond to -1.
3) Reduce the size of the transaction by committing at regular
intervals.

Cheers,


On Jun 6, 12:52 am, jan.mar...@gmail.com wrote:
> we have a table with jobs and a table with job_history information.
> Users can define jobs and let them run every X minutes/hours , like a
> cronjob.
>
> The jobs table has the following trigger:
> CREATE TRIGGER JOBS_AFTER_DELETE
> AFTER DELETE ON JOBS
> REFERENCING OLD AS o
> FOR EACH ROW MODE DB2SQL
> BEGIN ATOMIC
> DELETE FROM JOB_HISTORY WHERE JOB = o.JOB;
> END
>
> When the job_history table contains very much rows for a given job
> (say a job that has run every 30 min for the past 2 years), the log
> file gets full (error DIA8309C). This gives an error with inconsistent
> information, job_history entries for a job that doesn't exist anymore.
>
> Is there an easy way to prevent this error? I was thinking of
> deleting the rows in blocks of 100.000 and looping as long as rows
> exist for the given job. Is this possible with a trigger, or is there
> some better solution?
>
> regards,
> Jan831