search

ibmdb / node-ibm_db

IBM DB2 and IBM Informix bindings for node
MIT License
188 stars 151 forks source link

[Error: [IBM][CLI Driver] SQL30081N A communication error has been detected. #927

Closed Sanjotarm closed 1 year ago

Sanjotarm commented 1 year ago

AssertionError [ERR_ASSERTION]: [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: "...". Communication function detecting the error: "selectForRecvTimeout". Protocol specific error code(s): "0", "", "". SQLSTATE=08001 ] { error: '[ibm_db] SQL_ERROR', sqlcode: -30081, state: '08001' } == null server\node_modules\ibm_db\test\test-basic-test.js:10:10
server\node_modules\ibm_db\lib\odbc.js:142:11 server\node_modules\ibm_db\lib\odbc.js:371:18 { generatedMessage: true, code: 'ERR_ASSERTION', actual: [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: "...". Communication function detecting the error: "selectForRecvTimeout". Protocol specific error code(s): "0", "", "". SQLSTATE=08001 ] { error: '[ibm_db] SQL_ERROR', sqlcode: -30081, state: '08001' }, expected: null, operator: '==' }

Steps to Reproduce:

I'm encountering an issue when attempting to start the db2server((With my database and hostname) using the ibm_db sample example from ibm_db, whether I try with or without Protocol=TCPIP. The details of the issue are provided below.

Without PROTOCOL=TCPIP and with PROTOCOL=HTTP connString="DRIVER=IBM DB2 ODBC DRIVER;DATABASE=DBname;HOSTNAME=hostname;UID=uid;PWD=password;PORT=8471"

Server running at http://localhost:5200... [0] [Error: [IBM][CLI Driver] SQL1013N The database alias name or database name "" could not be found. SQLSTATE=42705 [0] ] { [0] error: '[ibm_db] SQL_ERROR', [0] sqlcode: -1013, [0] state: '08001' [0] } Database { [0] odbc: ODBC {}, [0] queue: SimpleQueue { fifo: [], executing: false }, [0] fetchMode: null, [0] connected: false, [0] connectTimeout: null, [0] systemNaming: undefined, [0] codeSet: null, [0] mode: null, [0] pool: null, [0] conn: ODBCConnection { [0] systemNaming: false, [0] connectTimeout: 30, [0] connected: false [0] } [0] } [0] [Error: [IBM][CLI Driver] SQL1013N The database alias name or database name "" could not be found. SQLSTATE=42705 [0] ] { [0] error: '[ibm_db] SQL_ERROR', [0] sqlcode: -1013, [0] state: '08001' [0] }

With PROTOCOL=TCPIP connString="DRIVER=IBM DB2 ODBC DRIVER;DATABASE=DBname;HOSTNAME=hostname;UID=uid;PWD=password;PORT=8471;PROTOCOL=TCPIP"

[0] Server running at http://localhost:5200... [0] [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: "...". Communication function detecting the error: "selectForRecvTimeout". Protocol specific error code(s): "0", "", "". SQLSTATE=08001 [0] ] { [0] error: '[ibm_db] SQL_ERROR', [0] sqlcode: -30081, [0] state: '08001' [0] } [0] [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: "...". Communication function detecting the error: "selectForRecvTimeout". Protocol specific error code(s): "0", "", "". SQLSTATE=08001 [0] ] { [0] error: '[ibm_db] SQL_ERROR', [0] sqlcode: -30081, [0] state: '08001' [0] } Database { [0] odbc: ODBC {}, [0] queue: SimpleQueue { fifo: [], executing: false }, [0] fetchMode: null, [0] connected: false, [0] connectTimeout: null, [0] systemNaming: undefined, [0] codeSet: null, [0] mode: null, [0] pool: null, [0] conn: ODBCConnection { [0] systemNaming: false, [0] connectTimeout: 30, [0] connected: false [0] } [0] }

Can you provide assistance with resolving this issue and connecting to db2?

bimalkjha commented 1 year ago

@Sanjotarm Please update your database connection info in ibm_db/test/config.testConnectionStrings.json file. I assume your Db2 Server is on remote host and so you are not using localhost as the value of hostname in connection info and in file ibm_db/test/config.testConnectionStrings.json. Let me know otherwise. Then run test-basic-test.js file.

The PROTOCOL should be always TCPIP. Any other value like HTTP is invalid.

If your Db2 server has enabled only SSL port but not TCPIP, then make sure to use SSL port in connection info and use Security=SSL along with sslservercertificate in connection string.

For TCPIP port of db2 server, your connection string should be:

connString="database=dbname;hostname=dbhost.ibm.com;port=446;protocol=tcpip;uid=dbuser;pwd=dbpasswd";
example:
connString="database=E7060x;hostname=dbt71a.rocketsoftware.com;port=446;protocol=tcpip;uid=bimal;pwd=xxx";

For SSL port of Db2 server, your connection string should be:

connString="database=E7060x;hostname=dbt71a.rocketsoftware.com;port=446;protocol=tcpip;uid=bimal;pwd=xxx;Security=SSL;SSLServerCertificate=C:\user\bjha\myservercert.arm";

If still do not work, then update database connection info in file ibm_db\installer\testODBCConnection.bat at line number 16 in validate command and save it. Then run testODBCConnection.bat from command prompt and share all the generated files along with output of testODBCConnection.bat script here in a zip file. Thanks.

bimalkjha commented 1 year ago

@Sanjotarm Please share update. Thanks.

Sanjotarm commented 1 year ago

@bimalkjha I'm currently facing an issue while using the ibm_db package to establish a database connection and retrieve data. I have provided the code snippet below that I'm using to establish the connection:

const options = { connectTimeout: 30, defaultDatabase: 'myDB', debug: true, };

ibm_db.open(connectionString, options) .then((database: ibm_db.Database) => { // Connection opened successfully with the specified options. console.log(database); }) .catch((error: Error) => { // Error occurred while opening the connection. console.error(error); });

The problem I'm facing is that the promise does not resolve, and the breakpoint set on the .then part of the code is not being hit. On the client side, the request shows as pending, indicating that the response is not received from the server.

I have checked the database logs, and it seems that the requests are arriving correctly. However, in the server code, I'm not receiving any response back in the ibm_db.open function.

I have already considered the following potential causes:

Connection String: I have verified that the connectionString variable is properly configured with the correct parameters for my database.

Package Installation: I have installed the ibm_db package using npm, ensuring that all dependencies are properly installed.

I would greatly appreciate any insights or suggestions regarding this issue. If you need any further information, please let me know. Thank you in advance for your help!

bimalkjha commented 1 year ago

@Sanjotarm Could you please provide the result of testODBCConnection.bat script and all generated files as mentioned in my previous update? Are you able to run test-basic-test.js: test after updating connection info in ibm_db/test/config.testConnectionStrings.json file? Increase connectTimeout: 30 to 120 in options and then try. You have posted so far that connection is not working, you are getting SQL30081N error, ibmdb.open() do not resolve but throw error, But, you are not providing any details? Without details or logs, how can I figure out, what is the root cause of the problem? You are saying your connection info is correct, then why test-basic-test.js is failing and why do you not run testODBCConnection.bat with updated connection info? I understood the problem, please share the details as I requested in previous post to proceed further investigation. Thanks.

Sanjotarm commented 1 year ago

Please find the attached testODBCConnection.bat sript and all generated files.

testODBCConnection_.zip

After updating connection info in ibm_db/test/config.testConnectionStrings.json (Attaching config.testConnectionStrings.json file for reference.)file running the test-basic-test.js. I am facing below error :

Trying to open a connection ... [Error: [IBM][CLI Driver] SQL1042C An unexpected system error occurred. SQLSTATE=58004 ] { error: '[ibm_db] SQL_ERROR', sqlcode: -1042, state: 'HY000' } node:assert:124 throw new AssertionError(obj);

^

AssertionError [ERR_ASSERTION]: [Error: [IBM][CLI Driver] SQL1042C An unexpected system error occurred. SQLSTATE=58004 ] { error: '[ibm_db] SQL_ERROR', sqlcode: -1042, state: 'HY000' } == null at \server\node_modules\ibm_db\test\test-basic-test.js:10:10 at \server\node_modules\ibm_db\lib\odbc.js:142:11 at \server\node_modules\ibm_db\lib\odbc.js:371:18 { generatedMessage: true, code: 'ERR_ASSERTION', actual: [Error: [IBM][CLI Driver] SQL1042C An unexpected system error occurred. SQLSTATE=58004 ] { error: '[ibm_db] SQL_ERROR', sqlcode: -1042, state: 'HY000' }, expected: null, operator: '==' }

bimalkjha commented 1 year ago

@Sanjotarm First let me know, you are trying SSL connection or non-SSL connection? For SSL connection, do you have ssl certificate from server? Because, I can see you have mentioned Security:SSL in config.testConnectionStrings.json file. If you are trying non-SSL connection and you do not have certificate from server, then remove "Security":"SSL" from config.testConnectionStrings.json file. If you are trying to connect to SSL port of server, then you need to add SSLServerCertificate keyword with full path of certificate too in the file. Note that testODBCConnection.bat verifies the non-SSL connection only.

In testODBCConnection.bat file, at line no 16, I see validate command as below: db2cli validate -database "mydatabase.com:446" -connect -user myuser -passwd mypasswd

In above command, database name is missing. It should be -database "GEORGE:mydatabase.com:446". Please correct it if it was missing. You have not shared the output of testODBCConnection.bat script. We need complete output of testODBCConnection.bat script too. We see all 1.* file is of 0 byte i.e. these files are empty and nothing has been captured. It tells some problem with configuration.

Please open windows command prompt as Administrator and then run testODBCConnection.bat script to generate 1.* files. You can run testODBCConnection.bat > out.txt command to redirect output of script to a file.

Please provide all answers. First, it is SSL connection or non-SSL? Thanks.

Sanjotarm commented 1 year ago

@bimalkjha I am trying with both, SSL connection and non-SSL connection.

Without SSL connection I am facing below error: Server running at http://localhost:5200... [1] - Generating browser application bundles (phase: setup)... [0] [Error: [IBM][CLI Driver] SQL1598N An attempt to connect to the database server failed because of a licensing problem. SQLSTATE=42968 [0] ] { [0] error: '[ibm_db] SQL_ERROR', [0] sqlcode: -1598, [0] state: '42968' [0] } Database { [0] odbc: ODBC {}, [0] queue: SimpleQueue { fifo: [], executing: false }, [0] fetchMode: null, [0] connected: false, [0] connectTimeout: null, [0] systemNaming: undefined, [0] codeSet: null, [0] mode: null, [0] pool: null, [0] conn: ODBCConnection { [0] systemNaming: false, [0] connectTimeout: 30, [0] connected: true [0] } [0] }

In testODBCConnection.bat file, at line no 16, I have updated database name: db2cli validate -database "GEORGE:mydatabase.com:446" -connect -user myuser -passwd mypasswd

I am in administrator and then running testODBCConnection.bat script to generate 1. files, but still all 1. file size is of 0 byte. server\node_modules\ibm_db\installer>testODBCConnection.bat > out.txt Trace could not allocate the necessary IPC resources. Please retry using a smaller buffer size. Trace is already off; no action was taken. Could not read from file "1.trc". Could not read from file "1.trc". Could not read from file "1.trc". Could not read from file "1.trc".

Generated out.txt is SSL connection. out - Copy.txt

As it's mentioned in out.txt Note: The validation utility could not find the configuration file db2cli.ini. The file is searched at "C:\ProgramData\IBM\DB2\C_ntx64_odbc_cli_clidriver\cfg\db2cli.ini".

I can not see cfg\db2cli.ini file in the path.

Hope, I have provided all mentioned details.

bimalkjha commented 1 year ago

@Sanjotarm I think you are trying to connect to a Db2 for z/OS or Db2 for iSeries server. Please confirm. First you need to fix the SQL1598N error which is about missing db2connect license or your db2 server is not activated for db2connect. Please read this doc for details: https://github.com/ibmdb/node-ibm_db#sql1598n

Also, please provide below answers:

  1. Are you using different ports for non-SSL connection and SSL connection?
  2. Does your Db2 server has both SSL and non-SSL port enabled for connection?
  3. For SSL connection, do you have ssl certificate from server?
  4. If you have certificate, are you using "SSLServerCertificate" keyword in connection string or 
      you are using your own keystore db?
  5. Share the connection string used for SSL connection by masking password so that I can know
      which keywords you are using in connection string.

Thanks for sharing output of testODBCConnection.bat script. We can see this error in the output: Trace could not allocate the necessary IPC resources. Please retry using a smaller buffer size. This error is from db2trc on command and it means tracing is not enabled and hence 0 size of 1.* files.

Thanks for sharing the out -Copy.txt file. By looking on output, if you copy a valid db2connect license of version 11.5 under C:\ntx64_odbc_cli\clidriver\license directory, your non-SSL connection should work fine.

So, for non-SSL connection, get db2connect license. For SSL connection, provide answer of above questions. For SSL connection too db2connect license is required along with SSL certificate file from server. Thanks.

bimalkjha commented 1 year ago

@Sanjotarm Are you still facing this issue? If yes, please provide details requested in above post. Else, let us know to close the issue. Thanks.

Sanjotarm commented 1 year ago

@bimalkjha I am currently in the process of addressing the license issue by collaborating with our internal team. I will diligently follow the necessary steps and provide you with an update as soon as possible. Please keep the ticket open while we work on resolving this issue. Thank you!

bimalkjha commented 1 year ago

@Sanjotarm Are you able to procure db2connect license? It has been about a month. You can verify connectivity using trial license too. IBM gives trial license for 90 days. Thanks.

Sanjotarm commented 1 year ago

@bimalkjha I have discovered another npm package that aligns with my specific needs. Please proceed to close the issue. Thank you for your assistance.