Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

delete row automatically

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
selvaraja

New User


Joined: 04 Apr 2008
Posts: 17
Location: Maryland

PostPosted: Fri Jul 31, 2009 12:08 am    Post subject: delete row automatically
Reply with quote

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
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jul 31, 2009 6:50 am    Post subject:
Reply with quote

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. . .
Back to top
View user's profile Send private message
selvaraja

New User


Joined: 04 Apr 2008
Posts: 17
Location: Maryland

PostPosted: Fri Jul 31, 2009 8:20 am    Post subject: Reply to: delete row automatically
Reply with quote

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?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jul 31, 2009 8:34 am    Post subject:
Reply with quote

Hello,

Quote:
currently COBOL programs takes 24 CPU time.
I'm not sure exactly what time this refers to. . . 24 what?

Is the date field used for deletes defined as an index? It should be.

What does the program do besides delete the old rows?
Back to top
View user's profile Send private message
selvaraja

New User


Joined: 04 Apr 2008
Posts: 17
Location: Maryland

PostPosted: Fri Jul 31, 2009 9:29 am    Post subject: Reply to: delete row automatically
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jul 31, 2009 10:01 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Fri Jul 31, 2009 10:48 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
No new posts Delete record or Reset RC code using ... mpawan Compuware & Other Tools 4 Tue Jul 26, 2016 3:52 pm
No new posts Delete VSAM File Records amitc23 CICS 6 Sun Apr 10, 2016 10:10 am
No new posts DB2 Stored Procedure - Dynamic SQL - ... GuyC DB2 2 Wed Feb 17, 2016 5:10 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us