ITPub博客

首页 > Linux操作系统 > Linux操作系统 > DB2_多维集群表索引延迟清理

DB2_多维集群表索引延迟清理

原创 Linux操作系统 作者:redhouser 时间:2011-07-14 10:23:33 0 删除 编辑

目的:
测试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;
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-702161/,如需转载,请注明出处,否则将追究法律责任。

上一篇: DB2_数据库角色
下一篇: DB2_获取健康设置
请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    826153