Microsoft PowerPoint - テーブル・パーティショニング.ppt

(1)

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

テーブル・

パーティショニング

＜第１．００版２００６年７月＞本書に含まれている情報は、正式なIBMのテストを受けていません。また、明記にしろ、暗黙的にしろ、なんらの保証もなしに配布されるものです。この情報の使用またはこれらの技術の実施は、いずれも、使用先の責任において行われるべきものであり、それらを評価し、実際に使用する環境に統合する使用先の判断に依存しています。それぞれの項目は、ある特定の状態において正確であることがIBMによって調べられていますが、他のところで同じまたは同様の結果が得られる保証はありません。これらの技術を自身の環境に適用することを試みる使用先は、自己の責任において行う必要があります。

(2)

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

内容

概要

パーティション表の作成

データ・パーティションのデタッチ

データ・パーティションのアタッチと追加

MQT使用時の考慮点

パーティション表の照会

ユーティリティの実行

ブランク・ページです。

(3)

概要

＜第１．００版２００６年７月＞

テーブル・パーティショニングとは

データの範囲によって、ひとつの表を複数の

パーティションに物理的に分割して保存

新しいパーティションのアタッチ/デタッチが可能

履歴表A

JAN01 区分のデタッチ

JAN05

JAN05 区分のアタッチ

(4)

テーブル・パーティショニングのメリット①

保存期間を過ぎた行の高速な削除

z

従来はDELETEによる大量行の削除

¾

低速、大量のログを発生、表の再編成必要

JAN01

JAN02

JAN03

JAN04

JAN01

JAN02

JAN03

JAN04

DELETE FROM 履歴表A WHERE 日付=‘20060101’

低速

ALTER TABLE 履歴表A DETACH PARTITION part0 INTO TABLE 履歴01

● レンジ分割されない表

_{● レンジ分割された表}

高速な

デタッチ

★ 保持期間を過ぎたデータの消去のための長期のメンテナンス時間を

確保する必要が無くなる。

要再編成

履歴表A 履歴表A

テーブル・パーティショニングのメリット②

新しいデータのオンラインでの高速ロード

z

LOAD済みの表をアタッチする

z

アタッチ処理の時点で瞬間的に読み書き不能

JAN02

JAN03

JAN04

JAN02

JAN03

JAN04

JAN05

● レンジ分割されない表

● レンジ分割された表

JAN 05

LOAD

9 LOAD実行中は表 への更新不可 履歴表A 履歴表A JAN 05

LOAD

② ロード後、新パーティションとしてアタッチ ① LOAD中、履歴表Aには読み書き可能

(5)

テーブル・パーティショニングのメリット③

データアクセスの性能向上

z

指定された条件によって、特定パーティションのみアクセス

JAN01

JAN02

JAN03

JAN04

履歴表A

JAN05

**SELECT * FROM 履歴表A WHERE 日付=‘20060103’**

該当データの配置された

パーティションのみを参照

テーブル・パーティショニングのメリット④

故障範囲の局所化

z

ある表スペースがアクセス不能になっても、その他の表ス

ペースに配置されたパーティションへのアクセスは可能

JAN01

履歴表A

JAN05

JAN02 JAN03 JAN04

JAN06 JAN07 JAN08

表スペース1 表スペース2 表スペース3 表スペース4

データ・パーティショﾝ

SELECT * FROM 履歴表A

WHERE 日付 BETWEEN ‘20060102’ AND ‘20060104’

索引の配置された表スペースがアクセス不能の場合には表全体がアクセス不能

(6)

©日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) Information Management部 11 create tablespace tbs1 in nodegroup ng0 managed by system

using ('tbs1')

DB20000I SQL コマンドが正常に終了しました。

create tablespace tbs2 in nodegroup ng0 managed by system using ('tbs2')

create table t1 (a int, b char(10)) partition by range(a) (starting 1 ending 5 in tbs1, starting 6 ending 10 in tbs2) DB20000I SQL コマンドが正常に終了しました。

insert into t1 values (1,'a'), (2,'b'), (3,'c'), (4,'d'), (5,'e'),

(6,'f'), (7,'g'), (8,'h'), (9,'i'), (10,'j') DB20000I SQL コマンドが正常に終了しました。

create table t2 (a int) in tbs2

quiesce tablespaces for table db2v9.t2 exclusive

DB20000I QUIESCE TABLESPACES コマンドが正常に終了しました。 connect reset DB20000I SQL コマンドが正常に終了しました。 connect to sample データベース接続情報データベース・サーバー = DB2/AIX64 9.1.0 SQL 許可 ID = DB2V9 ローカル・データベース別名 = SAMPLE

select * from t1 where a=5

A B -

---5 e

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

select * from t1 where a=6

A B - ---SQL0290N 表スペース・アクセスが許されていません。 SQLSTATE=55039

参考：故障範囲の局所化

解説：

TBS1とTBS2の２つの表スペースにひとつずつのデータ・パーティションを配置した表T1があ

ります。

ここで表スペースTBS2を静止状態にしました（TBS2に作成された表T2に対してQUIESCEを

実行）

この状態で、表T1をアクセスすると、TBS2のデータは参照できませんが、TBS1のデータは

参照可能です。

(7)

テーブル・パーティショニングのメリット⑤

バックアップ性能の向上

z

複数の表スペースに分割配置し、並列バックアップ可能

¾

BACKUPコマンドのPARALLELISMオプション

BACKUP DATABASE MYDB USE TSM

OPEN 4 SESSIONS PARALLELISM 4

JAN01

履歴表A

JAN05

JAN02 JAN03 JAN04

JAN06 JAN07 JAN08

表スペース1 表スペース2 表スペース3 表スペース4

データ・パーティショﾝ

(8)

データベース・パーティショニング（DB2/6000 PE V1～）

z

データベースを分割

¾

分割された単位は「データベース・パーティション」と呼ばれる

z

ハッシングによるデータ分散

z

大規模データベースの実現

¾

巨大な表、表スペース、バッファープール

¾

SQL、ユーティリティの高速並列処理

z

Database Partitioning Feature（DPF) 必要

Interconnect DB パーティショﾝ0 DB パーティショﾝ1 DB パーティショﾝ2 DB パーティショﾝ3 表 T1 表 T1 表 T1 表 T1

ハッシングによるデータ分散

CREATE TABLE T1 (

COL1 INT,

COL2 CHAR(3)

)

DISTRIBUTE BY HASH (COL1)

テーブル・パーティショニング（V9.1～）

z

表を分割

¾

区分化された単位は「データ・パーティション」、または

「レンジ」と呼ばれる

z

キーレンジによるデータ分散

z

大規模データベースの実現

¾

巨大な表

¾

高速なデータアクセス

¾

高速なロールイン/ロールアウト

DBパーティショﾝ0

キーレンジによるデータ分散

CREATE TABLE T1

( COL1 INT, COL2 DATE )

PARTITION BY RANGE (COL2)

（ STARTING FROM (‘2006-01-01’)

ENDING (‘2006-12-31’)

EVERY (3 MONTH) ）

表 T1

JAN-MAR APR-JUN JUL-SEP OCT-DEC

データ・パーティショﾝ0 データ・パーティショﾝ1 データ・パーティショﾝ2 データ・パーティショﾝ3

(9)

多次元クラスター表（V8.1～）

z

次元列に同じ値を持つ行同士を同じセルに保管

¾

セルは１つ以上のブロック（エクステント）から構成

¾

各ブロックを指し示す索引（ブロック索引）が作成される

¾

ひとつの表には複数の次元を設定可能

z

大規模テーブルの高速アクセスを実現

¾

ブロック索引を使った特定ブロックのみのアクセス

¾

削除によって開放されたブロックは、

LOADでも再利用可能

¾

セルの高速削除可能

–

db2set DB2_MDC_ROLLOUT=YES

DBパーティショﾝ0

次元列の値毎に

保管先ブロック確保

CREATE TABLE TEST01

( COUNTRY CHAR(5),

YEAR SMALLINT,

DATA CHAR(100) )

ORGANIZE BY DIMENSIONS

(COUNTRY,YEAR)

Year

Country

Jpn, 1993 Jpn, 1996 USA, 1996 USA, 1997 USA, 1998 Korea, 1999

DBパーティション、MDCとの組み合わせ

Tablespace A Part 1

Tablespace B Tablespace C Tablespace A Tablespace B Tablespace C

Part 2 Part 3 Part 1 Part 2 Part 3

CREATE TABLE my_hybrid

(A INT, B INT, C INT, D INT …)

IN Tablespace A, Tablespace B, Tablespace C …

INDEX IN Tablespace B

DISTRIBUTE BY HASH (A)

PARTITION BY RANGE (B) (STARTING FROM (100) ENDING (300) EVERY (100))

ORGANIZE BY DIMENSIONS (C,D)

D

Data Row

Distribute By Hash

Partition By Range

Organize By Organize By Organize By Organize By Organize By Organize By

D

C

(10)

（参考）レンジクラスター表（V8.1.4～）

z

表作成の時点で全行の格納場所を確保

z

シーケンス・キーの値に基づき、各行格納先となるページとスロットを特定

¾

シーケンス・キーはユニーク列である必要あり

z

求める行の高速取得

¾

表の定義情報から検索対象の物理位置の特定可能

¾

物理的な索引なしでピンポイントで行取得

z

OLTP系のシステムで有効

¾

マスター表のようなユニーク索引を使った高頻度のアクセス

CREATE TABLE T1

( COL1 INT NOT NULL,

COL2 CHAR(2) …… )

ORGANIZE BY KEY SEQUENCE

( COL1 STARTING FROM 1

ENDING AT 100 )

DISALLOW OVERFLOW

1 2 3 4 5 6 7 8 … … 97 98 99 100 Page1 Page2 Page25 シーケンスキーの条件値から即座に物理位置を特定可能 COL1=8 → 2ページ目の第4スロット」

概要まとめ

パーティション・テーブルの機能により、ひとつの

表を複数のパーティションに物理的に分割して保

存可能になった

パーティションのアタッチ/デタッチを用いること

で、大量データの追加、削除時において高い可

用性が提供される

特定パーティションのみを処理させることにより、

巨大な表の処理性能向上を実現

(11)

パーティション表の作成

＜第１．００版２００６年７月＞

パーティション表の作成

テーブル・パーティショニング・キー

z

PARTITION BY RANGEにて指定

z

LOBやLONG列は不可

z

複数列の組み合わせは可能

z

生成列を指定しても良い

06/1Q 06/4Q 06/2Q 06/3Q 07/1Q 07/2Q TBSP1 TBSP2 TBSP3 CREATE TABLE T1 ( COL1 INT, COL2 DATE )

IN TBSP1, TBSP2, TBSP3 PARTITION BY RANGE (COL2) （ STARTING ‘2006-01-01’

ENDING ‘2007-06-30’ EVERY (3 Month) ） CREATE TABLE T1 ( COL1 INT, COL2 DATE )

PARTITION BY RANGE (COL2) （ STARTING ‘2006-01-01’ ENDING ‘2006-03-31’ IN TBSP1, ENDING ‘2006-06-30’ IN TBSP2, ENDING ‘2006-09-30’ IN TBSP3, ENDING ‘2006-12-31’ IN TBSP1, ENDING ‘2007-03-31’ IN TBSP2, ENDING ‘2007-06-30’ IN TBSP3 ） T1

●短形式

●長形式

(12)

レンジの指定

各データ・パーティションの開始位置と終了位置を指定

z

開始位置、終了位置の暗黙指定も可能

¾

最初のデータ・パーティションは開始位置、最後のデータ・パーティションは

終了位置を必ず指定

¾

異なるデータ・パーティションで同じ範囲を指定しないこと

z

MINVALUE、MAXVALUEの指定も可能

CREATE TABLE T1

( COL1 INT, COL2 DATE ) IN TBSP1

PARTITION BY RANGE (COL2) （ STARTING ‘2006-01-01’,

STARTING ‘2006-02-01’, STARTING ‘2006-03-01’,

ENDING MAXVALUE）

CREATE TABLE T1 ( COL1 INT, COL2 DATE )

IN TBSP1

PARTITION BY RANGE (COL2) （ STARTING MINVALUE,

STARTING ‘2006-02-01’ ENDING ‘2006-02-29’, ENDING ‘2006-03-31’ ）

IN TBSP1

PARTITION BY RANGE (COL2) （ STARTING ‘2006-01-01’,

STARTING ‘2006-02-01’, STARTING ‘2006-03-01’ ）

OK!

OK！

_OK！

NG!

短形式でのパーティション定義

EVERYキーワードの指定

z

「ｘｘｘ毎に１パーティション」という暗黙的な指定

z

日付列や数値列を使って、同じ大きさのパーティションを作成する際に有効

CREATE TABLE sales(sale_date DATE, customer INT, …)

PARTITION BY RANGE(sale_date)

(STARTING ‘1/1/2000’ ENDING ’12/31/2004’

EVERY 3 MONTHS);

CREATE TABLE sales(sale_date DATE, customer INT, …) IN TBSP1,TBSP2,TBSP3

PARTITION BY RANGE(sale_date)

(13)

データ・パーティション名の指定

PARTITION（またはPART）オプションにて指定可能

z

指定が無い場合、システムが名前を自動生成

パーティションのデタッチ機能を使う場合に有用

z

デタッチでは、パーティショﾝ名を指定

CREATE TABLE sales(sale_date DATE, customer INT, …)

PARTITION BY RANGE(sale_date)

(

PART rest

STARTING MINVALUE,

PARTITION q1

STARTING ‘1/1/2000’,

PARTITION q2

STARTING ‘4/1/2000’,

PARTITION q3

STARTING ‘7/1/2000’,

PARTITION q4

STARTING ‘10/1/2000’ ENDING ’12/31/2004’);

表スペースの指定

各データ・パーティション毎に明示指定

z

データ・パーティション定義の中で指定

表スペースのリストを指定

z

各データ・パーティションを順番に割り当て

PARTITION BY RANGE (COL2) （ STARTING ‘2006-01-01’IN TS1,

STARTING ‘2006-02-01’IN TS2, STARTING ‘2006-03-01’

ENDING ‘2006-3-31’IN TS1 ）

CREATE TABLE T1 ( COL1 INT, COL2 INT )

IN TS1,TS2,TS3

PARTITION BY RANGE (COL2) （ STARTING 1

ENDING 1000 EVERY 100 )

CREATE TABLE T1 ( COL1 INT, COL2 INT )

IN TS1,TS2,TS3

PARTITION BY RANGE (COL2) （STARTING ‘2006-01-01’, STARTING ‘2006-02-01’, STARTING ‘2006-03-01’, STARTING ‘2006-04-01 ENDING ‘2006-3-31’IN TS4）レンジ毎に明示指定レンジ毎に明示指定 3つの表スペー スへ順番に割り当て 3つの表スペー スへ順番に割り当て最後のレンジのみ明示指定最後のレンジのみ明示指定

(14)

表スペース指定時の考慮点

指定する表スペースは以下を満たす必要がある

z

全て同じデータベース・パーティショングループ

z

全てSMSか全てDMS表スペース

z

全て同じページサイズ

¾

但し、LONGデータ部分を別ページサイズとすることは可

z

全て同じエクステントサイズ

（参考）パーティション表作成時のエラー

SQL0636N データ・パーティション “<partition-name>” に指定された範囲は無効です。理由コード = "<reason-code>"。説明: パーティション・キーに指定された範囲は、以下のいずれかの理由コードで無効です。

1 パーティションの STARTING 値が ENDING値より大きくなっています。通常、パーティションの STARTING 値は ENDING値より小さくなければなりません。ただし、両方の境界が包含的である場合は、開始値を終了値と等しくすることができます。 MINVALUEまたは MAXVALUEを含む境界は排他的です。たとえば、STARTING (1,MINVALUE) ENDING (1, MINVALUE)として定義されたパーティションは正しくありません (理由コード1)。なぜなら、境界値が等しく、両方とも排他的だからです。 1 つの境界のみ (たとえば STARTING境界のみ) が明示的に指定されている場合でも、(欠落している境界が生成された後の)結果の境界がこの規則に違反すれば、この理由コードが戻されます。 3 最下位キーを持つパーティションに STARTING値が指定されていません。 4 最上位キーを持つパーティションに ENDING値が指定されていません。 5 直前の ENDING値が指定されていないのに、STARTING値が指定されていません。 7 DB2 UDB for iSeries の場合のみ:データ・パーティションが昇順で指定されていません。

9 パーティションの STARTING 値または ENDING値が長すぎます。表パーティション・キーのしきい値の合計長さは、512バイト以下でなければなりません。

(15)

（参考）パーティション表作成時のエラー（続き）

10 範囲が別のパーティションとオーバーラップします。各データ・パーティションの開始と終了の境界は適切に定義されなければならず、各データ値はただ 1つのデータ・パーティションに入らなければなりません。また、あるパーティションの終了境界と次のパーティションの開始境界に同じ値(MINVALUE または MAXVALUE を除く)を使用する場合は、これらの境界の少なくとも 1つを排他的として定義する必要があります。既存の表のパーティションの境界が包含的か排他的かを判別するには、次のようにしてSYSCAT.DATAPARTITIONS カタログ表を照会します('table-schema' と 'table-name'は該当する値に置き換えてください)。

SELECT

DATAPARTITIONID, DATAPARTITIONNAME,

LOWVALUEINCLUSIVE, LOWVALUE, HIGHVALUEINCLUSIVE, HIGHVALUE FROM SYSCAT.DATAPARTITIONS

WHERE TABSCHEMA='table-schema' AND TABNAME='table-name' ORDER BY SEQNO

11 EVERY 文節を指定しているときに MINVALUE とMAXVALUE を指定することはできません。

12 EVERY 文節に指定される値はゼロより大きく、かつユニークなパーティションを定義できるだけの大きさでなければなりません。

13 パーティションの STARTING 値または ENDING 値のMINVALUE または MAXVALUEの後に定数を指定することはできません。 MINVALUEまたは MAXVALUE を指定したら、後続の(有意性の低い) すべての列はそれぞれ MINVALUEまたは MAXVALUE でなければなりません。 "<partition-name>" の値が "PARTITION=value"の形式のものである場合は、エラーが発生していてパーティション名を使用できませんでした。指定された値は、パーティション・リスト文節内の問題のパーティションの開始値または終了値を示しています。

（参考）パーティション表作成時のエラー（続き）

SQL1860N 表スペース “<tablespace-name>” は、表スペース “<tablespace-name>” と互換性がありません。理由コード = "<reason-code>"。説明: 指定された表スペースは、以下のいずれかの理由で互換性がありません。 1 パーティション表のすべての表スペース(データ、LONG、索引)が、同じデータベース・パーティション・グループでなければなりません。 2 パーティション表のデータ表スペースと LONG表スペースは、すべて SMS かすべて DMSでなければなりません。 3 すべてのデータ表スペースのページ・サイズが同じでなければなりません。すべての LONG表スペースのページ・サイズが同じでなければなりません。 (データ表スペースと LONG表スペースのページ・サイズは異なっていてもかまいません。) 4 すべてのデータ表スペースのエクステント・サイズが同じでなければなりません。 5 パーティション表の長いデータは、すべてのデータ・パーティションのデータと同じ表スペースか、またはすべてのデータ・パーティションのLARGE 表スペースに格納されなければなりません。このエラーは、LARGE表スペースではないがデータ表スペースとは異なる表スペースを指定する LONG IN文節が使用された場合に発生することがあります。 LONG IN は、データ表スペースと等しい非 LARGE表スペースを指定する場合のみ使用できます(つまり、LONG IN文節はそれが完全に省略された場合のデフォルトの動作と同じくデータ表スペースを指定するだけであり、LONG IN文節を冗長的に使用する場合になります) 。

(16)

境界値の扱い

EXCLUSIVE/INCLUSIVEを指定

z

デフォルトはINCLUSIVE（境界値を含む）

CREATE TABLE sales(sale_date DATE, customer INT, …)

PARTITION BY RANGE(sale_date)

(STARTING MINVALUE ENDING ’1/1/2000’

EXCLUSIVE

,

STARTING ‘1/1/2000’ ENDING ‘4/1/2000’

EXCLUSIVE

,

STARTING ‘4/1/2000’ ENDING ‘7/1/2000’

EXCLUSIVE

,

STARTING ‘7/1/2000’ ENDING ’10/1/2000’

EXLUSIVE

,

STARTING ‘10/1/2000’ ENDING ’12/31/2004’);

‘2000-01-01’は

このレンジには

含まない

NULL値の扱い

パーティショﾝ列=NULLの行の位置を指定可能

z

NULLS FIRST/NULLS LASTキーワードによって指定する。

¾

NULLS LAST（省略時の設定）：

NULL値をパーティショﾝ列に持った行は、MAXVALUEを終端に

持つパーティションに格納

¾

NULLS FIRST：

NULL値をパーティショﾝ列に持った行は、MINVALUEを始端に

持つパーティションに格納

CREATE TABLE sales ( sale_date DATE, customer INT, …)

PARTITION BY RANGE ( sale_date

NULLS FIRST

)

(STARTING MINVALUE,

STARTING ‘1/1/2000’,

STARTING ‘4/1/2000’,

STARTING ‘7/1/2000’,

(17)

複数列を使ったパーティショニング

複数列を使用したパーティショニングも可能

CREATE TABLE sales(year INT, month INT, …)

PARTITION BY RANGE(year, month)

(STARTING (2000, 1) ENDING (2000, 3),

STARTING (2000, 4) ENDING (2000, 6),

STARTING (2000, 7) ENDING (2000, 9),

STARTING (2000, 10) ENDING (2000, 12),

STARTING (2001,1) ENDING (2001,3);

ただし、Overlapは不可

CREATE TABLE sales(year INT, month INT, …)

PARTITION BY RANGE(year, month)

(STARTING (2000, 1) ENDING (2003, 3),

STARTING (2000, 4) ENDING (2003, 6),

STARTING (2000, 7) ENDING (2003, 9),

STARTING (2000, 10) ENDING (2003, 12)

パーティション表に対する索引

索引は分割されない

z

リーフページにパーティションIDとして2バイトが追

加される。

各Index毎に別々のストレージオブジェクト

z

最初のデータパーティションと同じ表スペースに作

成（デフォルト）

z

明示指定も可

¾

CREATE TABLEのINDEX IN

¾

CREATE INDEXのIN

z

INDEXはLARGE表スペースへの配置を推奨

tbsp3 tbsp2 tbsp1 t1.p1 t1.p2 t1.p3

CREATE TABLE t1(c1 INT, c2 INT, …)

IN tbsp1, tbsp2, tbsp3

INDEX IN tbsp4

PARTITION BY RANGE(c1)

(STARTING FROM (1) ENDING100) EVERY (33))

CREATE INDEX i1(c1)

CREATE INDEX i2 (c2)

IN tbsp5

tbsp5 tbsp4

i2 i1

(18)

LOBデータ

LOBデータ領域も分割

z

対応するデータパーティションと同じ

表スペースに配置（デフォルト）

z

LONG INによって表スペースの指定

が可能

tbsp3 tbsp2 tbsp1 t1.p1 t1.p2 t1.p3 tbsp5 i2 tbsp8 tbsp7 tbsp6

t1.LONG1 t1.LONG2 t1.LONG3

tbsp4

i1

CREATE TABLE t1(c1 INT, c2 INT,

c3 BLOB

)

IN tbsp1, tbsp2, tbsp3

INDEX IN tbsp4

LONG IN tbsp6, tbsp7, tbsp8

PARTITION BY RANGE(a)

(STARTING FROM 1 ENDING 90 EVERY 30)

CREATE INDEX i1(c1)

CREATE INDEX i2 (c2) IN tbsp5

（参考）SMS表スペースに作成されるファイル

create tablespace ts1 managed by system using ('ts1')

DB20000I SQL コマンドが正常に終了しました。 create table t1 (a int,b int) in ts1

DB20000I SQL コマンドが正常に終了しました。 create table t2 (a int,b int) in ts1

DB20000I SQL コマンドが正常に終了しました。 create table t3 (a int,b int not null primary key) in ts1 DB20000I SQL コマンドが正常に終了しました。 !ls -l /db2data2/db2v9/NODE0000/SQL00001/ts1 合計 56

-rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00002.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00003.DAT

-rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00004.DAT -rw--- 1 db2v9 staff 12288 Feb 21 20:25 SQL00004.INX -rw--- 1 db2v9 staff 512 Feb 21 20:25 SQLTAG.NAM create index t3x2 on t3(a)

DB20000I SQL コマンドが正常に終了しました。 !ls -l /db2data2/db2v9/NODE0000/SQL00001/ts1 合計 64

-rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00002.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00003.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00004.DAT -rw--- 1 db2v9 staff 16384 Feb 21 20:25 SQL00004.INX -rw--- 1 db2v9 staff 512 Feb 21 20:25 SQLTAG.NAM create table t4 (a int,b int) in ts1 partition by range (a)

(starting 1 ending 100 every 25) DB20000I SQL コマンドが正常に終了しました。 create table t5 (a int,b int) in ts1 partition by range (a)

(starting 1 ending 100 every 34)

!ls -l /db2data2/db2v9/NODE0000/SQL00001/ts1 合計 120

-rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00002.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00003.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00004.DAT -rw--- 1 db2v9 staff 16384 Feb 21 20:25 SQL00004.INX

-rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00005.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00006.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00007.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00008.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00009.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00010.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00011.DAT

-rw--- 1 db2v9 staff 512 Feb 21 20:25 SQLTAG.NAM create index t5x1 on t4(a)

DB20000I SQL コマンドが正常に終了しました。 !ls -l /db2data2/db2v9/NODE0000/SQL00001/ts1 合計 152

-rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00002.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00003.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00004.DAT -rw--- 1 db2v9 staff 16384 Feb 21 20:25 SQL00004.INX -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00005.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00006.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00007.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00008.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00009.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00010.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00011.DAT

-rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00012.DAT -rw--- 1 db2v9 staff 12288 Feb 21 20:25 SQL00012.INX

(19)

（参考） SMS表スペースに作成されるファイル

create index t5x2 on t4(b) DB20000I SQL コマンドが正常に終了しました。 !ls -l /db2data2/db2v9/NODE0000/SQL00001/ts1 合計 184

-rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00002.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00003.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00004.DAT -rw--- 1 db2v9 staff 16384 Feb 21 20:25 SQL00004.INX -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00005.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00006.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00007.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00008.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00009.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00010.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00011.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00012.DAT -rw--- 1 db2v9 staff 12288 Feb 21 20:25 SQL00012.INX

-rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00013.DAT -rw--- 1 db2v9 staff 12288 Feb 21 20:25 SQL00013.INX

-rw--- 1 db2v9 staff 512 Feb 21 20:25 SQLTAG.NAM

drop table t4

DB20000I SQL コマンドが正常に終了しました。 create table t6 (a int,b int) in ts1 partition by range (a)

(starting 1 ending 100 every 50) DB20000I SQL コマンドが正常に終了しました。 !ls -l /db2data2/db2v9/NODE0000/SQL00001/ts1 合計 104

-rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00002.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00003.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00004.DAT -rw--- 1 db2v9 staff 16384 Feb 21 20:25 SQL00004.INX

-rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00005.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00006.DAT

-rw--- 1 db2v9 staff 0 Feb 21 20:25 SQL00007.DAT -rw--- 1 db2v9 staff 0 Feb 21 20:25 SQL00008.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00009.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00010.DAT -rw--- 1 db2v9 staff 4096 Feb 21 20:25 SQL00011.DAT -rw--- 1 db2v9 staff 0 Feb 21 20:25 SQL00012.DAT -rw--- 1 db2v9 staff 0 Feb 21 20:25 SQL00012.INX -rw--- 1 db2v9 staff 0 Feb 21 20:25 SQL00013.DAT -rw--- 1 db2v9 staff 0 Feb 21 20:25 SQL00013.INX -rw--- 1 db2v9 staff 512 Feb 21 20:25 SQLTAG.NAM

（参考） SMS表スペースに作成されるファイル

select tableid, substr(tbspace,1,20) as tabspace,

substr(tabname,1,20) as tabname from syscat.tables where tabschema='DB2V9' order by 2,1

TABLEID TABSPACE TABNAME

--- ---2 TS1 T1 3 TS1 T2 4 TS1 T3 4 USERSPACE1 ORG 5 USERSPACE1 STAFF 6 USERSPACE1 STAFFG 7 USERSPACE1 DEPARTMENT 8 USERSPACE1 EMPLOYEE 9 USERSPACE1 EMP_ACT 10 USERSPACE1 PROJECT 11 USERSPACE1 EMP_PHOTO 12 USERSPACE1 EMP_RESUME 13 USERSPACE1 SALES 14 USERSPACE1 CL_SCHED 15 USERSPACE1 IN_TRAY -32768 - T6 -32767 - T5 17 レコードが選択されました。

(20)

SYSCAT.DATAPARTITIONS

このパーティションのHIGH KEY値 VARCHAR(512) HIGHVALUE 指定レンジのHIGH KEYが、このパーティションにふくまれるかどうか Y：含まれる N：含まれない CHAR(1) HIGHINCLUSIVE このパーティションのLOW KEY値 VARCHAR(512) LOWVALUE 指定レンジのLOW KEYが、このパーティションにふくまれるかどうか Y：含まれる N：含まれない CHAR(1) LOWINCLUSIVE データパーティションの順序番号（０から開始） INTEGER SEQNO このデータパーティションの状態 A：このパーティションは新規にアタッチされた D：このパーティションは新規にデタッチされたが、MQTのメンテナンス未了 I：このパーティションはデタッチされたが、索引のクリーンアップが未完了（デタッチされたパーティションを参照していたすべての索引キーが削除されたらこの行は消滅） VARCHAR(32) STATUS このデータパーティションのアクセス可否（F：フルアクセス、N:アクセス不可、 R: Read Only アクセス、 D：データ移動不可） CHAR(1) ACCESS_MODE LOB、LONGデータが保管される表スペースの表スペースID INTEGER LONG_TBSPACEID このデータパーティションが保管されるオブジェクトID INTEGER PARTITIONOBJECTID このデータパーティションが配置された表スペースの表スペースID INTEGER TBSPACEID データパーティション名 VARCHAR(128) DATAPARTITIONNAME データパーティションID INTEGER DATAPARTITIONID テーブル名 VARCHAR(128) TABNAME テーブル・スキーマ名 VARCHAR(128) TABSCHEMA 記述データタイプ列名

SYSCAT.DATAPARTITIONEXPRESSION

このキー部分の式（SQL形式）

CLOB(32K)

DATAPARTITIONEXPRESSION

2： 2番目のパーティション式

（以下同様）

１：最初のパーティション式

データパーティショニング列の順序番号

INTEGER

データパーティショニング列の

順序

テーブル名

VARCHAR(128)

TABNAME

テーブル・スキーマ名

VARCHAR(128)

TABSCHEMA

記述

データタイプ

列名

(21)

パーティション情報の取得

connect to sample データベース接続情報データベース・サーバー = DB2/AIX64 9.1.0 SQL 許可 ID = DB2V9 ローカル・データベース別名 = SAMPLE

create tablespace tbs1 managed by database using (file 'tbs01‘ 1024) DB20000I SQL コマンドが正常に終了しました。

create table t1 (a int, b clob(10)) partition by range(a) (starting 1 ending 2 in tbs1 long in tbs2,

starting 3 ending 4 in tbs3 long in tbs1, starting 5 ending 6 in tbs2 long in tbs3, starting 7 ending 10 in tbs1 long in tbs2) DB20000I SQL コマンドが正常に終了しました。パーティション表の作成 （各パーティションとそのLOB部分は 個別に表スペースを指定して配置）

パーティション情報の取得②

select

substr(DATAPARTITIONNAME,1,8) as dpname, substr(TABNAME,1,5) as tabname,

smallint(TBSPACEID) as tbs_id,

smallint(PARTITIONOBJECTID) as partobj_id, smallint(LONG_TBSPACEID) as longtbs_id,

ACCESS_MODE as mode, smallint(SEQNO) as seq , smallint(LOWVALUE) as lowval, smallint(HIGHVALUE) as highval from syscat.datapartitions where TABNAME='T1'

DPNAME TABNAME TBS_ID PARTOBJ_ID LONGTBS_ID MODE SEQ LOWVAL HIGHVAL - --- --- --- ---- --- --- ---PART0 T1 8 4 9 F 0 1 2 PART1 T1 10 4 8 F 1 3 4 PART2 T1 9 5 10 F 2 5 6 PART3 T1 8 6 9 F 3 7 10 4 レコードが選択されました。 パーティション表「T1」に関する パーティション情報を取得 （アクセス可能な4つのパーティション が定義されている）

(22)

パーティション情報の取得③

select

substr(TABNAME,1,8) tabname,

DATAPARTITIONKEYSEQ seq,

substr(DATAPARTITIONEXPRESSION,1,10) partcol from syscat.datapartitionexpression

where tabname='T1'

TABNAME SEQ PARTCOL --- - ---T1 1 A 1 レコードが選択されました。 パーティション表「T1」の 区分化キーはどれか？

パーティション情報の取得④

select

substr(a.DATAPARTITIONNAME,1,8) DATAPARTITIONNAME, substr(b.TBSPACE,1,5) dataspace,

substr(c. TBSPACE,1,5) longspace from syscat.datapartitions a, syscat.tablespaces b, syscat.tablespaces c where a.tbspaceid=b.tbspaceid and a.long_tbspaceid=c.tbspaceid and a.tabname = 'T1' and int(a.lowvalue) < 8 and int(a.highvalue) > 8

DATAPARTITIONNAME DATASPACE LONGSPACE --- ---PART3 TBS1 TBS2 1 レコードが選択されました。 パーティション表「T1」において 「A=8」の行はどのパーティションに 割り当てられたか？また、そのパーティションが置かれた表スペース名は？

(23)

DESCRIBE DATA PARTITIONSコマンド

データ・パーティションの情報を出力

z

DESCRIBE DATA PARTITIONS FOR TABLE テーブル名

$ db2 describe DATA PARTITIONS for table t1

PartitionId Inclusive (y/n) Inclusive (y/n) Low Value High Value

--- - - ---0 Y 1 Y 2 1 Y 3 Y 4 2 Y 5 Y 6 3 Y 7 Y 10 4 レコードが選択されました。

DESCRIBE DATA PARTITIONSコマンド（続き）

$ db2 describe DATA PARTITIONS for table t1 show detail PartitionId Inclusive (y/n) Inclusive (y/n)

Low Value High Value

--- - - ---0 Y 1 Y 2 1 Y 3 Y 4 2 Y 5 Y 6 3 Y 7 Y 10 4 レコードが選択されました。

PartitionId PartitionName TableSpId PartObjId LongTblSpId AccessMode Status --- - --- --- ---0 PART---0 8 4 9 F 1 PART1 10 4 8 F 2 PART2 9 5 10 F 3 PART3 8 6 9 F 4 レコードが選択されました。

(24)

パーティション表の作成まとめ

パーティション表の作成時には、テーブル・パー

ティショニング・キーをPARTITION BY RANGEに

て指定する。

各データパーティションの範囲は、長形式または

短形式にて指定する。

各データパーティションは、別々の表スペースに

配置することが可能。

索引は物理的な分割がなされない

ブランク・ページです。

(25)

データ・パーティションの

デタッチ

＜第１．００版２００６年７月＞ Tablespace C Tablespace B

データ・パーティションのデタッチ

パーティション表から特定パーティションを切り離す

切り離されたパーティションは通常の表として使用可能

データの移動はないため高速な処理

z

カタログ情報の更新のみ

ALTER TABLEのCOMMITで表(パーティション表、デタッチされた表）はフ

ルアクセス可

z

パーティション表をベースにしたMQTが存在する場合、SET INTEGRITY必要

パーティション表の索引はバックグランドでメンテナンス

z

デタッチされたパーティションのキーは削除される

Tablespace A

P1999

P2000

P2001

ALTER TABLE 履歴表A DETACH PARTITION part0 INTO TABLE T1999 パーティション表 Tablespace C Tablespace B Tablespace A

T1999

P2000

P2001

パーティション表 通常の表 T1999

高速な

デタッチ

(26)

デタッチの流れ

1. ALTER TABLEでパーティション表から特定パーティション

のデタッチ

パーティション表に対してZ-LOCKを取得

システム・カタログの更新

2. ALTER TABLEのCOMMIT

この時点でパーティション表、デタッチされた表はフルアク

セス（※）可能

このパーティション表にアクセスするパッケージは無効化

3. バックグランドでパーティション表の索引メンテナンス

（※）ただし、パーティション表をベースにしたMQT(refresh immediate)またはステージング表

がある場合、ALTER TABLEをCOMMIT後、SET INTEGRITYの実行が必要

デタッチの実行

ALTER TABLE のDETACHオプション

パーティション名

z

CREATE TABLE時のPARTITIONオプションで指定可能

z

指定がない場合は、システムが自動的にパーティション名作成

z

DESCRIBE DATA PARTITIONS FOR TABLE 表名 SHOW DETAILで取得可能

デタッチするパーティションの宛先の表

z

既に存在していてはいけない

z

ターゲットはソース（パーティション表）の属性を引き継ぐ

¾

列名、列タイプ、列長、Null属性、列デフォルト値など列属性

¾

表のタイプ（MDC)など

z

索引は作成されない

z

表スペースはそのまま（データの移動はなし）

ALTER TABLE

パーティション表

DETACH PARTITION

パーティション名

INTO

ターゲット表

パーティション表

：デタッチしたいパーティションが存在する表

パーティション名

：デタッチしたいパーティション名

(27)

デタッチ実行時の制限事項

実行時の制限事項

z

RIの親表に対してはデタッチできない（SQCODE=-270

RC=92）

¾

子表の外部キーを一時的に無効にする必要あり

z

DELETE トリガーは起動しない

z

パーティションの「削除」は不可

¾

パーティションの「追加」は可能

●親表からデタッチを行う場合、子表の外部キーを一旦無効にする

ALTER TABLE c ALTER FOREIGN KEY fk NOT ENFORCED;

ALTER TABLE p DETACH PARTITION p0 INTO TABLE pdet;

ALTER TABLE c ALTER FOREIGN KEY fk ENFORCED;

SET INTEGRITY FOR c ALL IMMEDIATE UNCHECKED;

COMMIT WORK;

デタッチ時のパーティション表のアクセス制限

ALTER TABLE実行中は表にZ-LOCK取得

COMMIT後フルアクセス

z

パーティション表、新規で作成されるターゲット表ともアクセス可

¾

ただしパーティション表をベースにしたMQTが存在する場合SET

INTEGRITY実行までアクセス制限あり

z

パーティション表の索引のメンテナンスはバックグランドにて処理

ALTER … DETACH

Table Z-lock

requested by

ALTER

Read/write

Lock

granted

ALTER

completes

COMMIT

Lock released

Asynchronous Index Cleanup

SYSCOLUMNS,

SYSUSERAUTH,SYSTABLES,SYSDA TAPARTITIONS表の同一表の全ての列にXロック

(28)

参考）デタッチ操作が要求するロック (LOCKのSNAPSHOT抜粋）

アプリケーション・ハンドル = 294 アプリケーション ID = *LOCAL.santav9.060311064643 シーケンス番号 = 00004 アプリケーション名 = db2bp CONNECT 許可 ID = SANTAV9 アプリケーション状況 = ロック待機状況変更時刻 = 2006-03-11 16:00:35.192139 アプリケーション・コード・ページ = 943 保留ロック = 48 合計待ち時間 (ミリ秒) = 27323 ロック保留中エージェント ID = 293 ロック保留中のアプリケーション ID = *LOCAL.santav9.060311064604 ロック名 = 0xFFFA8000FFFF00000000000054 ロック属性 = 0x00000000 リリース・フラグ = 0x40000000 ロック・オブジェクト・タイプ = 表ロック・モード = 意図的排他ロック (IX) ロック・モード保留 = 意図なしロック (IN) 要求ロック・モード = 超排他ロック (Z) ロック保留中の表スキーマ = SANTAV9 ロック保留中の表名 = DP1 パーティション表に対して更新処理実行中に ALTER TABLEを実行 → デタッチ処理はロック待機デタッチ処理がZ-LOCKを要求

参考）デタッチの実行例

select substr(name,1,8),status,access_mode,const_checked from sysibm.systables where name like 'DP1%' 1 STATUS ACCESS_MODE CONST_CHECKED

--- --- --- ---DP1 N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY

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

DBNAME PARTID TABNAME STATUS MODE -- -- ----PART0 0 DP1 F PART1 1 DP1 F PART3 3 DP1 F PART4 4 DP1 F PART5 5 DP1 F PART6 6 DP1 F PART2 2 DP1 F 7 レコードが選択されました。 1) パーティション表、索引の作成

create table dp1 (c1 int,c2 char(50),c3 char(50),c4 char(50),c5 varchar(100) ,c6 date) in tbs1 partition by range (c6) (starting '1/1/2000' ending '12/31/2006' every 1 years);

create unique index dp1_i1 on dp1 (c1,c6);

2000年～2006年までevery 1 yearsなので７つのパーティションが作成されている。

参考）システムカタログ SYSTABLES表 STATUS 列 N=Nomal, C=Check Pending ACCESS_MODE列 F=Full Access, N=No Access

(29)

参考）SYSCAT.DATAPARTITIONS

New このパーティションのHIGH KEY値 VARCHAR(512) HIGHVALUE 指定レンジのHIGH KEYが、このパーティションにふくまれるかどうか Y：含まれる N：含まれない CHAR(1) HIGHINCLUSIVE このパーティションのLOW KEY値 VARCHAR(512) LOWVALUE 指定レンジのLOW KEYが、このパーティションにふくまれるかどうか Y：含まれる N：含まれない CHAR(1) LOWINCLUSIVE データパーティションの順序番号（０から開始） INTEGER SEQNO このデータパーティションの状態 A：このパーティションは新規にアタッチされた D：このパーティションは新規にデタッチされたが、MQTのメンテナンス未了 I：このパーティションはデタッチされたが、索引のクリーンアップが未完了（デタッチされたパーティションを参照していたすべての索引キーが削除されたらこの行は消滅） VARCHAR(32) STATUS このデータパーティションのアクセス可否（F：フルアクセス、N:アクセス不可、 R: Read Onlyアクセス、 D：データ移動不可） CHAR(1) ACCESS_MODE LOB、LONGデータが保管される表スペースの表スペースID INTEGER LONG_TBSPACEID このデータパーティションが保管されるオブジェクトID INTEGER PARTITIONOBJECTID このデータパーティションが配置された表スペースの表スペースID INTEGER TBSPACEID データパーティション名 VARCHAR(128) DATAPARTITIONNAME データパーティションID INTEGER DATAPARTITIONID テーブル名 VARCHAR(128) TABNAME テーブル・スキーマ名 VARCHAR(128) TABSCHEMA 記述データタイプ列名

--- --- --- ---DP1 N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY DP1_2002 N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY

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

DBNAME PARTID TABNAME STATUS ACCESS_MODE --- --- ---PART0 0 DP1 F PART1 1 DP1 F PART3 3 DP1 F PART4 4 DP1 F PART5 5 DP1 F PART6 6 DP1 F PART2 2 DP1 I N PART0 0 DP1_2002 F 8 レコードが選択されました。 2) パーティション表からPART2をデタッチし、DP1_2002を作成する

alter table dp1 detach partition part2 into dp1_2002;

新しくDP1_2002が作成されている DP1も、DP1_2002もNomal、 Full Access デタッチ対象以外のDP1表のパーティションはFull Access デタッチされたパーティションは索引メンテナンス実行中 •ACCESS_MODE=N(No Access)

•STATUS=I (index cleanup)

(30)

参考）デタッチの実行例つづき

--- --- --- ---DP1 N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY DP1_2002 N F YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY

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

DBNAME PARTID TABNAME STATUS MODE -- -- ----PART0 0 DP1 F PART1 1 DP1 F PART3 3 DP1 F PART4 4 DP1 F PART5 5 DP1 F PART6 6 DP1 F PART0 0 DP1_2002 F 7 レコードが選択されました。 ３) 索引メンテナンスが終了する デタッチされたパーティションの情報は削除されている

デタッチ実行後の索引メンテナンス

索引のクリーンアップ処理

z

Asynchronous Index Clean-up (AIC)

デタッチされたパーティションの索引メンテナンス

z

無効なRIDを削除する

z

索引スキャンでは、まだクリーンアップされていない無効なRID

は無視する

ALTER TABLE （DETACH）のコミット後に自動開始

z

MQTがある場合には、SET INTEGRITYのコミット後

z

１索引あたり１エージェント（db2aic）が動作

¾

パラレル処理が可能

z

表スペースにIXロック、表にISロックを取得

¾

ただし、他のアプリケーションがロック待ちになった場合には、ロック

を一旦開放し、索引メンテナンスを5秒間中断する

(31)

db2aicエージェント

ID = 31

タイプ = ASYNCHRONOUS INDEX CLEANUP

データベース名 = SAMPLE

パーティション番号 = 0

説明 = Table: SANTAV9 .DP1, Index: SANTAV9 .DP1_I1

開始時刻 = 2006-03-11 16:03:25.485064 状態 = 実行中呼び出しタイプ = 自動スロットル: 優先順位 = 50 進捗モニター: 合計作業 = 3111 pages 完了作業 = 2927 pages 開始時刻 = 2006-03-11 16:03:25.511896

Auth Id Application Appl. Application Id DB # of Name Handle Name Agents

--- ---- -- --- ---SANTAV9 db2aic 339 *LOCAL.DB2.060311071815 SAMPLE 1

SANTAV9 db2bp 293 *LOCAL.santav9.060311064604 SAMPLE 1 SANTAV9 db2taskd 280 *LOCAL.DB2.060311064337 SAMPLE 1 SANTAV9 db2stmm 279 *LOCAL.DB2.060311064337 SAMPLE 1

LIST UTILITIES SHOW DETAILコマンドの出力例） LIST APPLICATIONS コマンドの出力例）

索引のクリーンアップ実行時のロック

アプリケーション・ハンドル = 339 アプリケーション ID = *LOCAL.DB2.060311070325 シーケンス番号 = 00001 アプリケーション名 = db2aic CONNECT 許可 ID = SANTAV9 アプリケーション状況 = 接続完了状況変更時刻 = 2006-03-11 16:03:26.715055 アプリケーション・コード・ページ = 943 保留ロック = 3 合計待ち時間 (ミリ秒) = 0 ロックのリストロック名 = 0x00030000000000000000000070 ロック属性 = 0x00000000 リリース・フラグ = 0x40000000 ロック・カウント = 1 保留カウント = 1 ロック・オブジェクト名 = 3 オブジェクト・タイプ = 表スペース表スペース名 = TBS1 モード = IX つづくロック名 = 0xFFFA8000000000000000000074 ロック属性 = 0x00000000 リリース・フラグ = 0x40000000 ロック・カウント = 1 保留カウント = 1 ロック・オブジェクト名 = 32768 オブジェクト・タイプ = 内部表変更ロック表スキーマ = SANTAV9 表名 = DP1 モード = S ロック名 = 0xFFFA8000FFFF00000000000054 ロック属性 = 0x00000000 リリース・フラグ = 0x40000000 ロック・カウント = 1 保留カウント = 1 ロック・オブジェクト名 = 32768 オブジェクト・タイプ = 表表スキーマ = SANTAV9 表名 = DP1 モード = IS

(32)

パーティションのデタッチまとめ

ALTER TABLEのDETACHオプションにより、

データパーティションを別表にすばやく切り離す

ことができる。

パーティション表の索引からは、不要になった索

引キーが非同期にてクリーンアップされる

ブランク・ページです。

(33)

データ・パーティションの

アタッチと追加

＜第１．００版２００６年７月＞

データ・パーティションのアタッチ

ある表を新規パーティションとしてパーティション表に追加

アタッチ処理の時点で瞬間的に読み書き不能

z

データの移動は発生しない

アタッチされたパーティションは、 SET INTEGRITYの実行後に

アクセス可能

z

既存のパーティションに関してはフルアクセス可能

z

SET INTEGRITYのALLOW WRITE ACCESSオプションを利用

JAN02 JAN03 JAN04 JAN02 JAN03 JAN04 JAN05 ● 非パーティション表の場合 ● パーティション表の場合 JAN 05 LOAD 9 LOAD実行中は表へ の更新不可 履歴表A 履歴表A JAN 05 LOAD ② ロード後、新パーティションとしてアタッチ ① LOAD中、履歴表Aには読み書き可能

(34)

アタッチの流れ

1. アタッチする表を準備（新規作成＆LOADなど）

2. ALTER TABLEでパーティション表へアタッチ

パーティション表に対してZ-LOCK取得

システム・カタログの更新

3. ALTER TABLEのCOMMIT

この時点で既存のパーティションはフルアクセス可能

アタッチしたパーティションはまだ見えない

パッケージはINVALIDになる

4. SET INTEGRITYの実行

ALLOW WRITE ACCESSオプションの指定可能

事前にSET LOCK TIMEOUT WAITを実行しておくと良い

5. SET INTEGRITYのCOMMIT

アタッチしたパーティションもフルアクセス可能

SET INTEGRITYステートメント

SET INTEGRITYで行われること

z

索引メンテナンス

z

追加したされた行がパーティションの定義範囲内にあることを確認

z

制約のチェック

¾ ユニーク制約、参照制約、検査制約

z

生成列、ID列のメンテナンス

z

MQTメンテナンス

SET INTEGRITYの新しいオプション

z

既存のパーティションへのアクセスを制御

¾

ALLOW WRITE ACCESS

¾

ALLOW READ ACCESS

¾

ALLOW NO ACCESS (従来）

z

生成列のメンテナンス実行

¾

FORCE GENERATED

z

ID列のメンテナンス実行

¾

GENERATE IDENTITY

これらの処理を

１UOWで実施する

(35)

アタッチの実行

ALTER TABLE のATTACHオプション

アタッチする表

z

非パーティション表か、単一パーティションの表でなければならない。

z

パーティション表の列属性と一致していなければいけない

¾

列タイプ、列長、Null属性、列デフォルト値、Compressionなどの列属性

¾

列名は違っても構わない

z

索引があってもDROPされる

z

属する表スペースの属性が同じでなければいけない

¾

SMS/DMS、ページサイズ、エクステントサイズ、データベース・パーティショングループ

ALTER TABLE

パーティション表

ATTACH PARTITION

STARTING (

開始位置

) ENDING (

終了位置

) FROM

ソース表

パーティション表

：アタッチしたいパーティション表

開始位置・終了位置

：アタッチするパーティション・レンジの開始位置と、終了位置

ソース表

：アタッチする表

アタッチ時のパーティション表のアクセス制限

ALTER … ATTACH

Table Z-lock

requested by

alter

Read/write

COMMIT

Lock released

New compilation allowed

SET INTEGRITY

Partition Z-lock

and catalog

locks requested

by SI

_{Locks granted}

COMMIT

SI completes

Locks released

R/W all

partitions

R/W existing partitions

X-lock

ATTACHed

partitions

Lock granted

Alter completes

z

ALTER TABLE ～ COMMITはZ-LOCK

z

SET INTEGRITY～COMMIT

(36)

オンラインSET INTEGRITY

z

ALLOW WRITE ACCESS

z

ALLOW READ ACCESS

z

ALLOW NO ACCESS (省略値）

照会・更新不可 (X)

照会可能、更新不可

(SIX)

ALLOW READ ACCESS

照会・更新不可 (Z)

照会・更新不可

(Z)

ALLOW NO ACCESS

照会・更新不可 (X)

照会・更新可能

（IX）

ALLOW WRITE ACCESS

アタッチしたパーティションに対し

て

パーティション表に対して

Set integrityオプション

非パーティション表に対しても対応

z

LOAD後のSET INTEGRITY実行中のREAD ACCESSを許可

¾

制約チェック、ＭＱＴメンテナンス

SET INTEGRITY ALLOW WRITE ACCESS実行時のロック

LOCKのスナップショットから抜粋アプリケーション・ハンドル = 1217 アプリケーション ID = *LOCAL.santav9.060316004911 シーケンス番号 = 00027 アプリケーション名 = db2bp CONNECT 許可 ID = SANTAV9 アプリケーション状況 = UOW 実行中状況変更時刻 = 2006-03-16 10:37:55.258541 アプリケーション・コード・ページ = 943 保留ロック = 17 合計待ち時間 (ミリ秒) = 0 ～省略～ロック名 = 0xFFFA8008FFFF00000000000054 ロック属性 = 0x00000000 リリース・フラグ = 0x40000000 ロック・カウント = 255 保留カウント = 0 ロック・オブジェクト名 = 32776 オブジェクト・タイプ = 表表スキーマ = SANTAV9 表名 = DP2 モード = IX ロック名 = 0x00000005000000000000000054 ロック属性 = 0x00000000 リリース・フラグ = 0x40000000 ロック・カウント = 3 保留カウント = 0 ロック・オブジェクト名 = 5 オブジェクト・タイプ = 表表スペース名 = SYSCATSPACE 表スキーマ = SYSIBM 表名 = SYSTABLES モード = IX ロック名 = 0xFFFA8008000600000000000054 ロック属性 = 0x00000000 リリース・フラグ = 0x40000000 ロック・カウント = 3 保留カウント = 0 ロック・オブジェクト名 = 32776 オブジェクト・タイプ = 表パーティション表スペース名 = TBS2 表スキーマ = SANTAV9 表名 = DP2 データ・パーティション ID = 6 モード = X アタッチしたパーティションID

(37)

SET INTEGRITY ALLOW READ ACCESS実行時のロック

LOCKのスナップショットから抜粋アプリケーション・ハンドル = 1217 アプリケーション ID = *LOCAL.santav9.060316004911 シーケンス番号 = 00027 アプリケーション名 = db2bp CONNECT 許可 ID = SANTAV9 アプリケーション状況 = UOW 実行中状況変更時刻 = 2006-03-16 10:37:55.258541 アプリケーション・コード・ページ = 943 保留ロック = 17 合計待ち時間 (ミリ秒) = 0 ～省略～ロック名 = 0xFFFA8008FFFF00000000000054 ロック属性 = 0x00000000 リリース・フラグ = 0x40000000 ロック・カウント = 255 保留カウント = 0 ロック・オブジェクト名 = 32776 オブジェクト・タイプ = 表表スキーマ = SANTAV9 表名 = DP2 モード = SIX ロック名 = 0x00000005000000000000000054 ロック属性 = 0x00000000 リリース・フラグ = 0x40000000 ロック・カウント = 3 保留カウント = 0 ロック・オブジェクト名 = 5 オブジェクト・タイプ = 表表スペース名 = SYSCATSPACE 表スキーマ = SYSIBM 表名 = SYSTABLES モード = IX ロック名 = ロック名 = 0xFFFA8008000600000000000054 ロック属性 = 0x00000000 リリース・フラグ = 0x40000000 ロック・カウント = 3 保留カウント = 0 ロック・オブジェクト名 = 32776 オブジェクト・タイプ = 表パーティション表スペース名 = TBS2 表スキーマ = SANTAV9 表名 = DP2 データ・パーティション ID = 6 モード = X アタッチしたパーティションID

SET INTEGRITY ALLOW NO ACCESS実行時のロック

LOCKのスナップショットから抜粋アプリケーション・ハンドル = 1217 アプリケーション ID = *LOCAL.santav9.060316004911 シーケンス番号 = 00030 アプリケーション名 = db2bp CONNECT 許可 ID = SANTAV9 アプリケーション状況 = UOW 実行中状況変更時刻 = 2006-03-16 10:48:54.112661 アプリケーション・コード・ページ = 943 保留ロック = 18 合計待ち時間 (ミリ秒) = 0 ～省略～ロック名 = 0x0004000A000000000000000054 ロック属性 = 0x00000000 リリース・フラグ = 0x40000000 ロック・カウント = 2 保留カウント = 0 ロック・オブジェクト名 = 10 オブジェクト・タイプ = 表表スペース名 = TBS2 表スキーマ = SANTAV9 表名 = DP2 モード = Z ロック名 = 0x00000005000000000000000054 ロック属性 = 0x00000000 リリース・フラグ = 0x40000000 ロック・カウント = 3 保留カウント = 0 ロック・オブジェクト名 = 5 オブジェクト・タイプ = 表表スペース名 = SYSCATSPACE 表スキーマ = SYSIBM 表名 = SYSTABLES モード = IX ロック名 = 0xFFFA8008000700000000000054 ロック属性 = 0x00000000 リリース・フラグ = 0x40000000 ロック・カウント = 3 保留カウント = 0 ロック・オブジェクト名 = 32776 オブジェクト・タイプ = 表パーティション表スペース名 = TBS2 表スキーマ = SANTAV9 表名 = DP2 データ・パーティション ID = 7 モード = Z アタッチしたパーティションID

(38)

LOAD

Load Data

Build Indexes Commit

LOAD into

staging table ATTACH

SET INTEGRITY

Build Indexes

Constraints

MQT maint

Constraints

MQT maint

read only

off line

available

パーティション表

: ATTACH + SET INTEGRITY

従来の方法：

LOAD + SET INTEGRITY

off line read only

Microsoft PowerPoint - テーブル・パーティショニング.ppt

テーブル・

パーティショニング

内容



概要



パーティション表の作成



データ・パーティションのデタッチ



データ・パーティションのアタッチと追加



MQT使用時の考慮点



パーティション表の照会



ユーティリティの実行

ブランク・ページです。

概要

テーブル・パーティショニングとは



データの範囲によって、ひとつの表を複数の

パーティションに物理的に分割して保存



新しいパーティションのアタッチ/デタッチが可能

履歴表A

JAN05

テーブル・パーティショニングのメリット①



保存期間を過ぎた行の高速な削除

z

従来はDELETEによる大量行の削除

¾

低速、大量のログを発生、表の再編成必要

JAN01

JAN02

JAN03

JAN04

JAN01

JAN02

JAN03

JAN04

低速

● レンジ分割されない表

● レンジ分割された表

高速な

デタッチ

★ 保持期間を過ぎたデータの消去のための長期のメンテナンス時間を

確保する必要が無くなる。

要再編成

テーブル・パーティショニングのメリット②



新しいデータのオンラインでの高速ロード

z

LOAD済みの表をアタッチする

z

アタッチ処理の時点で瞬間的に読み書き不能

JAN02

JAN03

JAN04

JAN02

JAN03

JAN04

JAN05

● レンジ分割されない表

● レンジ分割された表

LOAD

LOAD

テーブル・パーティショニングのメリット③



データアクセスの性能向上

z

指定された条件によって、特定パーティションのみアクセス

JAN01

JAN02

JAN03

JAN04

履歴表A

JAN05

_{● レンジ分割された表}

**SELECT * FROM 履歴表A WHERE 日付=‘20060103’**