Db2 (On Premises and Cloud)

Expand all | Collapse all

Inplace update support for BLU

Jump to Best Answer
  • 1.  Inplace update support for BLU

    Posted 10 days ago

    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



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

    Posted 10 days ago

    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.