MVSFORUMS.com

ed.sam13 · Beginner Joined: 09 Aug 2010 Posts: 31 Topics: 11

We have a table with some columns and Last modified timestamp. There will be updates to around 1-2% of the rows in this table everyday. So to extract all rows which were updated for a particular day, one suggestion is having an index on Last Modified Timestamp, so that it helps the extraction. But there is a concern that it will impact the updates happening to the table.

Is having an index on timestamp a overhead during updates? Since having an index on last modified timestamp will have a high cardinality how does DB2 handle an update? Should we increase the percentage free space on a page to help this? How different is having and index on a timestamp column different from having index on any other normal columns?

kolusu · Posted: Fri Sep 25, 2015 10:14 am Post subject:

ed.sam13,

I am guessing that you are going to add it as part of the composite index rather an index by itself. Timestamp fields usually have a high cardinality of having different values as the you can have micro seconds in it. so depending on the predicate on your sql statement you may still be doing a table scan instead of an index scan

Assuming you did indeed create an index and if your intention is to bring in the day records then if your WHERE clause is as follows

ed.sam13 · Beginner Joined: 09 Aug 2010 Posts: 31 Topics: 11

hi Kolusu,
Thanks for your reply. Actually I wanted to understand the impact of an update statement when we have a last modified timestamp as index. This will not be a primary index. This will be a non-composite, non-clustered index.

So the situation is that the table will have very minimal inserts everyday. There will be lot of updates. So everytime a row is updated, the last modified timestamp will be updated to the current timestamp. So when this update statement is executed and the last modified timestamp is changed, how much of an impact will it have on the index. From theory on Control Intervals and Control Areas for VSAM files i understand that when this update for a particular row occurs, the control area of the current row gets aligned and a new entry is made at the end of the index. Is this understanding right or when I update the timestamp the index gets broken and would need a reorg to fetch that row effectively?

kolusu · Posted: Fri Sep 25, 2015 11:59 am Post subject:

ed.sam13 · Beginner Joined: 09 Aug 2010 Posts: 31 Topics: 11

hi Kolusu, Yes the update statement is using the primary keys of the table on the predicate. Last Modified timestamp is one of the columns which get updated on the SET statement along with few other columns.

kolusu · Posted: Fri Sep 25, 2015 1:08 pm Post subject: