DB2 表分区创建与管理
表分区创建脚本
CREATE TABLESPACE ts_2011 INCREASESIZE 1g;
CREATE TABLESPACE ts_2012 INCREASESIZE 1g;
CREATE TABLESPACE ts_max INCREASESIZE 1g ;
GRANT USE OF TABLESPACE ts_2011 TO USER u1;
GRANT USE OF TABLESPACE ts_2012 TO USER u1;
GRANT USE OF TABLESPACE ts_max TO USER u1;
CREATE TABLE t
(id INT NOT NULL ,
rq DATE,
CONSTRAINT pk_t PRIMARY key(ID)
)
compress yes
PARTITION BY range(rq NULLS FIRST )(
PART p2011 starting MINVALUE ending '2012-01-01' EXCLUSIVE IN ts_2011
);
ALTER TABLE t ADD PARTITION p2012 ending '2013-01-01' EXCLUSIVE in ts_2012 ;
ALTER TABLE t detach PARTITION p2012 INTO t_2012;
ALTER TABLE t attach PARTITION p2012 ending '2013-01-01' EXCLUSIVE FROM TABLE t_2012;
SET INTEGRITY FOR t IMMEDIATE checked;
ALTER TABLE t detach PART p2012 INTO t_junk;
SELECT * FROM t_junk;
DROP TABLE t_junk;
表分区相关系统字典表
SELECT * FROM syscat.tables WHERE TABNAME LIKE 'T%' ORDER BY TABNAME;
SELECT * FROM SYSCAT.DATAPARTITIONS WHERE TABNAME LIKE 'T%' ORDER BY TABNAME;
SELECT * FROM syscat.indexes WHERE TABNAME LIKE 'T%' ORDER BY TABNAME;
SELECT * FROM SYSCAT.INDEXPARTITIONS;