Problem with DB2 Pool

[Martin Selecký]

Hi all.

Please I need to help with db pool. We running our application and database on Bluemix. We have node.js application connecting to DB2. We are using express server. 

Everything is working fine on local (with the same db as DEV space), also the code is working fine on our DEV (it has its own DB instance) and TEST space(it has its own db instance) in Bluemix. 

ONLY in PROD space (of course it has its own db instance) it is NOT WORKING !!!

I found the problem is in pool.init command or pool.open command. 

In shared module connectionPool.js I have following code ->

let ibmdb = require('ibm_db');

var Pool = require("ibm_db").Pool;

var pool = new Pool();

pool.init(5, dsnString); //dsnString contains connection string to db

module.exports = dbConnection => {

   pool.open(dsnString, (err, conn) => {

   if (err) {console.log(' === DATABASE POOL ERROR: init/open error ===');}

   dbConnection(conn);

   });

};

and calling this in other modules like this ->

let connPool = require('../../connectionPool');

 app.get('/some_url', (req, res) => {

    let sql = `SELECT * FROM TEST`;

    connPool(conn => {

        conn.query(sql, (err,data) => {

            if (err) {console.log(err);

            } else {            

                return res.render('someview', {results: data});

            }

            conn.close();

        });

    });

});

The code in PROD space is not going through pool.init(5, dsnString); line if its there , or its not going through this line: pool.open(dsnString, (err, conn);

if I remove line: pool.init(5, dsnString);

in both cases I am not getting any error message, the code just stop there waiting for something.

Again, strange thing is that this code is working in any variant on local and also in dev and test space.

The ONLY DIFFERENCE is that for local, and for Bluemix dev and test space we are using DB2 in version: EnterpriseTransactional2.8.500

and for production we have different version: EnterpriseTransactionalHA12.128.1400

Previously I was using following code to connect DB2 (which was working perfectly in all environments: local, dev, test and also in PROD!!!),:

- in shared module: connection.js I was connecting to db ->

...

var db = ibmdb.openSync(dsnString); //dsnString contains connection string to db

if (db.connected) {console.log('Application connected to database.');}

module.exports = db2creds;

module.exports = db;

- and in code I was calling exported db -> 

var db = require('./connection');

app.get('/some_url', (req, res) => {

    let sql = "SELECT * FROM TEST";

    db.query(sql, (err,data) => {

        if (err) {console.log(err);

        } else {            

            return res.render('suser', {results: data});

        }

    });

});

I will appreciate any help, as I don't know now if the problem is in different env setting for those two diff databases or where the problem is.

Thank you in advance

Martin 

[bimaljha]

Hi Martin,

Problem is not with the client or application code, problem is with the EnterpriseTransactionalHA12.128.1400 server.

In your test environment where application runs fine using EnterpriseTransactional2.8.500, modify the connection string to use EnterpriseTransactionalHA12.128.1400 and you should be able to repro the issue.

Problem is that EnterpriseTransactionalHA12.128.1400 is not allowing the connection to remote client in production. You see long wait as client is waiting for response from server. Verify the port and protocol is enabled on server or not. You should check with admin of server EnterpriseTransactionalHA12.128.1400 to tell what server is doing when connection requested by client? I don't see any issue with application code. If server is not supporting the port or protocol used in connection string, then you just need to update connection string with correct value of port or protocol. Check with admin to know the correct value of port and protocol.

Thanks and Regards,

Bimal Jha

[Martin Selecký]

Hi

thank you for your answer, I agree that there is probably problem with different environment in OS or db but I am using code to get env setting dirrectly from ENV:

so the whole code is actually:

let ibmdb = require('ibm_db'),

env = null,

db2creds = null;

if (process.env.VCAP_SERVICES) {

env = JSON.parse(process.env.VCAP_SERVICES);

}

if (!env){

var file = __dirname + '/dashDBcred.json';

try {

db2creds = require(file);

} catch(err) {

return {};

}

} else {

db2creds = env['dashDB'][0].credentials;

}

var dsnString = "DRIVER={DB2};DATABASE=" + db2creds.db + ";UID=" + db2creds.username + ";PWD=" + db2creds.password + ";HOSTNAME=" + db2creds.hostname + ";port=" + db2creds.port;

var Pool = require("ibm_db").Pool;

var pool = new Pool();

// pool.init(5, dsnString);

module.exports = dbConnection => {

pool.open(dsnString, (err, conn) => {

if (err) {

console.log(' === DATABASE POOL ERROR: init/open error ===');

}

dbConnection(conn);

});

};

for all environments its the same, it is returning something like: 

DRIVER={DB2};DATABASE=BLUDB;UID=user_name;PWD=password;HOSTNAME=some_bluemix_db_name.bluemix.net;port=50000

also production returning to by this string with all values

And I want to point out, my previous old version of code was also using this part to setup connection string for opening connection directly (as you see in in my old version of code). And that old code was working in production without problems.

So, do you have suggestions, how should I build up the connection differently?

thank you

Martin

Dňa streda, 13. decembra 2017 17:09:34 UTC+1 bimaljha napísal(-a):

[Martin Selecký]

Hi again :)

I have additional information,

when I am connecting now from local to production db with pool.open, it is not working when I am using pool.init, like this:

var Pool = require("ibm_db").Pool;

var pool = new Pool();

pool.init(5, dsnString);

module.exports = dbConnection => {

pool.open(dsnString, (err, conn) => {

if (err) {

console.log(' === DATABASE POOL ERROR: init/open error ===');

callBot.sendInfo("pool error");

}

dbConnection(conn);

});

};

but it IS working without it, like this:

var Pool = require("ibm_db").Pool;

var pool = new Pool();

module.exports = dbConnection => {

pool.open(dsnString, (err, conn) => {

if (err) {

console.log(' === DATABASE POOL ERROR: init/open error ===');

callBot.sendInfo("pool error");

}

dbConnection(conn);

});

};

any suggestions please? :)

Martin

Dňa streda, 13. decembra 2017 17:09:34 UTC+1 bimaljha napísal(-a):

Hi Martin,

[Martin Selecký]

Hi again

anyway, I have used your good advise (funny, simple solutions are sometimes hard to find :) )

and I changed on local connection string temporary as hard-coded to connect to production from local

and it is working, my local applicationgot data back from prod database!

So now it looks for me like there are differences in installation dependencies for my application.

I am using on local and in Bluemix (all spaces: dev, test, prod) the same package.json with:

ibm...@2.2.1

node version: 7.10.1

I also tested version no...@6.6.0 with the same result on production: code not connecting to DB and other options: connecting

Should I use some different version of ibm_db?

I have: "ibm_db": "*"

in my packege.json

thank you for any suggestions

Martin

Dňa streda, 13. decembra 2017 17:09:34 UTC+1 bimaljha napísal(-a):

Hi Martin,

[Martin Selecký]

Dňa streda, 13. decembra 2017 17:09:34 UTC+1 bimaljha napísal(-a):

Hi Martin,