はじめに
DB2を使っているときに、「内容は問わない（キーとなる列のIDだけ付いていればよい）から大量のレコードを素早くデータベースに投入したい」ときのTipsです。
TPC-CやTPC-Hのデータを使う手もありますが、dbgenのようなデータ生成プログラムをダウンロードしてコンパイルするのも手間がかかります。今回のやり方は、数分で簡単なデータを生成する方法として重宝しています。
手順
共通表式を使って任意の数のレコードをSELECT結果として取得する
SQLの規格には「共通表式」という、SQLの表現力を強化する上で大変便利な機能があります。その共通表式を応用して、指定したレコード数を返却するように書くことができます。
たとえば下のSQLでは、"with"句で"temp"という共通表を定義しており、"as"以降の括弧の中で列をひとつだけ持つ表を記述しています。
with temp(a) as ( values(1) union all select a+1 from temp where a<5)
select
a,
current timestamp
from temp
このSQLをDB2に投入してみると、以下のように5件のレコードが返却されます。SQLの"with"句に含まれる"where a<5"で指定した回数分だけ"union all"で再帰的に参照してレコード数を増幅するような表現になっています。
$ db2 connect to sample
Database Connection Information
Database server = DB2/AIX64 10.5.6
SQL authorization ID = DB2V105
Local database alias = SAMPLE
$ db2 "with temp(a) as ( values(1) union all select a+1 from temp where a<5)
> select
> a,
> current timestamp
> from temp"
A 2
----------- --------------------------
1 2016-11-15-18.00.11.489419
2 2016-11-15-18.00.11.489419
3 2016-11-15-18.00.11.489419
4 2016-11-15-18.00.11.489419
5 2016-11-15-18.00.11.489419
5 record(s) selected.
SELECTから得られた結果を直接データベースに投入する（少量）
件数が少ないうちはSELECTの結果をそのままINSERT文に投入する"INSERT FROM SELECT"が使えます。前述したSELECTをINSERTの入力にする場合は、以下のように書きます。
connect to sample;
create table t1 (c1 int,c2 timestamp);
insert into t1
with temp(a) as ( values(1) union all select a+1 from temp where a<5)
select
a,
current timestamp
from temp;
select count(*) from t1;
INSERT INTO <表> <SELECT文>とINSERTの後ろにSELECTをそのまま続けて書くだけです。
この例では、以下のような流れで処理をしています。
- データベースへ接続
- INSERT対象の表を作成
- INSERTを実行
- 件数をカウント
ファイルでSQLを与える場合、ステートメントの終わりには";"で、区切りを表すのがDB2の（デフォルトの）お作法なので、省略しないように注意してください。
$ db2 -tvf insert_from_select.sql
connect to sample
Database Connection Information
Database server = DB2/AIX64 10.5.6
SQL authorization ID = DB2V105
Local database alias = SAMPLE
create table t1 (c1 int,c2 timestamp)
DB20000I The SQL command completed successfully.
insert into t1 with temp(a) as ( values(1) union all select a+1 from temp where a<5) select a, current timestamp from temp
DB20000I The SQL command completed successfully.
select count(*) from t1
1
-----------
5
1 record(s) selected.
SELECTから得られた結果を直接データベースに投入する（大量）
タイトルのように大量のレコードをまとめて投入したい場合は、SQLによるINSERTでは速度も遅くデータベースのトランザクション・ログがパンクする危険もあります。
そんなときに役立つのが、SELECT結果をLOADユーティリティに直接投入するテクニックです。
connect to sample;
create table t2 (c1 int,c2 int,c3 char(20), c4 timestamp, c5 char(100));
declare c1 cursor for
with temp(a) as ( values(1) union all select a+1 from temp where a<1000000)
select
a,
cast(rand() * 1000000 as int),
cast(rand() * 1000000 as char(20)),
current timestamp,
repeat('char string ',8)
from temp;
load from c1 of cursor replace into t2;
select count(*) from t2;
こんどの例ではdeclare c1 cursorというステートメントが登場しました。これは、"カーソル"を宣言するためのステートメントです。カーソルの詳細についてはWikipediaなどを見ていただくとして。SELECTステートメントに対するポインター（参照）のようなものだと思ってください。
ここでは"c1"という名前で100万件を返却するSELECTを定義していて、そのC1カーソルをload from c1という指定でLOADユーティリティの入力にしています。つまり、100万件のSELECTをクライアント側で受け取ってから投入するのではなく、ポインターとしてLOADユーティリティが直接読み取り、そのままデータベースへの入力として利用しています。
動かしてみると、以下のようになります。LOADユーティリティからのメッセージがたくさん出ていますが、要するにSELECTによって生成された100万件のデータが、LOADユーティリティによってデータベースに投入されたことがわかると思います。
$ db2 -tvf load_from_cursor.sql
connect to sample
Database Connection Information
Database server = DB2/AIX64 10.5.6
SQL authorization ID = DB2V105
Local database alias = SAMPLE
create table t2 (c1 int,c2 int,c3 char(20), c4 timestamp, c5 char(100))
DB20000I The SQL command completed successfully.
declare c1 cursor for with temp(a) as ( values(1) union all select a+1 from temp where a<1000000) select a, cast(rand() * 1000000 as int), cast(rand() * 1000000 as char(20)), current timestamp, repeat('char string ',8) from temp
DB20000I The SQL command completed successfully.
load from c1 of cursor replace into t2
SQL3501W The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.
SQL3039W The memory available to LOAD for DATA BUFFER prohibits full LOAD
parallelism. Load parallelism of "7" will be used
SQL1193I The utility is beginning to load data from the SQL statement " with
temp(a) as ( values(1) union all select a+1 from temp where ...".
SQL3500W The utility is beginning the "LOAD" phase at time "11/15/2016
20:08:06.922900".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "1000000" rows were read from
the input file.
SQL3519W Begin Load Consistency Point. Input record count = "1000000".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "11/15/2016
20:08:14.059412".
Number of rows read = 1000000
Number of rows skipped = 0
Number of rows loaded = 1000000
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 1000000
select count(*) from t2
1
-----------
1000000
1 record(s) selected.
手元の環境で動かしてみると、一昔前のサーバーでも10秒ぐらいで完了しました。複雑なデータの作成はなかなか難しいですが、ちょこっとしたテストデータの作成には便利です。
（おまけ）PostgreSQLだとどうなるか
どうやらPostgreSQLでも共通表式（共通テーブル式、CTE）が使える模様なので、どこまで同じようにできるか試してみました。PostgresSQLはあまり詳しくないので、もっといい方法があったら是非教えてください。MySQLも、8.0ではCTEに対応する様子ですね。そのうち試してみます。
PostgreSQLの場合は、再帰的な参照を可能にする場合には"RECURSIVE"というキーワードをWITH句に追加する必要があるようです。そのほか、現在時刻を返すためのキーワードを"CURRENT TIMESTAMP"から"CURRENT_TIMESTAMP"に変えています。
with RECURSIVE temp(a) as ( values(1) union all select a+1 from temp where a<5)
select a,
current_timestamp
from temp;'
これをPostgreSQLに投入すると、DB2と同じように5件の結果セットが戻ってきました。
$ sudo -u postgres -i psql -c 'with RECURSIVE temp(a) as ( values(1) union all select a+1 from temp where a<5)
> select a,
> current_timestamp
> from temp;'
a | now
---+-------------------------------
1 | 2016-11-16 13:58:50.305138+00
2 | 2016-11-16 13:58:50.305138+00
3 | 2016-11-16 13:58:50.305138+00
4 | 2016-11-16 13:58:50.305138+00
5 | 2016-11-16 13:58:50.305138+00
(5 rows)
INSERT FROM SELECTもできました。
$ sudo -u postgres -i psql -c '
> create table t2 (c1 int,c2 timestamp);
>
> insert into t2
> with RECURSIVE temp(a) as ( values(1) union all select a+1 from temp where a<5)
> select a,
> current_timestamp
> from temp;'
INSERT 0 5
SELECTすると、共通表式で指定した5件のデータが格納されています。
$ sudo -u postgres -i psql -c 'select * from t2;'
c1 | c2
----+----------------------------
1 | 2016-11-16 14:04:51.065885
2 | 2016-11-16 14:04:51.065885
3 | 2016-11-16 14:04:51.065885
4 | 2016-11-16 14:04:51.065885
5 | 2016-11-16 14:04:51.065885
(5 rows)
PostgreSQLのほうは、INSERT FROM SELECTだけで100万件が投入できてしまいました。
$ sudo -u postgres -i psql -c '
> create table t3 (c1 int,c2 timestamp);
>
> insert into t3
> with RECURSIVE temp(a) as ( values(1) union all select a+1 from temp where a<1000000)
> select a,
> current_timestamp
> from temp;'
INSERT 0 1000000
確かに100万件入ってそうな様子
$ sudo -u postgres -i psql -c 'select * from t3 order by c1 desc fetch first 10 rows only'
c1 | c2
---------+----------------------------
1000000 | 2016-11-16 14:07:13.553718
999999 | 2016-11-16 14:07:13.553718
999998 | 2016-11-16 14:07:13.553718
999997 | 2016-11-16 14:07:13.553718
999996 | 2016-11-16 14:07:13.553718
999995 | 2016-11-16 14:07:13.553718
999994 | 2016-11-16 14:07:13.553718
999993 | 2016-11-16 14:07:13.553718
999992 | 2016-11-16 14:07:13.553718
999991 | 2016-11-16 14:07:13.553718
(10 rows)