Technical Blog Post
Abstract
50 DB2 Nuggets #40: Tech Tip - Configuring pureScale Client Affinity with Tomcat
Body
The pureScale client affinity feature allows client specification of a single primary server and failover
sequence to alternate servers and all rerouting is controlled by the client driver.
That means all you have to do is basically configuring the right DB2 JCC JDBC properties in your WAS(Web Application Server) side.
However the reality is many people has difficulty about how to have right setting about pureScale client affinity during configuration and failover test.
For having right configuration for them, some people take over 2 weeks or 4 weeks.
Sometimes many DBAs wants to refer to the configuration which worked successfully.
This blog is just from my simple test experience and describes the client affinity scenario test using Tomcat 7.0.5.4 and pureScale 10.1.
So I hope this helps for DBAs who need to configure the client affinity on Tomcat.
(Scenario)
1. pureScale member 1 for the first JDBC connection.
2. After db2stop member 1, see if the connection is made on other member 0
0. Set pureScale member hosts to the /etc/hosts in the pureScale client machine.
As pureScale java client refers the serverlist which is received from pureScale server,
we need to set the hosts and the pureScale member IP to the /etc/hosts file.
This is the popular point many people have missed and skipped.
.....
### pureScale client
192.168.153.65 db2ps1.au.ibm.com db2ps1
### pureScale server
192.168.153.64 db2ps2.au.ibm.com db2ps2 # pureScale member 0
192.168.153.68 db2ps3.au.ibm.com db2ps3 # pureScale member 1
1. Tomcat download :
http://tomcat.apache.org
2. Tomcat installation.
: Simple, Download and extract. I will call the directory as <TOMCAT_HOME> in this blog page.
3. Tomcat configuration
(1) JDK setting : Add following line to '<TOMCAT_HOME>/bin/catalina.sh
############# Jun Su
JAVA_HOME=/home/db2inst1/sqllib/java/jdk64
....
(2) add db2jcc.jar to the CLASSPATH.
- tomcat@db2ps1:~/tomcat7054/bin> cat setenv.sh <== create the setenv.sh
CLASSPATH=/home/tomcadt/db2jcc.jar:$CLASSPATH
- or copy db2jcc.jar to web application library directory
tomcat@db2ps1:~/tomcat7054/webapps/examples/WEB-INF/lib> ls -tlr |grep db2jcc.jar
-r--r--r-- 1 tomcat was 3516375 2014-06-10 14:26 db2jcc.jar
(3) Add following line to <TOMCAT_HOME>/conf/web.xml
........
<!-- junsulee : JNDI resource -->
<resource-ref>
<res-ref-name>jdbc/sample</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
(4) Add JNDI data source information to tomcat setting.
| tomcat@db2ps1:~/tomcat7054/conf> cat context.xml <?xml version='1.0' encoding='utf-8'?> <!-- The contents of this file will be loaded for each web application --> <Context> <!-- Default set of monitored resources --> <WatchedResource>WEB-INF/web.xml</WatchedResource> <!-- Uncomment this to disable session persistence across Tomcat restarts --> <!-- <Manager pathname="" /> --> <!-- Uncomment this to enable Comet connection tacking (provides events on session expiration as well as webapp lifecycle) --> <!-- <Valve className="org.apache.catalina.valves.CometConnectionManagerValve" /> --> <!-- added following lines --> <Resource auth="Container" driverClassName="com.ibm.db2.jcc.DB2Driver" name="jdbc/sample" password="psinst2" username="psinst2" type="javax.sql.DataSource" url="jdbc:db2://db2ps3.au.ibm.com:61118/sample" enableClientAffinitiesList="1" retryIntervalForClientReroute="2" affinityFailbackInterval="300" user="psinst2" enableSeamlessFailover="true" clientRerouteAlternateServerName="db2ps3.au.ibm.com,db2ps2.au.ibm.com" databaseName="sample" maxRetriesForClientReroute="3" clientRerouteAlternatePortNumber="61118,61118" driverType="4" currentSchema="psinst2" removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true" /> </Context> |
4. Start Tomcat
tomcat@db2ps1:~/tomcat7054/bin> ./startup.sh
Using CATALINA_BASE: /home/tomcat/tomcat7054
Using CATALINA_HOME: /home/tomcat/tomcat7054
Using CATALINA_TMPDIR: /home/tomcat/tomcat7054/temp
Using JRE_HOME: /home/db2inst1/sqllib/java/jdk64
Using CLASSPATH: /home/tomcat/tomcat7054/bin/bootstrap.jar:/home/tomcat/tomcat7054/bin/tomcat-juli.jar
Tomcat started.
5. Test JSP program for connecting to pureScale member 1.
| tomcat@db2ps1:~/tomcat7054/webapps/examples> cat pure_affinity_jdbc.jsp <%@ page import="java.sql.*,javax.sql.*,javax.naming.*"%> <% Connection conn = null; Statement stmt = null; ResultSet rs = null; try { Context c = new InitialContext(); // *************************** Depending on WAS, a bit differnt //DataSource ds = (DataSource)c.lookup("jdbc/sample"); //DataSource ds = (DataSource)c.lookup("sample"); DataSource ds = (DataSource)c.lookup("java:comp/env/jdbc/sample"); //Connection conn = ds.getConnection("user","password"); // or ds.getConnection() will use default user id if you specified it conn = ds.getConnection(); // or ds.getConnection() will use default user id if you specified it stmt = conn.createStatement(); String sql = "select * from employee"; rs = stmt.executeQuery(sql); while(rs.next()) out.println("column1 : " + rs.getString(1) + "<br>"); rs.close(); stmt.close(); conn.close(); } catch (java.sql.SQLException e){ e.printStackTrace(); System.err.print(e.getClass().getName()); System.err.println(e.getMessage()); } finally { try { if (rs != null) rs.close(); if (stmt != null) stmt.close(); if (conn != null) conn.close(); } catch(java.lang.Exception e) { e.printStackTrace(); } } //end of finally %> |
5. Client affinity test while calling the jsp.
(1) monitoring sql.
psinst2@db2ps2:~/junsulee> cat mon_uow.db2
select member,cached_timestamp, varchar(hostname,30) as hostname
, port_number, ssl_port_number, priority
from table(mon_get_serverlist(-2))
;
select member, substr(application_handle,1,10) app_handle
,substr(application_name,1,20) app_name
,substr(CLIENT_WRKSTNNAME,1,20) client
from table(mon_get_connection(null,-2)) t
where application_name='db2jcc_application'
with UR;
select member, count(*) connections
from table(mon_get_connection(null,-2)) t
where application_name='db2jcc_application'
group by member
with UR;
select member, count(*) active_transactions
from table(mon_get_unit_of_work(null, -2)) t
where uow_start_time is not null
group by member;
(2) Before member 1 stop : connection is made on member 1
----------------------------------------------------------------------
Thu Jun 26 10:44:30 EST 2014
----------------------------------------------------------------------
MEMBER CACHED_TIMESTAMP HOSTNAME PORT_NUMBER SSL_PORT_NUMBER PRIORITY
------ -------------------------- ------------------------------ ----------- --------------- --------
1 2014-06-26-10.43.13.000000 db2ps3.au.ibm.com 61118 0 100
1 2014-06-26-10.43.13.000000 db2ps2.au.ibm.com 61118 0 46
0 2014-06-26-10.40.50.000000 db2ps2.au.ibm.com 61118 0 100
0 2014-06-26-10.40.50.000000 db2ps3.au.ibm.com 61118 0 0
4 record(s) selected.
MEMBER APP_HANDLE APP_NAME CLIENT
------ ---------- -------------------- --------------------
1 65588 db2jcc_application 192.168.153.65
1 record(s) selected.
MEMBER CONNECTIONS
------ -----------
1 1
1 record(s) selected.
(3) db2stop member 1
psinst2@db2ps2:~/junsulee> db2stop member 1 force
06/26/2014 10:44:43 1 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
(4) After member 1 stop : connection is rerouted to other member 0
----------------------------------------------------------------------
Thu Jun 26 10:44:46 EST 2014
----------------------------------------------------------------------
MEMBER CACHED_TIMESTAMP HOSTNAME PORT_NUMBER SSL_PORT_NUMBER PRIORITY
------ -------------------------- ------------------------------ ----------- --------------- --------
0 2014-06-26-10.44.45.000000 db2ps2.au.ibm.com 61118 0 100
0 2014-06-26-10.44.45.000000 db2ps3.au.ibm.com 61118 0 0
2 record(s) selected.
MEMBER APP_HANDLE APP_NAME CLIENT
------ ---------- -------------------- --------------------
0 619 db2jcc_application 192.168.153.65
1 record(s) selected.
MEMBER CONNECTIONS
------ -----------
0 1
8. From tomcat logs, we can see the connection rerouting was done successful.
( <TOMCAT_HOME>/logs/catalina.log )
| com.ibm.db2.jcc.am.ClientRerouteException: [jcc][t4][2027][11212][3.63.123] A connection failed but has been re-established. The host name or IP address is "db2ps2.au.ibm.com" and the service name or port number is 61,118. |
UID
ibm11140046