How to verify connection is Alive and ready to execute statements.

[Kiryl Bucha]

Hi to all,

I go through the Database API ( https://github.com/ibmdb/node-ibm_db#PoolAPIs ) and can’t find any description of method that verify connection is Alive and ready to execute statements.

In out particular case, we use “ibm-db” to connection DashDB and NodeJS application. DashDB connection could be terminated by Administrator or by maintenance operation on DashDB cluster.

Next Exception will be generated by ODBC driver in case of statement execution on “dead” connection:

error:        "[node-ibm_db] 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: \"xxx.xxx.xxx.xxx\".  Communication function detecting the error: \"recv\".  Protocol specific error code(s): \"*\", \"*\", \"0\".  SQLSTATE=08001\r\n",

 

As result there are no chance to catch this exception with help of npm “ibm-db” package. Please provide the guidance how to solve this issue in correct way.

At the current moment, next bypass solution was created:

validate =
    function (con_instance) {
        // check Connection status (not changed to FALSE when connection dead)
        let result = !(con_instance.conn.connected == false);
        // check if connection catch exception during Exec Statement
        if (con_instance.conn.isErrored == true) {
            try {
                let conn = con_instance.conn;
                let result = conn.querySync("select 1 from sysibm.sysdummy1");
                result = true;
                con_instance.status = result;
                con_instance.conn.isErrored = false;
                return result;
            }
            catch (error) {
                con_instance.status = result;
                result = false;
                let notice = "Connection ID: %s Status: INVALID : %s ";
                con_instance.conn.isErrored = true;
                console.log(sprintf(notice, con_instance.conn._properties.uuid, options.prefix));
                return result;

            }
        } else {
            con_instance.conn.isErrored = false;
            return result;
        }
    };

 

[bimaljha]

If connection is broken then SQL30081N is expected. You can know some connection is good or not by using it only i.e. by running some query.

To avoid this SQL30081N error, you can configure ACR in db2dsdriver.cfg file. You should use a db2dsdriver.cfg file like below. The alternateserverlist tag and EnableAlternateServerListFirstConnect parameter is mandatory for reconnect.

<configuration>

   <dsncollection>

      <dsn alias="mydsn" name="SAMPLE" host="hotel.torolab.ibm.com" port="269"/>

   </dsncollection>

   <databases>

      <database name="SAMPLE" host="hotel.torolab.ibm.com" port="269">

         <parameter name="CommProtocol" value="TCPIP"/>         

         <parameter name="userid" value="newton"/>         

         <parameter name="password" value="xxxx"/>         

         <acr>

           <parameter name="enableACR" value="true"/>

           <parameter name="enableSeamLessACR" value="true"/>

           <parameter name="maxAcrRetries" value="3"/>

           <parameter name="acrRetryInterval" value="5"/>

           <parameter name="EnableAlternateServerListFirstConnect" value="true"/>

           <parameter name="affinityFailbackInterval" value="10"/>

           <alternateserverlist>

             <server name="S1" hostname="hotel.torolab.ibm.com" port="269"/>

             <server name="S2" hostname="hotel.torolab.ibm.com" port="269"/>

           </alternateserverlist>

           <affinitylist>

             <list name="list1" serverorder="S1,S2"/>

           </affinitylist>

         </acr>

      </database>

   </databases>

</configuration>

Update the connection information in this file and save it under ibm_db/installer/clidriver/cfg folder as db2dsdriver.cfg file. Then in your test program, use the connection string as "dsn=mydsn;" only.

Thanks and Regards,

Bimal Jha