selectForConnectTimeout and recv error

[Duraimurugan Rajendran]

I am on windows7, node version: 6.9.2, npm version: 3.10.9

When trying to make a connection to iSeries DB through ibm_db, I get an error as followed,

 

When tried without PORT, it takes more than 1 min to throw an ‘selectForConnectTimeout’ error,

 

{ Error: [IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the er

ror was detected: "153.6.245.250".  Communication function detecting the error: "selectForConnectTimeout".  Protocol specific error code(s): "0", "*", "*".  SQLSTATE=08001

 

    at Error (native)

  errors: [],

  error: '[node-odbc] SQL_ERROR',

  message: '[IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the

error was detected: "153.6.245.250".  Communication function detecting the error: "selectForConnectTimeout".  Protocol specific error code(s): "0", "*", "*".  SQLSTATE=08001\r\n',

  state: '08001' }

 

 

When tried with PORT value:

 

{ Error: [IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the er

ror was detected: "153.6.245.250".  Communication function detecting the error: "recv".  Protocol specific error code(s): "*", "*", "0".  SQLSTATE=08001

 

    at Error (native)

  errors: [],

  error: '[node-odbc] SQL_ERROR',

  message: '[IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: "TCP/IP".  Communication API being used: "SOCKETS".  Location where the

error was detected: "153.6.245.250".  Communication function detecting the error: "recv".  Protocol specific error code(s): "*", "*", "0".  SQLSTATE=08001\r\n',

  state: '08001' }

 

 

 

My connection settings:

 

  ibmdb.open("DATABASE=DBNAME; HOSTNAME=153.6.245.xxxx; UID=******;PWD=*****;",

      function (err,conn) {

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

 

  conn.query('SELECT ARDTGI, FNAMGI, LNAMGI FROM tablename WHERE KYDTGI = 35159 and SEQGI = 1810190',

      function (err, data) {

    if (err) console.log(err);

    else console.log(data);

 

    conn.close(function () {

      console.log('done');

    });

  });

});

 

Could you please help with this?

 

 

Thanks,

Durai

 

[bimaljha]

You should use Port and Protocol both in connection string as show here in QuickExample: node-ibm_db
To reduce the timeout error, pass connectTimeout in ibmdb.open().

Modify your code as below: 

 ibmdb.open("DATABASE=DBNAME; HOSTNAME=153.6.245.xxxx; UID=******;PWD=*****;Port=portnum;PROTOCOL=TCPIP", 

       {connectTimeout : 20}, function (err,conn) {

Here, connection timeout value used is 20 seconds. Thanks.

[Duraimurugan Rajendran]

What does that Database value signify here? When I checked iSeries, it does not have an actual database name itself. If I don't supply an database name, it's throwing an error as database name missing.

[bimaljha]

Check with your AS400 server admin to know the database name, port and other connection information. Thanks.

[Duraimurugan Rajendran]

Hi Bimal,

My DBA guy says that there is no DB name associated for iSeries, when I try with following connection string, it throws an error as I have included below,

  ibmdb.open("DATABASE=****; HOSTNAME=153.6.xxx.yyy; UID=*****; PWD=PWD; PORT=23",

      {connectTimeout : 15},   

      function (err,conn) {

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

  

  conn.query('SELECT * FROM TABLENAME WHERE CMCTCD="DEV"', 

      function (err, data) {

    if (err) console.log(err);

    else console.log(data);

    conn.close(function () {

      console.log('done');

    });

  });

});

Error:

{ Error: [IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: "TCP/IP".  Communication API being used

: "SOCKETS".  Location where the error was detected: "153.6.xxx.yyy".  Communication function detecting the error: "selectForRecvTimeout".  Protocol spec

ific error code(s): "0", "*", "*".  SQLSTATE=08001

    at Error (native)

  errors: [],

  error: '[node-odbc] SQL_ERROR',

  message: '[IBM][CLI Driver] SQL30081N  A communication error has been detected. Communication protocol being used: "TCP/IP".  Communication API being u

sed: "SOCKETS".  Location where the error was detected: "153.6.xxx.yyy".  Communication function detecting the error: "selectForRecvTimeout".  Protocol s

pecific error code(s): "0", "*", "*".  SQLSTATE=08001\r\n',

  state: '08001' }

Thanks,

Durai

[bimaljha]

You should use database name of DATABASE keyword not ****. DBA should be able to tell the storage name as they only create and also they should be able to tell the connection string. Just google about how to get the database name on iSeries and you'll get many links. In most of the cases I have seen the database name is AS400 or first word of hostname. f.e. database name on my rchaptf3.rch.stglabs.ibm.com server is rchaptf3. Without using correct database name in connection string, you can not connect to the server. Current error returned by ibm_db is expected when you use wrong database name in connection string. So, this issue has nothing to do with the node-ibm_db. If your DBA do not have such info, please check with some other DBA. Also, are you able to connect the server using any other CLIENT? Database name is must for any client to connect to the server. If you connection string for any other client, share with me and I'll tell you what is the correct dbname in that connection string. Thanks.

[bimaljha]

Hi Durai,

Seems you are able to connect to the database if client is local but not when client is on remote system. Can your DBA confirm that remote client is able to connect to the server? Any remote client JDBC/ODBC. Check your server is configured to accept remote connection or not i.e. SVCENAME should be set to the port number and DB2COMM should be set to TCPIP on server.

Thanks and Regards,
Bimal Jha

[bimaljha]

Hi Durai,

Please use PROTOCOL=TCPIP in your connection string. It is missing in all connection string shared by you.

The details about SQL30081N is documented here: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.messages.sql.doc/doc/msql30081n.html

It says:

• If protocol = TCP/IP, function = selectForRecvTimeout, rc1 is irrelevant, the recv request timed out before it could complete successfully. Possible causes: 
  • Either the system recv timeout or the timeout value set by DB2TCP_CLIENT_RCVTIMEOUT caused the timeout. Try adjusting these values.

Thanks and Regards,

Bimal Jha

[Duraimurugan Rajendran]

Hi Bimal,

I just changed the protocol and timeout values. I see the timeout error has gone now and it looks like the connection is made successfully now. 

I get a error as below,

Can you throw some light?

{ Error: [IBM][CLI Driver] SQL0902C  A system error occurred. Subsequent SQL statements cannot be processed. IBM software support reason code: "".  SQLST

ATE=58005

    at Error (native)

  errors: [],

  error: '[node-odbc] SQL_ERROR',

  message: '[IBM][CLI Driver] SQL0902C  A system error occurred. Subsequent SQL statements cannot be processed. IBM software support reason code: "".  SQ

LSTATE=58005\r\n',

  state: '58005' }

Thanks,

Durai

[Duraimurugan Rajendran]

Hi Bimal,

I believe the server is accepting remote connection. This because, when I try to make an OLEDB connection through .NET application; I was able to run the SELECT statement that I am expecting.. 

Ring any bells?? Any idea?

Thanks,

Durai

[bimaljha]

Hi Durai,

Please share your platform/OS on which you have installed ibm_db. Also, version of ibm_db in use. I hope you have installed the latest ibm...@2.0.0.

Thanks and Regards,

Bimal Jha

[bimaljha]

Also, you can try solutions documented here: https://github.com/ibmdb/node-ibm_db/issues/34

Thanks and Regards,

Bimal Jha

[Duraimurugan Rajendran]

In on,

OS: Windows7

node: v6.9.2

npm: 3.10.9

"ibm_db": "^2.0.0"

The above link you shared looks like specific to OSX. I am also googling for the solution, please help if you have already seen this. 

[bimaljha]

Hi Durai,

Seems your Windows system has some older version of GSKit installed and it is pointed by the system PATH environment variable. Add .../ibm_db/installer/clidriver/bin/icc at the start of PATH and LIB system environment variable. 

Thanks and Regards,

Bimal Jha

[Duraimurugan Rajendran]

Hi Bimal,

I see /ibm_db/installer/ only under node_modules folder only. 

I dont see ibm_db/installer/clidriver/bin/icc anywhere in my windows machine. Please note I did not install/add any drivers other than npm install with the package.json file. Am I missing something here?

Thanks,

Durai

[bimaljha]

Hi Durai,

You should have ibm_db/installer/clidriver folder after successful execution of 'npm install ibm_db' command unless you have set env variable IBM_DB_HOME to a preinstalled DB2 client/server folder. Please share the full output of below commands executed from DOS prompt:

echo %IBM_DB_HOME%

db2level

db2cli validate

npm install ibm_db

Thanks and Regards,

Bimal Jha

[Duraimurugan Rajendran]

It looks like the previous npm install did not run correctly. I removed all the files under node_modules folder and re-run npm install with package.json file. I see the path ibm_db/installer/clidriver/bin/icc and referenced the same in PATH and LIB under system variables.When I re-ran the app, I see the same error.

{ Error: [IBM][CLI Driver] SQL0902C  A system error occurred. Subsequent SQL statements cannot be processed. IBM software support reason code: "".  SQLSTATE=58005

    at Error (native)

  errors: [],

  error: '[node-odbc] SQL_ERROR',

  message: '[IBM][CLI Driver] SQL0902C  A system error occurred. Subsequent SQL statements cannot be processed. IBM software support reason code: "".  SQLSTATE=58005\r\n',

  state: '58005' }

Please note I see this error regardless of whatever be the connection string. Any help on this will be highly appreciated.

Thanks,

Durai

[bimaljha]

Hi Durai,

I would still request to share output of below commands to proceed further: 

db2level

db2cli validate

npm install ibm_db

You can also run the steps in installer/testODBCConnection.sh file and share the collected trace files to bima...@in.ibm.com. File testODBCConnection.sh is for non-windows platform so you need to use corresponding windows commands to collect the traces after updating connection string in this file.

Thanks and Regards,

Bimal Jha