Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27

    Question Unanswered: When does update becomes delete and insert?

    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 is Monday morning 3:02 AM. What is your SQL response time ?"

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: When does update becomes delete and insert?

    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

    Originally posted by gsreejith
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    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
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    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
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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 ...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Sep 2003
    Location
    Massachusetts
    Posts
    27
    yeah guess it makes sense..thanks
    "It is Monday morning 3:02 AM. What is your SQL response time ?"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •