This topic has been locked.
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?
SystemAdmin 110000D4XK17917 Posts
Re: Is UPDATE = DELETE and INSERT?2012-04-08T23:27:57ZThis is the accepted answer. This is the accepted answer.sakumar9, If you are asking if DB2 does an Delete / Insert when it does an Update, the answer is no.
doole 1200009BRM208 Posts
Re: Is UPDATE = DELETE and INSERT?2012-04-10T18:05:51ZThis is the accepted answer. This is the accepted answer.
- SystemAdmin 110000D4XK
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:13ZThis is the accepted answer. This is the accepted answer.The 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.
Re: Is UPDATE = DELETE and INSERT?2012-04-23T05:45:14ZThis is the accepted answer. This is the accepted answer.Yes, all our tables are compressed.