概要
DB2のデータをCSV形式でエクスポートし、bcpコマンドでSQL Serverにインポートする。
DB2からのエクスポート時に、bcpの仕様になるべく合わせてエクスポートする。
環境
- DB2 ver11.5(docker image)
- Red Hat Enterprise Linux release 8.6 (Ootpa) (DB2 docker image)
- SQL Server 2016
- Windows 10（bcpコマンド実行環境）
- bcp 15.0.2000.5
テストテーブル
数値、文字列、日付で考えてみた。
【DB2側】
db2 => DESCRIBE TABLE TBL1;
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
COL1 SYSIBM INTEGER 4 0 No
COL2 SYSIBM VARCHAR 50 0 Yes
COL3 SYSIBM CHARACTER 10 0 Yes
COL4 SYSIBM DATE 4 0 No
【SQL Server】
DB2に登録するデータ
文字列には、空文字、NULLを設定する。
INSERT INTO TBL1 (COL1, COL2, COL3, COL4) VALUES (12345, 'あいうえお', 'あ', CURRENT DATE);
INSERT INTO TBL1 (COL1, COL2, COL3, COL4) VALUES (67890, '', '', CURRENT DATE);
INSERT INTO TBL1 (COL1, COL2, COL3, COL4) VALUES (54321, NULL, NULL, CURRENT DATE);
DB2からデータをエクスポート
単純にDB2からエクスポートしてみる
db2 => export to /tmp/output.csv of DEL select * from TBL1;
output.csv
char型は空文字をINSERTしても桁数分半角スペースが設定される。
以下のような問題があるため、このままではbcpでは登録できない。
- 日付がCASTでエラー
- bcpの設定に依存するが、ダブルクォーテーションが削除されない。
DB2のexport文の変更
b2cで扱えるデータを出力するよう、export文に対して以下の修正を行う。
- 日付のフォーマットをYYYY-MM-DD
- 空文字の場合、制御コードをファイルに書き込む
- ダブルクォーテーションを出力しないようにする。
db2 => export to /tmp/output2.csv of DEL MODIFIED BY NOCHARDEL
select
COL1,
CASE WHEN COL2 = '' THEN x'00' ELSE COL2 END AS COL2,
COL3,
TO_CHAR(COL4,'YYYY-MM-DD') AS COL4
from TBL1
;
outpu2.csv
テキストファイルで見ると半角スペース1文字であるが、0x00が設定されている。
bcpコマンドでインポート
ファイルをWindowsに持ってきてbcpコマンドを実行する。入力ファイルの文字コードがutf8、改行コードがLFのためオプションにそれぞれ、「-C 65001」 、「-r 0x0A」に指定してインポート
bcp dbo.tbl1 in "output2.csv" -C 65001 -r 0x0A -c -t, -S <サーバ> -U <ユーザ> -P <パスワード> -d TEST
インポート結果
この場合は、インポートすることができたが、文字列に「,」が混じるとエラーになる。
ダブルクォーテーション付きでDB2からエクスポート
文字列に「,」が混じっていた場合、区切り文字の「,」との区別がつかず、bcpによるインポート処理に失敗する。
「,」が文字列に含まれている場合も扱えるよう、文字列をNULLの場合も含めてダブルクォーテーション付きでエクスポートするようにexportコマンドを変更する。
事前に、文字列のテストデータを"あいうえお"→""あいうえお,"に変更してエクスポートを実行する。
db2 => export to /tmp/output3.csv of DEL
select
COL1,
CASE WHEN COL2 IS NULL THEN '' WHEN COL2 = '' THEN x'00' ELSE COL2 END AS COL2,
CASE WHEN COL3 IS NULL THEN '' ELSE COL3 END AS COL3,
TO_CHAR(COL4,'YYYY-MM-DD') AS COL4
from TBL1
;
output3.csv
フォーマットファイルを使用してbcpコマンドでインポート
区切り文字が「,」ではないのでフォーマットファイルを作成し、区切り文字を変更してインポート時に指定する。
bcpコマンドでフォーマットファイルの雛形を作成して、編集する。以下のコマンドで、フォーマットファイルの雛形「test.fmt」を作成する。
bcp test.dbo.tbl1 format nul -c -f test.fmt -S <サーバ> -U <ユーザ> -P <パスワード>
フォーマットファイルの形式については以下を参考にした。
区切り文字編集後のtest.fmt
ダブルクォーテーションをエスケープしているため見にくいが、区切り文字を「,」カラムに合わせて「,"」や「","」に変更している。
フォーマットファイルを使用してbcpコマンドを実行
フォーマットファイルを使用する場合、オプションの「-C 65001」 、「-r 0x0A」が効かなかったので、入力ファイルの文字コードを事前にutf8からsjisに変更し、改行文字をLFかCRLFに変更する。
変更後のファイルを入力ファイルに指定して、コマンドを実行する。
bcp dbo.tbl1 in "output3_sjis.csv" -f test.fmt -S <サーバ> -U <ユーザ> -P <パスワード> -d TEST
インポート結果
