IBM Mainframe Forum Index
 
Register
 
IBM Mainframe Forum Index Mainframe: Search Log in to check your private messages Log in
 

row change timestamp


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
charanmsrit

New User


Joined: 25 Oct 2007
Posts: 82
Location: Australia

PostPosted: Tue Jul 10, 2018 12:30 pm
Reply with quote

I have a need to capture the timestamp when any of the row columns actual changed the last time. I tried defining a table with a timestamp column definition

Code:
GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP


What I found is that the timestamp column is updated every time an UPDATE statement is executed on the row even when there is no changes to any of the column values. Is there a way to define this column so that DB2 updates the timestamp only when the row changes?

p.s: Understand that there are other ways such as TEMPORAL, Triggers etc for capturing audit but due to the application complexity and time restriction, this would be ideal.

Thanks
Charan
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2508
Location: NY,USA

PostPosted: Tue Jul 10, 2018 5:00 pm
Reply with quote

Do you think it’s worth for IBM Developers to now magical identity and compare host values vs DB values for every column and then do change the time stamp? If so then wait till that rolled out.

However, what is that you want to achieve out of it, what’s the end goal? Plus any data modeler will recommend to have time stamp column from day 1 when the table is designed for any inserts/update and programmers use them. Now few places when you can smartly not update when no value change detected program coding but one can skip that and just blunt update.
Back to top
View user's profile Send private message
charanmsrit

New User


Joined: 25 Oct 2007
Posts: 82
Location: Australia

PostPosted: Tue Jul 10, 2018 6:25 pm
Reply with quote

rohit, with all due respect I find your response a bit over board. It may be a silly question but I don’t think it deserves such a response with full of attitude, seriously?? I couldn’t find from IBM manuals if there was a way to define a row change time stamp column that actually identify a change in the row data . Thought I may have missed something and decided to throw the question in the forum for help. Anyways have now found from IBM manuals to use search condition on UPDATE statement to bypass the time stamp update when there is no change to any columns.
Thanks for your response by the way.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2508
Location: NY,USA

PostPosted: Wed Jul 11, 2018 3:12 am
Reply with quote

charanmsrit, I don't want to respond to your arguments or observation since that's not why we here for, are we?
Quote:
IBM manuals to use search condition on UPDATE statement to bypass the time stamp update when there is no change to any columns.

If you get time , explain further to what is this approach ? Are you now changing the application program for this? I thought you don't wanted to.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 

Search our Forum:

Similar Topics
Topic Forum Replies
No new posts Timestamp in TCP/IP for Z/VSE JCL JCL & VSAM 0
No new posts how to add date & timestamp to th... DFSORT/ICETOOL 5
No new posts DB2 Timestamp field weird behaviour. DB2 8
No new posts How to add new LPARs and change exist... All Other Mainframe Topics 0
No new posts How to get MQPUT Timestamp? Java & MQSeries 3

Back to Top