#创建分区表
db2 “CREATE TABLE food(a INT) PARTITION BY RANGE(a) (STARTING FROM (1) ENDING AT (100) EXCLUSIVE, STARTING FROM (201) ENDING AT (300))”
插入数据
db2 “insert into food values (100)”
#创建分区表
CREATE TABLE SALES( ID INTEGER NOT NULL,SALES_PERSON VARCHAR(50),REGION VARCHAR(50),SALES_DATE DATE) PARTITION BY RANGE(SALES_DATE) (STARTING ‘1/1/2012’ ENDING ‘2/1/2012’ exclusive,STARTING ‘2/1/2012’ ENDING ‘3/1/2012’ exclusive,STARTING ‘3/1/2012’ ENDING ‘4/1/2012’ exclusive,STARTING ‘4/1/2012’ ENDING ‘5/1/2012’ exclusive)
查看表分区结构
db2 “describe DATA PARTITIONS for table sales”
#创建分区表
CREATE TABLE SALES1( ID INTEGER NOT NULL,SALES_PERSON VARCHAR(50),REGION VARCHAR(50),SALES_DATE DATE) PARTITION BY RANGE(SALES_DATE) (STARTING ‘1/1/2012’ ENDING ‘2/1/2012’ exclusive,STARTING ‘2/1/2012’ ENDING ‘3/1/2012’ exclusive,STARTING ‘3/1/2012’ ENDING ‘4/1/2012’ exclusive,STARTING ‘4/1/2012’ ENDING ‘4/30/2012’ exclusive)
#添加表分区
db2 “ALTER TABLE sales1 ADD PARTITION PART4 STARTING ‘4/30/2012’ ENDING ‘5/1/2012’ exclusive”
[db2inst1@node ~]$ db2 “describe DATA PARTITIONS for table sales1 show detail”
PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode
Status
0 PART0 4 19 4 4 F
1 PART1 4 20 4 4 F
2 PART2 4 21 4 4 F
3 PART3 4 256 4 4 F
4 PART4 4 257 4 4 F
5 record(s) selected.
PartitionId Inclusive (y/n) Inclusive (y/n)
Low Value High Value
0 Y ‘2012-01-01’ N ‘2012-02-01’
1 Y ‘2012-02-01’ N ‘2012-03-01’
2 Y ‘2012-03-01’ N ‘2012-04-01’
3 Y ‘2012-04-01’ N ‘2012-04-30’
4 Y ‘2012-04-30’ N ‘2012-05-01’
db2 “insert into SALES1 values (1,‘zhang3’,‘1’,‘2012-04-30’)”
[db2inst1@node ~]$ db2 “select * from db2inst1.sales1”
ID SALES_PERSON REGION SALES_DATE
2 li4 1 03/30/2012
1 zhang3 1 04/30/2012
db2 “insert into SALES1 values (2,‘li4’,‘1’,‘2012-03-30’)”
分区是无法直接删除，先卸载分区，再进行drop操作(上述SQL执行是若表存在会报错)
db2 “ALTER TABLE SALES1 DETACH PART PART4 INTO SALES120120430”
#注意:DETACH操作是异步的，所以执行卸载后立马执行删除操作容易出现删除失败
db2 “DROP TABLE SALES120120430”
[db2inst1@node ~]$ db2 “describe DATA PARTITIONS for table sales1 show detail”
PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode
Status
0 PART0 4 19 4 4 F
1 PART1 4 20 4 4 F
2 PART2 4 21 4 4 F
3 PART3 4 256 4 4 F
4 PART4 4 257 4 4 F
5 record(s) selected.
PartitionId Inclusive (y/n) Inclusive (y/n)
Low Value High Value
0 Y ‘2012-01-01’ N ‘2012-02-01’
1 Y ‘2012-02-01’ N ‘2012-03-01’
2 Y ‘2012-03-01’ N ‘2012-04-01’
3 Y ‘2012-04-01’ N ‘2012-04-30’
4 Y ‘2012-04-30’ N ‘2012-05-01’
5 record(s) selected.
#分区转出
[db2inst1@node ~]$ db2 “ALTER TABLE SALES1 DETACH PART PART4 INTO SALES120120430”
DB20000I The SQL command completed successfully.
[db2inst1@node ~]$ db2 “select * from SALES120120430”
ID SALES_PERSON REGION SALES_DATE
1 zhang3 1 04/30/2012
1 record(s) selected.
#分区转出
[db2inst1@node ~]$ db2 “ALTER TABLE SALES1 DETACH PART PART3 INTO SALES120120401_29”
DB20000I The SQL command completed successfully.
[db2inst1@node ~]$ db2 “select * from SALES120120401_29”
ID SALES_PERSON REGION SALES_DATE
0 record(s) selected.
[db2inst1@node ~]$ db2 “describe DATA PARTITIONS for table sales1 show detail”
PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode
Status
0 PART0 4 19 4 4 F
1 PART1 4 20 4 4 F
2 PART2 4 21 4 4 F
3 record(s) selected.
PartitionId Inclusive (y/n) Inclusive (y/n)
Low Value High Value
0 Y ‘2012-01-01’ N ‘2012-02-01’
1 Y ‘2012-02-01’ N ‘2012-03-01’
2 Y ‘2012-03-01’ N ‘2012-04-01’
3 record(s) selected.
@按月表分区
db2 “CREATE TABLE SALES2( ID INTEGER NOT NULL,SALES_PERSON VARCHAR(50),REGION VARCHAR(50),SALES_DATE DATE) PARTITION BY RANGE(SALES_DATE) (STARTING ‘1/1/2012’ ENDING ‘12/1/2013’ exclusive EVERY 1 month)”
db2 “CREATE TABLE SALES3(ID INTEGER NOT NULL,SALES_PERSON VARCHAR(50),REGION VARCHAR(50),SALES_DATE DATE) PARTITION BY (SALES_DATE) (STARTING ‘1/1/2012’ ENDING ‘12/1/2013’ exclusive EVERY 1 month)”
ALTER TABLE SALES1 ATTACH PARTITION PART3 STARTING FROM (‘4/1/2012’) ENDING AT (‘4/30/2012’) FROM TABLE SALES120120430
ALTER TABLE SALES1 ATTACH PARTITION PART3 (STARTING ‘4/1/2012’ ENDING AT ‘5/1/2012’ exclusive) FROM TABLE SALES120120430
#分区转入
db2 => ALTER TABLE SALES1 ATTACH PARTITION PART3 STARTING FROM (‘4/1/2012’) ENDING AT (‘4/30/2012’) FROM TABLE SALES120120430
SQL3601W The statement caused one or more tables to automatically be placed
in the Set Integrity Pending state. SQLSTATE=01586
#注意此时sales1表里该分区不可用，处于set integrity pending 状态，可以通过syscat.tables 里面的STATUS字段得到表的状态：
[db2inst1@node ~]$ db2 “describe DATA PARTITIONS for table sales1 show detail”
PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode
Status
0 PART0 4 19 4 4 F
1 PART1 4 20 4 4 F
2 PART2 4 21 4 4 F
3 PART3 4 257 4 4 N A
4 record(s) selected.
PartitionId Inclusive (y/n) Inclusive (y/n)
Low Value High Value
0 Y ‘2012-01-01’ N ‘2012-02-01’
1 Y ‘2012-02-01’ N ‘2012-03-01’
2 Y ‘2012-03-01’ N ‘2012-04-01’
3 Y ‘2012-04-01’ Y ‘2012-04-30’
4 record(s) selected.
[db2inst1@node ~]$ db2 “select tabname,colcount,status from syscat.tables where tabschema not like ‘SYS%’ order by tabname”
TABNAME COLCOUNT STATUS
FOO 1 N
FOOD 1 N
FUND_DAILY_INCOME 16 N
MULTI 2 N
NEWTABLE 3 N
SALES 4 N
SALES1 4 C
SALES10 4 N
SALES120120401_29 4 N
SALES2 4 N
SALES20 4 N
SALES3 4 N
T1 3 N
TB1 13 N
TB2 13 N
TB3 13 N
TEST 2 N
17 record(s) selected.
#需要手工对表执行完整性检查，此过程将在新连接的分区商完成范围检查，并强制执行存在的任何约束。完成之后，新连接的数据便能在数据库中可视。
[db2inst1@node ~]$ db2 “set integrity for sales1 immediate checked”
DB20000I The SQL command completed successfully.
[db2inst1@node ~]$ db2 “select tabname,colcount,status from syscat.tables where tabschema not like ‘SYS%’ order by tabname”
TABNAME COLCOUNT STATUS
FOO 1 N
FOOD 1 N
FUND_DAILY_INCOME 16 N
MULTI 2 N
NEWTABLE 3 N
SALES 4 N
SALES1 4 N
SALES10 4 N
SALES120120401_29 4 N
SALES2 4 N
SALES20 4 N
SALES3 4 N
T1 3 N
TB1 13 N
TB2 13 N
TB3 13 N
TEST 2 N
17 record(s) selected.
[db2inst1@node ~]$ db2 “describe DATA PARTITIONS for table sales1 show detail”
PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode
Status
0 PART0 4 19 4 4 F
1 PART1 4 20 4 4 F
2 PART2 4 21 4 4 F
3 PART3 4 257 4 4 F
4 record(s) selected.
PartitionId Inclusive (y/n) Inclusive (y/n)
Low Value High Value
0 Y ‘2012-01-01’ N ‘2012-02-01’
1 Y ‘2012-02-01’ N ‘2012-03-01’
2 Y ‘2012-03-01’ N ‘2012-04-01’
3 Y ‘2012-04-01’ Y ‘2012-04-30’
4 record(s) selected.
[db2inst1@node ~]$ db2 “select * from sales1”
ID SALES_PERSON REGION SALES_DATE
2 li4 1 03/30/2012
1 zhang3 1 04/30/2012
@DB2使用not partitioned和partitioned 关键字来指定索引是否分区，默认为分区索引
create index db2inst1.inedx1 on sales1(ID) not partitioned
create index db2inst1.index2 on sales1(SALES_DATE) partitioned
[db2inst1@node ~]$ db2 describe indexes for table sales1
Index Index Unique Number of Index Index Null
schema name rule columns type partitioning keys
SALES1 INEDX1 D 1 RELATIONAL DATA N Y
SALES1 INDEX2 D 1 RELATIONAL DATA P Y
db2 “insert into SALES1 values (3,‘wang2’,‘1’,‘2012-02-20’)”
db2 “insert into SALES1 values (4,‘gao4’,‘1’,‘2012-01-30’)”
db2 “insert into SALES1 values (5,‘liu5’,‘1’,‘2012-04-10’)”
db2 “insert into SALES1 values (6,‘wen6’,‘1’,‘2012-04-20’)”
[db2inst1@node ~]$ db2 “select * from sales1 where sales_date between ‘2012-03-01’ and ‘2012-05-01’”
ID SALES_PERSON REGION SALES_DATE
2 li4 1 03/30/2012
1 zhang3 1 04/30/2012
5 liu5 1 04/10/2012
6 wen6 1 04/20/2012
[db2inst1@node ~]$ db2expln -d testy -f expln.sql -g -o expln.txt
DB2 Universal Database Version 10.5, 5622-044 © Copyright IBM Corp. 1991, 2012
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
Output is available in “expln.txt”.
[db2inst1@node ~]$ vi expln.txt
| | | | Stop Key: Inclusive Value
| | | | | 1: 2012-05-01
| Active Data Partitions: 2-3
| Evaluate Predicates Before Locking for Key
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | 1: 2012-03-01
| | Stop Key: Inclusive Value
| | | 1: 2012-05-01
| Data Prefetch: Sequential(2), Readahead
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Return Data to Application
| | | #Columns = 4
Return Data Completion
End of section
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
4
RETURN
( 1)
15.2965
|
4
FETCH
( 2)
15.2965
/
4 6
IXSCAN Table:
( 3) DB2INST1
0.00184422 SALES1
|
6
Index:
DB2INST1
INDEX2
DB2 Universal Database Version 10.5, 5622-044 © Copyright IBM Corp. 1991, 2012
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = “SYSIBM”, “SYSFUN”, “SYSPROC”, “SYSIBMADM”,
“DB2INST1”
Statement:
select *
from sales1
where sales_date between ‘2012-03-01’ and ‘2012-05-01’
Section Code Page = 1386
Estimated Cost = 20.303848
Estimated Cardinality = 4.000000
Access Table Name = DB2INST1.SALES1 ID = -6,-32765
| #Columns = 4
| Data-Partitioned Table
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| May participate in Scan Sharing structures
| Scan may start anywhere and wrap, for completion
| Fast scan, for purposes of scan sharing management
| Scan can be throttled in scan sharing management
| Data Partition Elimination Info:
| | Range 1:
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | 1: 2012-03-01
| | | | Stop Key: Inclusive Value
| | | | | 1: 2012-05-01
| Active Data Partitions: 2-3
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | #Predicates = 2
| | Return Data to Application
| | | #Columns = 4
Return Data Completion
End of section
#这里引用的是syscat.datapartitions中的序列号(seqno)而不是describe data partitions 命令中的PartitionId
#表空间backup pending状态
[db2inst1@node ~]$ db2pd -d testy -tablespaces|more
Database Member 1001 – Database TESTY – Active – Up 0 days 02:49:24 – Date 2021-09-07-14.20.00.451656
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
0x00007FFF813A8A20 0 DMS Regular 4096 4 Yes 4 1 1 Off 1 0 3 Yes SYSCATSPACE
0x00007FFF91F50080 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE1
0x00007FFF91F5D240 2 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 Yes USERSPACE1
0x00007FFF91F6A400 3 DMS Large 4096 4 Yes 4 1 1 Off 1 0 3 Yes SYSTOOLSPACE
0x00007FFF91F775C0 4 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 Yes DMS_DATA
0x00007FFF91F84780 5 DMS Large 4096 32 Yes 32 1 1 Off 1 0 31 Yes DMS_INDEX
0x00007FFF91F91940 6 DMS Large 4096 32 Yes 32 2 2 Off 1 0 31 Yes TS1
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDrop
ped TrackmodState
0x00007FFF813A8A20 0 32768 32764 29228 0 3536 29228 29228 0x00000000 0 0 No
n/a
0x00007FFF91F50080 1 1 1 1 0 0 - - 0x00000000 0 0 No
n/a
0x00007FFF91F5D240 2 8192 8160 96 0 8064 96 96 0x00000000 0 0 No
n/a
0x00007FFF91F6A400 3 8192 8188 152 0 8036 152 152 0x00000000 1468502861 0 No
n/a
0x00007FFF91F775C0 4 25600 25568 6176 0 19392 6176 6176 0x00000020 1630978335 0 No
n/a
0x00007FFF91F84780 5 12800 12768 96 0 12672 96 96 0x00000000 1468504540 0 No
n/a
0x00007FFF91F91940 6 5000 4960 288 0 4672 288 288 0x00000000 1623052440 0 No
n/a
#执行备份解决
[db2inst1@node ~]$ db2 backup db testy tablespace DMS_DATA online to /dev/null
Backup successful. The timestamp for this backup image is : 20210907142049
[db2inst1@node ~]$ db2 connect to testy
Database Connection Information
Database server = DB2/LINUXX8664 10.5.3
SQL authorization ID = DB2INST1
Local database alias = TESTY
[db2inst1@node ~]$ db2 list tablespaces show detail|grep State
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
State = 0x0000
