自動採番の列が存在するテーブルへのデータロード

バックアップからのリストアではなく、あるDBサーバーからデータをエクスポートして、別のDBサーバーでインポートするという場合に、邪魔になってくるのが自動採番するように定義した列の存在。
DB2で言えば「GENERATED ALWAYS AS IDENTITY」が列定義に付いているような場合。

そのような場合に、どういう手順で移行すればいいのかを忘れないためのメモ。

前提

以下のテーブルとデータが移行元のDBに存在するとする。
(便宜上、複数のSQL文の区切り文字を「;」として記述する)

CREATE TABLE person(
      id INTEGER GENERATED ALWAYS AS IDENTITY
    , name VARCHAR(32) NOT NULL
    , CONSTRAINT person_pkey PRIMARY KEY(id)
);

CREATE TABLE phone(
      id INTEGER NOT NULL
    , phone_number VARCHAR(16) NOT NULL
    , CONSTRAINT phone_fkey FOREIGN KEY(id) REFERENCES person(id) ON DELETE CASCADE
);

INSERT INTO person(name) VALUES('John');
INSERT INTO phone(id, phone_number) VALUES(IDENTITY_VAL_LOCAL(), '012-345-6789');

INSERT INTO person(name) VALUES('ken');
DELETE FROM person WHERE name = 'ken';

INSERT INTO person(name) VALUES('Ken');
INSERT INTO phone(id, phone_number) VALUES(IDENTITY_VAL_LOCAL(), '012-987-6543');
INSERT INTO phone(id, phone_number) VALUES(IDENTITY_VAL_LOCAL(), '098-765-4321');

(NG)何も考えずにexport＆load(1)

上記のCREATE TABLE文を、そのまま移行先のDBサーバーで実行し、以下のようにしてデータを移行しようとする。

＜移行元＞

db2 "EXPORT TO person.del OF DEL SELECT * FROM person"
db2 "EXPORT TO phone.del OF DEL SELECT * FROM phone"

＜移行先＞

db2 "LOAD FROM person.del OF DEL INSERT INTO person"
db2 "LOAD FROM phone.del OF DEL INSERT INTO phone"

そうすると、personテーブルのロードで以下のような警告が出て、ロードができない。

SQL3550W  The field value in row "1" and column "1" is not NULL, but the 
target column has been defined as GENERATED ALWAYS.

まぁ、システム(DBサーバー)側で自動生成する値だと定義しているのに、手動で値を入れることなど普通はできない。

(NG)何も考えずにexport＆load(2)

それならば、と、自動採番される列以外の値をエクスポートすれば‥結果は分かりきっているが、一応‥

＜移行元＞

db2 "EXPORT TO person.del OF DEL SELECT name FROM person"
db2 "EXPORT TO phone.del OF DEL SELECT * FROM phone"

＜移行先＞

db2 "LOAD FROM person.del OF DEL INSERT INTO person(name)"
db2 "LOAD FROM phone.del OF DEL INSERT INTO phone"
db2 "SET INTEGRITY FOR phone IMMEDIATE CHECKED"

LOAD文では、参照制約などのチェックがされないので、SET INTEGRITY文でそのチェックを実行してやる必要がある。
で、そうすると、以下のようなnoticeが出る。

DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL3603N  Integrity processing through the SET INTEGRITY statement has found 
an integrity violation involving a constraint, a unique index, a generated 
column, or an index over an XML column. The associated object is identified by 
"DB2INST1.PHONE.PHONE_FKEY".  SQLSTATE=23514

要は、外部キー制約に違反しているということ。当然である。
(試しに、ファイルphone.delの中身と、コマンド「db2 "SELECT * FROM person"」の実行結果を見比べてみるとよく分かる。)

(OK)LOAD文でIDENTITYOVERRIDE修飾子を使ってみる

IBMのdeveloperWorksで、LOAD文にIDENTITYOVERRIDEを指定する方法がある、というのを見つけた。

LOADユーティリティー

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

これを使うと、以下のような手順になる。

＜移行元＞

db2 "EXPORT TO person.del OF DEL SELECT * FROM person"
db2 "EXPORT TO phone.del OF DEL SELECT * FROM phone"

＜移行先＞

db2 "LOAD FROM person.del OF DEL MODIFIED BY IDENTITYOVERRIDE INSERT INTO person"
db2 "LOAD FROM phone.del OF DEL INSERT INTO phone"
db2 "SET INTEGRITY FOR phone IMMEDIATE CHECKED"

基本的にはこれで問題ないのだが、注意点が2つある。

注意点：その1

ロードを実行した後、その接続を維持したままpersonテーブルに新たな行を挿入しようとすると、以下のようなエラーが発生することがある。(personテーブルへの挿入を行わないで接続の切断＆再接続を行っても同様)

DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or 
foreign key update caused by a DELETE statement are not valid because the 
primary key, unique constraint or unique index identified by "1" constrains 
table "DB2INST1.PERSON" from having duplicate values for the index key.  
SQLSTATE=23505

これは、原因は分からないが、採番される値がテーブル作成時の初期値(ここでは未指定なので「1」になる)をpersonテーブルのid列の値として挿入しようとして、主キー制約違反になるためである。(IDENTITY_VAL_LOCAL()は「3」を返すのに‥)

これを解決するための方法を探っていたが、ギブアップして、以下のサイトで答えを見つけた。

• 自動生成のID列のスタート値を初期値に戻すには | Unofficial DB2 BLOG

要は(今回の例の場合)、以下のSQL文を実行すればよいらしい。

ALTER TABLE person ALTER id RESTART WITH 4

注意点：その2

上記で基本的には問題ないのだが、接続を一旦切って再接続し、personテーブルにデータを挿入すると、idの値が「24」などとなる。

これは、「GENERATED ALWAYS AS IDENTITY」の後ろに隠れたデフォルト値が関係している。
指定可能なものの中に「CACHE」があるが、これのデフォルト値が「20」となっていて、DBサーバーに接続したときに「CACHE」で指定した数だけ、IDENTITY値をキャッシュするらしい。
本当に連番を振りたいという場合は、理屈上は「GENERATED ALWAYS AS IDENTITY (NO CACHE)」と指定すればいいのだが、パフォーマンスに何らかの影響が出ることは考えられる。