将常规表迁移到分区表

字数 14522阅读 4152评论 7赞 0

在我们的日常运维中，经常会碰到一些表随着时间的推移不断扩大，当初由于各种原因没有将其设计为分区表，所以当表很大时我们希望其迁移到分区表来缓解性能和存储方面的压力。

本文介绍如何将一个常规表迁移到空分区表，在做之前我们应该先了解几个知识点：

1.分区表的分区可以位于相同的表空间，也可以位于不同的表空间，但指定的所有表空间必须具有相同的页大小、扩展数据块大小、存储机制（SMS/DMS）和类型（Regular/Large），并且所有表空间都必须位于同一数据库分区组中；

2.load碰到check约束的处理。

环境：Red Hat 6x ; DB2 V9.7

1.创建表空间和分区表，如果你希望使用原表空间和新表空间来存放分区表的数据，那么应该建立一个与原表空间配置相同的表空间，这样也有助于未来对分区表的维护。通常我们也是这么做的。获取源表空间和源表的DDL：

方法1：db2look -d testdb1 -z bassweb -t NEW_KPI_DAILY_BAK -e -l -o nkdb.ddl；方法2：使用QC;

修改 nkdb.ddl代码为：

CONNECT TO TESTDB1

---------创建表空间-----------------
CREATE LARGE TABLESPACE "TBS_BASS_WEB1" -------原表空间为TBS_BASS_WEB

IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 32768 MANAGED BY DATABASE
         USING (FILE '/data/tbs_bass_web01' 1000, FILE '/data/tbs_bass_web02' 1000)
         EXTENTSIZE 32
         PREFETCHSIZE AUTOMATIC
         BUFFERPOOL IBMDEFAULTBP
         OVERHEAD 24.100000
         TRANSFERRATE 0.900000
         NO FILE SYSTEM CACHING
         DROPPED TABLE RECOVERY ON;
----------创建分区表-------------------------

CREATE TABLE "BASSWEB"."NEW_KPI_DAILY_BAK1" ( --------源表名称为NEW_KPI_DAILY_BAK
                  "DAILY_ID" VARCHAR(32) NOT NULL ,
                  "KPI_ID" VARCHAR(32) NOT NULL ,
                  "KPI_NAME" VARCHAR(128) NOT NULL ,
                  "KPI_DIM_DATA_GROUP_ID" VARCHAR(32) NOT NULL ,
                  "DAILY_DATE" TIMESTAMP NOT NULL ,
                  "DAILY_VALUE" DOUBLE ,
                  "DAILY_LAST_VALUE" DOUBLE ,
                  "DAILY_LAST_TREND" DOUBLE ,
                  "DAILY_WEEK_VALUE" DOUBLE ,
                  "DAILY_WEEK_TREND" DOUBLE ,
partition by range(DAILY_DATE)
                (

part 201001 starting '2010-01-01-00.00.00.000000' ending '2010-03-31-23.59.59.000000' IN "TBS_BASS_WEB1",
part 201002 starting '2010-04-01-00.00.00.000000' ending '2010-06-30-23.59.59.000000' IN "TBS_BASS_WEB1",
part 201003 starting '2010-07-01-00.00.00.000000' ending '2010-09-30-23.59.59.000000' IN "TBS_BASS_WEB1",
part 201004 starting '2010-10-01-00.00.00.000000' ending '2010-12-31-23.59.59.000000' IN "TBS_BASS_WEB1",

part 201101 starting '2011-01-01-00.00.00.000000' ending '2011-03-31-23.59.59.000000' IN "TBS_BASS_WEB1",

part 201102 starting '2011-04-01-00.00.00.000000' ending '2011-06-30-23.59.59.000000' IN "TBS_BASS_WEB1",
part 201103 starting '2011-07-01-00.00.00.000000' ending '2011-09-30-23.59.59.000000' IN "TBS_BASS_WEB1",
part 201104 starting '2011-10-01-00.00.00.000000' ending '2011-12-31-23.59.59.000000' IN "TBS_BASS_WEB1",

part 201201 starting '2012-01-01-00.00.00.000000' ending '2012-03-31-23.59.59.000000' IN "TBS_BASS_WEB1",
part 201202 starting '2012-04-01-00.00.00.000000' ending '2012-06-30-23.59.59.000000' IN "TBS_BASS_WEB1",
part 201203 starting '2012-07-01-00.00.00.000000' ending '2012-09-30-23.59.59.000000' IN "TBS_BASS_WEB1",
part 201204 starting '2012-10-01-00.00.00.000000' ending '2012-12-31-23.59.59.000000' IN "TBS_BASS_WEB1",

part 201301 starting '2013-01-01-00.00.00.000000' ending '2013-03-31-23.59.59.000000' IN "TBS_BASS_WEB1",
part 201302 starting '2013-04-01-00.00.00.000000' ending '2013-06-30-23.59.59.000000' IN "TBS_BASS_WEB1",
part 201303 starting '2013-07-01-00.00.00.000000' ending '2013-09-30-23.59.59.000000' IN "TBS_BASS_WEB1",
part 201304 starting '2013-10-01-00.00.00.000000' ending '2013-12-31-23.59.59.000000' IN "TBS_BASS_WEB1"

);

为了利于数据的导入，可以将约束和索引在导完数据后再建，注意表名。

----------约束---------------------

ALTER TABLE "BASSWEB "."NEW_KPI_DAILY_BAK1" ADD CONSTRAINT "P_IDENTIFIER_1" PRIMARY KEY("DAILY_ID");

-----------索引----------------------

CREATE INDEX "BASSWEB "."IDX_DAILY_03" ON "BASSWEB "."NEW_KPI_DAILY_BAK1" ("KPI_DIM_DATA_GROUP_ID" ASC) ALLOW REVERSE SCANS;

CREATE INDEX "BASSWEB "."IDX_DAILY_04" ON "BASSWEB "."NEW_KPI_DAILY_BAK1"("DAILY_DATE" ASC,"KPI_ID" ASC,"KPI_DIM_DATA_GROUP_ID" ASC) ALLOW REVERSE SCANS;

CREATE INDEX "BASSWEB "."IDX_DAILY_06" ON "BASSWEB "."NEW_KPI_DAILY_BAK1"("DAILY_DATE" ASC) ALLOW REVERSE SCANS;

COMMIT WORK;
CONNECT RESET;

TERMINATE;

2.检查分区表的分区情况和表状态：

bash-3.00$ db2 "describe data partitions for table BASSWEB.NEW_KPI_DAILY_BAK1 "

PartitionId Inclusive (y/n)                   Inclusive (y/n)
              Low Value                         High Value
----------- - ------------------------------- - -------------------------------
          0 Y '2010-01-01-00.00.00.000000'    Y '2010-03-31-23.59.59.000000'
          1 Y '2010-04-01-00.00.00.000000'    Y '2010-06-30-23.59.59.000000'
          2 Y '2010-07-01-00.00.00.000000'    Y '2010-09-30-23.59.59.000000'
          3 Y '2010-10-01-00.00.00.000000'    Y '2010-12-31-23.59.59.000000'
          4 Y '2011-01-01-00.00.00.000000'    Y '2011-03-31-23.59.59.000000'
          5 Y '2011-04-01-00.00.00.000000'    Y '2011-06-30-23.59.59.000000'
          6 Y '2011-07-01-00.00.00.000000'    Y '2011-09-30-23.59.59.000000'
          7 Y '2011-10-01-00.00.00.000000'    Y '2011-12-31-23.59.59.000000'
          8 Y '2012-01-01-00.00.00.000000'    Y '2012-03-31-23.59.59.000000'
          9 Y '2012-04-01-00.00.00.000000'    Y '2012-06-30-23.59.59.000000'
         10 Y '2012-07-01-00.00.00.000000'    Y '2012-09-30-23.59.59.000000'
         11 Y '2012-10-01-00.00.00.000000'    Y '2012-12-31-23.59.59.000000'
         12 Y '2013-01-01-00.00.00.000000'    Y '2013-03-31-23.59.59.000000'
         13 Y '2013-04-01-00.00.00.000000'    Y '2013-06-30-23.59.59.000000'
         14 Y '2013-07-01-00.00.00.000000'    Y '2013-09-30-23.59.59.000000'
         15 Y '2013-10-01-00.00.00.000000'    Y '2013-12-31-23.59.59.000000'
         16 Y '2014-01-01-00.00.00.000000'    Y '2014-03-31-23.59.59.000000'
         17 Y '2014-04-01-00.00.00.000000'    Y '2014-06-30-23.59.59.000000'
         18 Y '2014-07-01-00.00.00.000000'    Y '2014-09-30-23.59.59.000000'
         19 Y '2014-10-01-00.00.00.000000'    Y '2014-12-31-23.59.59.000000'

20 record(s) selected.

bash-3.00$ db2 "load query table BASSWEB.NEW_KPI_DAILY_BAK1"

Tablestate:
Normal

3.将源表BASSWEB.NEW_KPI_DAILY_BAK数据迁移到BASSWEB.NEW_KPI_DAILY_BAK1:

bash-3.00$ db2 "declare c1 cursor for select * from bassweb.NEW_KPI_DAILY_BAK"
DB20000I The SQL command completed successfully.
bash-3.00$ db2 "get db cfg for briodb" | grep -i log
....
Log retain for recovery enabled             (LOGRETAIN) = RECOVERY
User exit for logging enabled                (USEREXIT) = OFF
HADR log write synchronization mode     (HADR_SYNCMODE) = NEARSYNC
First log archive method                 (LOGARCHMETH1) = DISK:/home/backup
Options for logarchmeth1                  (LOGARCHOPT1) =
Second log archive method                (LOGARCHMETH2) = OFF
....

bash-3.00$ db2 "create table nkdb_ex like bassweb.NEW_KPI_DAILY_BAK in TBS_BASS_WEB1"
DB20000I The SQL command completed successfully.
bash-3.00$ db2 "load from c1 of cursor messages c1.msg insert into bassweb.NEW_KPI_DAILY_BAK1 for exception nkdb_ex nonrecoverable"

Number of rows read         = 17565142
Number of rows skipped      = 0
Number of rows loaded       = 17565142
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 17565142

*当表有检查约束时须执行set integrity 操作。具体操作请参考 http://www.db2china.net//home/space.php?uid=86517&do=blog&id=29357

bash-3.00$ db2 load query table bassweb.NEW_KPI_DAILY_BAK1
Tablestate:
Normal

bash-3.00$ db2 "select year(daily_date) as year,month(daily_date) as month ,count(*) as count from bassweb.NEW_KPI_DAILY_BAK1 group by year(daily_date),month(daily_date)"

YEAR        MONTH       COUNT
----------- ----------- -----------
       2011           1      842039
       2011           8      287490
       2011          11           5
       2011          12     3359281
       2012           1     3273657
       2012           2     4553382
       2012           3     5249288

7 record(s) selected.

bash-3.00$ db2 "select year(daily_date) as year,month(daily_date) as month ,count(*) as count from bassweb.NEW_KPI_DAILY_BAK group by year(daily_date),month(daily_date)"

YEAR        MONTH       COUNT
----------- ----------- -----------
       2011           1      842039
       2011           8      287490
       2011          11           5
       2011          12     3359281
       2012           1     3273657
       2012           2     4553382
       2012           3     5249288

7 record(s) selected.

4.修改名称：

db2 "rename bassweb.new_kpi_daily_bak to new_kpi_daily_bak_old"; 建议不要直接删除源表，除非您的存储太紧张~

db2 "rename bassweb.new_kpi_daily_bak1 to new_kpi_daily_bak";

建立约束和索引，脚本见第1步。

db2 "select INDNAME,TABNAME,COLNAMES TBSPACEID from syscat.indexes where tabname='NEW_KPI_DAILY_BAK' "; 检查索引

db2 "select CONSTRAINT_NAME,TABLE_SCHEMA,TABLE_NAME from sysibm.TABLE_CONSTRAINTS where table_name='NEW_KPI_DAILY_BAK' "; 检查约束

----------------------------

补：若源表有视图则不能rename,此时在保证获得视图DDL的前提下将视图删掉方可重命名，然后重建视图。

数据库数据迁移分区表

著作权归作者所有

如果觉得我的文章对您有用，请点赞。您的支持将鼓励我继续创作！