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

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'"