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 rows older than 3 month

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

New User


Joined: 06 Jun 2009
Posts: 99
Location: UK

PostPosted: Thu Mar 11, 2010 3:31 pm    Post subject: Delete rows older than 3 month
Reply with quote

Dear all,

Can you please provide me a query where I can delete rows if value on End_Date column is older than 3 month.


Many thanks,
ZH Lad
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Mar 11, 2010 3:57 pm    Post subject:
Reply with quote

What have you tried ?
Back to top
View user's profile Send private message
zh_lad

New User


Joined: 06 Jun 2009
Posts: 99
Location: UK

PostPosted: Thu Mar 11, 2010 4:01 pm    Post subject:
Reply with quote

I have tried following queries but all gave me some syntax error:

SELECT * FROM HIGHQAL.TAB
WHERE G4852_END_DT < DATE_SUB(NOW(), INTERVAL 3 MONTH)

SELECT * FROM HIGHQAL.TAB
WHERE G4852_END_DT < DATEADD(MONTH, -3, GETDATE())

SELECT * FROM HIGHQAL.TAB
WHERE DATEDIFF(day, G4852_END_DT, now()) > 90
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Thu Mar 11, 2010 4:09 pm    Post subject:
Reply with quote

You might want to read a DB2 manual / chapter on date functions and special registers, instead of an Oracle one.
Back to top
View user's profile Send private message
zh_lad

New User


Joined: 06 Jun 2009
Posts: 99
Location: UK

PostPosted: Thu Mar 11, 2010 4:13 pm    Post subject:
Reply with quote

DELETE * FROM HIGHQAL.TAB
WHERE (DAYS(END_DT) - DAYS(CURRENT DATE)) > 90

Seems it is working.

Thanks.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu Mar 11, 2010 4:13 pm    Post subject:
Reply with quote

Hello ZH_lad,

Read the manuals .Below hint might help
Code:
SELECT *                                     
FROM  table_name             
WHERE col_1 < CURRENT DATE- 3 MONTHS
WITH UR;                                     
Back to top
View user's profile Send private message
zh_lad

New User


Joined: 06 Jun 2009
Posts: 99
Location: UK

PostPosted: Thu Mar 11, 2010 4:31 pm    Post subject:
Reply with quote

I have got the previous answer from manual.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Thu Mar 11, 2010 4:35 pm    Post subject:
Reply with quote

Well, now that you have learned something, some extra advice :

using functions on columns in the where clause is not good for performance !

so
WHERE end_dt < CURRENT DATE- 3 MONTHS
is faster than
WHERE (DAYS(END_DT) - DAYS(CURRENT DATE)) > 90
Back to top
View user's profile Send private message
Kjeld

Active User


Joined: 15 Dec 2009
Posts: 365
Location: Denmark

PostPosted: Fri Mar 12, 2010 2:06 pm    Post subject:
Reply with quote

GuyC wrote:
Well, now that you have learned something, some extra advice :

using functions on columns in the where clause is not good for performance !

so
WHERE end_dt < CURRENT DATE- 3 MONTHS
is faster than
WHERE (DAYS(END_DT) - DAYS(CURRENT DATE)) > 90

...because you force DB2 to retrieve every row from data pages to do the function calculation before comparing to the literal value 90. The first construct can be calculated to a fixed value before data query starts, so DB2 can use an index value in the comparaison, if END_DT is part of an index, which it should be.

Before doing this kind of bulk deletes, you should check with your DBAs if the row counts involved should warrant a unload with a subsequent load of only the rows to be retained.
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 To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts SORT to append Month and YEAR in MMYY... tisamf DFSORT/ICETOOL 1 Wed Mar 08, 2017 4:46 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm
No new posts JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm


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