DB2_建立重組索引

目的：
測試DB2建立重組索引，本指令碼摘錄自DB2安裝目錄/admin_scripts/tbonlineinx.db2。
版本：Windows DB2 Express-C V9.7

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

-- connect to sample database
CONNECT TO SAMPLE;

1,建立索引
-- create an index on a table with different levels of access to the table
-- like read-write, read-only, no access

-- create an online index with read-write access to the table
CREATE INDEX index1 ON employee (lastname ASC);

-- drop the index
DROP INDEX index1;

-- create an index on a table while allowing only read access to it
LOCK TABLE employee IN SHARE MODE;

CREATE INDEX index1 ON employee (lastname ASC);

-- drop the index
DROP INDEX index1;

-- create an online index allowing no access to the table
LOCK TABLE employee IN EXCLUSIVE MODE;

CREATE INDEX index1 ON employee (lastname ASC);

-- reorg online index on the table

2,重組索引
-- reorganize the indexes on the table allowing read-write
REORG INDEXES ALL FOR TABLE employee
  ALLOW WRITE ACCESS
  ON ALL DBPARTITIONNUMS;

-- reorganize the indexes on the table allowing read-only
REORG INDEXES ALL FOR TABLE employee
  ALLOW READ ACCESS
  ON ALL DBPARTITIONNUMS;

-- reorganize the indexes on the table allowing no access
REORG INDEXES ALL FOR TABLE employee
  ALLOW NO ACCESS
  ON ALL DBPARTITIONNUMS;

-- drop the index
DROP INDEX index1;

3,建立超常列索引
-- create bufferpool with 32K pagesize
CREATE BUFFERPOOL bupl32k SIZE 500 PAGESIZE 32K;

-- create tablespace using above created bufferpool
CREATE TABLESPACE tbsp32k
  PAGESIZE 32k
  MANAGED BY DATABASE
  USING (FILE 'tbsp32k' 500)
  BUFFERPOOL bupl32k;

-- create table with large coloumn size.
CREATE TABLE inventory_ident (dept INTEGER,
                              serial_numbers VARCHAR(8190) NOT NULL)
  IN tbsp32k;

-- create a system temporary table space with 32K pages.
-- When the INDEXSORT database configuration parameter is set to Yes
-- (which is the default), then that data is sorted before it is passed
-- to index manager. If sort heap is big enough for the amount of data
-- being sorted, the sort will occur entirely in memory.  However, just
-- in case we need to spill to disk, DB2 will ensure that there is a
-- system temporary tablespace with a large enough page size to spill to.

CREATE SYSTEM TEMPORARY TABLESPACE tmptbsp32k
  PAGESIZE 32K
  MANAGED BY SYSTEM
  USING ('tmp_tbsp_32k')
  EXTENTSIZE 2
  BUFFERPOOL bupl32k;

-- create an index on the serial_numbers column
-- The upper bound for an index key length is variable based on 
-- page size. The maximum length of an index key part can be:   
-- 1024 bytes for 1K page size,
-- 2048 bytes for 8K page size,
-- 4096 bytes for 16K page size,
-- 8192 bytes for 32K page size,
-- and, the index name can be upto 128 char
CREATE INDEX inventory_serial_number_index_ident
  ON inventory_ident (serial_numbers);

-- perform. cleanup
DROP INDEX inventory_serial_number_index_ident;
DROP TABLE inventory_ident;
DROP TABLESPACE tmptbsp32k;
DROP TABLESPACE tbsp32k;
DROP BUFFERPOOL bupl32k;

-- disconnect from the database
CONNECT RESET;
TERMINATE;