IBM Support

HOWTO: Configure non-Java for automatic client reroute

Question & Answer


Question

Configure non-Java applications for automatic client reroute on Windows

Answer

There are additional Automatic Client Reroute (ACR) methods which can be used such as using ClientAffinities and the use of Alternate Groups but this example will use the simplest method. For Java application configuration refer to  FAQ: How to configure the JDBC driver for automatic client re-route .

Client/Application Side Configuration


1) Make ACR changes to your db2dsdriver.cfg after creating it using db2dsdcfgfill -i DB2. It may be easier just to copy and paste the stuff below then add in any additional parameters you have for your databases. The DSN=V105S connects to primary.ibm.com:50000. The alternate server is secondary.ibm.com:50000 if it is unable to establish initial connection to the primary to retrieve alternate server list.

Note: Parameters like maxAcrRetries, acrRetryInterval, etc... can be tuned Refer to ACR parameters for details.

Use db2dsdcfgfill -i DB2 to create the db2dsdriver.cfg or us the sample one below

Windows
C:\ProgramData\IBM\SQLLIB\DB2COPYxx\cfg\db2dsdriver.cfg
or
C:\ProgramData\IBM\SQLLIB\DB2COPYxx\cfg\db2dsdriver.cfg

Unix/Linux
~/sqllib/cfg/db2dsdriver.cfg

The configuration file connects to database V105S which also has a DSN definition with the same name to support ODBC connectivity. Primary database located on primary.ibm.com port 50000 and secondary located in host name secondary.ibm.com port 50000.

The secondary system is defined in the <alternateserverlist> parameter. If the application is unable to connect to primary to retrieve alternate server list upon first connection, it will check db2dsdriver.cfg and try the hostname defined in <alternateserverlist>.

db2dsdriver.cfg

<configuration>
<dsncollection>
<dsn alias="V105S" name="V105S" host="primary.ibm.com" port="50000">
</dsn>
</dsncollection>

<databases>
<database name="V105S" host="primary.ibm.com" port="50000">
<acr>
<!-- We can tune parameters like maxAcrRetries, acrRetryInterval -->
<parameter name="enableAcr" value="true" />
<parameter name="maxAcrRetries" value="10"/>
<parameter name="acrRetryInterval" value="1"/>
<parameter name="enableseamlessACR" value="true"/>
<parameter name="enableAlternateServerListFirstConnect" value="true"/>
<alternateserverlist>
<!-- Connect to this server if initial connection primary fails -->
<server name="secondary" hostname="secondary.ibm.com" port="50000" />
</alternateserverlist>
</acr>
</database>
</databases>
</configuration>


2) Validate db2dsdriver.cfg before using it.

db2cli validate -dsn V105S

Scroll down and look at the info for DSN=V105S. Ensure all of the ACR parameters specified in step #1 are picked up. If there is problem with XML file it will return an error about encountering invalid values on line xx.

====================================================================
db2dsdriver.cfg validation for data source name "V105S":
====================================================================

[ Parameters used for the connection ]

Keywords Valid For Value
---------------------------------------------------------------------------
DATABASE CLI,.NET,ESQL V105S
HOSTNAME CLI,.NET,ESQL primary.ibm.com
PORT CLI,.NET,ESQL 50000


[ Parameters used for ACR ]

Parameter Value
---------------------------------------------------------------------------
enableAcr true
maxAcrRetries 10
acrRetryInterval 1
enableseamlessACR true
enableAlternateServerListFirstConnect true

name:secondary hostname:secondary.ibm.com port:50000


3) Test ACR using the standalone CLI script below. It loops 20 times and waits 30s between each try so there is time to shutdown the primary or secondary.

HADR is not needed to test ACR since ACR functionality is independent of HADR. If If HADR unavailable, setup two DB2 instances and shut one down while leaving the other one up.

Execute from DB2 Command Window or when logged in as DB2 instance owner:

Linux/Unix: ~/sqllib/bin/db2cli <selectLoopACR.cli
Windows: C:\Program Files\IBM\SQLLIB\BIN\db2cli <selectLoopACR.cli


selectLoopACR.cli
# To Run: db2cli <selectLoopACR.cli
# Performs SELECT from sysibm.sysdummy1 on each database every 30s
# Suggestion: We can modify query to select from table, insert different values into test table
# on primary and standby

opt echo on
opt callerror on
SQLAllocEnv 1
SQLAllocConnect 1 1

# DBNAME: V105S
# USERID: db2inst1
# PASSWORD: mypass
sqlconnect 1 "V105S" -3 "db2inst1" -3 "mypass" -3

sqlallocstmt 1 1
opt autocommit off

# Loop the select statement 20 times
beginloop 20

#sqlallocstmt 1 1
# Sleep 30 seconds
sleep 30
SQLExecDirect 1 "select count(*) from sysibm.sysdummy1" SQL_NTS
FetchAll 1
SQLFreeStmt 1 SQL_CLOSE
endloop
killenv

Sample Output

> # Loop the select statement 20 times
> beginloop 20
>
> #sqlallocstmt 1 1
> # Sleep 30 seconds
> sleep 30
> SQLExecDirect 1 "select count(*) from sysibm.sysdummy1" SQL_NTS
> FetchAll 1
> SQLFreeStmt 1 SQL_CLOSE
> endloop
SQLExecDirect: rc = 0 (SQL_SUCCESS)
FetchAll: Columns: 1
1
1
FetchAll: 1 rows fetched.
SQLFreeStmt: rc = 0 (SQL_SUCCESS)
SQLExecDirect: rc = 0 (SQL_SUCCESS)
FetchAll: Columns: 1
1
1
FetchAll: 1 rows fetched.
SQLFreeStmt: rc = 0 (SQL_SUCCESS)

DB2 Server Side Configuration

Primary should point to standby for alternate server

db2 " update alternate server for db v105s using secondary.ibm.com port 50000"

db2 "list db directory"


Database alias = V105S
Database name = V105S
Local database directory = /data1
Database release level = 10.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname = secondary.ibm.com
Alternate server port number = 50000

Standby should point to primary for alternate server

db2 "update alternate server for db v105s using primary.ibm.com port 50000"

db2 "list db directory"


Database alias = V105S
Database name = V105S
Local database directory = /data1
Database release level = 10.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname = primary.ibm.com
Alternate server port number = 50000

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"High Availability - Cluster Management","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;10.1;10.5;11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
24 January 2019

UID

swg21982590