はじめて使うDb2 - コマンドの実行、基本操作

WSLの上でUbuntu, Db2を動かす において、Db2のインストールから、SAMPLEデータベースの作成までの手順を書いたので、今回は、Db2の基本操作方法を書いていきたい。
コマンドや、実行例は、Linuxのコマンドラインを前提とする。
コマンドは、すべてインスタンスオーナーで実行している。Windowsの場合は、AdministratorまたはDb2の導入ユーザーで操作する。

#1. Db2の起動/停止
Db2のプロセス単位は、インスタンスと呼ばれる。データベースはインスタンスの上(というか、インスタンスプロセスの中）に作成される。
##インスタンスを起動する

$ db2start
07/02/2021 13:04:56     0   0   SQL1063N  DB2START の処理が正常に終了しました。
SQL1063N  DB2START の処理が正常に終了しました。

##インスタンスを停止する

$ db2stop
2021-07-02 13:06:18     0   0   SQL1064N  DB2STOP の処理が正常に終了しました。
SQL1064N  DB2STOP の処理が正常に終了しました。

#2. データベースの操作

##インスタンスに作成済みのデータベースをリストする
下の実行例では、ENCDBとSAMPLEという名前の二つのデータベースがあることが分かる。

$ db2 list db directory

 システム・データベース・ディレクトリー

 ディレクトリー中の項目数 = 2

データベース 1 項目:

 データベース別名                     = ENCDB
 データベース名                             = ENCDB
 ローカル・データベース・ディレクトリー = /dbfs
 データベース・リリース・レベル       = 15.00
 コメント                       =
 ディレクトリー項目タイプ             = 間接
 カタログ・データベース・パーティション番号 = 0
 代替サーバー・ホスト名               =
 代替サーバーのポート番号             =

データベース 2 項目:

 データベース別名                     = SAMPLE
 データベース名                             = SAMPLE
 ローカル・データベース・ディレクトリー = /dbfs
 データベース・リリース・レベル       = 15.00
 コメント                       =
 ディレクトリー項目タイプ             = 間接
 カタログ・データベース・パーティション番号 = 0
 代替サーバー・ホスト名               =
 代替サーバーのポート番号             =

##データベースを活動化する
db2 activate db DB名

$ db2 activate db sample
DB20000I  ACTIVATE DATABASE コマンドが正常に完了しました。

データベースの活動化は、コマンドで明示的に実行しなくても、ユーザーが接続要求すると、自動的に活動化される。よって、自分だけのテスト環境であれば、activate dbは不要である。
しかし、自動的に活動化されたデータベースは、接続ユーザーがいなくなると、自動的に非活動化される。
本番業務システムで、ユーザーが少ない時間帯に、頻繁に落とし上げが発生したり、データベース起動時の負荷のため不幸にも最初に接続したユーザーのレスポンスが遅れたりすることがある他、クラッシュリカバリ時には最初の接続をトリガーに索引再作成が始まるというようなことが起こるので、お作法としてactivate dbは行う方がよい。

##データベースを非活動化する

db2 deactivate db DB名

$ db2 deactivate db sample
DB20000I  DEACTIVATE DATABASE コマンドが正常に完了しました。

##データベースに接続する
db2 connect to DB名 [user ユーザー名] [using パスワード]

$ db2 connect to sample

   データベース接続情報

 データベース・サーバー = DB2/LINUXX8664 11.5.6.0
 SQL 許可 ID            = DB2I115
 ローカル・データベース別名 = SAMPLE

DBサーバー上でのローカル接続の場合、上例のようにconnect to DB名と実行すると、そのコマンドの実行ユーザーIDで接続される。
別ユーザーとして接続したい場合は、USER ～USING ～ を指定する。
db2コマンドに対するPATHや、使用するDb2インスタンスなどの環境変数の設定は、インスタンスオーナーのsqllibディレクトリ下のdb2profileファイル /home/インスタンスオーナー名/sqllib/db2profile にある。インスタンスオーナー以外のユーザーでDb2を使用したい場合は、この.profileを実行して、環境変数を設定する。

#3. コマンドおよびSQLの実行

Db2のコマンドには、以下の2種類がある。

コマンドの種類	実行インタフェース	例
システムコマンド	OSのコマンドライン	db2start, db2stop
CLPコマンド	CLP(コマンドラインプロセッサ)	activate db

システムコマンドは、OSのコマンドラインから実行し、原則としてdb2xxxx のように、db2という文字で始まる。
CLPコマンドは、CLPインタフェースで実行する。先頭にDB2のような文字はつかない。
CLPコマンドを打つためには、以下の二つの方法がある。

(1) 対話モード：CLP環境を起動して、CLPのプロンプトが出た状態でコマンドを実行する
(2) コマンドモード：OSコマンドラインから、DB2というコマンドの引数としてCLPコマンドを実行する

SQLは、CLPコマンドと同様に、CLPの対話モード、またはコマンドモードで実行できる。

##CLP対話モード
DB2コマンドによってCLP対話モードを開始し、db2 => プロンプトの後にコマンドを入力して実行する。
OracleのSQL*PlusでSQLを実行しているのと、似たような感じである。

###CLP対話モードを開始する

db2コマンドをそのまま実行すると、対話モードになる。
コマンドは、改行によって実行される。

$ db2
(c) Copyright IBM Corporation 1993,2007
DB2 クライアント 11.5.6.0 コマンド行プロセッサー

コマンド・プロンプトからデータベース・マネージャーのコ
マンド、 および SQL ステートメントを呼び出せます。 例:
    db2 => connect to sample
    db2 => bind sample.bnd

一般ヘルプには ? をタイプしてください。
コマンドのヘルプには ? command をタイプしてください。
command には、データベース・マネージャー・コマンドの
最初のいくつかのキーワードを入力します。 例:
 ? CATALOG DATABASE は CATALOG DATABASE コマンドのヘルプです。
 ? CATALOG          は全 CATALOG コマンドのヘルプです。

db2 対話モードを抜けるには、コマンド・プロンプトで QUIT
とタイプしてください。
対話モード以外では、すべてのコマンドに接頭語 'db2'
を付ける必要があります。
現在のコマンド・オプションの設定をリストするには、
LIST COMMAND OPTIONS とタイプしてください。

詳細は、「オンライン・リファレンス」を参照してください
。

db2 => ここにコマンドを入力して、改行キーを押す.

###対話モードでDBに接続する

db2 => connect to sample

   データベース接続情報

 データベース・サーバー = DB2/LINUXX8664 11.5.6.0
 SQL 許可 ID            = DB2I115
 ローカル・データベース別名 = SAMPLE

###対話モードでOSコマンドを実行する
CLP対話モードから、OSコマンドを呼び出したい場合は、コマンドの頭に ! をつける。

db2 => !date
2021年  7月  2日 金曜日 16:32:11 JST

###SQLを実行する
対話モードでSQLを実行する。

db2 => create table test (id int not null, col1 varchar(100))
DB20000I  SQL コマンドが正常に完了しました。
db2 => insert into test values (1, '始めの一歩')
DB20000I  SQL コマンドが正常に完了しました。
db2 => select * from test

ID          COL1
----------- -----------------------------------------------------------------------------
          1 始めの一歩

  1 レコードが選択されました。

###対話モードで長いSQLを実行する
CLPでは、改行キーはコマンドの実行指示なので、単純に改行キーを押すと、そこでSQLが切れる。長いSQLで複数行に渡って入力を行いたい場合は、改行したい箇所に \ を入力する。

db2 => create table test2 (id int not null, \
db2 (続き) => col1 varchar(100), \
db2 (続き) => col2 dec(10,2))
DB20000I  SQL コマンドが正常に完了しました。

入力の終了、実行指示は、改行キーである。

###対話モードを終了する

db2 => quit
DB20000I  QUIT コマンドが正常に完了しました。

##CLPコマンドモード

CLPコマンドモードは、 db2 => のプロンプトを使わず、OSコマンドラインから、db2 コマンドを先頭につけて一行に一つ、コマンドを実行する。

###コマンドモードでのDB接続

$ db2 connect to sample

   データベース接続情報

 データベース・サーバー = DB2/LINUXX8664 11.5.6.0
 SQL 許可 ID            = DB2I115
 ローカル・データベース別名 = SAMPLE

###コマンドモードでのSQLの実行

$ db2 "create table test (id int, col1 varchar(100))"
DB20000I  SQL コマンドが正常に完了しました。
$ db2 "insert into test values (1, 'コマンドモード')"
DB20000I  SQL コマンドが正常に完了しました。
$ db2 "select * from test"

ID          COL1
----------- --------------------------------------------------------------------------------
          1 コマンドモード

  1 レコードが選択されました。

コマンドモードでの実行時には、* や () などの記号が、OSで解釈されてしまわないように、SQL全体をダブルクォートで囲むとよい。

###CLPにおけるコミット
CLPは、デフォルトでは、自動コミットモードである。つまり、一つのSQL文を実行すると、即座にそのSQLはコミットされる。
複数のSQLを実行しておいて、まとめてコミットしたい場合は、コミットしたくないSQLの前に、+cのオプションをつける。
-cは、自動コミットで、+cは自動コミットしない。感覚的に逆に思えてよく間違うが(少なくとも私はそう)、プラスがコミットしない方。
下は、3行のレコードをinsertしてrollbackした例。
+cをつけてinsertしたが明示的にcommitした一行目と、+cを指定せずデフォルトの自動コミットだった二行目はあるが、+cをつけてinsertして、そのままrollbackした三行目は消えている。

$ db2 "create table test (id int not null primary key, col1 varchar(100))"
DB20000I  SQL コマンドが正常に完了しました。
$ db2 +c "insert into test values (1, '一行目')"
DB20000I  SQL コマンドが正常に完了しました。
$ db2 commit
$ db2 "insert into test values (2, '二行目')"
DB20000I  SQL コマンドが正常に完了しました。
$ db2 +c "insert into test values (3, '三行目')"
DB20000I  SQL コマンドが正常に完了しました。
$ db2 rollback
DB20000I  SQL コマンドが正常に完了しました。
$ db2 "select * from test"

ID          COL1
----------- ----------------------------------------------------------------------------
          1 一行目
          2 二行目

  2 レコードが選択されました。

###ファイル入力によるSQLの実行(バッチモード)
Db2をよく使う人は、CLP対話モードより、コマンドモードを使うことの方が多い（と思う。私の周りではそう）。
OSのコマンドもエスケープ無しで打てるし、コマンドモードならではのバッチモードがあるからだ。
バッチモードは、ファイルにSQL文を書いておいて、ファイル名を指定して実行する方法である。

入力ファイル(test.sql)の内容

-- create table
create table test (id int not null,
                   col1 varchar(100));
-- データの投入
insert into test values (1, 'お試し');
insert into test values (2, 'もういっちょ');

-- select
select * from test;

-- drop tabel
drop table test;

ファイル中には、SQLを書く。
改行は適宜入れてよい。
SQL文の最後は、セミコロン ; で示す。
コメントは、ハイフン2つで始める。

実行は、以下のシンタックスで行う。
db2 -tvf ファイル名
-t：終端文字の指定。デフォルトがセミコロン。
-v：入力コマンドを標準出力にエコーする。
-f: 入力ファイル

$ db2 drop table test
DB21034E  コマンドが、有効なコマンド行プロセッサー・コマ
ンドでないため、 SQL
ステートメントとして処理されました。  SQL
処理中に、次のエラーが返されました。
SQL0204N  "DB2I115.TEST" は未定義の名前です。  SQLSTATE=42704
$ db2 -tvf test.sql
create table test (id int not null, col1 varchar(100))
DB20000I  SQL コマンドが正常に完了しました。

insert into test values (1, 'お試し')
DB20000I  SQL コマンドが正常に完了しました。

insert into test values (2, 'もういっちょ')
DB20000I  SQL コマンドが正常に完了しました。

select * from test

ID          COL1
----------- ------------------------------------------------------------------------------------
          1 お試し
          2 もういっちょ

  2 レコードが選択されました。


drop table test
DB20000I  SQL コマンドが正常に完了しました。

#4. 知っていると便利なコマンド
以下、よく使う、知っていると便利なコマンドを紹介する。
例はすべてOSコマンドラインからの実行例となる。

##表情報の取得

###データベースに定義されている表の一覧を取得する
タイプ列の記号の主なものは、以下のとおり。
T:表
A:別名(ALIAS)
V:ビュー
S:マテリアライズ照会表(MQT)

$ db2 list tables

表/ビュー                       スキーマ        タイプ 作成時刻
------------------------------- --------------- ----- --------------------------
ACT                             DB2I115         T     2021-06-28-19.54.17.007059
ADEFUSR                         DB2I115         S     2021-06-28-19.54.18.872658
CATALOG                         DB2I115         T     2021-06-28-19.54.33.845894
CL_SCHED                        DB2I115         T     2021-06-28-19.54.14.648341
CUSTOMER                        DB2I115         T     2021-06-28-19.54.21.277008
DEPARTMENT                      DB2I115         T     2021-06-28-19.54.14.813984
DEPT                            DB2I115         A     2021-06-28-19.54.15.077674
EMP                             DB2I115         A     2021-06-28-19.54.15.327167
EMPACT                          DB2I115         A     2021-06-28-19.54.17.005861
EMPLOYEE                        DB2I115         T     2021-06-28-19.54.15.078994
EMPMDC                          DB2I115         T     2021-06-28-19.54.19.157375
EMPPROJACT                      DB2I115         T     2021-06-28-19.54.16.851764
EMP_ACT                         DB2I115         A     2021-06-28-19.54.17.006552
                         :
                       以下略

###特定のスキーマ名の表の一覧を取得する
以下の例は、SYSCATスキーマのもの、つまりシステムカタログビューをリストしている。

$ db2 list tables for schema syscat

表/ビュー                       スキーマ        タイプ 作成時刻
------------------------------- --------------- ----- --------------------------
ATTRIBUTES                      SYSCAT          V     2021-06-28-19.52.59.697655
AUDITPOLICIES                   SYSCAT          V     2021-06-28-19.52.59.730695
AUDITUSE                        SYSCAT          V     2021-06-28-19.52.59.746300
BUFFERPOOLDBPARTITIONS          SYSCAT          V     2021-06-28-19.52.59.767614
BUFFERPOOLEXCEPTIONS            SYSCAT          V     2021-06-28-19.52.59.778816
BUFFERPOOLNODES                 SYSCAT          V     2021-06-28-19.52.59.788100
BUFFERPOOLS                     SYSCAT          V     2021-06-28-19.52.59.796748
CASTFUNCTIONS                   SYSCAT          V     2021-06-28-19.52.59.809168
CHECKS                          SYSCAT          V     2021-06-28-19.52.59.823562
COLAUTH                         SYSCAT          V     2021-06-28-19.52.59.834614
COLCHECKS                       SYSCAT          V     2021-06-28-19.52.59.846397
COLDIST                         SYSCAT          V     2021-06-28-19.52.59.861894
                         :
                       以下略

###ある表の列定義情報を表示する
スキーマ名を省略した場合は、ユーザー名がデフォルトのスキーマ名となる。

$ db2 describe table db2i115.employee

                            データ・タイ データ・         列の       スケ
列名                        プ・スキーマ タイプ名         長さ       ール  NULL
--------------------------- --------- ------------------- ---------- ----- ------
EMPNO                       SYSIBM    CHARACTER                    6     0 いいえ
FIRSTNME                    SYSIBM    VARCHAR                     12     0 いいえ
MIDINIT                     SYSIBM    CHARACTER                    1     0 はい
LASTNAME                    SYSIBM    VARCHAR                     15     0 いいえ
WORKDEPT                    SYSIBM    CHARACTER                    3     0 はい
PHONENO                     SYSIBM    CHARACTER                    4     0 はい
HIREDATE                    SYSIBM    DATE                         4     0 はい
JOB                         SYSIBM    CHARACTER                    8     0 はい
EDLEVEL                     SYSIBM    SMALLINT                     2     0 いいえ
SEX                         SYSIBM    CHARACTER                    1     0 はい
BIRTHDATE                   SYSIBM    DATE                         4     0 はい
SALARY                      SYSIBM    DECIMAL                      9     2 はい
BONUS                       SYSIBM    DECIMAL                      9     2 はい
COMM                        SYSIBM    DECIMAL                      9     2 はい

  14 レコードが選択されました。

###ある表に定義された索引の情報を取得する

$ db2 describe indexes for table employee

索引          索引           ユニーク    索引      索引              パーティ      NULL
スキーマ      名             規則        列数      タイプ            ョニング      キー
------------- -------------- ---------- --------- ---------------- -------------- ------
DB2I115       PK_EMPLOYEE    P                  1 RELATIONAL DATA    -              Y
DB2I115       XEMP2          D                  1 RELATIONAL DATA    -              Y

  2 レコードが選択されました。

describe indexes for table 表名 show detail のように show detail オプションをつけると、索引の定義された列名などの情報も表示される。

以上、マニュアルを見れば書いてあることだが、簡単なことは意外と見つけにくいので、まとめておいた。

#参考資料
Db2　V11.5マニュアル