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
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.