Using the db2dsdriver to connect to my server for hadr.

[Rdecoster]

Hi,

I am currently using a connection string to connect to my database. const config ='DATABASE=bludb;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=paswword123';

const connectDB = () => { const pool = new ibmdb.Pool(); const connection = pool.openSync(config); try { connection; } catch (error) { console.log('Unable to open conection, ', error); } console.log( 'Connected to DB', connection.querySync('SELECT * FROM "SYSIBMADM" . "DB2_MEMBER"') ); console.log(' creating new table for demo');

const error = connection.closeSync(); if (error) { console.log(' Error while closing connection ', error); } };

I want to use the db2dsdriver file to correctly configure my connection to my database cluster setup for hadr. I have configured it according to the documentation. But how do I use the driver as part of my connection string? I have created the db2sdriver.cfg in the ..../ibm_db/installer/clidriver/cfg/db2dsdriver.cfg. do I have to import it into my code anywhere?

And when Im using my driver correctly, will my new connection string look like this below because I have the host information in my config file?

const config = DATABASE=bludb;PROTOCOL=TCPIP;UID=db2inst1;PWD=password;

[bimalkjha]

@Rdecoster No need to import ..../ibm_db/installer/clidriver/cfg/db2dsdriver.cfg anywhere. It get read by ..../ibm_db/installer/clidriver automatically internally to read keywords related to a dsn used in connection string or the database:hostname:port combination.

If you have configured, dsn alias in your db2dsdriver.cfg file, then you need to use "dsn=<alias name>;" in connection string. driver will read the database, hostname, port and other keywords from cfg file, so no need to provide other keywords in connection string if it is already specified in cfg file. Your connection string is not correct. If you do not want to read the dsn section, but only the database section of cfg file, then use database, hostname, port all three in connection string. You must validate your db2dsdriver.cfg file contents using db2cli validate command before using it. f.e. if you have configured a dsn alias for your database in cfg file, verify the cfg file using db2cli validate -dsn <alias name> -connect command. Use -uid and -passwd option too if credentials are not in cfg file. i.e. db2cli validate -dsn <alias name> -uid <dbuser> -passwd <dbpass> -connect command. you can find db2cli under clidriver\bin directory.

If it works, just use "dsn=<alias name>;uid=<dbuser>;pwd=<dbpass>" as connection string in ibm_db. If you have used username and passwd in cfg file, then only "dsn=<alias name>" as connection string is sufficient.

If still you are facing issue, then delete your db2dsdriver.cfg file, and create it fresh using below commands from command prompt:

cd ..../ibm_db/installer/clidriver/bin
rm ../cfg/db2dsdriver.cfg
./db2cli writecfg add -dsn mydsn -database bludb -host hostname.com -port 50000 -parameters "userid=db2inst1;password=passwd123"
./db2cli validate -dsn mydsn -connect

Now, you can use "dsn=mydsn;" as connection string in ibm_db.

If you are facing any issue, please share the complete output of db2cli validate -dsn <alias name> -uid <dbuser> -passwd <dbpass> -connect command here. Thanks.

[Rdecoster]

Thanks that seemed to do the trick Appreciate that. But I have ran into a new problem. I am now seeing an error Error: [IBM][CLI Driver] SQL1776N The command cannot be issued on an HADR database. Reason code = "1". When I give the command to manage_hadr -takeover from my standby server (to simulate what would happen if my primary database went down or roles switched). My previous standby server takes the leader with my old primary to standby. It looks like its still trying to connect to the old leader. I noticed that this completely works correctly if I terminate the primary server rather than switching roles.

Is this something wrong with my configuration or is this something I have to handle in my app with appropriate error handling? I have configured my drier config as follows.

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<configuration>
  <dsncollection>
    <dsn alias="mydsn" host="c-db2oltp-primary-db2u-0" name="bludb" port="50000"/>
  </dsncollection>
  <databases>
    <database name="bludb" host="c-db2oltp-primary-db2u-0" port="32622">
      <acr>
        <parameter name="enableAcr" value="true"/>
        <parameter name="enableSeamlessACR" value="true"/>
        <parameter name="maxAcrRetries" value="100"/>
        <parameter name="acrRetryInterval" value="2"/>
        <parameter name="affinityFailbackInterval" value="2"/>
        <!-- <parameter name="enableAlternateServerListFirstConnect" value="true"/> -->
        <alternateserverlist>
          <server name="standby" hostname="c-db2oltp-standby-db2u-0" port="50000"/>
          <server name="primary" hostname="c-db2oltp-primary-db2u-0" port="50000"/>
        </alternateserverlist>`
        <affinitylist>
            <list name="list1" serverorder="standby,primary"/>
        </affinitylist>
        <clientaffinitydefined>
            <client name="client1" hostname="localhost" listname="list1"/>
        </clientaffinitydefined>
      </acr>
    </database>
  </databases>
</configuration>

[bimalkjha]

@Rdecoster The port number for bludb is different in dsn section and database section. It must be same if you are using mydsn in connection string. I think it should 50000 in database connection too instead of 32622.

Please share what connection string you are using for connection? Use db2cli validate -dsn mydsn -connect command to verify the db2dsdriver.cfg file and connectivity. Please follow the detailed instructions as documented here: HOWTO: Configure non-Java for automatic client reroute and Connecting clients to the HADR configuration Thanks.

[bimalkjha]

@Rdecoster Use below db2dsdriver.cfg file. It should work.

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<configuration>
  <dsncollection>
    <dsn alias="mydsn" name="bludb" host="primary-hostname.com" port="primary-port"/>
  </dsncollection>
  <databases>
    <database name="bludb" host="primary-hostname.com" port="primary-port">
      <!-- automatic client reroute (ACR)  -->
      <acr>
        <parameter name="enableACR" value="true"/>
        <parameter name="enableSeamlessACR" value="true"/>
        <parameter name="acrRetryInterval" value="2"/>
        <parameter name="maxAcrRetries" value="30"/>
        <parameter name="affinityFailbackInterval" value="2"/>
        <alternateserverlist>
            <server name="primary" hostname="primary-hostname.com" port="primary-port"/>
            <server name="standby" hostname="standby-hostname.com" port="standby-port"/>
        </alternateserverlist>
        <affinitylist>
          <list name="list1" serverorder="standby,primary"/>
        </affinitylist>
        <clientaffinitydefined>
            <client name="client1" hostname="localhost" listname="list1"/>
        </clientaffinitydefined>
      </acr>
    </database>
  </databases>
</configuration>

Thanks.