DB2_多維叢集表索引延遲清理

目的：
測試DB2多維叢集表索引延遲清理，本指令碼摘錄自DB2安裝目錄admin_scripts/fasterrollout.db2。
版本：Windows DB2 Express-C V9.7

操作步驟：
使用"db2cmd db2 -t"進入互動模式,執行後續操作。

1,使用預設"IMMEDIATE INDEX CLEANUP":
CONNECT TO sample;

-- *************************************************************************
-- Following shows how to perform. delete that uses IMMEDIATE INDEX
-- CLEANUP roll out type.
-- *************************************************************************

-- Create MDC table 'MDC_temp'.

CREATE TABLE MDC_emp (emp_no INT NOT NULL, emp_sal DOUBLE,
                      emp_location CHAR (25))        
  ORGANIZE BY DIMENSIONS (emp_no, emp_location);

-- Populate table 'MDC_emp' with data.

INSERT INTO MDC_emp values (100, 1.25, 'BANGALORE');
INSERT INTO MDC_emp values (200, 2.00, 'BANGALORE');
INSERT INTO MDC_emp values (300, 2.00, 'CHENNAI');
INSERT INTO MDC_emp values (400, 3.00, 'CHENNAI');
INSERT INTO MDC_emp values (500, 2.00, 'PUNE');
INSERT INTO MDC_emp values (600, 2.00, 'BANGALORE');

-- Create index on columns 'emp_no' and 'emp_location'.

CREATE INDEX indx1 ON MDC_emp (emp_no, emp_location);

-- The below DELETE statement uses 'IMMEDIATE INDEX CLEANUP ROLLOUT' as default.
-- Indexes are cleaned up at delete time and rolled out blocks will be
-- available for immediate use.

DELETE FROM MDC_emp WHERE emp_sal = 2.00 AND emp_location = 'BANGALORE';

-- Drop the table.

DROP TABLE MDC_emp;

2,使用"DEFERRED INDEX CLEANUP":
-- *************************************************************************
-- Following shows how to perform. DELETE that uses DEFERRED INDEX CLEANUP
-- roll out type. This type of index cleanup is very efficient in case of
-- large tables. This even shows how to change the DEFAULT mode to
-- DEFERRED mode.
-- *************************************************************************

-- Create MDC table 'MDC_temp'.

CREATE TABLE MDC_emp (emp_no INT NOT NULL, emp_sal DOUBLE,
                      emp_location CHAR (25))        
  ORGANIZE BY DIMENSIONS (emp_no, emp_location);

-- Populate table 'MDC_emp' with data.

INSERT INTO MDC_emp values (100, 1.25, 'BANGALORE');
INSERT INTO MDC_emp values (200, 2.00, 'BANGALORE');
INSERT INTO MDC_emp values (300, 2.00, 'CHENNAI');
INSERT INTO MDC_emp values (400, 3.00, 'CHENNAI');
INSERT INTO MDC_emp values (500, 2.00, 'PUNE');
INSERT INTO MDC_emp values (600, 2.00, 'BANGALORE');

-- Create index on columns 'emp_no' and 'emp_location'.

CREATE INDEX indx1 ON MDC_emp (emp_no, emp_location);

-- Change the roll out type to 'DEFERRED'.

SET CURRENT MDC ROLLOUT MODE = DEFERRED;

-- The above statement changes the roll out type from 'IMMEDIATE' to 'DEFERRED'.
-- Once the delete statement is committed, DB2 begins to cleanup
-- RID indexes asynchronously.  Users cannot use the rolled out blocks immediately
-- after DELETE. These blocks will be available for reuse only after index cleanup
-- is completed by DB2.

DELETE FROM MDC_emp WHERE emp_sal = 2.00 OR emp_location = 'BANGALORE';

-- Drop table.

DROP TABLE MDC_emp;

-- Disconnect form. database.

CONNECT RESET;

TERMINATE;