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

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

タグ:db2

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


Apache Derby(或いは "Cloudscape")という RDB をご存知でしょうか?
derby-logo-web


最近は SQLite や HTML5 のローカルデータストアの台頭であまり名前を聞かなくなりましたが、Pure Java で記述された軽量の RDB です。元々は Cloudscape Inc. によって開発されましたが、Informix Software を経て IBM 製品として扱われていた時代もあります。その影響もあってか "DB2 互換 SQL" に対応し、DB2 の SQL が動く軽量の Java RDB という立ち位置でした。軽量であるが故に組み込み系のアプリケーション内で使われることが多いようです。歴史的には 2004 年に IBM から Apache 財団へソースコードが寄贈され、現在の Apache Derby という名称のプロジェクトになりました。また Oracle JDK 1.6 以降に(オプションとして)組み込まれている JavaDB の実装はこの Apache Derby です。

私自身は "Cloudscape" と呼ばれていた頃に使ったことがありました。今回、久しぶりに Apache Derby を使ってみました。

JDK のオプションに組み込まれているとはいえ、せっかくなので最新版を使ってみることにしました。まずは Apache Derby のダウンロードページから最新バージョン(2016/Oct/07 時点では 10.12.1.1)のリンクをクリックします:
2016100601


最新版のアーカイブファイル:db-derby-(バージョン番号)-bin.zip をクリックしてダウンロードします:
2016100602


ダウンロードした zip ファイルを展開し、lib フォルダ内の必要な JAR ファイルを取り出します。今回は本体である derby.jar と、日本語ロケールが含まれた derbyLocale_ja_JP.jar の2ファイルを取り出します:
2016100603


この2ファイルを Java の開発環境から使えるようにします。J2SE/EE プロジェクトであれば、WebContent/WEB-INF/lib 以下にコピーするなどして、コンパイル/実行時に参照できるようにしておきます:
2016100604


試しに以下のような index.jsp ファイルを用意してみました:
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="java.util.*" %>
<%@ page import="java.io.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="me.juge.derby.*" %>
<%
  request.setCharacterEncoding("utf-8");
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3c.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:v="urn:schemas-microsoft-com:vml">
<head>
<title>Derby JDBC Sample</title>
</head>
<body>

<table border="1">
<tr><th>ID</th><th>NAME</th><th>PRICE</th></tr>
<%
final String driverName = org.apache.derby.jdbc.EmbeddedDriver.class.getCanonicalName();
final String dbName = "derbydb";
final String connURL = "jdbc:derby:" + dbName + ";create=true";  //. DBが存在していない場合は作成するオプション

try{
  Class.forName( driverName );
  Connection conn = null;

  try{
    conn = DriverManager.getConnection( connURL );
  }catch( Exception e ){
  }

  if( conn != null ){
    ResultSet rs = null;
    //. 初期化
    try{
      //. 試しに items テーブルにアクセス
      Statement s0 = conn.createStatement();
      rs = s0.executeQuery( "select count(*) from items" );
    }catch( SQLException e ){
      //e.printStackTrace();

      String state = e.getSQLState();
      if( state.equals( "42X05" ) ){
        //. テーブルが存在しない
        try{
          Statement s1 = conn.createStatement();
          s1.execute( "create table items("
            + " id int generated always as identity primary key"  //. この辺りが DB2 互換 SQL
            + ", name varchar(100)"
            + ", price int"
            + " )");

          PreparedStatement s2 = conn.prepareStatement( "insert into items( name, price ) values( ?, ? )" );
          s2.setString( 1, "シャンプー" );
          s2.setInt( 2, 1000 );
          s2.execute();

          PreparedStatement s3 = conn.prepareStatement( "insert into items( name, price ) values( ?, ? )" );
          s3.setString( 1, "石鹸" );
          s3.setInt( 2, 500 );
          s3.execute();
        }catch( Exception e1 ){
        }
      }else if( state.equals( "42X14" ) || state.equals( "42821" ) ){
        //. テーブル定義が不正

      }else{
        //. その他の想定外の例外

      }
    }finally{
      if( rs != null ) rs.close();
    }

    //. レコード表示
    try{
      rs = conn.createStatement().executeQuery( "select * from items" );
      while( rs.next() ){
        int id = rs.getInt( 1 );
        String name = rs.getString( 2 );
        int price = rs.getInt( 3 );
%>
<tr><td><%= id %></td><td><%= name %></td><td><%= price %></td></tr>
<%
      }
    }catch( SQLException e ){
    }finally{
      if( rs != null ) rs.close();
    }
  }
}catch( Exception e ){
  e.printStackTrace();
}
 %>
</table>
</body>
</html>

コードそのものは一般的な JDBC プログラミングです。DB を開く際に目的の DB が存在していない場合はその場で作成するようなオプション("create=true")を付与しています。また items というテーブルのレコード数を取得して、エラーが出るようであればテーブルが存在していないと判断し、新規に items テーブルを定義して作成し、2つほどデータを insert するようにしています。 最終的には items テーブル内の全レコードを画面に出力する、という内容にしています。


このプロジェクトを実行して、index.jsp にブラウザでアクセスすると以下のような表形式で2つのレコード内容が確認できます:
2016100701

まあ普通に RDB として使うこともできますが、組み込み系以外であればフットプリントの小ささからローカルレプリカ DB として利用する、というケースも考えられます。特にサーバー側が DB2 だったりすると SQL 互換が便利に使えたりしますね。

久しぶりに Cloudscape を使ってみました。相変わらずですが( Java が動いている前提はありますが)JAR ファイル置くだけで使える RDB は便利ですね。



モデル定義だけで OpenAPI 対応の REST API を生成してくれる StrongLoop LoopBackIBM DB2 に対応していたので使ってみました。

基本的な考え方や手順は以前にこちらで紹介した MySQL 対応での手順と同様ですが、以下ではデータソースをDB2 とした場合の手順を紹介します:
StrongLoop アプリで MySQL を使う


まずは DB2 サーバーを用意します。自前で用意できる場合はその環境を使っていただいても構いませんが、IBM Bluemix ユーザーであれば SQL Database 等、DB2 サーバーのインスタンスを使っていただいても構いません。

では次のような DB2 サーバー環境が用意できているものとします:
ホスト名XX.XX.XX.XX
ポート番号50000
データベース名SQLDB
ユーザー名db2_username
パスワードdb2_password


この DB2 インスタンスに REST API で読み書きするモデルのテーブルを作成します。上記の MySQL での例と同様の item テーブルを用意します。ここで1点注意があります。DB2 の CREATE TABLE 文は普通に実行するとテーブル名や列名を全て大文字で作成します。普通に利用する際には大文字小文字を区別することなく利用できるのですが、LoopBack 環境では全て小文字で定義されている前提で API が発行されます。つまりデフォルトの大文字のままでテーブルを定義すると後で「テーブルが見つからない」といったエラーが発生してしまいます。それを避ける目的で create table 内のテーブル名や列名の部分は全てダブルクォーテーションで括り、明示的に小文字で作成する必要があります。この点に注意して作成してください:
> create table "item"("id" int primary key generated always as identity (start with 1 increment by 1),"name" varchar(50) not null,"code" varchar(50) not null,"price" int);

これで DB2 側の準備はできました。ではこの item テーブルのデータを読み書きする API を LoopBack で用意しましょう。以下 CentOS 環境を前提に紹介します。

まず、StrongLoop や LoopBack は npm を通じて提供され、Node.js 上で動きます。というわけで Node.js と npm を導入する必要があります:
# yum install epel-release
# yum install nodejs npm --enablerepo=epel

次に npm を使って LoopBack と StrongLoop を導入します:
# npm install -g loopback
# npm install -g strongloop

StrongLoop / LoopBack が導入できたら、これらを使って API を作成します。まずは作業用のディレクトリ(下の例では ~/tmp)を作って、そこに移動しておきます:
# cd
# mkdir tmp
# cd tmp

このディレクトリに LoopBack のアプリケーション環境を作成します。ここではアプリケーション名を db2app と指定しています:
# slc loopback db2app

アプリケーション名と、アプリケーションディレクトリを聞かれますが、いずれもデフォルトの db2app をそのまま指定します。またアプリケーションの種類を聞かれますが、これもデフォルトのまま api-server を選択します:
2016031401


すると db2app アプリケーションの基本部分のインストールが開始され、しばらく待つとこのような画面になり、導入が完了します(この時点で db2app ディレクトリが作られているはずです):
2016031402


上図の最後の部分にこの後の手順が記載されています。が、LoopBack の標準 DB ではなく、他の外部 DB を利用する場合の手順は少し異なります。まずはアプリケーションディレクトリに移動しておきます:
# cd db2app

次にこのプロジェクトのデータソースが DB2 になるようカスタマイズします。まずは DB2 コネクタをインストールします。このコマンドで DB2 ODBC CLI ドライバなどもダウンロード&インストールされます:
# npm install --save loopback-connector-db2

DB2 コネクタがインストールできたら、このコネクタを使ってデータソースを定義します。カスタムデータソースの名称は "mydb" としています:
# slc loopback:datasource mydb

そしてデータソース名はデフォルトのまま mydb を選択します。次のコネクターの選択肢において "IBM DB2" を選択します(ここが今回、新たに DB2 対応した機能です):
2016031403


続けて DB2 サーバーへの接続情報を指定します。上記表の内容通りにホスト名、ポート番号、ユーザー名、パスワード、そしてデータベース名を入力します:
2016031404


ちなみにここでの作業の内容は server/datasource.json ファイルに記載されます。直接編集する場合は同ファイルをテキストエディタで編集してください。


データソースの定義ができたら最後に item モデルを生成します:
# slc loopback:model item

モデル名はデフォルトのまま item を指定します。次にデータソースを聞かれるので上記で作成した mydb を指定します(これでデータソースは DB2 になります)。以下はこれまでと同様でベースクラスには PersistedModel、REST API 公開は Yes、カスタム plural form はなし(そのままリターン)、そして common モデルを指定します:
2016031405


そして各列の定義では上記表のように name 列は string 型で必須(Yes)、code 列も string 型で必須(Yes)、price 列は number 型で必須ではない(No)をそれぞれ指定します:
2016031406


最後に Property name を聞かれた所でそのまま Enter を入力して、列のカスタマイズを完了します。これでモデルの定義も完了しました。


モデルが出来たので、LoopBack で API が稼働し公開されるはずです。実際の動作を確かめてみましょう:
# node .

すると画面には API Explorer にアクセスするための URL (最後が /explorer になっている方)が表示されます:
2016031407


ウェブブラウザでこのページにアクセスすると、db2app アプリケーションと、その中に item モデルを読み書きする一連の API が生成されていることが確認できます:
2016031408


後はここで紹介したような方法で実際の API を実行して、この画面から動作確認することが可能です:
2016031409


ここでデータレコードを作成した場合、当然 DB2 サーバー内に格納されます。DB2 側のデータを参照して同じデータが保存されていることを確認できます:
2016031410


これまで LoopBack は Oracle や SQL Server には対応していましたが、ようやく "StrongLoop An IBM Company" っぽい連携ができるようになりました(笑)。
2016031411


このページのトップヘ