はじめに
DB2の何かしらのselect結果をcsvとして取得する必要があり、やってみました。
前提
Windows 10
Powershell 5.1
DB2 v10.1 (すごく古い)
DB2へODBC接続ができること
コード
冒頭の $settings を環境に応じて書き換えてください。
$settings = @{
"odbc_name" = "DB2"
"conn_user" = "USR1"
"conn_pass" = "PASS1"
"table_name" = "TABLE1"
}
##########################################################################
# 対象テーブルから主キーを抽出する
##########################################################################
$query = @"
connect to $($settings.odbc_name) user $($settings.conn_user) using $($settings.conn_pass);
export to temporary_p_keys.csv of del
select colname from syscat.keycoluse where tabname = '$($settings.table_name)'
order by colseq;
"@
$query | Out-File -Encoding default temporary.sql
db2cmd /c /w /i db2 -tvf temporary.sql | Out-Null
if($? -eq $False) {
Write-Host -ForegroundColor Red "ERROR: DB2へのクエリ発行に失敗しました。"
Write-Host ""
Write-Host "接続情報 :"
Write-Host "ODBC : $($settings.odbc_name) USER : $($settings.conn_user) PASSWORD : $($settings.conn_pass)"
Write-Host ""
Write-Host "発行クエリ :"
Write-Host $query
read-host "エラー内容を確認してください。[Enter]キーで終了"
exit -1
}
# 抽出した主キーをCSVから読み込んでarraylist化する
# arraylist化した主キーはorder by句で使用する
$p_keys = import-csv temporary_p_keys.csv -Header "COLNAME"
$order_keys = New-Object System.Collections.ArrayList
foreach ($p in $p_keys) { $order_keys.add($p.COLNAME) | out-null }
##########################################################################
# 対象テーブルから列を抽出する
##########################################################################
$query = @"
connect to $($settings.odbc_name) user $($settings.conn_user) using $($settings.conn_pass);
export to temporary_columns.csv of del
select colname
from syscat.columns
where tabschema=(select current_schema from dual)
and tabname = '$($settings.table_name)'
order by colno
;
"@
$query | Out-File -Encoding default temporary.sql
db2cmd /c /w /i db2 -tvf temporary.sql | Out-Null
if($? -eq $False) {
Write-Host -ForegroundColor Red "ERROR: DB2へのクエリ発行に失敗しました。"
Write-Host ""
Write-Host "接続情報 :"
Write-Host "ODBC : $($settings.odbc_name) USER : $($settings.conn_user) PASSWORD : $($settings.conn_pass)"
Write-Host ""
Write-Host "発行クエリ :"
Write-Host $query
read-host "エラー内容を確認してください。[Enter]キーで終了"
exit -1
}
# 抽出した列をCSVから読み込んでarraylist化する
# dump.csvのヘッダー行として使用する
$cols = import-csv temporary_columns.csv -Header "COLNAME" -Encoding Default
$headers = New-Object System.Collections.ArrayList
foreach ($c in $cols) { $headers.add($c.COLNAME) | out-null }
##########################################################################
# 対象テーブルをCSV化する
##########################################################################
$query = @"
connect to $($settings.odbc_name) user $($settings.conn_user) using $($settings.conn_pass);
export to temporary_body.csv of DEL
select * from $($settings.table_name)
order by $($order_keys -join ",")
;
"@
$query | Out-File -Encoding default temporary.sql
db2cmd /c /w /i db2 -tvf temporary.sql | Out-Null
if($? -eq $False) {
Write-Host -ForegroundColor Red "ERROR: DB2へのクエリ発行に失敗しました。"
Write-Host ""
Write-Host "接続情報 :"
Write-Host "ODBC : $($settings.odbc_name) USER : $($settings.conn_user) PASSWORD : $($settings.conn_pass)"
Write-Host ""
Write-Host "発行クエリ :"
Write-Host $query
read-host "エラー内容を確認してください。[Enter]キーで終了"
exit -1
}
# 最終的なCSVファイルを作成する
$headers -join "," | Out-File -Encoding default "$($settings.table_name)_dump.csv"
Get-Content "temporary_body.csv" | Out-File -Encoding default -Append "$($settings.table_name)_dump.csv"
Remove-Item temporary*
掲載用に改変・抜粋しています。
使い捨て感覚で書いてるので手続き型の書き方になっていて、冗長な箇所があると思います。
完成度を上げたいのであれば、以下のような改修をしたほうが便利になりそうです。
- db2cmdの実行部分をfunction化
- $settings を外だし
- エラー処理何とかする
- テーブル名は $settings じゃなく、read-hostで取得したほうが便利
経緯
大量のテーブルをcsv化する必要があり、人海戦術で対応する方針だったので、eclipseからselectしてcsv出力するつもりでした。
ですが、eclipseからのcsv出力で過去に正常に出力できなかったことがあったそうです。
その際にdb2cmdを使用したとのことで、全面db2cmdでexportすることにしました。
exportができて喜んでいたところ、以下の制約があることがわかりました。
- column名が付与されない
- 後々の工程の都合で各tableの主キーでorder byする必要がある
もうわかったよ。
全部自動化すればいいんだろう!?と作ったのが前述のコードです。
export時にcolumn名が付与されないのは、結構困っている方が居そうでした。
似たような境遇の方がいらっしゃいましたら活用ください。
DB2使ってる人は少数派ですかね…。
補足
DBへの接続部分がオーバーヘッドになるので、odbc経由でつながるなら、db2cmdなんて使わずに、System.Data.Odbc.OdbcConnectionを使って、接続を使いまわせば早くなるんじゃない?と思ってやってみました。
それはそれであっていると思うのですが、予想外の問題にあたりました。
Powershellを32bitで起動する必要がある
これが結構めんどうくさくて…。
というのも、すごくレガシーな環境に居るので、その他のツールが軒並み32bitで構成されています。
当然、ODBCも32bitで設定しています。
世の中は64bitになっているので、何も考えずにPowershellを起動すると64bitで起動してしまう…。
64bitのPowershellは、64bitのODBCを参照してしまうので、残念ながら動きません。
このスクリプトは、DB2、Powershellなんて知らない人でも使ってもらう予定なので、32bitで起動してください、というより、多少遅いけど、db2cmdを使う方法のままにすることにしました。
db2cmdは32bitしかインストールされていない環境なので、db2cmdを起動するだけでODBCも32bitに強制され、ストレスフリーです。
参考
export時にcolumn namesが無くて困る件
https://stackoverflow.com/questions/10122505/export-data-from-db2-with-column-names
解決策は提示されていますが、column nameを固定で記述しなければならなくて、大量作業には不向きだと思います。