IBM Cloud から提供されている Db2 on Cloud を、2021/05/23 時点では最新となる v4 API を使ってアクセスするまでを実現した Node.js のサンプルアプリケーションを作りました。アクセストークンを取得して SQL を実行し、その結果を取得するまでの一連の流れを紹介します。


Db2 on Cloud は IBM 製リレーショナルデータベース製品である Db2 をマネージドサービスとして IBM Cloud から提供しているサービスです。IBM Cloud のライトアカウントを作成し、フリープランを選択することで、データは 200MB までなどの制約はありますが無料で利用することも可能です：



この Db2 は製品としての経緯もあり、専用（ネイティブ）クライアントライブラリをインストールした上で JDBC/ODBC などから利用することが多かったのですが、近年は Node.js 向けのライブラリなども提供されるようになり、各種プログラミング言語からの利用もできるようになっていました。その API の最新版（v4）では REST API 対応が行われ、（ラズベリーパイなど）専用クライアントライブラリが存在しなかった環境からの利用も可能になりました。

というわけで、今回のブログエントリではこの v4 API を使って Db2 on Cloud のインスタンスにアクセスして SQL を実行し、その実行結果を取得するまでの一連の手順をサンプルアプリケーションとそのコードを参照しつつ紹介します。


【サンプルアプリケーション】
以下で紹介するサンプルアプリケーションの（Node.js 向け）ソースコードはこちらで公開しています：
https://github.com/dotnsf/node-db2


Node.js がインストールされていれば Windows でも Mac でも Linux でも動きます。特にこのアプリケーションは v4 API で作っているので、これまでは（クライアントライブラリが提供されていないため）Db2 にアクセスできなかったラズベリーパイなどの環境からでも利用できる点が特徴になっています。なのでラズパイ環境をお持ちだったらぜひラズパイから利用してみていただきたいです。

サンプルアプリを利用するには、まず IBM Cloud 側の準備が必要です。大まかに以下２段階の準備を行います：
（１）IBM Cloud にログインして Db2 on Cloud サービスインスタンスを作成
（２）作成したインスタンスの接続情報を取得


まず（１）を行います。IBM Cloud に（必要であればアカウントを作成して）ログインし、Db2 サービスインスタンスを作成します：



この際に "Lite" プランを選択しておくとデータ量 200MB 上限や、同時接続数 15 などの制約がありますが、無料で利用することができます：



（１）のインスタンスを作成したら（２）の準備を行います。作成したインスタンスの「サービス資格情報」タブを選択し、「新規資格情報」ボタンで「サービス資格情報」を１つ追加して、その中身を確認します：



以下のような JSON フォーマットの情報を確認することができます：

{
  "db": "BLUDB",
  "dsn": "DATABASE=BLUDB;HOSTNAME=dashdb-xxx-xxxxx-xxxxxx.services.dal.bluemix.net;PORT=50000;PROTOCOL=TCPIP;UID=username;PWD=password;",
  "host": "dashdb-xxx-xxxxx-xxxxxx.services.dal.bluemix.net",
  "hostname": "dashdb-xxx-xxxxx-xxxxxx.services.dal.bluemix.net",
  "https_url": "https://dashdb-xxx-xxxxx-xxxxxx.services.dal.bluemix.net",
  "jdbcurl": "jdbc:db2://dashdb-xxx-xxxxx-xxxxxx.services.dal.bluemix.net:50000/BLUDB",
  "parameters": {
    "role_crn": "crn:v1:bluemix:public:iam::::serviceRole:Manager"
  },
  "password": "password",
  "port": 50000,
  "ssldsn": "DATABASE=BLUDB;HOSTNAME=dashdb-xxx-xxxxx-xxxxxx.services.dal.bluemix.net;PORT=50001;PROTOCOL=TCPIP;UID=username;PWD=password;Security=SSL;",
  "ssljdbcurl": "jdbc:db2://dashdb-xxx-xxxxx-xxxxxx.services.dal.bluemix.net:50001/BLUDB:sslConnection=true;",
  "uri": "db2://username:password@dashdb-xxx-xxxxx-xxxxxx.services.dal.bluemix.net:50000/BLUDB",
  "username": "username"
}

ここで必要になるのは以下の３つの値です：

・username
・password
・hostname


加えて deployment id を取得します。この ID はサービス固有の ID で、すべての REST API 実行時に必要な値です。取得方法はサービスを表示しているブラウザ画面の URL を確認します：



おそらくこのようなフォーマットの URL 文字列になっています：

https://cloud.ibm.com/services/dashdb-for-transactions/crn%3Av1%3Abluemix%3Apublic%3Adashdb-for-transactions%3Aus-south%3Aa%2Fxxxxxxxxxxxx%3Axxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx%3A%3A?paneId=manage

この中の "crn" で始まる文字列（? が含まれる場合はその手前まで）を URL デコードした値が deployment id です。URL デコードといってもパターンは２つに決まっていて、"%3A" を ":" に、"%2F" を "/" に変換するだけです。例えば該当の Db2 on Cloud サービスを表示している時の URL 内文字列がこの内容だった場合、

https://cloud.ibm.com/services/dashdb-for-transactions/crn%3Av1%3Abluemix%3Apublic%3Adashdb-for-transactions%3Aus-south%3Aa%2Fxxxxxxxxxxxx%3Axxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx%3A%3A?paneId=manage

deployment id は crn で始まる青字部分を URL デコードした値ということになり、具体的には以下の値となります：

crn:3v1:bluemix:public:dashdb-for-transactions:us-south:a/xxxxxxxxxxxx:xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx::

以上、ここまでの作業で Db2 on Cloud のサービスインスタンスを作成し、その username, password, hostname, deployment id の４つの情報を取得することができました。この４つの値が取得できれば、実際にサンプルアプリケーションを動かすことができます。


上述のリポジトリ URL から git clone やダウンロード＆展開するなどしてソースコード一式を取得します。そして settings.js をテキストエディタで開いて、先程取得した４つの値を入力して保存します：



これでソースコード側に必要な変更も完了です。では実際に Node.js で動かしてみましょう：

$ cd node-db2

$ npm install

$ node app

ここまでのコマンドを実行すると、サンプルのウェブアプリケーションは 8080 番ポートで待ち受ける形で起動します。ウェブブラウザ（別 PC からでも可）からサンプルの HTTP リクエストを送信して、Db2 on Cloud で SQL を実行し、その結果を取得します：

http://（上記の $ node app を実行したマシンの IP アドレス）:8080/ping

なお、この GET /ping リクエストは Db2 on Cloud インスタンスに対して以下の SQL を実行した結果のシステム情報を取得するように作られたものです：

select TABNAME, TABSCHEMA, OWNER from syscat.tables fetch first 5 rows only;

実際にウェブブラウザでこの URL にアクセスするとこのような結果になります。上述の SQL を実行した結果が JSON で返ってきました。仮にアプリケーションをラズベリーパイ上で動かしていても同様の結果が取得できているはずです。これまでは Db2 のクライアントライブラリが導入できる環境からでないと実行できなかった SQL を、v4 API では（サーバー上のもの以外には）クライアントライブラリを使わない環境でも実行して結果を取得することができる、ということがわかります：



【ソースコード解説】
ではソースコードを見ながら v4 API の解説をします。上述のオペレーションで使っているファイルは変数設定以外は app.js のみなので、全容を確認したい場合はこのファイルを参照ください。

上述で行ったのは１つの SQL を実行して、その結果を取得する、というごく普通のオペレーションですが、v4 API では大きく３つのパートに分かれて処理されています（（２）と（３）が別れて処理されている点に注目）：

（１）アクセストークン取得
（２）SQL 実行
（３）SQL 結果取得


まず（１）について。これまでの Db2 API では認証時にユーザー名とパスワードがあればログインして SQL を実行することができましたが、v4 API では（今どきらしく）まずアクセストークンを取得して、アクセストークンを付与しながら SQL を実行する必要があります。というわけでアクセストークンの取得手順を紹介します。

具体的には以下のようなコードを実行しているのですが、settings.js に設定した username と password を https://（hostname）/dbapi/v4/auth/tokens に POST しています。またその際の HTTP リクエストヘッダ内で deployment id の値を送付しています（HTTP リクエストヘッダに deployment id を含める、というのは以下すべての REST API で共通です）。成功した場合、返されるオブジェクトの token キーにアクセストークンが付与されて返ってくるので、この値を保存しています（実際のアプリではセッション等に保存しておくべきです）：

var access_token = null;
var option = {
  url: 'https://' + settings.hostname + '/dbapi/v4/auth/tokens',
  json: { userid: settings.username, password: settings.password },
  headers: { 'content-type': 'application/json', 'x-deployment-id': settings.deployment_id },
  method: 'POST'
};
request( option, async function( err, res0, body ){
  if( err ){
    console.log( { err } );
  }else{
    if( body && body.token ){
      access_token = body.token;
      console.log( { access_token } );
    }
  }
});

GET /ping にリクエストがあると、このアクセストークンを使って（２）SQL を実行します。app.js では以下のように実行しています：

app.get( '/ping', function( req, res ){
  res.contentType( 'application/json; charset=utf-8' );
  if( access_token ){
    var sql0 = 'select TABNAME, TABSCHEMA, OWNER from syscat.tables fetch first 5 rows only;';
    var option0 = {
      url: 'https://' + settings.hostname + '/dbapi/v4/sql_jobs',
      json: { commands: sql0, limit: 10, separator: ';', stop_on_error: 'no' },
      headers: { 'content-type': 'application/json', 'Authorization': 'Bearer ' + access_token, 'x-deployment-id': settings.deployment_id },
      method: 'POST'
    };
    request( option0, async function( err0, res0, body0 ){
      if( err0 ){
        console.log( { err0 } );
        res.status( 400 );
        res.write( JSON.stringify( { error: err0 }, null, 2 ) );
        res.end();
      }else{
        if( body0 && body0.id ){
            :
            :

GET /ping リクエストへのハンドラとして、まず "select TABNAME, TABSCHEMA, OWNER from syscat.tables fetch first 5 rows only;" という SQL を https://(hostname)/dbapi/v4/sql_jobs に POST して実行しています。この処理が成功すると、返されるオブジェクトに id というキー値が含まれています。

そして（３）では、実行した SQL の結果を取得します。その際には（２）で取得した id キー値を指定して GET https://(hostname)/dbapi/v4/sql_jobs/(id) を実行して、その結果が先程のブラウザ画面のようなフォーマットで取得されていたのでした：

            :
            :
        if( body0 && body0.id ){
          console.log( { body0 } );
          var option1 = {
            url: 'https://' + settings.hostname + '/dbapi/v4/sql_jobs/' + body0.id,
            headers: { 'content-type': 'application/json', 'Authorization': 'Bearer ' + access_token, 'x-deployment-id': settings.deployment_id },
            method: 'GET'
          };
          request( option1, async function( err1, res1, body1 ){
            if( err1 ){
              console.log( { err1 } );
              res.status( 400 );
              res.write( JSON.stringify( { error: err1 }, null, 2 ) );
              res.end();
            }else{
              //. body1 は string
              body1 = JSON.parse( body1 );
              console.log( { body1 } );
              res.write( JSON.stringify( body1, null, 2 ) );
              res.end();
            }
          });
        }else{
          res.status( 400 );
          res.write( JSON.stringify( { error: body0 }, null, 2 ) );
          res.end();
        }
      }
    });
  }else{
    res.status( 400 );
    res.write( JSON.stringify( { error: 'no access_token' }, null, 2 ) );
    res.end();
  }
});

NoSQL とは違って、トランザクションを含む（時間のかかる）処理を実行する可能性もあるため、その間で処理をブロックしないようにこのような仕様で API が用意されているのだと思います。上の例では SQL の実行と結果の取得を続けて行って実行結果を返していますが、（途中に取得できる id をいったんクライアントに返すことで）これら２つの処理を別の REST API で分けて実行させることも可能になります。シングルスレッドの Node.js での実装を考慮すると、こちらのほうがより正しいといえるかもしれません。


【良い点・悪い点】
なんといっても「ラズベリーパイなどの、Db2 クライアントライブラリが提供されていないシステムから Db2 サーバーに接続してクエリーを実行できる」ようになったことが大きな改善点であると感じました。また時間のかかる処理を実行する際にも、まとめて実行して、その間の他のリクエストをブロックすることなく、後から結果を確認できるようになっている点もクラウド時代らしいといえます。

唯一残念な点は「プレースホルダー型の SQL に未対応」な点です。SQL を比較的安全に実行する上でよく使われるプレースホルダー型の SQL（"SELECT * from xxxxx where id = ?" で、パラメーターの id を分けて送って実行する SQL）には現時点ではこの REST API では未対応のようで、現時点では開発者が気をつけて実装するか、（クライアントライブラリが提供されているシステムから）従来の専用ライブラリを使って実行する必要がありそうでした。


【参考】
IBM Db2 on Cloud REST API

タグ ：

nodejs

node.js

ibm

cloud

db2

v4

先日、コマンドラインツールで 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.

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

 

タグ ：

db2

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


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

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



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



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




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



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


以下では、これら５つの値がこのような内容になっていたと仮定して説明を続けます：

{
  "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 という２つのデータベースエイリアスが登録されていました。この例ではリモートサーバーのエイリアスは登録されていません。


エイリアスの登録

では先程作成した 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.

成功したようです。念の為もう１回エイリアス一覧を確認します：

$ 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         =

エイリアスが２つから３つに増え、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

cli

sql

db2