Watson DB2 (Connection Pool)

[uniquejava]

Db2 on cloud(先前叫dashDB), 注册需要credit card

Db2 warehouse, 可以免费注册, 以此为例, 它提供了各种编程语言的API, NODE的还是那个叫ibm_db的

提供了JDBC URL, 但是密码使用的placeholder. 需要点左边的credentials, 创建一个credential 然后从json格式的一个文件中找到密码部分.

可以用DBVisualizer连上去, 并可以建表和建SEQ.

Sybase Power Designer可以建Physical Data Model (PDM) 然后生成对应的SQL

Db2 warehouse提供的UI只能建表, 找不到建sequence的地方.

node-ibm_db

node-ibm_db是ibm db2的node版驱动. 安装npm install ibm_db --save, 安装过程要下载好几十M的clidriver安装包, 并且因为墙的原因可能会经常安装失败(下载的压缩包格式损坏) 官方的安装文档提供了不用重复下载clidriver的办法, 如果你之前有在其它项目已经用npm安装过ibm_db , 执行如下命令可以秒安装:

export IBM_DB_HOME=/Users/cyper/xxxx/ibm_db/installer/clidriver
npm install ibm_db@2.0.0 --save

也可以使用yarn安装: yarn add ibm_db

20170929: 今天在新项目安装ibm_db报错:

gyp: Call to 'node -e "require('nan')"' returned exit status 0 while in binding.gyp. while trying to load binding.gyp
gyp ERR! configure error 
gyp ERR! stack Error: `gyp` failed with exit code: 1
gyp ERR! stack     at ChildProcess.onCpExit (/usr/local/lib/node_modules/npm/node_modules/node-gyp/lib/configure.js:305:16)
gyp ERR! stack     at emitTwo (events.js:106:13)
gyp ERR! node-gyp -v v3.4.0

换成yarn的时候有如下提示

Agreeing to the Xcode/iOS license requires admin privileges, please run “sudo xcodebuild -license” and then retry this command.

gyp ERR! build error
gyp ERR! stack Error: `make` failed with exit code: 69
gyp ERR! stack     at ChildProcess.onExit (/usr/local/lib/node_modules/npm/node_modules/node-gyp/lib/build.js:276:23)
gyp ERR! stack     at emitTwo (events.js:106:13)
gyp ERR! stack     at ChildProcess.emit (events.js:191:7)
gyp ERR! stack     at Process.ChildProcess._handle.onexit (internal/child_process.js:219:12)

NM的, 原来macOS自动更新了我的Xcode至9.0, 我得打开xcode同意一下新的条款. WTF

connection pool

从官方docs中找到了一些关于pool的文档, https://github.com/ibmdb/node-ibm_db#PoolAPIs 文档上关于init方法, 有如下示例代码

var ret = pool.init(5, connStr);
if(ret != true)
{
    console.log(ret);
    return false;
}

pool.open(connStr, function(err, db) { ...

一开始没怎么看明白, 比如init方法为什么没有callback, 要是出错了怎么知道(ret != true呗), 那出错的具体原因在哪? debug了一下源代码, :

原来init是一个阻塞方法, 内部会循环调用Database.openSync(...)初始化连接, 并将connection放在一个内部数组里.

init方法的返回值(示例中的ret)要么为true(表示pool初始化成功), 要么是一个Error Object. (出错的堆栈信息全在里面)

最后要注意的是连接bluemix上的db2, 需要在connectionString最后拼上Security=SSL

据此我设计了自己的db2 pool工具类 db2.js, 如下

var format = require('string-format');
var ibm_db = require('ibm_db');
var pool = new ibm_db.Pool;
var cn = null;

module.exports.initPool = initPool;
module.exports.ping = ping;
module.exports.open = open;
module.exports.close = close;
module.exports.closePool = closePool;

function initPool(db2_config, cb) {

  cn = format('DATABASE={db};HOSTNAME={host};PORT={port};PROTOCOL=TCPIP;UID={user};PWD={pass};Security=SSL', db2_config);

  // set to true then db2 driver will print debug logs.
  ibm_db.debug(!!db2_config.debug);

  pool.setMaxPoolSize(db2_config.maxPoolSize || 10);

  console.log('initializing pool, please wait ...');

  var result = pool.init(db2_config.poolSize || 1, cn);

  if (result != true) {
    return cb && cb(result);

  } else {
    console.log('pool initialized(' + db2_config.poolSize + ')');
    cb && cb(null);
  }

}

function ping() {
  console.log('ping db2 server.');

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

    var rows = conn.querySync('select 1 from sysibm.sysdummy1');
    console.log(rows);

    close(conn);
  });

}

function open(cb) {
  pool.open(cn, function (err, conn) {
    if (err) {
      return cb(err);
    }

    console.log('get one connection');

    cb(null, conn);
  });
}

function close(rs, stmt, conn, cb) {

  // overload close, now we can use close(conn) | close(conn, cb) | close(rs, stmt, conn) | close(rs, stmt, conn, cb)
  if (arguments.length > 2) {
    rs && rs.closeSync();
    stmt && stmt.closeSync();

  } else {
    conn = rs;
    cb = stmt;
  }

  conn && conn.close(function () {
    console.log('connection released to pool.');
    cb && cb();
  });
}

function closePool(cb) {
  pool.close(function () {
    console.log('all connections in the pool are closed');
    cb && cb();
  });
}

我在./bin/www初始化日志组件log4js就立即初始化db2 pool, 这样如果有任何出错的日志, 会记录到日志文件, 并且如果db2 pool 初始化失败, 则直接退出app. 如果process.exit(1) 打印出来的NPM ERR吓着你, 可以用process.exit(0) 😄

// init db2 connection pool
db2.initPool(db2_config, function (err) {
  if (err) {
    console.error(err);

    log4js.shutdown();

    process.exit(1);
  } else {
    db2.ping();
  }
});

// express启动失败时的处理
    case 'EADDRINUSE':
      logger.error(bind + ' is already in use');

      db2.closePool(function () {
        log4js.shutdown();
      });

      process.exit(1);

启动时的日志如下.

[2017-09-19 14:57:55.227 INFO] env= development
[2017-09-19 14:57:55.230 INFO] node-ibm_db logs disabled.
[2017-09-19 14:57:58.876 INFO] pool initialized
[2017-09-19 14:57:58.877 INFO] ping db2 server.
[2017-09-19 14:57:58.877 INFO] get one connection
[2017-09-19 14:57:59.503 INFO] [ { '1': 1 } ]
[2017-09-19 14:57:59.505 INFO] connection released to pool.
[2017-09-19 14:57:59.509 INFO] Listening on port 3000

References

https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.doc/learn_how/designdb_create_new.html

[uniquejava]

大坑.

可以说是power designer的坑, 可以说是dashDB的坑.

有两条sql

select t.* from t_user t
select t.user_id from t_user t

第一条正常, 第二条报如下错误.

[Error Code: -206, SQL State: 42703]  "T.USER_ID" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.15.82. 2) 
[Error Code: -727, SQL State: 56098]  An error occurred during implicit system action type "2". Information returned for the error 
includes SQLCODE "-206", SQLSTATE "42703" and message tokens "T.USER_ID".. SQLCODE=-727, SQLSTATE=56098, DRIVER=4.15.82

字段名确认了就是user_id

select '<' || name || '>' from sysibm.syscolumns where tbname = 'T_USER';

1           
----------  
<PASSWORD>  
<user_id>   
<ORG_ID>    
<USERNAME> 

难道.. 字段名 不能用小写. ,我改成 select USER_ID from t_user 还是报同样的错.. 哈哈, 最终发现.. 在dashDB上建表有下面的猫腻. 👎

create table T_USER (
   "user_id"            INT     --- 不能这样写
   "USERNAME"           VARCHAR(32)            not null, -- 这样写没问题
   password           VARCHAR(32)            not null, -- 这样写没问题
);

当然, 错误只会在你使用字段名做查询的时候才会触发, 建表, 插数据, select *等其它一切情况都完全正常.

结论, 不要太相信工具, 要是我手写SQL就不会加上这些该死的""号.

小坑

rs和stmt只有closeSync方法, 未提供close(他们的示例代码也全部用的closeSync), 虽然没什么影响, 但着实让我 surprise 😮 了一下.

[uniquejava]

sequence

建法(copy 自 dbv)

CREATE sequence "DASH7609"."XXX_SEQ" AS INTEGER START WITH 1 increment BY 1 no minvalue no maxvalue
no cycle cache 20 no ORDER

各种使用方法

select NEXT VALUE for USER_SEQ as SEQ from sysibm.sysdummy1

INSERT INTO t_user (user_id, username, password) VALUES (NEXT VALUE FOR USER_SEQ, 'xxx','yyy', 1)
SELECT NEXT VALUE FOR SEQUENCE_COL;

INSERT INTO tbname (SEQUENCE_COL, ...) VALUES (NEXT VALUE FOR SEQUENCE_COL, ...);
SELECT PREVIOUS VALUE FOR SEQUENCE_COL;

SELECT SEQUENCE_COL FROM NEW TABLE (
  INSERT INTO tbname (SEQUENCE_COL, ...) VALUES (NEXT VALUE FOR MY_SEQUENCE, ...)
)

可以看到NEXT VALUE for USER_SEQ 整体相当于oracle中的USER_SEQ.nextval, 在易用性方面db2总是比oracle差一些, 至少不管做什么操作都要多敲些字母,....

参考: https://stackoverflow.com/questions/6781135/how-to-obtain-a-db2-sequence-value-in-a-multithreaded-application