PowershellでDB2のテーブルをcsv化する(列名付き！)

はじめに

DB2の何かしらのselect結果をcsvとして取得する必要があり、やってみました。

前提

Windows 10
Powershell 5.1
DB2 v10.1 (すごく古い)
DB2へODBC接続ができること

コード

冒頭の $settings を環境に応じて書き換えてください。

テーブルをcsv化

$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に強制され、ストレスフリーです。

参考

• db2cmdの説明
  https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0002036.html

• export時にcolumn namesが無くて困る件
  https://stackoverflow.com/questions/10122505/export-data-from-db2-with-column-names
  解決策は提示されていますが、column nameを固定で記述しなければならなくて、大量作業には不向きだと思います。