delete row automatically

selvaraja · New User Joined: 04 Apr 2008 Posts: 17 Location: Maryland

Hi,
We have COBOL program to delete records from table which is a year old ( this Table has date field when each record is added). this COBOL program runs every day to delete one year older records.

Is there way to delete those records by DB2 itself instead of running COBOL program? Might be Trigger or Stored procedures?

Thanks

dick scherrer · Posted: Fri Jul 31, 2009 6:50 am

Hello,

A trigger will not do what you want.

A stored procedure could, but that is just another bit of code to be scheduled.

What is the problem with the current implementation? Hopefully, this is a scheduled process. . .

selvaraja · New User Joined: 04 Apr 2008 Posts: 17 Location: Maryland

There is no problem in the COBOL program. I am looking for performance improvement. currently COBOL programs takes 24 CPU time. if i change that to handle with in DB2, will it improve the performance and save CPU time?

dick scherrer · Posted: Fri Jul 31, 2009 8:34 am

Hello,

selvaraja · New User Joined: 04 Apr 2008 Posts: 17 Location: Maryland

Hi Dick

sorry, its 24 SEC CPU TIME

yes, Date field has been used as index.

COBOL program does nothing other then executes DELETE * FROM TABLE WHERE DATE GT then one year.

dick scherrer · Posted: Fri Jul 31, 2009 10:01 am

Hello,

How many rows are deleted? 24 seconds may not be such a long time. . .

Please post the exact code used to delete the old rows.

Ketan Varhade · Posted: Fri Jul 31, 2009 10:48 am

Can you just post the DB2 quires and let us know how many row are getting deleted on daily basis.
One of the suggestion is that u can schedule the job on weekly of monthly basis. This wont affect the system much. We too have such jobs in our system we trigger them of month basis.