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 identify the newly inserted Records in DB2 Table

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

New User


Joined: 04 Oct 2005
Posts: 31

PostPosted: Fri Jul 29, 2011 11:24 am    Post subject: How to identify the newly inserted Records in DB2 Table
Reply with quote

How to Identify newly inserted Records in DB2 Table.

Eg: A Table have 5 Fields and there is no date filed on table. Existing Table have few records and few records are inserted through a Application Program. How to identify newly inserted Records?
Back to top
View user's profile Send private message

Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1712
Location: UK

PostPosted: Fri Jul 29, 2011 11:46 am    Post subject:
Reply with quote

Produce a report of updates at the time of insertion or bite the bullet and modify the table to have a timestamp column.
Back to top
View user's profile Send private message
naveensrimf

New User


Joined: 04 Oct 2005
Posts: 31

PostPosted: Wed Aug 03, 2011 11:15 am    Post subject: Reply to: How to identify the newly inserted Records in DB2
Reply with quote

There is no Timestamp column in the Table. Then How to identify the newly inserted Records?
Back to top
View user's profile Send private message
Yong dong Wu

New User


Joined: 03 Aug 2011
Posts: 3
Location: China

PostPosted: Wed Aug 03, 2011 11:51 am    Post subject: Reply to: How to identify the newly inserted Records in DB2
Reply with quote

if you use DB2 v9 or later, you can use row change timestamp even you didn't have the timestamp column, but it may had risk.

SELECT 1 FROM T1 WHERE ROW CHANGE TIMESTAMP FOR TAB t1 > current timestamp - 1 hours;
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed Aug 03, 2011 12:11 pm    Post subject:
Reply with quote

Quote:
but it may had risk


Yong Dong Wu,

If you are saying there is a risk, can you explain that at the same time as passing on the code, please.
Back to top
View user's profile Send private message
Yong dong Wu

New User


Joined: 03 Aug 2011
Posts: 3
Location: China

PostPosted: Wed Aug 03, 2011 12:24 pm    Post subject: Reply to: How to identify the newly inserted Records in DB2
Reply with quote

copy from IC of DB2:
If the table does not have a ROW CHANGE TIMESTAMP column, DB2 returns all rows on each page that has had any changes within the given time period. In this case, your result set can contain rows that have not been updated in the give time period, if other rows on that page have been updated or inserted

Here is the source link:
[url=http://http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2z10.doc.apsg/src/tpc/db2z_findrowschangedtime.htm][/url]
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed Aug 03, 2011 12:43 pm    Post subject:
Reply with quote

OK, so if you haven't followed this Recommendation (from the above, link, I'm not sure it is entered correctly)

Quote:
Recommendation: Ensure that the table has a ROW CHANGE TIMESTAMP column that was defined prior to the time period that you want to query. This column ensures that DB2® returns only those rows that were updated in the given time period.


I don't think you can rely on the method. It'll get all the recent, but other stuff as well (or, potentially at least, and then how do you know which is which).

Also, if you are looking for inserts only, this will get "changes" to confuse the mix further.

naveensrimf, a bit of a "design oversight"? Having been caught out by this on this table, someone should be checking all your other tables as well, to catch any others as quickly as possible (those others being tables where, for likely business reasons, you need to know about add/change/delete).
Back to top
View user's profile Send private message
Yong dong Wu

New User


Joined: 03 Aug 2011
Posts: 3
Location: China

PostPosted: Wed Aug 03, 2011 12:53 pm    Post subject:
Reply with quote

Yes, this way is not the trust way, that's why I said it may have risk. but if he/she just want to do some quick check, it may provide some helpful info.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Wed Aug 03, 2011 12:57 pm    Post subject:
Reply with quote

Yes, much better than nothing. TS might even get lucky and get the whole answer he wants at this time. Better to have more, but including everything you need, than nothing.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Aug 03, 2011 7:15 pm    Post subject:
Reply with quote

Compare unloads from imagecopies
or DSN1COPY an old imagecopy into a different table
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 Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Sort records based on numeric field. Alks SYNCSORT 2 Wed Oct 19, 2016 10:14 pm
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts how to split records based on specifi... Venkata Ramayya DFSORT/ICETOOL 6 Wed Sep 28, 2016 3:20 am


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