Regarding Commit

lakshmipathik · New User Joined: 14 Oct 2005 Posts: 0 Location: bangalore

Hi,
Current Process -
"We have one job in the production which is deleting some records from one table (Ex. A). Now after deleting each record from table 'A' we are doing COMMIT. Now the problem due to Referential Integrity, approximately 15,000 entries were deleted (a single delete from table 'A' causes deletes from 5 other tables because of 'on delete cascade' effect)

PROBLEM - On the Maintenance weekend the Operations had some issues shutting down DB2 for maintenance. The job did not commit for over 10,000,000 log writes.

Could anyone please suggest the solution for this problem.

dick scherrer · Posted: Tue Nov 25, 2008 2:45 am

Hello,

There is typically no good reason to issue a commit after every delete.

Indeed, unless restart/recovery has already been incorporated into the process, there is probably no reason to issue a commit at all.

ashok_uddaraju · New User Joined: 21 Feb 2007 Posts: 72 Location: US

I guess the as you are shutting down the system, it would anuhow be an auto commit at that stage.

No need to additional commits

dick scherrer · Posted: Tue Nov 25, 2008 10:13 pm

Hello,

FWIW - with a very long-running process there may be shut-down timing issues with or without commits.

It sounds like you need to schedule things so that the long-running delete is run after the maintenance window.

skkp2006 · Posted: Wed Nov 26, 2008 10:54 am

Lakshmi Pathi,

In such a scenario DBA should be consulted for the recovery processes. I guess the job has to be restarted with Restart=phase and DB2 will take care of the rest of the activities from the active logs in the system. Please refer the following link....

Normal restart and recovery

Syam