Db2

 View Only
  • 1.  Inplace update support for BLU

    Posted Thu April 08, 2021 09:38 AM

    Hello,

    I wonder if there can be an "inplace update" support for BLU, to avoid those DELETE and INSERTs taking much time, in a coming DB2 11.1 fixpack.

    As an exemple: db2 update T1 set top_closing=1 where date_mvt<'2016-01-01' and top_closing=0    can take 2h30 min on BLU while 2min30s on SybaseIQ (Top_closing can only be 0 or 1, and the update in that case updates 65 millions rows out of 320 millions).  A LOAD FROM cursor into a new table will take minimum 30 min and implies DDL change and dropping a table.

    Another case is ALTER TABLE T1 ALTER COLUMN COL1 SET NOT NULL

    What are your thoughts about this ?

    Also, why don't you expose in EXPLAIN plans UPDATEs clearly as a DELETE / INSERT, like this is done for MDC tables in non BLU databases ?

    Best regards, JMB


    #Db2
    #Db2EarlyAccessProgram(EAP)Forum


  • 2.  RE: Inplace update support for BLU
    Best Answer

    Posted Thu April 08, 2021 09:40 AM

    Performance improvement for BLU update are a top priority. The exact schedule for when they will be delivered is not decided at this point. The performance improvements might involve in-place updates, but other options are being explored.

    Decomposed updates for BLU tables aren't explicitly represented in EXPLAIN because the actual operations are done at a lower level and subject to change in the future. It is an implementation detail that users don't generally need to be aware of.