Generated（生成）列が定義されているDB2テーブルのデータ移動についての解説

Paul Zikopoulos
配信 2003年 2月 13日

はじめに

GENERATEDテーブル列は、IBM® DB2® Universal DatabaseTMの便利な機能です。GENERATED（生成）列は、INSERT（挿入）またはUPDATE（更新）操作からではなく式から値を取得します。

GENERATED列とIDENTITY列

データベースのテーブルを定義する次のデータ定義言語（DDL）を考えてください。

db2 create table db2admin.actor (
     actor_id int generated by default as identity , 
     actor_name varchar(20) , 
     act_yr_of_birth smallint ,
     act_yr_of_death smallint ,
     age_at_death smallint generated always as (act_yr_of_death - act_yr_of_birth) 
    )
    in userspace1

db2 alter table db2admin.actor
    add primary key (actor_id)

このDDLには、GENERATED列の単純な例が含まれています。AGE_AT_DEATH 列は式を使ってDB2により自動的に生成されます。このDDLには、GENERATED列の単純な例が含まれています。AGE_AT_DEATH 列は式を使ってDB2により自動的に生成されます。

ACT_YR_OF_DEATH - ACT_YR_OF_BIRTH = AGE_AT_DEATH

テーブルにGENERATED列を作成するには、CREATE TABLEまたは ALTER TABLEステートメントで列にGENERATED ALWAYS ASクローズを指定します。

この例には、IDENTITY列と呼ばれる特殊なタイプのGENERATED列の定義も含まれており、IDENTITY列には、DB2により自動生成される数値が格納されます。IDENTITY列は、主キー値を作成する際に実に便利です。実際、この例には、ACTOR_ID列をACTORテーブルの主キーとして定義するALTER TABLEステートメントが含まれています。

常にDB2により生成：GENERATED ALWAYSとして定義されたIDENTITY列は、値が常にDB2により生成されるため、基本的に、DB2によって一意性が保証されます。

「基本的に」と断りを入れたのは、多くのユーザーがこの一意性の保証を誤解しているためです。つまり、IDENTITY列の属性を実際にどのように定義するかによって、この保証が該当する場合と該当しない場合が生じるという意味です。たとえば、最小値1から最大値10までの間で値が循環するIDENTITY列を定義した場合、このテーブルの行数がごく少ない数（10以下）でない限り、この列の値は定義上、一意とはなりません。

列の値の一意性を保証する方法は、列に一意制約を定義することです。先ほどの例でactor_id列の一意性を保証できる理由は、テーブルを変更して、主キー制約を当該の列に追加したためです。どうしてIDENTITY列を使用したかというと、それは、テーブル・カウンターのセットアップに伴う並行性の問題に煩わされずに数値を生成できるためです。

ユーザーの供給するIDENTITY：GENERATED BY DEFAULTとして定義されたIDENTITY列は、アプリケーションの供給する値を受け取ることになります。このタイプの列を含むテーブルにアプリケーションが値を供給しない場合には、DB2が代わって値を生成します（ただし、例にあるように、テーブルに一意制約が定義されていない限り、DB2はIDENTITY列の値の一意性は保証できません）。

actor tableの例では、actor_id列をGENERATED BY DEFAULTとして定義しました。これは、必要に応じてactor IDを自分で挿入できることを意味します。たとえば、次の INSERT ステートメントは、3つの行をACTORテーブルに追加します。

db2 insert into db2admin.actor (actor_id, actor_name, act_yr_of_birth)
    values (150,'Bruce Willis',1955), 
           (default,'Tom Cruise',1962),
           (default,'Tommy Lee Jones',1946)

上記の例では、最初の行にIDENTITY値（このケースでは150）が指定されています。残りの行はDB2によりIDENTITY値が生成されるようにDEFAULT キーワードで指定されています。このINSERTステートメントを実行したあと、Tom CruiseとTommy Lee JonesにはDB2生成値、Bruce Willisには150のユーザー供給値が挿入されています。これを確認するには、ACTORテーブルのコンテンツを参照します。図1に示すとおりです。

図1.ACTORテーブルのコンテンツ

クリックして大きなイメージを見る

仮に、actor_id列を（GENERATED BY DEFAULTの代わりに）GENERATED ALWAYSとして定義していたとしたら、この例のINSERTステートメントは、150の値の代わりにDEFAULTキーワードを指定していなければなりませんでした。そうしない場合、アプリケーションは、SQL00798エラーを受け取ることになります。

実際にご自分でお試しください。先ほどのACTORテーブルのときと同じDLLを使って、ACTOR2という名前の新しいテーブルを作成します。太字の部分が変更箇所です。

db2 create table db2admin.actor2 (
     actor_id int generated always as identity , 
     actor_name varchar(20) , 
     act_yr_of_birth smallint ,
     act_yr_of_death smallint ,
     age_at_death smallint generated always as (act_yr_of_death - act_yr_of_birth) 
    )
    in userspace1

db2 alter table db2admin.actor2
    add primary key (actor_id)

次に、ACTORテーブルに使用したのと同じINSERTステートメントをACTOR2テーブルに実行します（変更箇所を忘れないでください...挿入先はdb2admin.actor2です...）。どうなったでしょうか。結果はSQL00798Nエラーです。

今度は、次のように太字部分の変更を加えて、同じINSERTステートメントを実行します。今度は、次のように太字部分の変更を加えて、同じINSERTステートメントを実行します。

db2 insert into db2admin.actor2 (actor_id, actor_name, act_yr_of_birth)
    values (default,'Bruce Willis',1955), 
           (default,'Tom Cruise',1962),
           (default,'Tommy Lee Jones',1946)

成功です。何故でしょうか。それは、常にDB2がIDENTITY値を生成するというオプションによってACTOR2テーブルが作成されたためです。ACTOR2テーブルのコンテンツをサンプリングすると、図2のようになります。

図2.ACTOR2テーブルのコンテンツ

クリックして大きなイメージを見る

今、2つのテーブルが手元にあります。

• ACTOR（GENERATED BY DEFAULTオプション付きで定義）
• ACTOR2（GENERATED ALWAYSオプション付きで定義）

IDENTITY列のインポート、エクスポートおよびロード

インポートとエクスポート
IXF（Integrated Exchange Format）フォーマットにはIDENTITY列のプロパティーが維持されるため、DB2 EXPORTユーティリティーを使って、IDENTITY列を格納したテーブルからデータをエクスポートすることができます。DB2 IMPORTユーティリティーは、IXFファイル・フォーマットを使って、テーブルを再作成することができます。

たとえば、ACTORテーブルについてIXFファイルを生成するには（このテーブルにはGENERATED BY DEFAULTというIDENTITY列があることを思い出してください）、次のコマンドを実行します。

db2 export to actor.ixf of ixf messages export.msg select * from db2admin.actor

ここで、次のコマンドを使って、単一ステートメントで、データをインポートし、ACTOR3という名前の新しいテーブルを作成します。

db2 import from actor.ixf of ixf modified by identityignore messages import.msg
            create into db2admin.actor3

新しく作成したACTOR3テーブルのコンテンツをサンプリングします。図3のように見えるはずです。

図3.ACTOR3テーブルのコンテンツ

クリックして大きなイメージを見る

ACTOR3テーブルのコンテンツに驚かれたでしょうか。ACTOR テーブルのコンテンツをIXFファイルにエクスポートし、これを使ってACTOR3テーブルを作成しました。しかし、結果として、ACTOR3テーブルのコンテンツはACTOR2テーブルと同じになっています。

この例で、エクスポートされたデータは、ACTOR3という名前で新しく作成したテーブルにインポートされます。このテーブルのプロパティーはACTORテーブルと同じです。しかし、コマンド呼び出しに、identityignoreという修飾子が含まれていました。

IMPORTユーティリティーは、IDENTITY列に関連して2つのタイプの修飾子をサポートしています。

• identityignore‐この修飾子は、入力ファイルのIDENTITY値を無視して、DB2により各行に新しいIDENTITY値が生成されるように指定します。
• identitymissing‐この修飾子は、ターゲット・テーブルのIDENTITY列にはnull値を含めてどのような値も格納しないように指定します（注記：IDENTITY列を含むIXFファイルをインポートしようとするが、identitymissingを使ってIMPORTユーティリティーにはそうしないように指定している場合、期待した結果にはなりません）。

上記以外の2つのファイル・タイプ修飾子‐generatedignoreとgeneratedmissing‐は、役割は上記と同じで、インポートされる生成列を対象としています。

例において、仮にidentityignore修飾子を指定せず、かつ、ターゲット・テーブル定義に、GENERATED ALWAYS IDENTITY列を含めていたとしたら、IDENTITY値は常にDB2により生成されるように指定されているため、IXFファイルの入力データはすべて拒否されていたはずです（先ほどのACTOR2テーブル用に用意したDDLでは、GENERATED ALWAYS列を使用していました）。

入力ファイルの対応する行にIDENTITY列の値が欠落している（あるいはnull値が格納されている）ときは、その列のIDENTITY値が生成されます。IDENTITY列に非null値が指定されている場合は、その行は拒否されます。他方、identityignore修飾子を指定せず、かつ、IDENTITY列をGENERATED BY DEFAULTに指定している場合には、IMPORTユーティリティーは、入力ファイルに格納されている値を受け入れます。その値が欠落しているあるいはnullである場合には、DB2が新しい値を生成します。

きっと、何度か読み返しても、わかりづらいと思われることでしょう。表1に、最も一般的に使用されるIMPORTオプション‐INSERT、CREATE、REPLACE‐についてこの結果を要約します（identitymissing修飾子については、自分で、結果を調べてみてください。データをIXFにエクスポートするときにIDENTITY列を含まないように留意してください。そうでなければ、この修飾子を使用する意味がなくなります）。

エクスポート・ファイルの生成：以下の例はすべて、この記事の中で先ほど用意したDDLから生成されたIXFファイルを適用しています。EXPORT、IMPORTコマンドはすべて、CLPから実行されます。Control Centerを使ってエクスポート・ファイルを生成し、Columnタブを使ってテーブルのすべての列を選択している場合、Control Centerは、図4に示すように、各列についてMETHOD Nパラメーターを添えます。

図4.METHOD Nパラメーターを添えた生成EXPORTステートメント

これは、希望する結果にはなりません。METHOD NパラメーターはIXFファイルからのインデックスおよびIDENTITY列情報のすべてを排除するため、このサンプルはフェールすることになります。したがって、このオプションなしで、IXFファイルを生成する必要があります。

EXPORTのデフォルトでは、...select * from ... toを使ってデータを選択します。したがって、Columnタブは使用しないようにします。それにより、すべてがうまくいきます。列を明示的に指定しないときのShow Commandボタンの結果（図5）と指定したときの結果（図4）を比較してください。

図5.生成EXPORTコマンド

理解の助けとなるよう、参考として、ここにactorテーブルとactor2テーブルのエクスポート・ファイルがあります。

表1.IDENTITY列のインポート結果の要約

LOADユーティリティー

DB2 LOADユーティリティーは、IDENTITY列に関連して3つのファイル・タイプ修飾子をサポートしています。ロード・ユーティリティーは、identityignoreとidentitymissingのほか、identityoverride修飾子を受け入れます。

identityoverride修飾子は、GENERATED ALWAYS IDENTITY列のあるテーブルにデータをロードするときに、入力ファイルのIDENTITY値を使用するように指定します。この修飾子が指定されていると、IDENTITY列で値のない（またはnull値の）行は拒否されます。IDENTITY列がプライマリキーでないとき、またはIDENTITY列に一意のインデックスが定義されていないとき、この修飾子を使用するとGENERATED ALWAYS列の一意性プロパティーに違反する可能性があります。

上記以外の3つのファイル・タイプ修飾子‐generatedignore、generatedmissing、generatedoverride‐は、役割は上記と同じで、ロードされる生成列を対象としています。

DB2 Version 7.2ユーザーのための注記

DB2 Version 7.2では、LOADは、非IDENTITY列の値を生成できません。したがって、非IDENTITY生成列のあるテーブルをロードすると、SET INTEGRITYを実行するまで、ターゲット・テーブルは、CHECK PENDING状態のままになります。

SET INTEGRITY FOR <tablename> IMMEDIATE CHECKED FORCE GENERATEDを実行すると、SET INTEGRITYが、ユーザーに代わって列の値を生成します。 generateoverrideを指定して、有効であることがわかっているユーザー自身の値を提供すると、テーブルのCHECK PENDING状態を解除することができます。次のコマンドを使用します。

SET INTEGRITY FOR <tablename> GENERATED COLUMN IMMEDIATE UNCHECKED

このオプションを付けてSET INTEGRITYコマンドを実行すると、DB2は、供給された値が実際に正確であることを検証する作業をスキップします（当然、これにはリスクが伴う可能性があります）。テーブルのCheck Pending状態を解除し、ユーザー供給値の検証を強制するには、次のコマンドを実行します。SET INTEGRITY FOR <tablename> IMMEDIATE CHECKED。

ダウンロード可能なリソース

• このコンテンツのPDF
• actor.ixf (actor.ixf | 7KB)
• Download the sample code: (actor2.ixf | 7KB)

関連トピック

• developerWorks Japan: Information Management : Information Managementの日本語技術情報サイトです
• Automatically Generating Sequences of Numeric Values in DB2 Universal Database
• 『SQLリファレンス』の「CREATE TABLE」