1. はじめに
パーティション表とは v9.1 から使用できるようになった 機能であり、
ひとつの表を複数の区分に分割することで、
大規模な表を容易に管理したり、表データをロールイン・ロールアウトしたり、照会処理を効率化することが可能になります。
今回は パーティション表の概要 と 作成方法 についてを まとめます。
2. 準備
パーティション表を作成するにあたって、
Db2環境 と 必要なデータベース・オブジェクトを 準備します。
2-1. Db2環境 準備
Db2環境を準備します。
バージョンは v11.5 を使用します。
Db2の導入方法については、以下リンク先の記事に記載しています。
・Qiita: 【備忘録】Db2 インストール手順まとめ（Linux）
https://qiita.com/Haruka-Ogawa/items/9576399209b32f3016ea
・Qiita: 【備忘録】Docker版 Db2環境 取得手順
https://qiita.com/Haruka-Ogawa/items/0a4696ded4fa40e5e983
ここでは Dockerで用意したDb2環境を使用します。
2-2. オブジェクト作成
必要なデータベース オブジェクトを作成します。
① データベース作成
データベース SAMPDB を作成します。
実行例は以下の通りです。
$ db2 create db SAMPDB
DB20000I The CREATE DATABASE command completed successfully.
② 表スペース作成
まずは 作成したデータベース SAMPDB に接続します。
実行例は以下の通りです。
$ db2 connect to SAMPDB
Database Connection Information
Database server = DB2/LINUXX8664 11.5.5.0
SQL authorization ID = DB2INST1
Local database alias = SAMPDB
表スペース TS1, TS2, TS3, TS4, TS5, TS6, TS7, TS8 作成します。
実行例は以下の通りです。
$ db2 "CREATE TABLESPACE TS1"
DB20000I The SQL command completed successfully.
$ db2 "CREATE TABLESPACE TS2"
DB20000I The SQL command completed successfully.
$ db2 "CREATE TABLESPACE TS3"
DB20000I The SQL command completed successfully.
$ db2 "CREATE TABLESPACE TS4"
DB20000I The SQL command completed successfully.
$ db2 "CREATE TABLESPACE TS5"
DB20000I The SQL command completed successfully.
$ db2 "CREATE TABLESPACE TS6"
DB20000I The SQL command completed successfully.
$ db2 "CREATE TABLESPACE TS7"
DB20000I The SQL command completed successfully.
$ db2 "CREATE TABLESPACE TS8"
DB20000I The SQL command completed successfully.
・IBM Docs：CREATE TABLESPACE ステートメント
https://www.ibm.com/docs/ja/db2/11.5?topic=statements-create-tablespace
3. 作成方法
準備したDb2環境の データベース SAMPDB 上で、パーティション表を作成します。
3-1. 概要
基本的な パーティション表の作成方法について記載します。
ここでは、テーブルTBL1を作成し、
DATE型の列COL2を パーティション・キーとして、
2020-01-01~2021-12-31 の２年間を 3ヶ月ごとに分割し、
8つのパーティションを作成します。
また、各パーティションは 表スペース TS1, TS2, TS3, TS4 に 格納します。
図で示すと以下の通りです。
また、CREATE TABLEコマンドでパーティション表を作成する際、
STARTING・ENDING節で 各パーティションの範囲（開始値・終了値）を指定するか、
EVERY節で 各パーティションの範囲の幅を指定するか
のいずれかの方法をとります。
※ EVERY節を使用する場合は 各パーティションの範囲の幅は一律となります。
3-2項 にて、
a. 範囲（下限値・上限値）で指定する場合
b. 範囲の幅で指定する場合
それぞれでのパーティション表の作成方法を記載します。
3-2. 作成方法
a. 範囲（下限値・上限値）で指定する場合
① 作成
パーティション表 TBL1 を作成する方法を示します。
PARTITION BY RANGE節で 列COL2を パーティション・キーに指定し、
STARTING・ENDING節で、各パーティションの開始値・終了値を指定し、列COL2で 3ヶ月ごとにパーティションが分割するようにします。
また、IN節で 各パーティションを格納する表スペース(TS1, TS2, TS3, TS4)を指定します。
コマンドは以下の通りです。
CREATE TABLE TBL1 ( COL1 INT, COL2 DATE )
PARTITION BY RANGE ( COL2 )
( STARTING '2020-01-01'
ENDING '2020-03-31' IN TS1,
ENDING '2020-06-30' IN TS2,
ENDING '2020-09-30' IN TS3,
ENDING '2020-12-31' IN TS4,
ENDING '2021-03-31' IN TS1,
ENDING '2021-06-30' IN TS2,
ENDING '2021-09-30' IN TS3,
ENDING '2021-12-31' IN TS4
)
実行例は以下の通りです。
$ db2 "
> CREATE TABLE TBL1 ( COL1 INT, COL2 DATE )
> PARTITION BY RANGE ( COL2 )
> ( STARTING '2020-01-01'
> ENDING '2020-03-31' IN TS1,
> ENDING '2020-06-30' IN TS2,
> ENDING '2020-09-30' IN TS3,
> ENDING '2020-12-31' IN TS4,
> ENDING '2021-03-31' IN TS1,
> ENDING '2021-06-30' IN TS2,
> ENDING '2021-09-30' IN TS3,
> ENDING '2021-12-31' IN TS4
> )
> "
DB20000I The SQL command completed successfully.
パーティション表 TBL1が作成できました。
・IBM Docs：CREATE TABLE ステートメント
https://www.ibm.com/docs/ja/db2/11.5?topic=statements-create-table
② 確認
作成した パーティション表 TBL1について確認します。
確認には DESCRIBEコマンド を使用します。
コマンドは以下の通りです。
DESCRIBE DATA PARTITIONS FOR TABLE TBL1 SHOW DETAIL
実行例は以下の通りです。
$ db2 DESCRIBE DATA PARTITIONS FOR TABLE TBL1 SHOW DETAIL
PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode
Status
----------- ------------------------------- ----------- ----------- ------------ ----------- - ------
0 PART0 4 4 4 4 F
1 PART1 5 4 5 5 F
2 PART2 6 4 6 6 F
3 PART3 7 4 7 7 F
4 PART4 4 5 4 4 F
5 PART5 5 5 5 5 F
6 PART6 6 5 6 6 F
7 PART7 7 5 7 7 F
8 record(s) selected.
PartitionId Inclusive (y/n) Inclusive (y/n)
Low Value High Value
----------- - ------------------------------- - -------------------------------
0 Y '2020-01-01' Y '2020-03-31'
1 N '2020-03-31' Y '2020-06-30'
2 N '2020-06-30' Y '2020-09-30'
3 N '2020-09-30' Y '2020-12-31'
4 N '2020-12-31' Y '2021-03-31'
5 N '2021-03-31' Y '2021-06-30'
6 N '2021-06-30' Y '2021-09-30'
7 N '2021-09-30' Y '2021-12-31'
8 record(s) selected.
テーブルTBL1が3ヶ月ごとのパーティション（PART0, PART1, PART2, PART3, PART4, PART5, PART6, PART7）に分割されていて、
それぞれ別の表スペースに格納されていることがわかります。
・IBM Docs：DESCRIBE コマンド
https://www.ibm.com/docs/ja/db2/11.5?topic=commands-describe
補足
- パーティション名
CREATE TABLEコマンドにて、各パーティションに 名前を指定することが可能です。
パーティション名はPARTITIONオプションにて指定します。
パーティション名の指定がない時は、自動的に名前（PART0, PART1, PART2, …）がつけられます。
コマンド例は以下の通りです。
CREATE TABLE TBL1(COL1 INT, COL2 DATE)
PARTITION BY RANGE(COL2)
( PARTITION P20201Q STARTING '2020-01-01' ENDING '2020-03-31' IN TS1,
PARTITION P20202Q ENDING '2020-06-30' IN TS2,
PARTITION P20203Q ENDING '2020-09-30' IN TS3,
PARTITION P20204Q ENDING '2020-12-31' IN TS4,
PARTITION P20211Q ENDING '2021-03-31' IN TS1,
PARTITION P20212Q ENDING '2021-06-30' IN TS2,
PARTITION P20213Q ENDING '2021-09-30' IN TS3,
PARTITION P20214Q ENDING '2021-12-31' IN TS4
)
・IBM Docs：CREATE TABLE ステートメント
https://www.ibm.com/docs/ja/db2/11.5?topic=statements-create-table
- 境界値
CREATE TABLEコマンドのSTARTING・ENDING節での デフォルトはINCLUSIVEであり、
指定した範囲値をパーティションに含めるようになっています。
指定した範囲値をパーティションに含めない場合は、EXCLUSIVEを指定します。
コマンド例は以下の通りです。
CREATE TABLE TBL1(COL1 INT, COL2 DATE)
PARTITION BY RANGE(COL2)
( STARTING '2020-01-01'
ENDING '2020-04-01' EXCLUSIVE IN TS1,
ENDING '2020-07-01' EXCLUSIVE IN TS2,
ENDING '2020-10-01' EXCLUSIVE IN TS3,
ENDING '2021-01-01' EXCLUSIVE IN TS4,
ENDING '2021-04-01' EXCLUSIVE IN TS1,
ENDING '2021-07-01' EXCLUSIVE IN TS2,
ENDING '2021-10-01' EXCLUSIVE IN TS3,
ENDING '2022-01-01' EXCLUSIVE IN TS4
)
・IBM Docs：CREATE TABLE ステートメント
https://www.ibm.com/docs/ja/db2/11.5?topic=statements-create-table
b. 範囲の幅で指定する場合
① 作成
パーティション表 TBL1 を作成する方法を示します。
PARTITION BY RANGE節で 列COL2を パーティション・キーに指定し、
EVERY節で、パーティション内での範囲の幅を3 Monthに設定し、
列COL2で 3ヶ月ごとにパーティションが分割するようにします。
また、IN節で 各パーティションを格納する表スペース(TS1, TS2, TS3, TS4)を指定します。
各パーティションは、順番に 表スペース TS1, TS2, TS3, TS4へ割り当てられます。
コマンドは以下の通りです。
CREATE TABLE TBL1(COL1 INT, COL2 DATE)
IN TS1, TS2, TS3, TS4
PARTITION BY RANGE (COL2)
( STARTING '2020-01-01'
ENDING '2021-12-31'
EVERY (3 Month)
)
実行例は以下の通りです。
$ db2 "
> CREATE TABLE TBL1(COL1 INT, COL2 DATE)
> IN TS1, TS2, TS3, TS4
> PARTITION BY RANGE (COL2)
> ( STARTING '2020-01-01'
> ENDING '2021-12-31'
> EVERY (3 Month)
> )
> "
DB20000I The SQL command completed successfully.
パーティション表 TBL1が作成できました。
・IBM Docs：CREATE TABLE ステートメント
https://www.ibm.com/docs/ja/db2/11.5?topic=statements-create-table
② 確認
作成した パーティション表 TBL1について確認します。
確認には DESCRIBEコマンド を使用します。
コマンドは以下の通りです。
DESCRIBE DATA PARTITIONS FOR TABLE TBL1 SHOW DETAIL
実行例は以下の通りです。
$ db2 DESCRIBE DATA PARTITIONS FOR TABLE TBL1 SHOW DETAIL
PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode
Status
----------- ------------------------------- ----------- ----------- ------------ ----------- - ------
0 PART0 4 4 4 4 F
1 PART1 5 4 5 5 F
2 PART2 6 4 6 6 F
3 PART3 7 4 7 7 F
4 PART4 4 5 4 4 F
5 PART5 5 5 5 5 F
6 PART6 6 5 6 6 F
7 PART7 7 5 7 7 F
8 record(s) selected.
PartitionId Inclusive (y/n) Inclusive (y/n)
Low Value High Value
----------- - ------------------------------- - -------------------------------
0 Y '2020-01-01' N '2020-04-01'
1 Y '2020-04-01' N '2020-07-01'
2 Y '2020-07-01' N '2020-10-01'
3 Y '2020-10-01' N '2021-01-01'
4 Y '2021-01-01' N '2021-04-01'
5 Y '2021-04-01' N '2021-07-01'
6 Y '2021-07-01' N '2021-10-01'
7 Y '2021-10-01' Y '2021-12-31'
8 record(s) selected.
テーブルTBL1が3ヶ月ごとのパーティション（PART0, PART1, PART2, PART3, PART4, PART5, PART6, PART7）に分割されていて、
それぞれ別の表スペースに格納されていることがわかります。
・IBM Docs：DESCRIBE コマンド
https://www.ibm.com/docs/ja/db2/11.5?topic=commands-describe
4. 索引
パーティション表における 索引について 記載します。
Db2では v9.7 からパーティション索引 が使用できるようになり、
パーティション表においては、パーティション索引 と 非パーティション索引 の2つが使用できます。
パーティション索引 ではパーティションごとに索引を分割し、
非パーティション索引 では 索引が分割されず、表全体に対し ひとつの索引で構成されます。
・IBM Docs：パーティション表の索引
https://www.ibm.com/docs/ja/db2/11.5?topic=indexes-partitioned-tables
4-1. パーティション索引
① テーブル作成
テーブルTBL1 を作成します。
テーブル作成にはCREATE TABLEコマンドを使用します。
PARTITION BY RANGE節で 列COL2を パーティション・キーに指定し、
STARTING・ENDING節で、各パーティションの開始値・終了値を指定し、列COL2で 3ヶ月ごとにパーティションが分割するようにします。
IN節で 各パーティションを格納する表スペース(TS1, TS2, TS3, TS4)を指定します。
INDEX IN節にて、パーティション表TBL1のパーティション索引を保管する表スペース(TS5, TS6, TS7, TS8)を指定します。
コマンドは以下の通りです。
CREATE TABLE TBL1(COL1 INT, COL2 DATE)
IN TS1,TS2, TS3, TS4
PARTITION BY RANGE(COL2)
( STARTING '2020-01-01'
ENDING '2020-03-31' INDEX IN TS5,
ENDING '2020-06-30' INDEX IN TS6,
ENDING '2020-09-30' INDEX IN TS7,
ENDING '2020-12-31' INDEX IN TS8
)
実行例は以下の通りです。
$ db2 "
> CREATE TABLE TBL1(COL1 INT, COL2 DATE)
> IN TS1,TS2, TS3, TS4
> PARTITION BY RANGE(COL2)
> (STARTING '2020-01-01'
> ENDING '2020-03-31' INDEX IN TS5,
> ENDING '2020-06-30' INDEX IN TS6,
> ENDING '2020-09-30' INDEX IN TS7,
> ENDING '2020-12-31' INDEX IN TS8
> )
> "
DB20000I The SQL command completed successfully.
パーティション表 TBL1が作成できました。
・IBM Docs：CREATE TABLE ステートメント
https://www.ibm.com/docs/ja/db2/11.5?topic=statements-create-table
② 索引作成
パーティション索引 IDX1 を作成します。
作成にはCREATE INDEXコマンドを使用します。
コマンドは以下の通りです。
CREATE INDEX IDX1 ON TBL1 (COL2)
実行例は以下の通りです。
$ db2 "CREATE INDEX IDX1 ON TBL1 (COL2)"
DB20000I The SQL command completed successfully.
パーティション索引IDX1が作成できました。
・IBM Docs：CREATE INDEX ステートメント
https://www.ibm.com/docs/ja/db2/11.5?topic=statements-create-index
③ 確認
- パーティション表
作成した パーティション表TBL1について確認します。
確認には DESCRIBEコマンド を使用します。
コマンドは以下の通りです。
DESCRIBE DATA PARTITIONS FOR TABLE TBL1 SHOW DETAIL
実行例は以下の通りです。
$ db2 DESCRIBE DATA PARTITIONS FOR TABLE TBL1 SHOW DETAIL
PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode
Status
----------- ------------------------------- ----------- ----------- ------------ ----------- - ------
0 PART0 4 4 8 4 F
1 PART1 5 4 9 5 F
2 PART2 6 4 10 6 F
3 PART3 7 4 11 7 F
4 record(s) selected.
PartitionId Inclusive (y/n) Inclusive (y/n)
Low Value High Value
----------- - ------------------------------- - -------------------------------
0 Y '2020-01-01' Y '2020-03-31'
1 N '2020-03-31' Y '2020-06-30'
2 N '2020-06-30' Y '2020-09-30'
3 N '2020-09-30' Y '2020-12-31'
4 record(s) selected.
テーブルTBL1が3ヶ月ごとのパーティション（PART0, PART1, PART2, PART3）に分割されていて、
それぞれ別の表スペースに格納されていることがわかります。
・IBM Docs：DESCRIBE コマンド
https://www.ibm.com/docs/ja/db2/11.5?topic=commands-describe
- 索引確認
パーティション表TBL1の索引IDX1を確認します。
確認には DESCRIBEコマンド を使用します。
コマンドは以下の通りです。
DESCRIBE INDEXES FOR TABLE TBL1 SHOW DETAIL
実行例は以下の通りです。
$ db2 DESCRIBE INDEXES FOR TABLE TBL1 SHOW DETAIL
Index Index Unique Number of Index Index Null Index Data Max Varchar Xml BUSINESS_TIME Column
schema name rule columns type partitioning keys ID type Hashed Length pattern WITHOUT OVERLAPS names
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------- -------------- --------------------------- -------------- ------ --------------- -------------------------------------------------------------------------------------------------------------------------------- ------- --------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DB2INST1 IDX1 D 1 RELATIONAL DATA P Y 1 - - - - NO +COL2
1 record(s) selected.
パーティション索引IDX1が無事作成されたことが確認できました。
・IBM Docs：DESCRIBE コマンド
https://www.ibm.com/docs/ja/db2/11.5?topic=commands-describe
4-2. 非パーティション索引
① テーブル作成
テーブルTBL1 を作成します。
テーブル作成にはCREATE TABLEコマンドを使用します。
PARTITION BY RANGE節で 列COL2を パーティション・キーに指定し、
STARTING・ENDING節で、各パーティションの開始値・終了値を指定し、列COL2で 3ヶ月ごとにパーティションが分割するようにします。
IN節で 各パーティションを格納する表スペース(TS1, TS2, TS3, TS4)を指定します。
INDEX IN節にて、パーティション表TBL1の 非パーティション索引を保管する表スペースTS5を指定します。
コマンドは以下の通りです。
CREATE TABLE TBL1(COL1 INT, COL2 DATE)
IN TS1, TS2, TS3, TS4
INDEX IN TS5
PARTITION BY RANGE(COL2)
( STARTING '2020-01-01'
ENDING '2020-03-31',
ENDING '2020-06-30',
ENDING '2020-09-30',
ENDING '2020-12-31'
)
※ EVERY節を使う場合は、コマンドは以下の通りです。
CREATE TABLE TBL1(COL1 INT, COL2 DATE)
IN TS1, TS2, TS3, TS4
INDEX IN TS5
PARTITION BY RANGE(COL2)
( STARTING '2020-01-01'
ENDING AT '2020-12-31'
EVERY (3 Month)
)
実行例は以下の通りです。
$ db2 "
> CREATE TABLE TBL1(COL1 INT, COL2 DATE)
> IN TS1, TS2, TS3, TS4
> INDEX IN TS5
> PARTITION BY RANGE(COL2)
> ( STARTING '2020-01-01'
> ENDING '2020-03-31',
> ENDING '2020-06-30',
> ENDING '2020-09-30',
> ENDING '2020-12-31'
> )
> "
② 索引作成
非パーティション索引 IDX1 を作成します。
作成にはCREATE INDEXコマンドを使用します。
非パーティション索引を作成する場合、NOT PARTITIONED 節を追加します。
コマンドは以下の通りです。
CREATE INDEX IDX1 ON TBL1 (COL2) NOT PARTITIONED
実行例は以下の通りです。
$ db2 "CREATE INDEX IDX1 ON TBL1 (COL2) NOT PARTITIONED"
DB20000I The SQL command completed successfully.
非パーティション索引 IDX1 が作成できました。
・IBM Docs：CREATE INDEX ステートメント
https://www.ibm.com/docs/ja/db2/11.5?topic=statements-create-index
③ 確認
- パーティション表
作成した パーティション表TBL1について確認します。
確認には DESCRIBEコマンド を使用します。
コマンドは以下の通りです。
DESCRIBE DATA PARTITIONS FOR TABLE TBL1 SHOW DETAIL
実行例は以下の通りです。
$ db2 DESCRIBE DATA PARTITIONS FOR TABLE TBL1 SHOW DETAIL
PartitionId PartitionName TableSpId PartObjId IndexTblSpId LongTblSpId AccessMode
Status
----------- ------------------------------- ----------- ----------- ------------ ----------- - ------
0 PART0 4 4 4 4 F
1 PART1 5 4 5 5 F
2 PART2 6 4 6 6 F
3 PART3 7 4 7 7 F
4 record(s) selected.
PartitionId Inclusive (y/n) Inclusive (y/n)
Low Value High Value
----------- - ------------------------------- - -------------------------------
0 Y '2020-01-01' Y '2020-03-31'
1 N '2020-03-31' Y '2020-06-30'
2 N '2020-06-30' Y '2020-09-30'
3 N '2020-09-30' Y '2020-12-31'
4 record(s) selected.
テーブルTBL1が3ヶ月ごとのパーティション（PART0, PART1, PART2, PART3）に分割されていて、
それぞれ別の表スペースに格納されていることがわかります。
・IBM Docs：DESCRIBE コマンド
https://www.ibm.com/docs/ja/db2/11.5?topic=commands-describe
- 索引
パーティション表TBL1の索引IDX1を確認します。
確認には DESCRIBEコマンド を使用します。
コマンドは以下の通りです。
DESCRIBE INDEXES FOR TABLE TBL1 SHOW DETAIL
実行例は以下の通りです。
$ db2 DESCRIBE INDEXES FOR TABLE TBL1 SHOW DETAIL
Index Index Unique Number of Index Index Null Index Data Max Varchar Xml BUSINESS_TIME Column
schema name rule columns type partitioning keys ID type Hashed Length pattern WITHOUT OVERLAPS names
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------- -------------- --------------------------- -------------- ------ --------------- -------------------------------------------------------------------------------------------------------------------------------- ------- --------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DB2INST1 IDX1 D 1 RELATIONAL DATA N Y 1 - - - - NO +COL2
1 record(s) selected.
非パーティション索引IDX1が無事作成されたことが確認できました。
・IBM Docs：DESCRIBE コマンド
https://www.ibm.com/docs/ja/db2/11.5?topic=commands-describe
5. おわりに
今回は Db2におけるパーティション表 作成方法についてまとめました。
パーティション表では 特有の操作として デタッチ・アタッチ操作があり、
古くなった区分をデタッチで 切り離したり、
逆に新しい区分をアタッチで 取り付ける ということが可能になります。
これにより 素早いデータの追加・削除ができ、表の管理が容易になることが期待されます。
アタッチ・デタッチ操作については、以下リンク先の記事をご参照ください。
・Qiita：【備忘録】Db2 パーティション表 デタッチ・アタッチ操作
https://qiita.com/Haruka-Ogawa/items/fe13f0be983e2f10b2d3
参考情報
・IBM Docs：パーティション表
https://www.ibm.com/docs/ja/db2/11.5?topic=schemes-partitioned-tables
・IBM Docs：パーティション表の索引
https://www.ibm.com/docs/ja/db2/11.5?topic=indexes-partitioned-tables