DB2を使ってアプリ開発を行う際によく使うコマンドです。DB2チートシートと大半が重複していますが、「アプリエンジニアが」「現場で使う」という観点からサンプルと解説をまとめています。
##環境構築
###DBを作る
よく使うテンプレ。
$ db2 create database DBNAME codeset UTF-8 territory JP pagesize 8K
文字コードと照合順序（ソート時のルール）はDB作成後に変更できないので注意。UTF-8の場合は照合順序はどれを指定しても変わらないので上記の例では指定していない。UTF-8以外の場合は日本語環境であれば照合順序はIDENTITY（バイト単位での比較を行う）とするのが基本で、collate identityを指定する。
ページサイズ（データを格納する際の最小単位であるページの大きさ）はDB作成後でもテーブル作成時に変更可能だが、ローカルで動かす分にはDB作成時に（デフォルトの）ページサイズを指定してしまうのが楽1。ページサイズは4KB・8KB・16KB・32KBから選択でき、それぞれ1レコードあたりの行長、列数の上限が異なる。列数がやたらと多いテーブルが現れた時に困らないように、8KBを選択するのが無難。
| ページサイズ | 行長(byte) | 列数 |
|---|---|---|
| 4KB | 4005 | 500 |
| 8KB | 8101 | 1012 |
| 16KB | 16293 | 1012 |
| 32KB | 32677 | 1012 |
###DBを設定する
DB2の構成パラメータは、構成単位に合わせて以下の3種類がある。
- レジストリ変数（DBサーバー全体の設定。
db2setで更新） - DBM構成パラメータ（インスタンス単位の設定。
db2 update dbm cfgで更新） - DB構成パラメータ（DB単位の設定。
db2 update db cfgで更新）
これらのうち、アプリ観点で変更が必要になるのはほとんどの場合DB構成パラメータのみ。
①設定値を確認する
$ db2 get db cfg for DBMANE
=>
データベース構成リリース・レベル = 0x1000
データベース・リリース・レベル = 0x1000
データベース・テリトリー = JP
データベース・コード・ページ = 1208
データベース・コード・セット = UTF-8
データベース国/地域コード = 81
データベース照合順序 = IDENTITY
代替照合シーケンス (ALT_COLLATE) =
NUMBER データ・タイプの互換性 = OFF
VARCHAR2 データ・タイプの互換性 = OFF
データ・タイプ DATE の TIMESTAMP(0) への互換性 = OFF
データベース・ページ・サイズ = 8192
（以下省略）
②設定値を更新する
$ db2 update db cfg for DBNAME using LOGFILSIZ 100000
# using KEY VALUEの形式でパラメータを指定する
③インスタンスの再起動
$ db2stop
$ db2start
パラメータはたくさんあるが、特に重要なものを以下に記載する。ローカルで動かす際によく変えるのは、上のサンプルでも更新しているLOGFILSIZ。
| パラメータ | 説明 | デフォルト値 |
|---|---|---|
| DLCHKTIME | デッドロックのチェックインターバル(ミリ秒) | 10000 |
| LOCKTIMEOUT | ロックタイムアウト(秒)。デフォルト(-1)の場合はタイムアウトしないので適当な値に設定した方がいい。 | -1 |
| LOGFILSIZ | トランザクションログファイルのサイズ(4KB単位)。デフォルト(4000KB)だと大きなSQLが流せないので、100000くらいにするといい。 | 1000 |
| AUTO_RUNSTATS | ONの場合、自動でRUNSTATS(統計情報更新)が行われる。これを本番運用でOFFにする場合、別途RUNSTATSを実行するジョブを用意する必要があるので注意。 | ON |
| AUTO_REORG | ONの場合、自動でREORG(再編成)が行われる。デフォルトでOFFのため本番運用する際は自動にするのか、別途ジョブを用意するのかプロジェクトで取り決めが必要。 | OFF |
| CUR_COMMIT | 分離レベルがReadCommited(CursorStability)の場合に、Xロック中のレコードを別トランザクションで読もうとした時の挙動を決める。ONの場合、更新前のデータを返す(Oracleと同じ動作)。OFFの場合、更新中のトランザクションがコミットされるまで待つ。詳細はこのスライドの45ページ以降を参照。 | ON |
###バックアップ・リストアを行う
手元のデータを入れ替える目的でよく使うのは、(オンライン)フルバックアップ→リストアの流れ。
①バックアップ
$ db2 backup database DBNAME to DIRNAME compress
# incrementalを指定すると差分バックアップ、delteを指定すると増分バックアップを取得
②リストア
$ db2 restore database DBNAME from DIRNAME
# DIRNAMEはディレクトリ名でバックアップファイル名でない点に注意
###リモートDBをカタログする
リモートDBに接続するには、カタログ（接続情報の事前登録）が必要。もちろん、サーバーにsshで入って直接操作してもいい。
①ノードの登録（ノード=DB2のインスタンス）
$ db2 catalog tcpip node NODENAME remote HOSTNAME server 50000
# ポート番号が50000でない場合は適当に変更すること。NODENAMEには分かりやすい名前をつける
②エイリアスの登録（エイリアス=DBの別名）
$ db2 catalog db DBNAME as ALIASNAME at node NODENAME
# NODENAMEは①で登録したのと同じものを指定する。
以上で、db2 connect to ALIASNAMEでリモートDBに接続できるようになる。上記の設定を解除する場合はuncatalogを使う。
$ db2 uncatalog node NODENAME
$ db2 uncatalog database DBNAME
##DB操作
DBへの接続、接続解除を行う
DBレベルの操作を行う際は、まずDBへ接続する。
①接続
$ db2 connect to DBNAME [user USERNAME using PASSWORD]
②接続解除
$ db2 terminate
###SQLを実行する
SQLの動作確認が目的であればターミナルよりもGUIツールを使った方がいい。公式のツールとしては、Data StudioというものがIBMから無償で提供されている。Macには対応していないので、MacユーザーであればSQL Workbenchなどの汎用的なSQLクライアントを使う。
どうしてもターミナルで実行したいのであれば以下のようにする。
$ db2 "select * from employee"
###スキーマ名を指定する
SQL・コマンド中のテーブル名にスキーマ名が指定されていない場合、DB2は暗黙的にログインユーザー名をスキーマ名として補完する。例えば、db2inst1としてログインしている場合、select * from employeeはselect * from db2inst1.employeeとして実行される。これだと困る場合は以下のようにして補完するスキーマ名を指定することができる。
$ db2 set schema SCHEMANAME
データをファイルに書き出し、ファイルから読み込みを行う
テストデータ準備とか、初期データのセットアップに使う。
①ファイルにexport
db2 export to FILENAME of del "select * from employee"
# of delの指定によりカンマ区切りで出力が行われる。他の出力フォーマットとしてixf(DB2バイナリ形式)がある
②ファイルからimport
db2 import from FILENAME of del [insert|replace] into TABLENAME
# insertは追加、replaceは洗い替え
③ファイルからload
db2 load from FILENAME of del [insert|replace] into TABLENAME nonrecoverable
# loadはトランザクションログを残さないのでimportよりも高速
# loadが失敗したテーブルはロードペンディング状態というアクセスできない状態になるため、この例ではnonrecoverableオプションを指定して、リカバリー不可能なトランザクションとして実行している(本番環境では使う際は注意)
###ファイル中のコマンド・SQLを実行する
DDLやよく使うimport/exportコマンドはファイル化して実行する。
$ db2 -tvf FILENAME
# オプションの意味は以下の通り
# -t: セミコロンをステートメントの終了文字とする
# -v: 実行結果を標準出力に出す
# -f: ファイルを入力にする
###DBをメンテナンスする
REORG（再編成）とRUNSTATS（統計情報更新）は自動保守機能を使うか専用のジョブで実行するのが通常で、インフラエンジニアが設計を担当することが多いため自分で実行することは少ない2。詳しく知りたければIBMの記事を参照。
①再編成
$ db2 reorg table TABLENAME
②統計情報の更新
$ db2 runstats on table TABLENAME on all columns
ただし、RUNSTATSに関してはアプリから大量のデータ更新（全レコードの10~20%が目安）を行った直後に実行した方が良いケースもある。JDBCドライバ経由ならばADMIN_CMDストアドプロシージャーを利用して、以下のように実行できる。
public boolean runstats(String tableName) throws SQLException {
String cmd = String.format("CALL SYSPROC.ADMIN_CMD('RUNSTATS ON TABLE %s ON ALL COLUMNS')", tableName);
try(Connection conn = getConnection()) {
try(CallableStatement cs = conn.prepareCall(cmd)) {
return cs.execute();
}
}
}
##デバッグ
###SQLExceptionの原因を調べる
エラーコードでググってもいいけど、インターネットに繋がらない環境ではコマンドで調べられる。
db2 ? ERROR_CODE
###ロック待ちを解消する
デッドロックは自動ですぐに検知されるので3、ターミナル経由でinsertしてコミットしないまま家に帰った輩がいるとか、大量のデータ更新中にバッチがハングして他の処理がロック待ちで動かないとかのケースで使う。この手の問題が起こった場合、他のアプリが止まっても構わないのであればすべてのクライアント接続を切断してしまうのが手っ取り早い。
$ db2 force applications all
少しこだわって、ロックをつかみっぱなしのクライアント接続だけを切断したい場合は以下のステップを踏む。コマンド出力例はDB2の公式マニュアルを引用した。
①ロック状況、ロック待ちを調べる
$ db2pd -db DBNAME -locks
=>
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HldCnt Att ReleaseFlg
0x07800000202E5238 3 00020002000000040000000052 Row ..X G 3 1 0 0x0000 0x40000000
0x07800000202E4668 2 00020002000000040000000052 Row ..X W* 2 1 0 0x0000 0x40000000
# TranHld: トランザクションID
# Mode: ロックのモード（典型的なものは、X:排他、U:更新、S:共有）
# Sts: ステータス（G:保有している、W:待機している）
# この場合、トランザクションID=3がXロックを保有しているので、トランザクションID=2のXロックが待機させられている
②ロックしているアプリケーションを調べる
$ db2pd -db DBNAME -transations
=>
Transactions:
Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2 Firstlsn Lastlsn LogSpace SpaceReserved TID AxRegCnt GXID
0x0780000020251B80 11 [000-00011] 2 4 READ 0x00000000 0x00000000 0x000000000000 0x000000000000 0 0 0x0000000000B7 1 0
0x0780000020252900 12 [000-00012] 3 4 WRITE 0x00000000 0x00000000 0x000000FA000C 0x000000FA000C 113 154 0x0000000000B8 1 0
# ①のトランザクションIDとアプリケーションID(AppHandl)を付き合わせると、ロックを保持しているアプリケーションIDは12で、ロックを待機しているアプリケーションIDは13と分かる。
③アプリケーションからの接続を強制的に切断する
$ db2 force applications (12)
さらに踏み込んで、アプリケーションが発行しているSQLを調べる場合は-applicationsオプションと-dnyオプションをつける。出力量が多いのでlessやmoreにリダイレクトした方がいい。
$ db2pd -db DBNAME -appications -dny
=>
Applications:
Address AppHandl [nod-index] NumAgents CoorPid Status C-AnchID C-StmtUID L-AnchID L-StmtUID Appid
0x07800000006879E0 12 [000-00012] 1 1073336 UOW-Waiting 0 0 17 1 *LOCAL.burford.060303225602
0x0780000000685E80 11 [000-00011] 1 1040570 UOW-Executing 17 1 94 1 *LOCAL.burford.060303225601
Copy
Dynamic SQL Statements:
Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text
0x07800000209FD800 17 1 1 1 2 2 update pdtest set c1 = 5
0x07800000209FCCC0 94 1 1 1 2 2 set lock mode to wait 1
# Applicationsセクションから、AppHandl→L-AnchIDと追い、Dynamic SQL StatementsセクションのAnchIDと突き合わせる。これによって、アプリケーションID=11は「set lock mode to wait 1」、アプリケーションID=12は「update pdtest set c1 = 5」というSQLを発行していると分かる。
###遅いSQLを突き止める
DB2で調べることもできるが、アプリログにSQL実行時間を出力して集計した方がいい。
-
ページサイズは、実際には表スペース（表データを格納する論理的な領域）に対して指定する。例えば、データベース作成時にページサイズを4KBとした場合にページサイズ32KBのテーブルを作りたければ、32KBの表スペース作成し、その表スペースにテーブルを作ればいい。ただし、既存の表スペースとページサイズが異なる表スペースを新しく作る場合、同じページサイズの一時表スペース（ソート、JOINなどに使う一時的な領域）とバッファプール（データ、インデックスをキャッシュする領域）も作らなくてはいけなく管理が複雑になる。DB作成時にデフォルトのページサイズを指定しておけば、同じページサイズで表スペースやバッファプールが自動的に作られるので手間がかからない。 ↩
-
RUNSTATSはデフォルトで自動実行されるが、REORGはされない。そのため、インフラエンジニア（あるいはDBA）が無能だったり、やる気がなかったり、他人任せだったりすると、REORGを行う設定・設計をしないまま本番リリースを迎えてしまうことがあるので注意。やがて断片化が進んでいくと、SQLのパフォーマンスが急激に低下する。 ↩
-
DB構成パラメータDLCHKTIMEで決まる。デフォルトでは10秒 ↩