| コンテンツ |
 |
|
はじめに
IBM® DB2® Universal DatabaseTM
(UDB) for Linux、UNIX®、Windows® バージョン8.1、および最近発表されたDB2
Universal Database for z/OSTM 、OS/390®バージョン8は、数値シーケンス自動生成の豊富な機能をサポートしています。上記最新リリースのDB2
UDBは、新たにデータベース・シーケンス・オブジェクトを提供し、すでに採用されているIDENTITY列の技術をさらに強化しています。この記事では、IDENTITY列とシーケンスの代表的な用法を簡単に説明し、さらに、それぞれの実際の用例とそれぞれの機能の比較を行います。
数値シーケンスを生成する2つの方法
DB2には、数値シーケンスを自動生成する2つの方法があります。
| |
DB2 UDB for Linux、UNIX、Windows |
DB2 UDB for z/OS、OS/390 |
DB2 UDB for iSeriesTM |
| IDENTITY列 |
V7 |
V6 |
V5R2 |
| SEQUENCEオブジェクト |
V7.2 |
V8（この記事の執筆時点でベータ） |
将来のリリースで予定 |
| 国際SQL標準：IDENTITY列とSEQUENCEオブジェクトの両方を次期バージョンの国際SQL標準に盛り込むことが現在提案されています。DB2に実装されたサポートは、この提案内容と的確に一致するものであり、国際SQL標準の現行ドラフト・バージョンに組み込まれています。 |
|
IDENTITY列
テーブルの列がIDENTITY属性で定義されているとき、そのテーブルに行を挿入すると、当該の列に対応する数値が自動的に生成されます。
SEQUENCEオブジェクト
DB2によって数値シーケンスを自動生成する2つ目の方法は、SEQUENCEオブジェクトを作成する方法です。このオブジェクトの参照は、シーケンス式を使用して行うことができます。シーケンス式は、式の使える場所であればほぼどこでも使うことができます。シーケンス式では、返りの値が新しく生成される値であるか、またはすでに生成済みの値であるかを指定できます。
シーケンス参照の参照先がnext value（次の値）であるときは、数値がシーケンス用に自動生成され、シーケンス式の結果として返されます。たとえば、orders_seqという名前のシーケンスを作成した場合、このシーケンス式は、シーケンス用に生成される次の値を返します。
| |
NEXT VALUE FOR orders_seq
|
シーケンス参照の参照先がprevious value（前の値）であるときは、前のSQLステートメントでシーケンス用に生成された数値がシーケンス式の結果として返されます。このシーケンス式は、シーケンスにより生成された前の値を返します。
| |
PREVIOUS VALUE FOR orders_seq6
|
DB2 UDBによるシーケンスの採用に伴い、NEXT VALUEに替わって非SQL標準構文のNEXTVALもサポートされました。同様にPREVIOUS
VALUEに替わってPREVVALもサポートされました。これらは、今後も引き続きサポートされます。
IDENTITYとシーケンスの選択
数値生成にはIDENTITYとシーケンスの両方を使用しますが、状況に応じてどちらかを選択することもできます。
IDENTITY列は、以下の場合に有用です。
- 単一テーブルの1つの列のみ、自動生成値を必要とする
- 各行に個別の値が要求される
- テーブルの主キーに自動ジェネレーターが望まれる
- 挿入方法に関わらず、新しい値を生成するプロセスと、テーブルへ挿入するプロセスが緊密に結び付いている
SEQUENCEオブジェクトは、以下の場合に有用です。
- 1つのシーケンスから生成した値を複数のテーブルに格納する
- 各テーブルの複数の列に自動生成値が必要とされる（同じシーケンスまたは複数のシーケンスを使って、各行に複数の値を生成することも可能）
- 新しい値を生成するプロセスと、テーブルへの参照はそれぞれ独立している
SEQUENCEオブジェクトと違って、IDENTITY列は、テーブルについて定義されるため、特定の制約があります。各テーブルにつき定義できるIDENTITY列は最大1つです。ある列をIDENTITY列として作成するとき、その列には厳密な数値データ型を使用しなければなりません。IDENTITY属性による列の値の生成は、DEFAULTのそれと抵触するため、IDENTITY列を定義するときに、DEFAULTを指定することはできません。IDENTITY列は、暗黙にNOT
NULLとして定義されます。
例1.顧客テーブルとサプライヤー・テーブルの結合
IDENTITY列の代表的な使い方の1の例（架空）として、Widget社という会社のデータベース用に顧客注文情報を格納するテーブルを考えてみましょう。Widget社は、各行（注文）に注文番号を自動生成し、テーブルに挿入することを希望しています。
この例のためのDDL
リスト1に示すように、注文番号用にIDENTITY列を使用し、主キーの一部として注文番号列を定義します。IDENTITY属性それ自体は、生成されたシーケンス値が一意であることを保証しない点に留意してください。ただしPRIMARY
KEY制約は、テーブルの行の一意性を保証します。自動生成された値のみがIDENTITY列に挿入されるようにするには、GENERATED ALWAYSを指定します。各四半期末に、Widget社は、最後に生成されたorder_idを使って、その四半期の受注数を確認します。NO
CACHEとORDERのオプションにより、システム障害発生時に、まだ使用されていないIDENTITY値が破棄されることはありません。Widget社は、ALTER
TABLEステートメントを使って、新しい四半期の始まりに注文番号列を1から再開する予定にしています。
リスト1にはIDENTITY列のすべての属性が明示的に示されています。指定しなかった場合にはデフォルトの値に設定されますが、デフォルト値はベンダーの実装に応じて異なることがあるため、オプションのすべてを指定するのがコーディング慣行としては適しています。
リスト1. IDENTITY列を使って注文テーブルを作成する
| |
CREATE TABLE customer_orders_t (
order_id INT NOT NULL
GENERATED ALWAYS
AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE 1
NO MAXVALUE
NO CYCLE
NO CACHE
ORDER),
order_date DATE NOT NULL,
cust_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
status CHAR(9) NOT NULL,
PRIMARY KEY (order_date, order_id))
|
下記は、テーブルに行を挿入するINSERTステートメントの例です。
| |
INSERT INTO customer_orders_t VALUES
(DEFAULT, CURRENT DATE,
:cid, :pid, :qty, :cost, 'PENDING')
|
Widget社は、顧客からの受注のほか、サプライヤーへの発注の管理も行っています。サプライヤーへの注文を格納するサプライヤー注文テーブルの定義は、顧客注文テーブルの定義によく似ています。
| |
CREATE TABLE supplier_orders_t (
order_id INT NOT NULL
GENERATED ALWAYS
AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE 1
NO MAXVALUE
NO CYCLE
NO CACHE
ORDER),
order_date DATE NOT NULL,
supp_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
status CHAR(9) NOT NULL,
PRIMARY KEY (order_date, order_id))
|
下記は、supplier_orders_tテーブルに行を挿入するINSERTステートメントの例です。
| |
INSERT INTO supplier_orders_t VALUES
(DEFAULT, CURRENT DATE,
:sid, :pid, :qty, :cost, 'PENDING')
|
テーブルの結合
Widget社は、顧客注文テーブルとサプライヤー注文テーブルの両方を、顧客とサプライヤーの両方に対応する総合注文テーブルに一元化することにより、効率とシナジーを高めることができると考えています。顧客とサプライヤーの注文の違いは、その注文が受注であるか発注であるかという点のみであり、これは、STATUSフィールドに反映されます。両テーブルを最小限の中断で結合するために、Widget社は、時間をかけて段階的に変更を行う予定です。計画されているステップは、以下のようなものです。
- テーブル間で一意になるように、各テーブルについて生成される注文番号を同期化する
- 同期化されていない注文が完了するまで待つ（あるいは、注文番号のリセットされる四半期開始まで待つ）
- サプライヤー注文テーブルを廃止し、顧客注文テーブルにより顧客からの注文とサプライヤーへの注文のすべてを管理するようにする
- クリーンアップする
ステップ1.注文番号を同期化する
両方のテーブルで使用される、生成order_id番号を同期化します。このため、IDENTITY列の値を毎回生成するのではなく、SEQUENCEオブジェクトにより値が供給されるように両方のテーブルを変更します。つまり、単一のシーケンスorders_seqで、両方のテーブルのIDENTITY列の値が生成されるようにします。NEXT
VALUE式でorders_seqを参照することにより、IDENTITY列の値が明示的に提供されるように、各テーブルのINSERTステートメントを変更します。orders_seqシーケンスを次のように定義します。
| |
CREATE SEQUENCE orders_seq AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
NO MAXVALUE
NO CYCLE
NO CACHE
ORDER
|
挿入操作でIDENTITY列の値が明示的に提供されるように、SET GENERATED BY DEFAULTを使って、顧客注文テーブルとサプライヤー注文テーブルの両方を変更します（ALTER
TABLEステートメントに使用するSET GENERATED BY DEFAULTとSET GENERATED ALWAYSは、DB2 UDB
for iSeries V5R2、DB2 UDB for z/OS、OS/390 V8で提供されています。将来のリリースでDB2 UDB for
Linux、UNIX、Windowsでも提供を予定しています）。
| |
ALTER TABLE customer_orders_t
ALTER COLUMN order_id SET GENERATED BY DEFAULT
ALTER TABLE supplier_orders_t
ALTER COLUMN order_id SET GENERATED BY DEFAULT
|
order_id列の明示的な値を供給するように注文テーブルのINSERTステートメントのすべてが変更され、orders_seqシーケンスが該当する値で開始される間、2つのテーブルへの挿入を制限するには、LOCK
TABLEステートメントを使用します。
下記は、サプライヤーと顧客注文テーブル用にINSERTステートメントをどのように変更するかを示しています。
| |
INSERT INTO customer_orders_t VALUES
(NEXT VALUE FOR orders_seq, CURRENT DATE,
:cid, :pid, :qty, :cost, 'PENDING')
INSERT INTO supplier_orders_t VALUES
(NEXT VALUE FOR orders_seq, CURRENT DATE,
:sid, :pid, :qty, :cost, 'PENDING')
|
続いて今度は、顧客注文テーブル、サプライヤー注文テーブルの両方のorder_id IDENTITY列によりこれまでに生成済みの最も大きな値の直後の値から始まるように、orders_seqシーケンスが変更されるようにする方法です。下記はそのやり方です。最初にSELECTステートメントを使って、この値を判定しています。
| |
SELECT MAX(c.order_id), MAX(s.order_id)
FROM customer_orders_t c, supplier_orders_t s
|
たとえば、上記のクエリーで2つの値42331、57231が返された場合、orders_seqシーケンスは、次のように変更されることになります。
| |
ALTER SEQUENCE orders_seq
RESTART WITH 57232
|
COMMITステートメントで、テーブルのロックを解除し、2つの注文テーブルへ挿入できるように戻します。それぞれのorder_id列の値がIDENTITY属性により相互に独立して生成される代わりに、単一のシーケンスorders_seqにより挿入値が生成されます。この値は、2つのテーブルにわたって一意となります。
ステップ1について、図1を参照してください。
図1.order_idシーケンスの値がIDENTITY列をオーバーライドする
ステップ2.注文が同期化されるまで待つ
Widget社としては、注文IDがリセットされる四半期の開始まで待つのは希望に沿いません。それよりも、注文のステータスを監視する方をWidget社は選択します。次のクエリーの結果が空テーブルのときは、ステップ3に進行することができます。
| |
SELECT order_id
FROM (SELECT order_id FROM customer_orders_t
WHERE status <> 'COMPLETED' AND order_id < 57232) AS x
UNION ALL (SELECT order_id FROM supplier_orders_t
WHERE status <> 'COMPLETED' AND order_id < 57232)
|
ステップ2について、図2を参照してください。
図2. 重複の可能性のあるすべてのorder_id番号が完了
ステップ3.サプライヤー注文テーブルを廃止します。
supplier_orders_tテーブルを廃止するため、次のように名前を変更し、一時的にテーブルを利用不能にします。
| |
RENAME TABLE supplier_orders_t TO supplier_orders_t_old
|
そのあとsupplier_orders_tへの既存の参照が引き続き基礎データにアクセスできるように、ビューを作成します。
| |
CREATE VIEW supplier_orders_t
(order_id, order_date, supp_id, product_id, quantity,
price, status)
AS SELECT
order_id, order_date, cust_id, product_id, quantity,
price, status
FROM customer_orders_t
|
これで、従来の顧客とサプライヤーの両方についてアクティブな注文のすべてが、customer_orders_tテーブルで管理されるようになります。今後のこのテーブルの保守をより簡単なものとするために、ステップ4で若干のクリーンアップ作業を行います。
ステップ3について、図3を参照してください。
図3.すべての新規注文がcustomer_orders_tに送られる
ステップ4.クリーンアップします。
注文番号の生成が必要なのは単一列（order_id）のみであり、値は、独立のSEQUENCEオブジェクトを利用せずに、IDENTITY列で生成することができます。また、テーブルを一時的に利用不能にし、IDENTITY値が、シーケンスにより生成される次の値にリセットされるようにします。
| |
LOCK TABLE customer_orders_t IN EXCLUSIVE MODE
VALUES NEXT VALUE FOR orders_seq INTO :nextorder
|
たとえば、上記のクエリーが値64243を返したと想定した場合、customer_orders_tテーブルは、次のように変更することができます。
| |
ALTER TABLE customer_orders_t
ALTER COLUMN order_id
SET GENERATED ALWAYS
RESTART WITH 64243
|
さらにまた、INSERTステートメントがorders_seqシーケンスを使用する各インスタンスについて、ステップ1で示したように、DEFAULTの使用に戻るように変更する必要があります。これで、シーケンスをDROPすることができます。
| |
DROP SEQUENCE orders_seq RESTRICT
|
元のサプライヤー注文テーブルのデータをアーカイブに保管したあと、このテーブルもDROPすることができます。
ステップ4について、図4を参照してください。
図4.再度IDENTITY列を使って64243から値を生成する
パフォーマンスのチューニング
customer_orders_tテーブルがおよそ2倍のアクティビティーに対応することになったことで、Widget社は、このテーブルを並行環境で利用することを決断します。並行挿入のメリットを活用するために、Widget社は、値をキャッシングしてこのテーブルのIDENTITY列のパフォーマンスをチューニングすることにします。1時間当たりの注文数、およびなんらかの理由でデータベースが再起動される頻度に照らして、キャッシュサイズ=50が妥当であるとWidget社は判断します。四半期に実際に出された注文数の計算方法も変更しました。したがって、シーケンス値が順に生成されるように強制する理由はありません。1四半期の期間中に一意の値が生成されるための主な要件は引き続き充足されます。そこで、IDENTITY列の値のシーケンス生成のパフォーマンスを強化するために次の調整を加えます。
| |
ALTER TABLE customer_orders_t
ALTER COLUMN order_id
SET CACHE 50
SET NO ORDER
|
例2.衛星測定値の収集
例1に示したように、シーケンスを使って、複数のテーブルにわたって一意の値を生成することができます。シーケンスで、同じテーブル内の複数の列の値を自動生成することもできます。
今度は、惑星または月を周回する軌道衛星の例を考えます。この衛星SAT1は、軌道上の16カ所でデータ測定値を収集します。このデータを収集するテーブルには、値が自動的に生成される3つの列があります。1つの列はIDENTITY属性を使って、測定値IDを生成します。残る2つは、シーケンスから値を取得します。図5を参照してください。
図5.衛星と軌道上の16カ所の測定ポイント
この例のためのDDL
測定値の数が膨大になる可能性があるため、IDENTITY列には、DECIMAL(31)データ型を使用します。
| |
CREATE TABLE SAT1_readings (
reading_id DECIMAL(31) NOT NULL PRIMARY KEY
GENERATED ALWAYS AS IDENTITY
(START WITH 1
INCREMENT BY 1
MINVALUE 1
NO MAXVALUE
NO CYCLE
NO CACHE
ORDER),
orbit_location SMALLINT NOT NULL,
horizon_adjustment SMALLINT NOT NULL,
planet_image BLOB(100 M)
|
orbit_locationには、測定値の収集される軌道上の16ポイントについて0〜15の値があります。この16個の値の周期的シーケンスを生成するため、次のシーケンスを作成します。
| |
CREATE SEQUENCE orbit_location_seq
AS SMALLINT
START WITH 0
INCREMENT BY 1
MINVALUE 0
MAXVALUE 15
CYCLE
NO CACHE
ORDER
|
horizon_adjustment値は、水平プレーンとの相対関係で、衛星の位置を表します。0の値は、衛星が水平プレーン上にあることを意味し、+4の値は、衛星が水平プレーンよりそのポイント分だけ上に位置することを意味し、-4の値は、衛星が水平プレーンよりその値分だけ下に位置することを意味します。シーケンスは、最小値でも最大値でもない-4の値からスタートします。この値が、水平プレーン調整値を計算する方程式で使用されることになります。軌道の周回がシーケンスの周期になります。horizon_adjustment値の計算に次のシーケンスを使用することになります。
| |
CREATE SEQUENCE horizon_adjustment_seq
AS SMALLINT
START WITH -4
INCREMENT BY 1
MINVALUE -7
MAXVALUE 8
CYCLE
NO CACHE
ORDER
|
測定値テーブルへの値の挿入
次のINSERTステートメントは、測定値が取得される毎にテーブルに値を挿入します。
| |
INSERT INTO SAT1_readings VALUES
(DEFAULT, NEXT VALUE FOR orbit_location_seq,
ABS(NEXT VALUE FOR horizon_adjustment_seq) - 4,
:planet_image)
|
最初の17回の測定値取得、およびそれに対応するINSERTステートメントの後に、reading_id (r_i)、orbit_location
(o_l)、horizon_adjustment (h_a)列に自動生成される値は、次のようになります。
| r_i |
1. |
2. |
3. |
4. |
5. |
6. |
7. |
8. |
9. |
10. |
11. |
12. |
13. |
14. |
15. |
16. |
17. |
| o_l |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
13 |
14 |
15 |
0 |
| h_a |
0 |
-1 |
-2 |
-3 |
-4 |
-3 |
-2 |
-1 |
0 |
1 |
2 |
3 |
4 |
3 |
2 |
1 |
0 |
追加の測定値の取得
計器の優れた感度により、惑星に関する測定値は衛星が実際に軌道に到着するまえに取得することができます。計器のレンジは、衛星が軌道に乗るまでに惑星に接近する段階に応じて測定値を10回取得することができます。正規の軌道の外にあるこの追加測定値を取得するために、シーケンス値は、周期を定義しているMINVALUE値〜MAXVALUE値のレンジの外でスタートします。
次のステートメントは、軌道上の16ポイントで測定値を取得する前に10個の測定値を追加で取得できるよう、IDENTITY値とシーケンス値を変更します。RESTARTオプションは、指定した値でIDENTITY列またはシーケンスをリスタートさせるか、または、値が指定されないときは、IDENTITY列またはシーケンスの作成時にSTART
WITHとして指定された値でリスタートさせます。
| |
ALTER TABLE SAT1_readings
ALTER COLUMN reading_id
RESTART
ALTER SEQUENCE orbit_location_seq
RESTART WITH -10
ALTER SEQUENCE horizon_adjustment_seq
RESTART WITH -14
|
次のテーブルは、対応するINSERTステートメントより先に上記の3つのALTERステートメントが実行された場合の最初の17個の測定値を示しています。17個の測定値には、軌道到着前に取得された10個の測定値と軌道周回中に取得された最初の7個の測定値が含まれています。
| r_i |
1. |
2. |
3. |
4. |
5. |
6. |
7. |
8. |
9. |
10. |
11. |
12. |
13. |
14. |
15. |
16. |
17. |
| o_l |
-10 |
-9 |
-8 |
-7 |
-6 |
-5 |
-4 |
-3 |
-2 |
-1 |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
| h_a |
10 |
9 |
8 |
7 |
6 |
5 |
4 |
3 |
2 |
1 |
0 |
-1 |
-2 |
-3 |
-4 |
-3 |
-2 |
結論
DB2は、数値を自動生成する2つの柔軟な方法をサポートしています。1つは、テーブルに緊密に結び付いているIDENTITY列、もう1つは、どのテーブル参照からも独立して値を生成するSEQUENCEオブジェクトです。上記の例は、数値を自動生成するのにIDENTITY列とSEQUENCEオブジェクトがどのように有効かつ柔軟であるかを例証しています。これらの例は、特にIDENTITY列とSEQUENCEオブジェクトの以下の機能と特長を明確に示しています。
- 主キーにIDENTITY列を使用する
- 2つのテーブルにSEQUENCEオブジェクトを使用する
- 値を生成させる代わりに、IDENTITY列の値を明示的に指定する
- 既定の値で値のシーケンスを開始する
- 指定した値でIDENTITY列の値をリスタートさせる
- IDENTITY値をキャッシングして、パフォーマンスを向上させる
- 周期的な値を生成するようにIDENTITY列またはシーケンスを定義する
- シーケンスに定義済みの最小値を超える開始値をシーケンスに指定する
- 負の値の開始値をシーケンスに指定する
- インクリメントし、そのあとデクリメントする値を生成するようにシーケンスを定義する
- 生成順に値を返すようにする
シーケンスとIDENTITY列は、皆さまのアプリケーションに大きなメリットをもたらすことでしょう。
リソース
著者について
 |
Bobby Fielding氏は、IBMデータベースの開発において14年の経験を持ち、現在、DB2
UDB for Linux、UNIX、Windows開発チームでSQL言語アーキテクトを務めています。国際SQL標準の拡張を推進するIBM
SQL Language Councilのメンバーです。
|
 |
Claire McFeely氏は、IBMデータベースの開発に14年の経験を持ち、現在、DB2 UDB
for z/OS開発チームでSQL言語アーキテクトを務めています。国際SQL標準の拡張を推進するIBM SQL Language
Councilのメンバーです。 |
|
IBM、DB2、DB2 Universal Database、iSeries、OS/390およびz/OSは、IBM
Corporationの米国またはその他の国（あるいはその両方）における商標または登録商標です。
 原文はこちら
|
