ユーザー

SQL INSERT, UPDATE, DELETE, LOAD

変更

ユーザーによるメンテナンス

REFRESHコマンド

または

SET INTEGRITYコマンド REFRESH…

システムによるメンテナンス

©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 91

MQTとパーティション表

基礎表

(非パーティション)

MQT

(非パーティション)

ATTATCH DETACH

Staging表

(非パーティション)

基礎表

(非パーティション) 基礎表

(パーティション)

基礎表

(パーティション)

MQT

(パーティション)

● MQTをパーティション表にした場合、

アタッチは不可（SQLCODE=-270 RC=106）

● ステージング表はパーティション表にて作成不可（SQLCODE=-270 RC=87）

Staging表

(パーティション)

MQT使用時の操作（基礎表がパーティション表）

Refresh Immediate MQT

z 基礎表からのデタッチがコミットされた時点で：

MQTはSET INTEGRITY PENDING状態

基礎表は更新不可

デタッチのコミット後、MQTに対するSET INTEGRITYを実行する

z 基礎表へのアタッチ時点で：

特にMQTに変更なし

基礎表およびMQTへSET INTEGRITY実行し、追加データを反映

Refresh Deferred MQT+ステージング表

z 基礎表からのデタッチ時点で：

ステージング表は、SET INTEGRITY PENDING状態

基礎表への更新可能

デタッチのコミット後、MQTおよびステージング表へのSET INTEGRITYを実行

z 基礎表へのアタッチ時点で：

特にMQTに変更なし

基礎表、ステージング表、およびMQTへSET INTEGRITYを実行し、追加デー

タを反映

©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 93

MQT使用時の操作（MQTがパーティション表）

MQTの高速リフレッシュ

z MQTに対してパーティションのデタッチ/アタッチを行う

MQTへ反映される行数が大量の場合に高速処理可能

z MQTへのSET INTEGRITY

基礎表とMQTの整合性をユーザー責任にて確保し、UNCHECKEDオプションでSET INTEGRITYを実行する

MQTへのパーティションのアタッチは不可

z 一旦MQTから通常の表に変更してからアタッチを行い、再びMQT に戻す

ブランク・ページです。

©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 95

基礎表からのデタッチ①

1) パーティション表、MQT表、索引の作成

create table test4 (c1 int not null, c2 int, primary key (c1)) in tbs1 partition by range (c2) (starting 1 ending 100 every 25)

create summary table test4_mqt as (select * from test4 where c1 > 50) data initially deferred refresh immediate maintained by system

set integrity for test4_mqt immediate checked

insert into test4 values(1,1),(26,26),(51,51),(52,52),(76,76)

select * from test4_mqt C1 C2

---51 ---51 52 52 76 76 3 レコードが選択されました。

パーティション表に対して、MQTを作成 REFRESH IMMEDIATE で作成する

ベース表には４レコード MQT表には2レコード

基礎表からのデタッチ②

１）パーティション表、MQT表、索引の作成つづき

select substr(name,1,8) as name,type,status,access_mode,const_checked from sysibm.systables where name like 'TEST4%'

NAME TYPE STATUS ACCESS_MODE CONST_CHECKED

--- ---- --- --- ---TEST4 T N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY TEST4_MQ S N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY

2 レコードが選択されました。

select substr(DATAPARTITIONNAME,1,8) as dpname, smallint(DATAPARTITIONID) as partid, substr(TABNAME,1,8) as tabname, substr(STATUS ,1,2) as status, ACCESS_MODE from syscat.datapartitions where tabname like 'TEST4%'

DPNAME PARTID TABNAME STATUS ACCESS_MODE --- --- --- --- ---PART0 0 TEST4 F

PART1 1 TEST4 F PART2 2 TEST4 F PART3 3 TEST4 F PART0 0 TEST4_MQ F

©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 97 ２）パーティションのデタッチ実行

alter table test4 detach partition part3 into test4_p

SQL3601W ステートメントにより 1 つ以上の表が自動的に SET INTEGRITYペンディング状態になりました。 SQLSTATE=01586

select substr(name,1,8) as name,type, status,access_mode,const_checked from sysibm.systables where name like 'TEST4%' NAME TYPE STATUS ACCESS_MODE CONST_CHECKED

--- ---- --- --- ---TEST4 T N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY TEST4_MQ S C N YYYYNYYYYYYYYYYYYYYYYYYYYYYYYYYY TEST4_P L N N YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY 3 レコードが選択されました。

DPNAME PARTID TABNAME STATUS ACCESS_MODE --- --- --- --- ---PART0 0 TEST4 F

PART1 1 TEST4 F PART2 2 TEST4 F PART3 3 TEST4 D N PART0 0 TEST4_MQ N PART0 0 TEST4_P N

基礎表からのデタッチ③

デタッチ実行後、SQL3601発生

MQTがCHECK PENDING状態になっている REFRESH IMMEDIATEであるため、パーティションのデタッチ分をMQTにも反映しなければならない

デタッチされたパーティション以外に、

MQT表と、ターゲット表がNo Accessになっている

基礎表からのデタッチ④

３）SET INTEGRITY実行前の各表へのアクセス状況を確認する select * from test4_mqt

C1 C2

---SQL0668N 操作は、理由コード "1" のため、表 "SANTAV9.TEST4_MQT"に対して許可されません。 SQLSTATE=57016$

select * from test4_p

SQL20285N 表 "SANTAV9.TEST4_P"に切り離された従属物がある場合は、ステートメントまたはコマンドの実行が許可されません。

SQLSTATE=55057 select * from test4 C1 C2

---1 ---1 26 26 51 51 52 52 4 レコードが選択されました。

delete from test4 where c1=1

DB21034E コマンドが、有効なコマンド行プロセッサー・コマンドでないため、 SQLステートメントとして処理されました。 SQL

処理中に、そのコマンドが返されました。SQL0668N 操作は、理由コード "1" のため、表 "SANTAV9.TEST4_MQT"に対して許可されません。 SQLSTATE=57016

MQT表と、ターゲット表へのアクセスは出来ない

パーティション表は、照会は可能だが更新は不可

©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 99

基礎表からのデタッチ⑤

３）SET INTEGRITY実行前の各表へのアクセス状況を確認するつづき

select substr(bname,1,8) as bname, btype, substr(dname,1,8) as dname, dtype from sysibm.sysdependencies where bname like 'TEST4%'

BNAME BTYPE DNAME DTYPE --- --- ---TEST4_P L TEST4_MQ S

1 レコードが選択されました。

４）表の読み書き行数確認

select substr(TABNAME,1,18),DATA_PARTITION_ID,ROWS_READ,ROWS_WRITTEN from sysibmadm.snaptab where tabname like 'TEST4%' order by 1,2 1 DATA_PARTITION_ID ROWS_READ ROWS_WRITTEN --- --- ---TEST4 0 0 2 TEST4 1 0 1 TEST4 2 0 2 TEST4 3 0 1 TEST4_MQT - 0 3

5 record(s) selected.

表スナップショットのためのビュー

（管理機能の章参照）

TEST4から切り離されたTEST4_P表とTEST4 を基礎表としたMQTであるTEST4_MQの間には依存関係がある（TEST4_Pの内容にて TEST4_MQTは更新される必要がある）

L: デタッチされた表 S：MQT

基礎表からのデタッチ⑥

４）SET INTEGRITYを実行する

set integrity for test4_mqt immediate checked incremental DB20000I The SQL command completed successfully.

5 record(s) selected.

select * from test4_mqt C1 C2

---51 ---51 52 52 2 record(s) selected.

MQTには差分のみ反映

©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 101

基礎表からのデタッチ（ステージング表あり）①

create table test4 (c1 int not null, c2 int, primary key (c1)) partition by range (c2) (starting 1 ending 100 every 25) DB20000I The SQL command completed successfully.

create summary table test4_mqt as (select * from test4 where c1 > 50) data initially deferred refresh deferred maintained by system DB20000I The SQL command completed successfully.

create table test4_st for test4_mqt propagate immediate DB20000I The SQL command completed successfully.

set integrity for test4_st, test4_mqt immediate checked DB20000I The SQL command completed successfully.

insert into test4 values(1,1),(26,26),(51,51),(76,76) DB20000I The SQL command completed successfully.

select * from test4_mqt C1 C2

---0 record(s) selected.

パーティション表(基礎表)の作成

MQTの作成

ステージング表の作成

基礎表へのデータ挿入

MQTには未反映

基礎表からのデタッチ（ステージング表あり）②

select C1,C2,OPERATIONTYPE from test4_st C1 C2 OPERATIONTYPE --- ---

---76 ---76 1

51 51 1

2 record(s) selected.

set integrity for test4_mqt immediate checked incremental DB20000I The SQL command completed successfully.

select * from test4_mqt C1 C2

---76 ---76 51 51

select substr(name,1,8) as name,type ,status,access_mode,const_checked from sysibm.systables where name like 'TEST4%' NAME TYPE STATUS ACCESS_MODE CONST_CHECKED

--- ---- --- --- ---TEST4 T N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY TEST4_MQ S N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY TEST4_ST T N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY

ステージング表には更新反映

すべての表はフルアクセス MQTにSET INTEGRITY実行

MQTに更新反映

©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 103

基礎表からのデタッチ（ステージング表あり）③

DPNAME PARTID TABNAME STATUS ACCESS_MODE --- --- --- --- ---PART0 0 TEST4 F

PART1 1 TEST4 F PART2 2 TEST4 F PART3 3 TEST4 F PART0 0 TEST4_MQ F PART0 0 TEST4_ST F

6 record(s) selected.

alter table test4 detach partition part3 into test4_p

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

SQLSTATE=01586

select substr(name,1,8) as name,type ,status,access_mode,const_checked from sysibm.systables where name like 'TEST4%' NAME TYPE STATUS ACCESS_MODE CONST_CHECKED

--- ---- --- --- ---TEST4 T N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY TEST4_MQ S N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY TEST4_P L N N YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY TEST4_ST T C N YYYYYYNYYYYYYYYYYYYYYYYYYYYYYYYY

4 record(s) selected.

すべてのパーティションはフルアクセス

4番目のパーティションをデタッチ

（TEST4_P表へ切り離し）

切り離されたTEST4_P表とステージング表はアクセス不可

基礎表からのデタッチ（ステージング表あり）④

DPNAME PARTID TABNAME STATUS ACCESS_MODE --- --- --- --- ---PART0 0 TEST4 F

PART1 1 TEST4 F PART2 2 TEST4 F PART3 3 TEST4 D N PART0 0 TEST4_MQ F PART0 0 TEST4_ST N PART0 0 TEST4_P N

7 record(s) selected.

select * from test4_mqt C1 C2

---0 record(s) selected.

select * from test4_p

SQL20285N The statement or command is not allowed while table "SHIRAV9.TEST4_P" has detached dependents.

SQLSTATE=55057

切り離された4番目のパーティションもアクセス不可

切り離されたTEST4_P表もアクセス不可

©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 105

基礎表からのデタッチ（ステージング表あり）⑤

select C1,C2,OPERATIONTYPE from test4_st C1 C2 OPERATIONTYPE --- ---

---SQL0668N Operation not allowed for reason code "1" on table "SHIRAV9.TEST4_ST". SQLSTATE=57016 select * from test4

C1 C2

---1 ---1 51 51 26 26 3 record(s) selected.

delete from test4 where c1=1

DB20000I The SQL command completed successfully.

ステージング表もアクセス不可

切り離された4番目のパーティション以外のパーティ

ションのデータのみ可視

基礎表への更新可能

基礎表からのデタッチ（ステージング表あり）⑥

select * from test4 C1 C2

---51 ---51 26 26 2 record(s) selected.

select substr(bname,1,8) as bname, btype, substr(dname,1,8) as dname, dtype from sysibm.sysdependencies where bname like 'TEST4%'

BNAME BTYPE DNAME DTYPE --- --- ---TEST4_P L TEST4_ST T

1 record(s) selected.

set integrity for test4_st, test4_mqt immediate checked DB20000I The SQL command completed successfully.

ステージング表、MQTにそれぞれ Set Integrity実行

基礎表からのデタッチ（ステージング表あり）⑦

select substr(bname,1,8) as bname, btype, substr(dname,1,8) as dname, dtype from sysibm.sysdependencies where bname like 'TEST4%'

BNAME BTYPE DNAME DTYPE --- ---

---0 record(s) selected.

select * from test4_mqt C1 C2

---51 ---51 1 record(s) selected.

MQTに変更反映

基礎表へのアタッチ①

create table test4 (c1 int not null, c2 int, primary key (c1)) partition by range (c2) (starting 1 ending 100 every 25) DB20000I The SQL command completed successfully.

create summary table test4_mqt as (select * from test4 where c1 > 50) data initially deferred refresh immediate maintained by system

DB20000I The SQL command completed successfully.

set integrity for test4_mqt immediate checked DB20000I The SQL command completed successfully.

insert into test4 values(1,1),(26,26),(51,51),(76,76) DB20000I The SQL command completed successfully.

select * from test4_mqt C1 C2

---76 ---76 51 51 2 record(s) selected.

基礎表の作成

MQTの作成

基礎表へのアタッチ②

select substr(name,1,8) as name,type, status,access_mode,const_checked from sysibm.systables where name like 'TEST4%'

NAME TYPE STATUS ACCESS_MODE CONST_CHECKED

--- ---- --- --- ---TEST4 T N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY TEST4_MQ S N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY TEST4_P T N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY

3 record(s) selected.

select substr(DATAPARTITIONNAME,1,8) as dpname, smallint(DATAPARTITIONID) as partid, substr(TABNAME,1,8) as tabname, substr(STATUS ,1,2) as status, ACCESS_MODE

from syscat.datapartitions where tabname like 'TEST4%' DPNAME PARTID TABNAME STATUS ACCESS_MODE

--- --- --- --- ---PART0 0 TEST4 F

PART1 1 TEST4 F PART2 2 TEST4 F PART3 3 TEST4 F PART0 0 TEST4_MQ F PART0 0 TEST4_P F

6 record(s) selected.

すべての表、パーティションはフルアクセス可能

基礎表へのアタッチ③

create table test4_p like test4

DB20000I The SQL command completed successfully.

insert into test4_p values (25,101),(75,102) DB20000I The SQL command completed successfully.

alter table test4 attach partition starting 101 ending 150 from test4_p SQL3601W The statement caused one or more tables to automatically be placed in the Set Integrity Pending state. SQLSTATE=01586

select substr(name,1,8) as name ,type, status, access_mode, const_checked from sysibm.systables where name like 'TEST4%'

NAME TYPE STATUS ACCESS_MODE CONST_CHECKED

--- ---- --- --- ---TEST4 T C F YYYYYYYNYYYYYYYYYYYYYYYYYYYYYYYY TEST4_MQ S N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY

2 record(s) selected.

基礎表に新規パーティション追加

→SET INTEGRITYペンディング

基礎表へのアタッチ④

select substr(DATAPARTITIONNAME,1,8) as dpname, smallint(DATAPARTITIONID) as partid, substr(TABNAME,1,8) as tabname, substr(STATUS ,1,2) as status, ACCESS_MODE

from syscat.datapartitions where tabname like 'TEST4%' DPNAME PARTID TABNAME STATUS ACCESS_MODE

--- --- --- --- ---PART0 0 TEST4 F

PART1 1 TEST4 F PART2 2 TEST4 F PART3 3 TEST4 F PART0 0 TEST4_MQ F PART4 4 TEST4 A N

6 record(s) selected.

select * from test4 C1 C2

---76 ---76 1 1 51 51 26 26 4 record(s) selected.

新しいパーティションはアタッチ済＆アクセス不可

新しいパーティションのデータは見えない

基礎表へのアタッチ⑤

select * from test4_mqt C1 C2

---76 ---76 51 51 2 record(s) selected.

set integrity for test4, test4_mqt immediate checked DB20000I The SQL command completed successfully.

select substr(name,1,8) as name ,type, status,access_mode,const_checked from sysibm.systables where name like 'TEST4%'

NAME TYPE STATUS ACCESS_MODE CONST_CHECKED

--- ---- --- --- ---TEST4 T N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY TEST4_MQ S N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY

2 record(s) selected.

SET INTEGRITYの実行

（すべてのRefresh Immediate MQTと基礎表を指定）

新しいパーティションのデータは MQTにも未反映

基礎表へのアタッチ⑥

select substr(DATAPARTITIONNAME,1,8) as dpname, smallint(DATAPARTITIONID) as partid, substr(TABNAME,1,8) as tabname, substr(STATUS ,1,2) as status, ACCESS_MODE

from syscat.datapartitions where tabname like 'TEST4%' DPNAME PARTID TABNAME STATUS ACCESS_MODE

--- --- --- --- ---PART0 0 TEST4 F

PART1 1 TEST4 F PART2 2 TEST4 F PART3 3 TEST4 F PART0 0 TEST4_MQ F PART4 4 TEST4 F

6 record(s) selected.

select * from test4 C1 C2

---76 ---76 26 26 1 1 51 51 75 102 25 101 6 record(s) selected.

新しいパーティションのデータが参照可能になった

基礎表へのアタッチ⑦

select * from test4_mqt C1 C2

---76 ---76 51 51 75 102 3 record(s) selected.

新しいパーティションのデータが MQTにも反映

ドキュメント内 Microsoft PowerPoint - テーブル・パーティショニング.ppt (ページ 45-67)

変 更

ユーザーによるメンテナンス

または

システムによるメンテナンス

MQTとパーティション表

● MQTをパーティション表にした場合、

アタッチは不可（SQLCODE=-270 RC=106）

● ステージング表はパーティション表にて 作成不可（SQLCODE=-270 RC=87）

MQT使用時の操作（基礎表がパーティション表）

 Refresh Immediate MQT

z 基礎表からのデタッチがコミットされた時点で：

MQTはSET INTEGRITY PENDING状態

基礎表は更新不可

デタッチのコミット後、MQTに対するSET INTEGRITYを実行する

z 基礎表へのアタッチ時点で：

特にMQTに変更なし

基礎表およびMQTへSET INTEGRITY実行し、追加データを反映

 Refresh Deferred MQT+ステージング表

z 基礎表からのデタッチ時点で：

ステージング表は、SET INTEGRITY PENDING状態

基礎表への更新可能

デタッチのコミット後、MQTおよびステージング表へのSET INTEGRITYを実行

z 基礎表へのアタッチ時点で：

特にMQTに変更なし

基礎表、ステージング表、およびMQTへSET INTEGRITYを実行し、追加デー

タを反映

MQT使用時の操作（MQTがパーティション表）

 MQTの高速リフレッシュ

z MQTに対してパーティションのデタッチ/アタッチを行う

MQTへ反映される行数が大量の場合に高速処理可能

z MQTへのSET INTEGRITY

基礎表とMQTの整合性をユーザー責任にて確保し、UNCHECKEDオプ ションでSET INTEGRITYを実行する

 MQTへのパーティションのアタッチは不可

z 一旦MQTから通常の表に変更してからアタッチを行い、再びMQT に戻す

ブランク・ページです。

基礎表からのデタッチ①

基礎表からのデタッチ②

基礎表からのデタッチ③

基礎表からのデタッチ④

基礎表からのデタッチ⑤

基礎表からのデタッチ⑥

基礎表からのデタッチ（ステージング表あり）①

基礎表からのデタッチ（ステージング表あり）②

基礎表からのデタッチ（ステージング表あり）③

基礎表からのデタッチ（ステージング表あり）④

基礎表からのデタッチ（ステージング表あり）⑤

基礎表からのデタッチ（ステージング表あり）⑥

基礎表からのデタッチ（ステージング表あり）⑦

基礎表へのアタッチ①

基礎表へのアタッチ②

基礎表へのアタッチ③

基礎表へのアタッチ④

基礎表へのアタッチ⑤

基礎表へのアタッチ⑥

基礎表へのアタッチ⑦

変更

● ステージング表はパーティション表にて作成不可（SQLCODE=-270 RC=87）

Refresh Immediate MQT

Refresh Deferred MQT+ステージング表

MQTの高速リフレッシュ

基礎表とMQTの整合性をユーザー責任にて確保し、UNCHECKEDオプションでSET INTEGRITYを実行する

MQTへのパーティションのアタッチは不可