Topic
5 replies Latest Post - ‏2012-04-23T05:45:14Z by sakumar9
sakumar9
sakumar9
79 Posts
ACCEPTED ANSWER

Pinned topic Is UPDATE = DELETE and INSERT?

‏2012-04-07T20:24:15Z |
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
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: Is UPDATE = DELETE and INSERT?

    ‏2012-04-08T23:27:57Z  in response to sakumar9
    sakumar9, If you are asking if DB2 does an Delete / Insert when it does an Update, the answer is no.
    • doole
      doole
      208 Posts
      ACCEPTED ANSWER

      Re: Is UPDATE = DELETE and INSERT?

      ‏2012-04-10T18:05:51Z  in response to SystemAdmin
      Logically, 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.)


      Doug Doole
      DB2 for Linux, UNIX and Windows
      IBM Toronto Labs
  • sakumar9
    sakumar9
    79 Posts
    ACCEPTED ANSWER

    Re: Is UPDATE = DELETE and INSERT?

    ‏2012-04-11T08:10:13Z  in response to sakumar9
    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.
  • sakumar9
    sakumar9
    79 Posts
    ACCEPTED ANSWER

    Re: Is UPDATE = DELETE and INSERT?

    ‏2012-04-23T05:45:14Z  in response to sakumar9
    Yes, all our tables are compressed.