签到成功

知道了

CNDBA社区CNDBA社区

DB2普通表修改成分区表

2022-08-23 09:26 1406 0 原创 DB2
作者： hbhe0316
本文链接：http://www.cndba.cn/hbhe0316/article/108590

1.导出表结构

http://www.cndba.cn/hbhe0316/article/108590
http://www.cndba.cn/hbhe0316/article/108590http://www.cndba.cn/hbhe0316/article/108590

db2 connect to TESTDB
db2look -d TESTDB -z DB2INST1 -e -x -t T1 -o DB2INST1.T1 -nofed

2.重命名table name,index namehttp://www.cndba.cn/hbhe0316/article/108590http://www.cndba.cn/hbhe0316/article/108590

su - db2inst1
set -o vi
db2 "set current schema DB2INST1"
db2 "rename table T1 to T1_BAK"
db2 "rename index IDX_T1_P1 to IDX_T1_P1_BAK"
db2 "rename index IDX_T1_P2 to IDX_T1_P2_BAK"

3.修建分区表
3.1 表DB2INST1.T1

http://www.cndba.cn/hbhe0316/article/108590 

CONNECT TO TESTDB;
CREATE TABLE "DB2INST1  "."T1"  (
                  "ID" VARCHAR(64 OCTETS) NOT NULL WITH DEFAULT '' ,
                  "NAME" VARCHAR(64 OCTETS) NOT NULL WITH DEFAULT '' ,
                  "CLAIM_TIME" TIMESTAMP NOT NULL WITH DEFAULT CURRENT TIMESTAMP ,
                 INDEX IN "IDXSPACE2" PARTITION BY RANGE("CLAIM_TIME")
                 PART "T1_202205" STARTING('2022-05-01-00.00.00.000000') ENDING('2022-06-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202206" STARTING('2022-06-01-00.00.00.000000') ENDING('2022-07-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202207" STARTING('2022-07-01-00.00.00.000000') ENDING('2022-08-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202208" STARTING('2022-08-01-00.00.00.000000') ENDING('2022-09-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202209" STARTING('2022-09-01-00.00.00.000000') ENDING('2022-10-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202210" STARTING('2022-10-01-00.00.00.000000') ENDING('2022-11-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202211" STARTING('2022-11-01-00.00.00.000000') ENDING('2022-12-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202212" STARTING('2022-12-01-00.00.00.000000') ENDING('2023-01-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202301" STARTING('2023-01-01-00.00.00.000000') ENDING('2023-02-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202302" STARTING('2023-02-01-00.00.00.000000') ENDING('2023-03-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202303" STARTING('2023-03-01-00.00.00.000000') ENDING('2023-04-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202304" STARTING('2023-04-01-00.00.00.000000') ENDING('2023-05-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202305" STARTING('2023-05-01-00.00.00.000000') ENDING('2023-06-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202306" STARTING('2023-06-01-00.00.00.000000') ENDING('2023-07-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202307" STARTING('2023-07-01-00.00.00.000000') ENDING('2023-08-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202308" STARTING('2023-08-01-00.00.00.000000') ENDING('2023-09-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202309" STARTING('2023-09-01-00.00.00.000000') ENDING('2023-10-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202310" STARTING('2023-10-01-00.00.00.000000') ENDING('2023-11-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202311" STARTING('2023-11-01-00.00.00.000000') ENDING('2023-12-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202312" STARTING('2023-12-01-00.00.00.000000') ENDING('2024-01-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202401" STARTING('2024-01-01-00.00.00.000000') ENDING('2024-02-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202402" STARTING('2024-02-01-00.00.00.000000') ENDING('2024-03-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202403" STARTING('2024-03-01-00.00.00.000000') ENDING('2024-04-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202404" STARTING('2024-04-01-00.00.00.000000') ENDING('2024-05-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202405" STARTING('2024-05-01-00.00.00.000000') ENDING('2024-06-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202406" STARTING('2024-06-01-00.00.00.000000') ENDING('2024-07-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202407" STARTING('2024-07-01-00.00.00.000000') ENDING('2024-08-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202408" STARTING('2024-08-01-00.00.00.000000') ENDING('2024-09-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202409" STARTING('2024-09-01-00.00.00.000000') ENDING('2024-10-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202410" STARTING('2024-10-01-00.00.00.000000') ENDING('2024-11-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202411" STARTING('2024-11-01-00.00.00.000000') ENDING('2024-12-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2",
                 PART "T1_202412" STARTING('2024-12-01-00.00.00.000000') ENDING('2025-01-01-00.00.00.000000') EXCLUSIVE IN "ISDATASPACE2" INDEX IN "IDXSPACE2")
                 ORGANIZE BY ROW;


SET SYSIBM.NLS_STRING_UNITS = 'SYSTEM';

CREATE INDEX "DB2INST1  "."IDX_T1_P2" ON "DB2INST1  "."T1"
                ("CLAIM_TIME" ASC)
                PARTITIONED
                COMPRESS NO
                INCLUDE NULL KEYS ALLOW REVERSE SCANS;


GRANT SELECT ON TABLE "DB2INST1  "."T1" TO ROLE "HELPDESK" ;

COMMIT WORK;

CONNECT RESET;

TERMINATE;

执行命令创建分区表http://www.cndba.cn/hbhe0316/article/108590http://www.cndba.cn/hbhe0316/article/108590

#db2 -tvf DB2INST1.T1.ddl | tee DB2INST1.T1.out

4.导入数据至分区表http://www.cndba.cn/hbhe0316/article/108590

su - db2inst1
db2 connect to TESTDB
db2 "insert into DB2INST1.T1  select * from DB2INST1.T1_BAK"
db2 "select count(*) from DB2INST1.T1 with ur"

5.执行runstats

http://www.cndba.cn/hbhe0316/article/108590 

db2 "runstats on table DB2INST1.T1 with DISTRIBUTION and indexes all"

版权声明：本文为博主原创文章，未经博主允许不得转载。

DB2

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
hbhe0316

hbhe0316

关注

1.只有承认无知，才能装下新的东西； 2.进步来自一点点滴滴的积累； 3.广博让你更优秀，而专业让你无法替代； 4.挫折和失败能够转换为一种财富。

  • 889
    原创
  • 1
    翻译
  • 13
    转载
  • 24
    评论
  • 访问：1143471次
  • 积分：1523
  • 等级：核心会员
  • 排名：第6名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ