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;

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

转载于:http://blog.itpub.net/18922393/viewspace-702190/