DB2_使用大表空間

目的：
測試DB2使用大表空間(支援Large RID)，本指令碼摘錄自DB2安裝目錄admin_scripts/largerid.db2。
版本：Windows DB2 Express-C V9.7
說明:由於該版本不支援表分割槽,對第二部分沒有測試.

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

-- Connect to database.
CONNECT TO sample;

1，從常規表空間轉換為大表空間
-- Create a regular DMS tablespace.
CREATE REGULAR TABLESPACE largetbsp MANAGED BY DATABASE USING (FILE 'cont1' 1000);

-- Create a table in 'largetbsp' DMS tablespace.
CREATE TABLE large (max INT, min INT) IN largetbsp;

-- Create an index on 'large' table.
CREATE INDEX large_ind ON large (max);

-- Alter tablespace from a regular DMS tablespace to large DMS tablespace to
-- support large RIDs.
ALTER TABLESPACE largetbsp CONVERT TO LARGE;
--&gtSQL1237W  表空間 "LARGETBSP" 正在從 REGULAR 表空間轉換為 LARGE
表空間。必須對此表空間中的表的索引進行重組或重建，以便支援大型 RID。
SQLSTATE=01686

-- Rebuild/Reogranize indexes on table to support large RIDs.
-- Reorg reorganizes all indexes defined on a table by rebuilding the
-- index data into unfragmented, physically contiguous pages.
-- This will permit the table use 4-byte page numbers but not enable
-- the table to use more than 255 slots on a page.

-- To use more than 255 slots on a page:
-- a) The table definition and the table space page size must allow it.
-- b) the table must be reorganized using classic, off-line reorg.

REORG INDEXES ALL FOR TABLE large;

-- Drop index, table and tablespace.
DROP INDEX large_ind;
DROP TABLE large;
DROP TABLESPACE largetbsp;

2,分割槽錶轉換
-- The following scenario shows how a partitioned table which resides in a
-- regular DMS tablespaces  can be converted to reside into large DMS
-- tablespaces to support large RIDs.

-- Create regular DMS tablespaces.
CREATE REGULAR TABLESPACE tbsp1 MANAGED BY DATABASE USING (FILE 'cont1' 1000);
CREATE REGULAR TABLESPACE tbsp2 MANAGED BY DATABASE USING (FILE 'cont2' 1000);
CREATE REGULAR TABLESPACE tbsp3 MANAGED BY DATABASE USING (FILE 'CONT3' 1000);

-- Create a partitioned table.
CREATE TABLE large (max SMALLINT NOT NULL, CONSTRAINT CC CHECK (max>0))
  PARTITION BY RANGE (max)
    (PART  part1 STARTING FROM (1) ENDING (3) IN tbsp1,
    PART part2 STARTING FROM (4) ENDING (6) IN tbsp2,
    PART part3 STARTING FROM (7) ENDING (9) IN tbsp3);

--&gt
SQL8027N  正在沒有 DB2 企業伺服器版許可證的情況下使用表分割槽功能。DB2
檢測到正在沒有 DB2 企業伺服器版許可證的情況下使用表分割槽功能。確保您已從 IBM
代表或授權經銷商處購買了有效的 DB2 企業伺服器版權利並使用 DB2 許可證中心或
db2licm 命令列實用程式更新了您的許可證。有關更新許可證的更多資訊，請參閱您的平臺

的《快速入門》手冊。有關 db2licm 實用程式的更多資訊，請參閱《DB2 命令參考》。

-- Insert data into the table.
INSERT INTO large VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9);

-- Display records from the table.
SELECT * FROM large;

-- If a partitioned table has data partitions in different regular DMS
-- tablespaces, then the tablespaces cannot be converted to large
-- with the current definition.
-- To do this, first detach all the partitions of the table, later
-- convert all the tablespaces to large, reorg all the detached
-- partitions to support large RID. Finally, reattach the partitions.
-- Now the entire table supports large RIDs.

ALTER TABLE large DETACH PARTITION PART3 INTO TABLE detach_part3;
ALTER TABLE large DETACH PARTITION PART2 INTO TABLE detach_part2;

-- Display records contained in each table.
SELECT * FROM large;
SELECT * FROM detach_part2;
SELECT * FROM detach_part3;

-- Convert all tablespaces from regular DMS tablespace to large DMS tablespace.
ALTER TABLESPACE tbsp3 CONVERT TO LARGE;
ALTER TABLESPACE tbsp2 CONVERT TO LARGE;
ALTER TABLESPACE tbsp1 CONVERT TO LARGE;

-- Reorganize the detached partitions in order to support large RIDs.
-- Reorg reorganizes a table by reconstructing the rows to eliminate
-- fragmented data, and by compacting information.

REORG TABLE detach_part3;
REORG TABLE detach_part2;
REORG TABLE large;

-- Reattach the reorganized detached partitions for table to support
-- large RIDs.
ALTER TABLE large ATTACH PARTITION part2 STARTING FROM (4) ENDING (6)
  FROM TABLE detach_part2;
ALTER TABLE large ATTACH PARTITION part3 STARTING FROM (7) ENDING (9)
  FROM TABLE detach_part3;

-- After performing above ALTER statements, table is put into
-- set integrity peniding  state.
-- Before performing SELECT, table must be brought out from pending state.
SET INTEGRITY FOR large IMMEDIATE CHECKED;

-- Display records from the table.
SELECT * FROM large;

-- Drop tables and tablespaces.
DROP TABLE large;
DROP TABLESPACE tbsp1;
DROP TABLESPACE tbsp2;
DROP TABLESPACE tbsp3;

-- Disconnect from a database.
CONNECT RESET;

TERMINATE;