¾ MQTが存在する場合、デタッチされたパーティションのターゲットと MQTは、SET INTEGRITY実行まで再編成不可
z 表単位の再編成のみ可能。パーティション単位の再編成は不可
¾
一度デタッチしてから再編成を実行し、アタッチすることは可能
z REORG TABLE実行中はテーブル全体がオフライン状態。
¾
ALLOW READ ACCESSオプション、INPLACEオプションの指定はエ ラー（SQL0270N、RC=88)を返す
¾ 個別Index指定、Cleanup OnlyのREORG INDEXはREAD ACCESS 可
z REORG Table失敗後の再実行は、もういちど最初のパーティショ
ンから再実行
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 179
REORGによる回復履歴ファイルのエントリー
回復履歴ファイルのエントリーはパーティション毎に出力される。
z
LOADは、パーティション表が対象でも1エントリーのみ
5パーティションのテーブルに対してREORGを実行した例
z
パーティション毎に1エントリと、表全体に対して1エントリが出力される。
z
各パーティションが順次処理されたことがStart/Endのタイムスタンプからわかる
20060712171450 20060712171217
REORG
20060712171450 20060712171421
REORG PARTN 4
20060712171421 20060712171350
REORG PARTN 3
20060712171350 20060712171319
REORG PARTN 2
20060712171319 20060712171248
REORG PARTN 1
20060712171248 20060712171217
REORG PARTN 0
End Time Start Time
Comment
$ db2 list history reorg all for sample
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- --- ---- --- -- --
---G R 20060712171217 F S0000000.LO---G S0000000.LO---G
---Table: "TUKIV9 "."DP1"
---Comment: REORG PARTN 0
Start Time: 20060712171217 End Time: 20060712171248
Status: A
---EID: 219
Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID -- --- --- ---- --- -- --
---G R 20060712171248 F S0000000.LO---G S0000000.LO---G
---Table: "TUKIV9 "."DP1"
---Comment: REORG PARTN 1
Start Time: 20060712171248 End Time: 20060712171319
Status: A
---EID: 220
＜省略＞
REORGによる回復履歴ファイルのエントリー（続き）
個別Index指定のReorg不可 End TimeとStart Timeが同じ。
前のパーティションの再編成が終わった後に、
次のパーティションの再編成が開始されている。
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 181
REORGの実行例（パーティション表）
>db2look -d smpdb -e -t DP1 CREATE TABLE "TUKIV9 "."DP1" (
"PKEY" INTEGER NOT NULL ,
"COL1" INTEGER ,
"COL2" CHAR(30) )
PARTITION BY RANGE("PKEY")
(PART "PART0" STARTING(MINVALUE) ENDING(200000) IN "TS0", PART "PART1" ENDING(400000) IN "TS1",
PART "PART2" ENDING(600000) IN "TS2", PART "PART3" ENDING(800000) IN "TS3", PART "PART4" ENDING(1000000) IN "TS4");
CREATE UNIQUE INDEX "TUKIV9 "."IDX0_DP1" ON "TUKIV9 "."DP1"
("PKEY" ASC)
NOT PARTITIONED IN ITS0;
CREATE INDEX "TUKIV9 "."IDX5_DP1" ON "TUKIV9 "."DP1"
("PKEY" ASC, "COL1" DESC, "COL2" ASC) NOT PARTITIONED IN ITS2;
>db2look -d smpdb -e -t NP1 CREATE TABLE "TUKIV9 "."NP1" (
"PKEY" INTEGER NOT NULL ,
"COL1" INTEGER ,
"COL2" CHAR(30) ) IN "TS5" ;
CREATE UNIQUE INDEX "TUKIV9 "."IDX0_NP1" ON "TUKIV9 "."NP1"("PKEY" ASC)
REORGの実行例（パーティション表）
> db2 reorg table dp1 allow read access
SQL0270N Function not supported (Reason code = "88"). SQLSTATE=42997
> db2 reorg table dp1 allow no access
DB20000I The REORG command completed successfully.
> db2 reorg table dp1 inplace
SQL0270N Function not supported (Reason code = "88"). SQLSTATE=42997
> db2 reorg indexes all for table dp1 allow write access
SQL0270N Function not supported (Reason code = "88"). SQLSTATE=42997
> db2 reorg indexes all for table dp1 allow read access
SQL0270N Function not supported (Reason code = "88"). SQLSTATE=42997
> db2 reorg indexes all for table dp1 allow no access DB20000I The REORG command completed successfully.
> db2 reorg indexes all for table dp1 allow write access cleanup only DB20000I The REORG command completed successfully.
> db2 reorg indexes all for table dp1 allow read access cleanup only DB20000I The REORG command completed successfully.
> db2 reorg indexes all for table dp1 allow no access cleanup only DB20000I The REORG command completed successfully.
> db2 reorg index idx5_dp1 allow write access DB20000I The REORG command completed successfully.
> db2 reorg index idx5_dp1 allow read access DB20000I The REORG command completed successfully.
> db2 reorg index idx5_dp1 allow no access
DB20000I The REORG command completed successfully.
Reorg Tableは no accessのみ可
Reorg Indexes all も no accessのみ可
cleanup onlyと 個別Indexは
Write/Read/Noすべて可
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 183
REORGの実行例（非パーティション表）
> db2 reorg table np1 allow read access
DB20000I The REORG command completed successfully.
> db2 reorg table np1 allow no access
DB20000I The REORG command completed successfully.
> db2 reorg table np1 inplace
DB20000I The REORG command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
> db2 reorg indexes all for table np1 allow write access DB20000I The REORG command completed successfully.
> db2 reorg indexes all for table np1 allow read access DB20000I The REORG command completed successfully.
> db2 reorg indexes all for table np1 allow no access DB20000I The REORG command completed successfully.
> db2 reorg index idx0_np1 allow write access
SQL0270N Function not supported (Reason code = "89"). SQLSTATE=42997
> db2 reorg index idx0_np1 allow read access
SQL0270N Function not supported (Reason code = "89"). SQLSTATE=42997
> db2 reorg index idx0_np1 allow no access
SQL0270N Function not supported (Reason code = "89"). SQLSTATE=42997
Reorg Indexes allは Write/Read/No すべて可能
個別Index指定のReorg不可 Reorg Tableは Read/No/Inplace すべて可能
MQTが存在するケースでのREORG
> db2 "create summary table DP1_MQT as ( select * from dp1 where col1 < 100) data initially deferred refresh immediate maintained by system"
DB20000I The SQL command completed successfully.
> db2 set integrity for dp1_mqt immediate checked DB20000I The SQL command completed successfully.
> db2 "select count(*) from dp1_mqt"
1
---936
> db2 "select substr(name,1,12) name,status,access_mode,type,const_checked from sysibm.systables where name like 'DP1%' with ur"
NAME STATUS ACCESS_MODE TYPE CONST_CHECKED
--- --- --- ---- ---DP1 N F T YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY DP1_MQT N F S YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY
> db2 "select substr(DATAPARTITIONNAME,1,8) as dpname, smallint(DATAPARTITIONID) as partid, substr(TABNAME,1,12) as tabname, substr(STATUS ,1,2) as status, ACCESS_MODE as
mode ,substr(b.tbspace,1,10) tbspace from syscat.datapartitions a,syscat.tablespaces b where tabname like 'DP1%' and a.tbspaceid=b.tbspaceid"
DPNAME PARTID TABNAME STATUS MODE TBSPACE --- --- -- --- ---- ---PART0 0 DP1 F TS0 PART1 1 DP1 F TS1 PART2 2 DP1 F TS2 PART3 3 DP1 F TS3 PART4 4 DP1 F TS4 PART0 0 DP1_MQT F TS0
DP1表にMQTを作成
パーティション表、MQT表 ともにフルアクセス可能
©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 185