|
View previous topic :: View next topic
|
| Author |
Message |
naveensrimf
New User
Joined: 04 Oct 2005
Posts: 34
|
|
|
|
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 |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| 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 |
|
|
naveensrimf
New User
Joined: 04 Oct 2005
Posts: 34
|
|
|
|
| There is no Timestamp column in the Table. Then How to identify the newly inserted Records? |
|
| Back to top |
|
|
Yong dong Wu
New User
Joined: 03 Aug 2011
Posts: 3
Location: China
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| 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 |
|
|
Yong dong Wu
New User
Joined: 03 Aug 2011
Posts: 3
Location: China
|
|
|
|
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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Yong dong Wu
New User
Joined: 03 Aug 2011
Posts: 3
Location: China
|
|
|
|
| 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 |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Compare unloads from imagecopies
or DSN1COPY an old imagecopy into a different table |
|
| Back to top |
|
|
|
|
