まだプログラマーですが何か?

プログラマーネタとアスリートネタ中心。たまに作成したウェブサービス関連の話も http://twitter.com/dotnsf

タグ:db2

先日、コマンドラインツールで IBM Cloud 上の Db2 サービスインスタンスに接続する、というエントリを書きました:
Db2 コマンドラインインターフェースから IBM Cloud 上のリモート Db2 インスタンスに接続する


接続するまでの設定項目は上記エントリに書いてあるのですが、実際に接続したあとに SQL を実行する上でコマンドを実行する際のチートシート的なまとめを書いておきます。

まずは前回も書いた DB2 のスタート。db2inst1 ユーザーになってから db2start コマンドを実行します(青字は実行結果です):
$ su - db2inst1
$ db2start
05/27/2018 20:13:33     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

データベースの一覧を確認する場合は list db directory を実行します:
$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 3

Database 1 entry:

 Database alias                       = REMOTEDB
 Database name                        = BLUDB
 Node name                            = DASHDB
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = JSONDB
 Database name                        = JSONDB
 Local database directory             = /home/db2inst1
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Database 3 entry:

 Database alias                       = MYDB
 Database name                        = MYDB
 Local database directory             = /home/db2inst1
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

上記コマンドでデータベースの一覧を確認した上でどこかのデータベース(下の例では mydb)に接続します:
$ db2 connect to mydb

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.1.2.2
 SQL authorization ID   = DB2INST1
 Local database alias   = MYDB

データベースに接続した上で、テーブルの一覧を照会する場合は list tables を実行します:
$ db2 list tables

Table/View                      Schema          Type  Creation time
------------------------------- --------------- ----- --------------------------
GOKON                           DB2INST1        T     2018-05-23-19.09.07.748477

  1 record(s) selected.


DB2 コマンドをそのまま実行するとインタラクティブモードになります:
$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 11.1.2.2

You can issue database manager commands and SQL statements from the command
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 =>

インタラクティブモードでは接続中のデータベースに対して、SQL を直接実行することができます:
db2 => select * from gokon

GIRL_ID     GNAME      AGE         GOKON_DATE
----------- ---------- ----------- ----------
          2 Beth                19 2016-01-01
          3 Karmen              34 1990-10-01
          4 Hanako              69 2009-05-01

  3 record(s) selected.

インタラクティブモードから抜けるには quit を実行します:
db2 => quit
DB20000I  The QUIT command completed successfully.


最後に DB2 を停止します:
$ db2stop
2018-05-27 20:10:25     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.

管理系コマンドについてはノータッチでしたが、とりあえずデベロッパーである自分が最低限使いそうなコマンドはこんなもんでしょうかね。。

 

以前に Linux 版 Db2 を導入した時の手順を紹介したブログがありました:
http://dotnsf.blog.jp/archives/2960284.html


3年以上前のこの時の仮想環境が奇跡的に残っていたので久しぶりに使ってみました。で、折角の Db2 コマンドライン環境(?)なので、このコマンドラインインターフェースから IBM Cloud 上の DBaaS である Db2 に接続して使ってみました。以下、その手順の紹介です。

まずは IBM Cloud にログインして Db2 インスタンスを作成します。無料のライトアカウントでも使えるサービスが用意されているので、派手に使わない限りは無料で試すこともできます:
2018052302


無料のライトプランを使う場合はデプロイ先ロケーションを「米国南部」に設定します。加えて自分のメールアドレスを指定します:
2018052303


デプロイ先ロケーションが米国南部の場合、無料の Lite プランが選択可能になります(もちろん他のプランでも構いません)。プラン設定後に「作成」ボタンでインスタンスが作成されます:
2018052304



インスタンス作成後、「サービス資格情報」メニューから「資格情報の表示」を選択します(「資格情報の表示」が存在しない場合は、最初に「新規資格情報」をクリックして作成し、その後に改めて「資格情報の表示」を選択します)。すると画面下部に JSON フォーマットで接続先サーバー名やユーザー、パスワードといった情報を確認できます:
2018052301


この中で、今回使うのは以下の5つの情報です:
 - hostname(接続先のサーバー名)
 - password(接続時のパスワード)
 - port(接続先のポート番号)
 - db(データベース名)
 - username(接続時のユーザー名)


以下では、これら5つの値がこのような内容になっていたと仮定して説明を続けます:
{
  "hostname": "dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net",
  "password": "xxxxxxxx",
  "port": 50000,
  "db": "BLUDB",
  "username": "lrz77612",
    :
}



ではこの DBaaS 上のデータベース(BLUDB)にコマンドラインから接続するための手順を紹介します。


db2inst1 ユーザーへの変更

ここから下のコマンドはすべて db2 インストール時に指定したユーザー(今回の例では db2inst1)に切り替えて行う必要があります。というわけでターミナル上でのユーザーを切り替えます:
$ su - db2inst1

ノードカタログの確認

接続先となる Db2 サーバーはサーバーノードとしてカタログに記録されている必要があります。というわけで、まず現在のノードカタログを確認します(青字は出力結果):
$ db2 list node directory
SQL1037W  The node directory is empty.  SQLSTATE=01606

この出力結果によるとノードカタログは空で何も登録されていないようです。


ノードのカタログ登録

というわけで今回の接続先となる IBM Cloud 上の Db2 サーバーをカタログに登録します。以下のように接続先のサーバー名とポート番号を指定し、今回は "dashdb" という名前でカタログに登録しています(太字部分は各自の環境や希望に併せて変更してください):
$ db2 catalog tcpip node dashdb remote dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net server 50000
DB20000I  The CATALOG TCPIP NODE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.
成功したようです。この状態で再度ノードカタログを確認すると、今度は dashdb という名前のカタログが確認できるはずです:
$ db2 list node directory

 Node Directory

 Number of entries in the directory = 1

Node 1 entry:

 Node name                      = DASHDB
 Comment                        =
 Directory entry type           = LOCAL
 Protocol                       = TCPIP
 Hostname                       = dashdb-txn-sbox-yp-dal09-03.services.dal.bluemix.net
 Service name                   = 50000



ノードカタログを削除

今回は作業しませんが、登録したカタログを削除する場合はカタログ名を指定して以下のように実行します:
$ db2 uncatalog node dashdb


エイリアスの確認

DB2 でデータベースに接続するにはデータベースをエイリアスとして登録する必要があります。というわけで次に接続先となるリモートデータベースのエイリアスを作成します。まずは現在登録されているエイリアスの一覧を確認します:
$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 2

Database 1 entry:

 Database alias                       = JSONDB
 Database name                        = JSONDB
 Local database directory             = /home/db2inst1
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = MYDB
 Database name                        = MYDB
 Local database directory             = /home/db2inst1
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =


この例では(いずれもローカルサーバー内の)JSONDB と MYDB という2つのデータベースエイリアスが登録されていました。この例ではリモートサーバーのエイリアスは登録されていません。


エイリアスの登録

では先程作成した dashdb ノード上の接続先データベース(BLUDB)を "remotedb" という名前でエイリアス登録します:
$ db2 catalog database BLUDB as remotedb at node dashdb
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

成功したようです。念の為もう1回エイリアス一覧を確認します:
$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 3

Database 1 entry:

 Database alias                       = REMOTEDB
 Database name                        = BLUDB
 Node name                            = DASHDB
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

Database 2 entry:

 Database alias                       = JSONDB
 Database name                        = JSONDB
 Local database directory             = /home/db2inst1
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

Database 3 entry:

 Database alias                       = MYDB
 Database name                        = MYDB
 Local database directory             = /home/db2inst1
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =


エイリアスが2つから3つに増え、REMOTEDB が新たに登録されました。


エイリアスを削除

この作業も今回は不要ですが、登録したエイリアスを削除する場合はエイリアス名を指定して次のように実行します:
$ db2 uncatalog db remotedb


エイリアスを指定して接続

では改めてエイリアスを作ったデータベースに接続します。接続時にはユーザー名とパスワードが必要になるので、以下のように指定します:
$ db2 connect to remotedb user lrz77612 using xxxxxxxx

   Database Connection Information

 Database server        = DB2/LINUXX8664 11.1.2.2
 SQL authorization ID   = LRZ77612
 Local database alias   = REMOTEDB

IBM Cloud 上のデータベースに接続できました。後はこのリモートデータベースに対して各種 SQL を実行してテーブルやデータの作成/更新/削除/検索といったコマンドが実行できます。


接続しているエイリアスから切断

なお、接続しているエイリアスから切断する場合は以下のコマンドを実行します:
$ db2 terminate




IBM Bluemix からも提供されている IBM の DBaaS サービスである dashDB に Node.js からアクセスする方法を紹介します。実際には dashDB だけでなく、DB2 のサービスやオンプレミスデータベースへも同様に応用できますが、今回は Bluemix 上の DB2/dashDB 関連サービスを例に紹介します:
2017063002


dashDB は行指向/列指向型のテーブルをどちらも作成することができるリレーショナル・データベースのサービスですが、そのデータベースシステムとしての実体は IBM DB2 です。というわけで、このライブラリを使ってアクセスします:
https://www.npmjs.com/package/ibm_db

2017063001


まず以下のコマンドを実行して ibm_db をインストールします(このコマンドだけで DB2 ODBC Driver ごとインストールされます):
$ npm install ibm_db


そして以下のようなコードを用意して dashDB にアクセスします:

(settings.js)
exports.db_host = 'dashdb-entry-yp-XXXXXXXX.services.dal.bluemix.net';
exports.db_port = 50000;
exports.db_name = 'BLUDB';
exports.db_username = 'dashNNNN';
exports.db_password = 'PASSWORD';

(sample.js)
var ibm_db = require( 'ibm_db' );
var settings = require( './settings' );

var db_str = "DATABASE=" + settings.db_name
  + ";HOSTNAME=" + settings.db_host
  + ";UID=" + settings.db_username
  + ";PWD=" + settings.db_password
  + ";PORT=" + settings.db_port
  + ";PROTOCOL=TCPIP";
var sql = "select OBJECTID, NAME from SAMPLES.GEO_CUSTOMER limit 10";

ibm_db.open( db_str, function( err, conn ){
  if( err ) return console.log( err );

  conn.query( sql, function( err, data ){
    if( err ) console.log( err );
    else console.log( data );

    conn.close( function(){
      console.log( 'done.' );
    });
  });
});

settings.js の中身はユーザー名やパスワードといった dashDB に接続するためのサービス資格情報です。IBM Bluemix の画面から取得できる値を使って、実際の値で書き換えて使ってください:

2017063003


アプリケーションの実体は sample.js です。今回の例ではシンプルに接続して、サンプルデータとして GEO_CUSTOMER テーブルから OBJECTID と NAME の値を 10 件だけ取得する、という SQL (青字部分)を実行しました。また settings.js で定義した情報を取り出して接続文字列(赤字部分)を生成しています。

node コマンドで sample.js を実行して、以下のような結果が表示されれば成功です:
$ node sample.js
[ { OBJECTID: 1322, NAME: 'Kami Labarbera' },
  { OBJECTID: 1323, NAME: 'Johnathon Tunney' },
     :
  { OBJECTID: 1587, NAME: 'Althea Alcazar' } ]
done.







 

リレーショナルデータベースにデータを格納する場合、まずテーブルの定義を行って、その後にテーブル定義に沿ったデータを挿入(insert)していくことになります。

例えば、以下のようなデータをリレーショナルデータベースに格納することを考えてみます:
2016112101


(主キーとか制約とか)深く考えずにこのデータ格納用テーブルを定義すると、その SQL はこんな感じになると思います:
create table users( id long, name varchar(100), height float, weight float );

いわゆる「普通の」リレーショナルデータベースの場合、テーブルは行指向になります。1つのレコードを1行のデータと見なして格納します(直感的に理解しやすいと思います):
2016112102


これに対して、列指向と呼ばれるテーブル定義の場合、データは列ごとにまとまった形で持つことになります。そのため1つのレコードを格納する場合も、複数の列のデータに各値を追加することになります(こちらは直感的には理解しにくいと思います):
2016112103


なぜこんな直感的にわかりにくいテーブル定義が存在しているのか、というと、この方が便利になるケースがあるからです。例えば「身長の平均値」を取り出そうとすると、SQL では AVG 関数を使って以下のような処理を実行することになります:
select avg(height) from users;

SQL ではシンプルに見えますが、行指向データベースでこの時に内部で実行される処理は比較的重いものになります。各レコードを取り出し、各レコードの身長(height)の値を取り出しては合計して平均値を取り出し・・・という処理を行います。レコード数が増えるほど「各レコードを取り出す」回数が増え、全体負荷が大きなものになります。Oracle や SQL Server だと一発で標準偏差を求める STDDEV 関数とかもありますが、これも処理はかなり重いものです。

一方これが列指向データベースの場合、身長列のデータをひとまとめに取り出せるので、そこから平均値を計算するのが比較的楽になります。

では列指向データベースの方がパフォーマンスに優れているのか、というとそうとも限りません。データを1つ(1レコード)挿入する場合の内部処理を考えると、行指向では1つのレコードを追加するだけですが、列指向では各列に値を1つずつ追加することになります。データの追加や更新といった処理におけるパフォーマンスは、一般的には行指向データベースの方が優れていることになります。

要するに利用用途に応じて行指向データベースと列指向データベースを使い分けるのが理想ということになります。ただ「普段は行指向データベースを使って、統計処理を行う時のために列指向データベースに定期的に同期する」といったように、行指向データベースと列指向データベースを連携させる必要が出てきた場合など、データや形式の互換性などをあらかじめ考慮した上で製品やサービスを選ぶ必要がでてきます。


さて、そこで dashDB です。IBM Bluemix 上のサービスの1つとして提供されている DBaaS のデータベースですが、その特徴の1つに「テーブル定義の際に行指向か列指向かを指定できる」ことがあります。

例えば上記のテーブルを dashDB 上に行指向テーブルとして作成する場合は以下のような SQL コマンドを実行します:
create table users( id long, name varchar(100), height float, weight float ) organized by row;

一方、同じテーブルを列指向テーブルで作成する場合はこちらの SQL コマンドを実行します(ちなみに dashDB ではこちらがデフォルト):
create table users( id long, name varchar(100), height float, weight float ) organized by column;

つまり dashDB を使えばデータベース間の互換性を意識する必要もなく、1つのデータベースサービスインスタンスの中でテーブル毎に行指向か列指向かを指定して定義することができるのでした。トランザクション用途であれば行指向、分析用途であれば列指向といった具合にテーブルを定義することで、データベースサーバーの使い分けを意識することなく両方の用途で利用できるクラウドの DBaaS であることが最大の特徴だと思っています。

ちなみにこれが IBM Bluemix での dashDB のアイコンです。左が(普通の)dashDB で、右がトランザクションモードの dashDB Transaction 。パッと見て列指向か行指向かがわかるようになっている(らしい)です:
2016112201


加えて dashDB は Bluemix 上のフルマネージドな RDB クラウドサービスなので、データベースサーバーとしての管理は不要です。しかもデータ量が無料枠である1GB 未満であれば課金されることもないため、データベースとして本格的に利用する前の検証用途として安心して使ってくださいませ。


以前からずっと悩んでいたのが、PHP と IBM DB2 の相性の悪さ(苦笑)でした。

知らない方が多いと思うので補足すると、PHP からデータベースにアクセスする場合には PDO(PHP Data Object) の拡張モジュールが必要になります。更に PDO に加えてデータベースの種類毎(例えば MySQL とか PostgreSQL とか)に接続するための拡張モジュールをシステムに導入しておく必要があります。

CentOS での例を書いておくと、例えば MySQL や PostgreSQL を使う場合はそれぞれ以下のようにして、これらのモジュールをインストールすることができます(PDO 拡張モジュールごとインストールできます):
(MySQL の場合)
# yum install php-mysql
(PostgreSQL の場合)
# yum install php-postgresql

さて IBM DB2 のケースです。普通に "yum install php-db2" などと入力しても「何それ?」みたいな扱いになります(苦笑)。
# yum install php-db2
  :
  :
パッケージ php-db2 は利用できません。
エラー: 何もしません

ではどこから拡張モジュールをインストールするのか・・・ と思って調べてみたのですが、かなりハードル高そうです:
http://php.net/manual/ja/ibm-db2.installation.php


↑リンク先を読んでいただくとわかりますが、自分でヘッダファイルやライブラリファイルを用意して、ビルドしろ、ということになっています。ということは DB2 のインストールモジュールが必要になるわけで・・・ 普通に PHP から使いたいだけなのですが、クラウドの DBaaS として使うにはかなりハードルが高めに設定されているように見えますね。。


一方で IBM Bluemix 環境であれば、このハードルはぐっと下がります。なにしろ「用意されたビルドパックを使うだけ」です。

前提として、IBM Bluemix 上に dashDB のインスタンスを1つ作成し、以下のようなテーブル(names)を定義しておきます:
2016111401
↑ID(int) と NAME(varchar(20)) だけのテーブル定義

2016111402
↑3つのレコードを入力済み


この dashDB インスタンスを、IBM Bluemix 上に作成した PHP ランタイムからバインドしてアクセスする、というアプリケーションを作ります:
2016111401



ビルドパックはこちらにあるものをそのまま利用します:
https://github.com/ibmdb/db2heroku-buildpack-php

ソースコード(index.php)はこんな感じで用意します(ホスト名やユーザー名、パスワードなどは Bluemix の接続情報を参照して取得します):
<html>
<head>
<title>DB2</title>
</head>
<body>
<table border="1">
<tr><th>ID</th><th>NAME</th></tr>
<?php
$host = "xxxxx.services.dal.bluemix.net"; # DB2(dashDB) のホスト名
$port = 50000; # ポート番号
$db = "BLUDB"; # データベース名
$username = "(username)"; # ユーザー名
$password = "(password)"; # パスワード

$conn_str = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=" . $db . ";HOSTNAME=" . $host . ";PORT=" . $port . ";PROTOCOL=TCPIP;UID=" . $username . ";PWD=" . $password . ";";
$conn = db2_connect( $conn_str, $username, $password );
if( $conn ){
  $sql = "SELECT * FROM \"names\"";
  $stmt = db2_prepare( $conn, $sql );
  $result = db2_execute( $stmt );
  if( $result ){
    while( $row = db2_fetch_array( $stmt )){
?>
<tr><td><?php print_r($row[0]); ?></td><td><?php print_r($row[1]); ?></td></tr>
<?php
    }
  }else{
    $error = db2_stmt_error( $stmt );
    $errormsg = db2_stmt_errormsg( $stmt );
?>
<tr><td><?php print_r($error); ?></td><td><?php print_r($errormsg); ?></td></tr>
<?php } } ?> </table> </body> </html>

PHP 内で dashDB のインスタンスに接続して SQL を実行し、全レコードを取り出して表示する、という内容になっています。PHP としては非常にシンプルですが、"db2_" で始まる拡張関数が数か所使われており、DB2 用のコードになっていることがわかると思います。

こいつを cf コマンドでプッシュします。その際に -b オプションで上記のビルドパックを指定します:
# cf push (アプリ名) -b https://github.com/ibmdb/db2heroku-buildpack-php

この方法で作成したアプリケーションにアクセスすると、PHP の IBM DB2 拡張が読み込まれて実行され、期待通りの結果が表示されます:
2016111403


PHPer の皆さん、IBM Bluemix と dashDB であれば面倒な手続きなく PHP からも使えます。是非アプリを作ってみましょう!


なお、PHP の IBM DB2 拡張機能については公式ドキュメントが存在しているので、関数リファレンスも含めて詳しくはこちらを参照ください:
http://php.net/manual/ja/book.ibm-db2.php


このページのトップヘ