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
 

 

How to get Timestamp ,which the db2 got updated any columns

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

New User


Joined: 17 Jun 2010
Posts: 72
Location: Chennai

PostPosted: Tue Feb 05, 2013 9:00 pm    Post subject: How to get Timestamp ,which the db2 got updated any columns
Reply with quote

Is possible to get the last timestamp using db2 query,which updated tables using program/tep'2 ?
Back to top
View user's profile Send private message

Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Feb 05, 2013 9:06 pm    Post subject:
Reply with quote

Do you wanted to know the timestamp when record was updated or the timestamp when record was table was updated ?

But both the timestamp cant be retreived via sql unless you have some process of storing those timestamp in a DB2 table
Back to top
View user's profile Send private message
cmsmoon

New User


Joined: 17 Jun 2010
Posts: 72
Location: Chennai

PostPosted: Tue Feb 05, 2013 9:08 pm    Post subject: Reply to: How to get Timestamp ,which the db2 got updated an
Reply with quote

Yes ,Extactly..
But i do not have Timestamp field in that table
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Feb 05, 2013 9:12 pm    Post subject:
Reply with quote

Dear cmsmoon,


Quote:
Do you wanted to know the timestamp when record was updated or the timestamp when record was updated ?


With all due respect to you reminding you gently that You did not answer this question yet

But anyway what you could do is try the following options

Alter the table by adding a new field of datatype TIMESTAMP and update it with the CURRENT TIMESTAMP when updating the table
so you can qury the table for MAX(TIMESTAMP) to know the latest update happened

or if you just need it for reporting purpose and dont bother to refer it in future you can generate the X(26) programmatically
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Tue Feb 05, 2013 9:14 pm    Post subject:
Reply with quote

Dear Pandora,

Quote:
Do you wanted to know the timestamp when record was updated or the timestamp when record was table was updated ?


With all due respect, the 'or' portion of the question is confusing.
Back to top
View user's profile Send private message
cmsmoon

New User


Joined: 17 Jun 2010
Posts: 72
Location: Chennai

PostPosted: Tue Feb 05, 2013 9:19 pm    Post subject: Reply to: How to get Timestamp ,which the db2 got updated an
Reply with quote

Sorry for confusion.

I tried the below query ,but it support only ver 9 and above

SELECT * FROM TAB
WHERE ROW CHANGE TIMESTAMP FOR TAB <= CURRENT TIMESTAMP AND
ROW CHANGE TIMESTAMP FOR TAB >= CURRENT TIMESTAMP - 30 days;
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Feb 05, 2013 9:20 pm    Post subject:
Reply with quote

Dave,

The quote is editted icon_smile.gif
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 645
Location: Pennsylvania

PostPosted: Tue Feb 05, 2013 9:22 pm    Post subject:
Reply with quote

Pandora,

Quote:
Do you wanted to know the timestamp when record was updated or the timestamp when record was updated ?


Now both sides of the 'or' ask the same question?
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Tue Feb 05, 2013 9:24 pm    Post subject:
Reply with quote

I realised it by the time I elapsed the grace time of correcting things
icon_redface.gif

All I wanted to ask was if TS wants the timestamp when the table is updated or the timestamp when record for a key is updated
Back to top
View user's profile Send private message
Binop B

Active User


Joined: 18 Jun 2009
Posts: 407
Location: Nashville, TN

PostPosted: Tue Feb 05, 2013 9:32 pm    Post subject:
Reply with quote

Hi cmsmoon,

Suggest you describe what your actual requirement is... That might help the experts here to suggest an appropriate solution...

cmsmoon wrote:
Sorry for confusion.

I tried the below query ,but it support only ver 9 and above

SELECT * FROM TAB
WHERE ROW CHANGE TIMESTAMP FOR TAB <= CURRENT TIMESTAMP AND
ROW CHANGE TIMESTAMP FOR TAB >= CURRENT TIMESTAMP - 30 days;

What version of DB2 are you using ?
You have used ROW CHANGE TIMESTAMP - did you do any research to understand what is the functionality of that ? [I have not worked in DB2 now for the last 5 years but did a quick google and doesnt look to be fitting your 'problem' ]
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10202
Location: italy

PostPosted: Tue Feb 05, 2013 10:19 pm    Post subject: Reply to: How to get Timestamp ,which the db2 got updated an
Reply with quote

Quote:
Is possible to get the last timestamp using db2 query,which updated tables using program/tep'2 ?


If I understand correctly the TS wants to know <what> and <when> was updated using DSNTEP2
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
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Converting EBCDIC to Unicode (TimeStamp) muralikrishnan_new COBOL Programming 2 Mon Jul 25, 2016 10:42 pm
No new posts Convert Timestamp Value mrdinesh DFSORT/ICETOOL 7 Thu May 05, 2016 6:01 pm
No new posts Combine columns without breaking words Indrajit_57 SYNCSORT 1 Sat Mar 19, 2016 9:33 am


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