db2分区表创建实战个案分享

我不是女神ヾ 2024-03-23 13:41 93阅读 0赞

## 1、db2分区表概 述： ##

DB2是一种关系型数据库管理系统，支持分区表的创建。分区表是将大型表分成多个小表的一种方式，以提高查询和维护的效率。在创建分区表时，需要指定分区键和分区数，分区键是用于将数据分配到不同分区的列，分区数是指分区的数量。分区表的创建可以通过命令行或者图形化界面完成，具体步骤包括创建表空间、定义分区键、创建分区表等。分区表的使用可以提高数据库的性能和可伸缩性，使得大型数据集的查询和维护变得更加高效。

## 2、常规的分区表定义格式 ##

db2 "CREATE TABLE LINEITEM 
    ( l_orderkey         DECIMAL(10,0) NOT NULL,
      l_partkey          INTEGER,
      l_suppkey          INTEGER,
      l_linenumber       INTEGER,
      l_quantity         DECIMAL(12,2),
      l_extendedprice    DECIMAL(12,2),
      l_discount         DECIMAL(12,2),
      l_tax              DECIMAL(12,2),
      l_returnflag       CHAR(1),
      l_linestatus       CHAR(1),
      l_shipdate         DATE,
      l_commitdate       DATE,
      l_receiptdate      DATE,
      l_shipinstruct     CHAR(25),
      l_shipmode         CHAR(10),
      l_comment          VARCHAR(44))
      PARTITION BY RANGE(l_shipdate)
    ( STARTING MINVALUE,
      STARTING '2018-01-01' ENDING '2018-06-30',
      STARTING '2018-07-01' ENDING '2018-12-31',
      STARTING '2019-01-01' ENDING '2019-06-30',
      STARTING '2019-07-01' ENDING '2019-12-31',
      ENDING MAXVALUE)"

## 3、创建实际测试分区表 ##

db2 "CREATE TABLE  pptab (
    IDNUM VARCHAR(18) , 
    USERID VARCHAR(40) , 
    BUSINESSCODE VARCHAR(10) , 
    SENDTIME VARCHAR(20) ,     
    ORGID VARCHAR(12) , 
    INTERCODE VARCHAR(15) , 
    ISSUEOFFICE VARCHAR(90) , 
    IDNAME VARCHAR(60) , 
    RECVTIME VARCHAR(25) , 
    CHECKRES VARCHAR(5) , 
    IDNUMMD5 VARCHAR(50) , 
    MDLNUM VARCHAR(30) , 
    TIMEDIFFERENCE VARCHAR(3) , 
    ISUP VARCHAR(1) , 
    CHECKFROM VARCHAR(1) , 
    RECORDID BIGINT NOT NULL , 
    REQUESTTYPE VARCHAR(1) , 
    CHECKSIGN VARCHAR(100) , 
    TRANSACNUM VARCHAR(5) , 
    SESSIONID VARCHAR(100) , 
    TRANSFAILFLAG VARCHAR(2) WITH DEFAULT '0' , 
    CRTKIND VARCHAR(2) , 
    LASTDATE VARCHAR(20) , 
    BANKCODE VARCHAR(12) )   
    
    PARTITION BY RANGE(SENDTIME ) 
    (
    PARTITION PART01 STARTING '1000-01-01 00:00:00' INCLUSIVE  ENDING '2018-08-31 59:59:59' INCLUSIVE,
    PARTITION PART02 STARTING '2018-09-01 00:00:00' INCLUSIVE  ENDING '2018-09-30 59:59:59' INCLUSIVE,
    PARTITION PART03 STARTING '2018-10-01 00:00:00' INCLUSIVE  ENDING '2018-10-31 59:59:59' INCLUSIVE,
    PARTITION PART04 STARTING '2018-11-01 00:00:00' INCLUSIVE  ENDING '2018-11-30 59:59:59' INCLUSIVE, 
    PARTITION PART05 STARTING '2018-12-01 00:00:00' INCLUSIVE  ENDING '2018-12-31 59:59:59' INCLUSIVE,
    PARTITION PART06 STARTING '2019-01-01 00:00:00' INCLUSIVE  ENDING '9999-12-31 59:59:59' INCLUSIVE
    )
    DISTRIBUTE BY HASH (SENDTIME)"

##    4、查看分布信息并建立索引 ##

db2 describe data partitions for table pptab  show detail
    
    --检查分区表数据在各分区的分布情况
    $ db2 "SELECT DATAPARTITIONNUM(SENDTIME) Partition, COUNT(*) Total_Rows FROM pptab group by DATAPARTITIONNUM(SENDTIME) order by DATAPARTITIONNUM(SENDTIME)"
    
    --建立索引
    
    db2 "create index I_SENDTIME on pptab(SENDTIME) in Index_dms"

##    5、load数据导入数据 ##

db2 "load from T_SINGLECHECK_HISTORY.del of del    insert into  pptab"
    
    --要避免在平面文件中创建第三份数据副本，请发出 LOAD 命令以将数据从 SQL 查询直接放入新分区表。
       SELECT * FROM t1;
       DECLARE c1 CURSOR FOR SELECT * FROM t1; 
       LOAD FROM c1 of CURSOR INSERT INTO sales_dp;SELECT * FROM sales_dp;

## 6、插入一条不在范围内的 测试数据 ##

db2 "insert into  pptab values ('370823194501081524','908040400002','01','liumingji','241000007','001008004007','此项暂不返回核查结果','孔凡荣','2018-09-17 09:30:00','00','3708231945010815247573245b46615b356c2a3326bc7a3682','0','01','0','0',80917093000547,'1',null,null,null,'0','2',null,'402461900070')"

报错 DB21034E The command was processed as an SQL statement because it was not a  
valid Command Line Processor command. During SQL processing it returned:  
SQL0327N The row cannot be inserted into table "DB2INST1.PPTAB" because it is  
outside the bounds of the defined data partition ranges. SQLSTATE=22525

## 7、分离一个分区 ##

db2 "ALTER TABLE pptab DETACH PARTITION PART06 INTO  pp_part06"
    db2 "select * from pp_part06"

## 8.将分离出来分区添加回去 ##

db2 "ALTER TABLE pptab ATTACH PARTITION part06 
    STARTING '2019-01-01 00:00:00' INCLUSIVE  ENDING '9999-12-31 59:59:59' INCLUSIVE
    FROM pp_part06"

如果pp\_part06不存在，要先创建一个表结构同分区表的pp\_part06的普通表

SQL3601W The statement caused one or more tables to automatically be placed  
in the Set Integrity Pending state. SQLSTATE=01586

db2 describe data partitions for table pptab show detail

PartitionId Inclusive (y/n) Inclusive (y/n)  
Low Value High Value  
\----------- - ------------------------------- - -------------------------------  
0 Y '1000-01-01 00:00:00' Y '2018-08-31 59:59:59'  
1 Y '2018-09-01 00:00:00' Y '2018-09-30 59:59:59'  
2 Y '2018-10-01 00:00:00' Y '2018-10-31 59:59:59'  
3 Y '2018-11-01 00:00:00' Y '2018-11-30 59:59:59'  
4 Y '2018-12-01 00:00:00' Y '2018-12-31 59:59:59'  
5 Y '2019-01-01 00:00:00' Y '9999-12-31 59:59:59'

6 record(s) selected.

PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode  
Status  
\----------- ------------------------------- ----------- ----------- ------------ ----------- - ------  
0 PART01 3 92 3 3 F  
1 PART02 3 93 3 3 F  
2 PART03 3 94 3 3 F  
3 PART04 3 95 3 3 F  
4 PART05 3 96 3 3 F  
5 PART06 3 97 3 3 N A

## 9、设置新分区完整性    ##

db2 "set integrity for pptab allow write access immediate checked incremental"
    db2 "SELECT DATAPARTITIONNUM(SENDTIME) Partition, COUNT(*) Total_Rows FROM sfhc.T_SINGLECHECK_HISTORY group by DATAPARTITIONNUM(SENDTIME) order by DATAPARTITIONNUM(SENDTIME)"
    db2 describe data partitions for table sfhc.T_SINGLECHECK_HISTORY
    db2 "ALTER TABLE sfhc.T_SINGLECHECK_HISTORY ADD PARTITION  par84 starting '2021-01-01 00:00:00' ending '2021-01-31 59:59:59'"
    db2 "ALTER TABLE sfhc.T_SINGLECHECK_HISTORY ADD PARTITION  85 starting '2021-02-01 00:00:00' ending '2021-02-31 59:59:59'"

##  ##