NOVOTS KMS 词汇表 Glossary    联系我们 Contact Us
查询 Search  
   
按类别浏览 Browse by Category
NOVOTS KMS .: 数据库 .: 分区表的实施步骤和方法

分区表的实施步骤和方法

    在我们的日常运维中,经常会碰到一些表随着时间的推移不断扩大,当初由于各种原因没有将其设计为分区表,所以当表很大时我们希望其迁移到分区表来缓解性能和存储方面的压力。
   本文介绍如何将一个常规表迁移到空分区表,在做之前我们应该先了解几个知识点:
1.分区表的分区可以位于相同的表空间,也可以位于不同的表空间,但指定的所有表空间必须具有相同的页大小、扩展数据块大小、存储机制(SMS/DMS)和类型(Regular/Large),并且所有表空间都必须位于同一数据库分区组中;
2.load碰到check约束的处理。

 1、创建空的分区表uwip_bak
2、使用cursor把数据load进分区表
3、删除uwip表
4、重命名uwip_bak为uwip

 
 在65.8测试UWIP_PART_HIST:

导出导入数据:
export to g:\UWIP_PART_HIST.ixf of ixf messages g:\UWIP_PART_HIST.txt select * from PROM.UWIP_PART_HIST
import from UWIP_PART_HIST.ixf of ixf commitcount 100000 messages UWIP_PART_HIST.out replace into PROM.UWIP_PART_HIST

更新表的统计信息:
runstats on table PROM.UWIP_PART_HIST with distribution and detailed indexes all

查询数据分布:
select time_stamp from prom.uwip_part_hist order by time_stamp fetch first 1 rows only
2009-04-01-13.59.04.726866

select time_stamp from prom.uwip_part_hist order by time_stamp desc fetch first 1 rows only
2009-08-06-16.20.24.498086

select case
when TIME_STAMP<'2009-05-01-00.00.00.696640'  and TIME_STAMP >'2009-04-01-00.00.00.696640' then '0904'
when TIME_STAMP<'2009-06-01-00.00.00.696640'  and TIME_STAMP >'2009-05-01-00.00.00.696640' then '0905'
when TIME_STAMP<'2009-07-01-00.00.00.696640'  and TIME_STAMP >'2009-06-01-00.00.00.696640' then '0906'
when TIME_STAMP<'2009-08-01-00.00.00.696640'  and TIME_STAMP >'2009-07-01-00.00.00.696640' then '0907'
when TIME_STAMP<'2013-09-01-00.00.00.696640'  and TIME_STAMP >'2009-07-01-00.00.00.696640' then '0908' else null end,
count(1) as num
from prom.UWIP_PART_HIST group by
case
when TIME_STAMP<'2009-05-01-00.00.00.696640'  and TIME_STAMP >'2009-04-01-00.00.00.696640' then '0904'
when TIME_STAMP<'2009-06-01-00.00.00.696640'  and TIME_STAMP >'2009-05-01-00.00.00.696640' then '0905'
when TIME_STAMP<'2009-07-01-00.00.00.696640'  and TIME_STAMP >'2009-06-01-00.00.00.696640' then '0906'
when TIME_STAMP<'2009-08-01-00.00.00.696640'  and TIME_STAMP >'2009-07-01-00.00.00.696640' then '0907'
when TIME_STAMP<'2013-09-01-00.00.00.696640'  and TIME_STAMP >'2009-07-01-00.00.00.696640' then '0908' else null end with ur;

0904     5182434
0905     1058054
0906      650028
0907    13908247
0908        1925

创建表空间:
db2 create tablespace dms_d1 managed by database using (file 'c:\ts1' 10000);
db2 create tablespace dms_d2 managed by database using (file 'c:\ts2' 10000);
db2 create tablespace dms_d3 managed by database using (file 'c:\ts3' 10000);
db2 create tablespace dms_d4 managed by database using (file 'c:\ts4' 10000);

创建分区表(见DDL):
PARTITION BY RANGE(P_DATE) (PARTITION OTHERS STARTING MINVALUE IN DMS_D1, 
PARTITION P201204 STARTING '1/1/1992' ENDING '31/12/1992' IN DMS_D2,  
PARTITION P201205 STARTING '1/1/1993' ENDING '31/12/1993' IN DMS_D3,  
PARTITION P201206 ENDING MAXVALUE IN DMS_D4 );

查询表的索引、外键、触发器、存储过程:
索引:
select char(NAME,26) NAME,char(CREATOR,20) CREATOR,char(COLNAMES,50) COLNAMES,UNIQUERULE,COLCOUNT,NLEVELS  from sysibm.sysindexes where TBNAME ='UWIP_PART_HIST'
NAME                       CREATOR              COLNAMES                                           UNIQUERULE COLCOUNT NLEVELS
-------------------------- -------------------- -------------------------------------------------- ---------- -------- -------
SQL070330142016560         PROM                 +UWIP_PART_HIST_ID                                 P                 1     1
I_UWIP_PART_HIST_1         PROM                 +UWIP_KEY+PART_NAME                                D                 2     1
I_UWIP_PART_HIST_2         PROM                 +UWIP_BARCODE                                      D                 1     1
I_UWIP_PART_HIST_3         PROM                 +TIME_STAMP                                        D                 1     1
I_UWIP_PART_HIST_4         PROM                 +PART_BARCODE                                      D                 1     1
I_UWIP_PART_HIST_5         PROM                 +IBMSNAP_LOGMARKER                                 D                 1     1

外键:
select * from syscat.tabconst where type in ('P', 'F') and tabname = 'UWIP_PART_HIST';

触发器:
SELECT CAST(TRIGNAME AS varchar(25)) TRIGNAME,CAST(TABNAME AS varchar(25)) TABNAME FROM SYSCAT.TRIGGERS where tabname='UWIP_PART_HIST'

存储过程:
select bschema,bname,btype,specificname from syscat.routinedep where bname='UWIP_PART_HIST'
SELECT char(routineschema,15) as chema,char(routinename,30) as name,specificname FROM SYSCAT.routines where specificname=''

查询表空间使用情况:
IN "TS_USR_2" INDEX IN "TS_IDX"
db2 "select substr(tbsp_name,1,15) as tbspc_name,tbsp_type,tbsp_page_size,tbsp_total_size_kb/1024 as TOTAL_MB,tbsp_used_size_kb/1024 as USED_MB,tbsp_free_size_kb/1024 as FREE_MB,tbsp_utilization_percent from SYSIBMADM.TBSP_UTILIZATION where TBSP_TYPE='DMS' "

TBSPC_NAME      TBSP_TYPE  TBSP_PAGE_SIZE TOTAL_MB             USED_MB              FREE_MB              TBSP_UTILIZATIO
N_PERCENT
--------------- ---------- -------------- -------------------- -------------------- -------------------- ------------------------
TS_USR_1        DMS                  4096                19165                  581                18583     3.03
TS_USR_2        DMS                  4096                33471                    1                33470     0.00
TS_USR_3        DMS                  4096                12695                 2832                 9861    22.31
TS_IDX          DMS                  4096                19531                  155                19374     0.79
    
导入数据:
DECLARE c1 CURSOR FOR SELECT * FROM t1;
LOAD FROM c1 of CURSOR INSERT INTO sales_dp;
记录时间。


这篇文章对你多有用?

用户评语

添加评语
当前还没有评语.


.: .: .: .: .:
[ 登陆 ]
北京护航科技有限公司 2006

Novots Technologies Limited