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 records based on TIMESTAMP from a tabel

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

New User


Joined: 26 Nov 2008
Posts: 9
Location: Hyderabad

PostPosted: Thu Nov 27, 2008 2:03 pm    Post subject: Delete records based on TIMESTAMP from a tabel
Reply with quote

Hi

I created a tabel whose one of the column value is UPDATE_TIMESTAP this time stamp will be the value when the record has been laoded into the tabel(for example: today if i load a record at 12PM value(UPDATED_TIMESTAMP) loads into table is 2008-11-27-12.33.27.238657)

So now i want issue a delete query where All the records having (UPDATE_TIMESTAMP + 24 months) > TODAY’s date must be deleted from the tabel. i have a confusion with the value loaded and current timestamp valueloaded is in the format of (2008-11-27-12.33.27.238657) and current timestamp is 20081127123327238657 how can i compare both value for deleting the record from table..

FYI i coded as:

EXEC SQL DELETE FROM PENDTABL WHERE UPDATE_TIMESTAMP > CUREENT_TIMESTAMP - 24months ;

will it works or need any corrections
Back to top
View user's profile Send private message

shaikfayaz

New User


Joined: 26 Nov 2008
Posts: 9
Location: Hyderabad

PostPosted: Fri Nov 28, 2008 8:02 pm    Post subject: Delete DB2 Table Records
Reply with quote

Hi

i want to delete records whose timestamp is more than 24months.. how can i proceed to this.

FYI i coded as:

EXEC SQL DELETE FROM PENDTABL WHERE UPDATE_TIMESTAMP > CUREENT_TIMESTAMP - 24months ;

will it works or need any corrections
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Nov 28, 2008 9:18 pm    Post subject:
Reply with quote

Code:


DELETE FROM PENDTABL WHERE UPDATE_TIMESTAMP > CURRENT TIMESTAMP - 2 YEARS

Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Nov 28, 2008 9:21 pm    Post subject:
Reply with quote

24 months will also work ...
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Sat Nov 29, 2008 8:15 am    Post subject:
Reply with quote

I think you meant that > sign to be a < sign didn't you?
Back to top
View user's profile Send private message
shaikfayaz

New User


Joined: 26 Nov 2008
Posts: 9
Location: Hyderabad

PostPosted: Mon Dec 01, 2008 5:17 pm    Post subject:
Reply with quote

But the UPdate_TIMESTAMP is the value which is edited while loading into the table for example;2008-11-27-12.33.27.238657

If i pull the value CUREENT TIMESTAMP the value will be 20081127123327238657, How can we compare a relation between them ?
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Mon Dec 01, 2008 6:02 pm    Post subject:
Reply with quote

"If i pull the value CUREENT TIMESTAMP the value will be 20081127123327238657,..."
Look again at the format of CURRENT TIMESTAMP in your DB2 manual.
Back to top
View user's profile Send private message
shaikfayaz

New User


Joined: 26 Nov 2008
Posts: 9
Location: Hyderabad

PostPosted: Mon Dec 01, 2008 8:48 pm    Post subject:
Reply with quote

Even though there is change in time representation format like HH:MM:SS in CURRENT TIME STAMP value, there would be any change in YYYYMMDD field,

I mean: UPDATED TIME STAMP VALUE = 2008-11-27-12.33.27.238657
CURRENT TIME STAMP VALUE = 2008112712:34:45123957

How the date format will be evaluated in expression ?
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Mon Dec 01, 2008 8:55 pm    Post subject:
Reply with quote

See section 1.1.9 in the DB2 Application Programming and SQL Guide. The format for CURRENT TIMESTAMP is not what you indicate.
Back to top
View user's profile Send private message
shaikfayaz

New User


Joined: 26 Nov 2008
Posts: 9
Location: Hyderabad

PostPosted: Thu Dec 11, 2008 8:31 pm    Post subject:
Reply with quote

Terry Thank you very much for your assistance.

but unfortunetly i do not have the manual.. If you don't mind can you fwd that to my id e-addr removed per forum rules, so that i will chck and let you know the results.
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Thu Dec 11, 2008 9:31 pm    Post subject:
Reply with quote

Click "IBM Manuals" at the top of this screen.
Back to top
View user's profile Send private message
shaikfayaz

New User


Joined: 26 Nov 2008
Posts: 9
Location: Hyderabad

PostPosted: Thu Dec 11, 2008 10:17 pm    Post subject:
Reply with quote

Hi Terry,

I am glad to inform you that i am able to Delete the records whose timestamp is greater than 24months

Thank you very much for you support & my pleasure to meet u in this forumn
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 Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am
No new posts Extract set of records matching on ac... bhaskar_kanteti DFSORT/ICETOOL 3 Mon Mar 06, 2017 7:19 am
No new posts Inserting records based on conditions vickey_dw DFSORT/ICETOOL 9 Wed Feb 22, 2017 1:33 pm


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