Hi,

I was always thinking that any UPDATE statement will internally handled by DB2 as a DELETE and INSERT (I remember reading this somewhere).

But now my colleague tells me that an update becomes a delete and insert only you were updating the index. Otherwise its just a straight update.

which one is true? if the latter, why?

Thanks in advance..
Sreejith

It depends on whether the table has a clustering index defined, which is used to determine the physical sequence of the rows on the table. If the clustering index value changes, then DB2 would want to move the row to the correct location in the table (the correct page if possible) in order to maintain the clustering sequence.

There are also some issues about updating varchar columns which are increased in size with an update, and the row no longer fits in the same space. This may require a pointer to the new row location.

AFAIK, in a table, UPDATE is NOT handled as DELETE-INSERT pair ..

In an index, if a key is affected, then the index record is deleted and inserted ..

HTH

Sathyaram

Does DB2 try to maintain the clustering order by moving the rows immediately during an update or does it happen when I do a Reorg? (I think it would happen immediately, but wanna confirm nonetheless)

So what exactly is the answer? DB2 does a delete/insert only for updates to the clustering index? rest everything, including other type of indexes will result in a simple update?

and how exactly is a simple update done internally? (if its not doing a delete/insert)

Thanks

I think the question about whether an update is really a delete and insert internally is a semantic question that is irrelevant to anyone but the DB2 developers. Suffice to say that on an update, the entire row is not logged, as would be the case in a delete and/or insert.

AFAIK, the clustering sequence of the table is maintained real time when the clustering index is changed, but I could certainly be wrong about that. It might be different among the DB2 platforms also.

Thanks but I think my basic Q remains unanswered here -

Why is that only for the index it is an delete/insert?

And does it mean any index?

Thanks

I can't say why it is not delete/insert for table ... that is a basic design issue (doesn't it make better sense to change only the affected columns than to remove the exisiting one and inserting )

As index is maintained in the index pages in the order of keys, an update to a key will normally mean that the index key should be in another page ... That's why it is delete - insert ...

yeah guess it makes sense..thanks