This topic has been locked.
5 replies Latest Post - 2012-04-23T05:45:14Z by sakumar9
Pinned topic Is UPDATE = DELETE and INSERT?
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Is UPDATE equivalent to DELETE and INSERT in DB2?
Updated on 2012-04-23T05:45:14Z at 2012-04-23T05:45:14Z by sakumar9
SystemAdmin 110000D4XK17917 Posts
doole 1200009BRM208 PostsACCEPTED ANSWER
Re: Is UPDATE = DELETE and INSERT?2012-04-10T18:05:51Z in response to SystemAdminLogically, there is no difference between updating a row and deleting the old copy of the row and inserting the new copy. The delete/insert model takes a lot more I/O and processing, however, so DB2 prefers to update the row in place. There are also a number of other features that are tied specifically to update processing, such as monitoring and trigger invocation.
There are a few cases where DB2 must use delete and insert. For example, an updating of the partitioning key in a range or hash partitioned table may require the row to be moved between physical tables or even database members. In this case, DB2 cannot do the update in place and must use delete and insert. (Note that the operation is still considered an update as far as monitoring and triggers are concerned.)
DB2 for Linux, UNIX and Windows
IBM Toronto Labs
Re: Is UPDATE = DELETE and INSERT?2012-04-11T08:10:13Z in response to sakumar9The reason I asked this question is that I can see overflow records in my MQTs. This MQT is incrementally refreshed.
1. This MQT only has DATE and INTEGER columns. In that case, UPDATE should not cause any overflow. Am I right?
2. Along with UPDATES, it also does INSERTs which again should not cause overflows. Correct?
3. It also does some DELETE which I don't understand why because our base table is INSERT only and no deletes happen on that. Then why do I see DELETE in access plan?
4. Even if it does DELETE, that also does not create any overflow records.
Is my understanding correct? You can find the access plan for refresh table attached.
For you reference: MSD_MNTHLY_TABLE is my MQT and MSS_MNTHLY_TABLE is the staging table.