Technical Blog Post
Abstract
50 DB2 Nuggets #39: Tech Tip - Configuring pureScale Client Affinity with WebLogic
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 have 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 Weblogic 10.3.5.0 and pureScale 10.1.
So I hope this helps for DBAs who need to configure the client affinity on Oracle WebLogic.
(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.
$ cat /etc/hosts
.....
### 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. Weblogic download : With License agreement, I could get the developer edition.
( That's enough for test. )
http://www.oracle.com/technetwork/middleware/weblogic/downloads/wls-main-097127.html
For 64 bit JVM, we can use the generic installation jar file. ( i.e. wls1035_generic.jar )
2. Weblogic installation.(Refer the following link.)
http://docs.oracle.com/cd/E24902_01/doc.91/e18840/install_config_win.htm
3. Set db2jcc.jar to the Weblogic classpath.
(1) I used the sample Weblogic instance of following path
weblogic@db2ps1:~/Oracle/Middleware/wlserver_10.3/samples/domains/wl_server> pwd
/home/weblogic/Oracle/Middleware/wlserver_10.3/samples/domains/wl_server
(2) add db2jcc.jar to the CLASSPATH.
weblogic@db2ps1:~/Oracle/Middleware/wlserver_10.3/samples/domains/wl_server> cat setExamplesEnv.sh
......
echo "Modifying classpath for the samples"
CLASSPATH="${CLASSPATH}${CLASSPATHSEP}/home/weblogic/Oracle/Middleware/wlserver_10.3/samples/server/examples/build/clientclasses"
export CLASSPATH
####### junsulee
CLASSPATH=/home/weblogic/db2jcc.jar:$CLASSPATH
......
4. Start Weblogic ( If we issue the ctrl+c or close the terminal, Weblogic will be killed. So maintain the Weblogic ssh/telnet terminal. )
weblogic@db2ps1:~/Oracle/Middleware/wlserver_10.3/samples/domains/wl_server> ./startWebLogic.sh
......
<Jun 27, 2014 11:55:48 AM EST> <Warning> <Server> <BEA-002611> <Hostname "localhost", maps to multiple IP addresses: 127.0.0.1, 0:0:0:0:0:0:0:1>
<Jun 27, 2014 11:55:48 AM EST> <Notice> <Server> <BEA-002613> <Channel "Default[1]" is now listening on fe80:0:0:0:250:56ff:febc:8e:7001 for protocols iiop, t3, ldap, snmp, http.>
<Jun 27, 2014 11:55:48 AM EST> <Notice> <Server> <BEA-002613> <Channel "Default[4]" is now listening on 0:0:0:0:0:0:0:1:7001 for protocols iiop, t3, ldap, snmp, http.>
<Jun 27, 2014 11:55:48 AM EST> <Notice> <Server> <BEA-002613> <Channel "DefaultSecure[1]" is now listening on fe80:0:0:0:250:56ff:febc:8e:7002 for protocols iiops, t3s, ldaps, https.>
<Jun 27, 2014 11:55:48 AM EST> <Notice> <Server> <BEA-002613> <Channel "Default[2]" is now listening on 127.0.0.1:7001 for protocols iiop, t3, ldap, snmp, http.>
<Jun 27, 2014 11:55:48 AM EST> <Notice> <Server> <BEA-002613> <Channel "Default" is now listening on 192.168.153.65:7001 for protocols iiop, t3, ldap, snmp, http.>
......
5. Connect to Weblogic console ( http://ip:7001/console ) : The default login account is 'weblogic/welcome1'.
6. Configure Data Source JNDI Resource.
Especially, in the JDBC setting followings should be set. ( This setting was tested. )
Caution : the property name is case-sensitive.
For example clientRerouteAlternateServername will not work. Use 'clientRerouteAlternateServerName"
| 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 |
Followings are configuration capture.
--------
---------
7. Test JSP program for connecting to pureScale member 1.
| weblogic@db2ps1:~/Oracle/Middleware/wlserver_10.3/samples/server/examples/build/examplesWebApp> pwd /home/weblogic/Oracle/Middleware/wlserver_10.3/samples/server/examples/build/examplesWebApp weblogic@db2ps1:~/Oracle/Middleware/wlserver_10.3/samples/server/examples/build/examplesWebApp> cat testjdbc.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 psinst2.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 finallya %> |
7. 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 Weblogic log, there are no application errors since the instance started. It means the seamless failover was successful.
| <Jun 26, 2014 10:42:47 AM EST> <Notice> <Server> <BEA-002613> <Channel "DefaultSecure" is now listening on 192.168.153.65:7002 for protocols iiops, t3s, ldaps, https.> <Jun 26, 2014 10:42:47 AM EST> <Notice> <Server> <BEA-002613> <Channel "DefaultSecure[3]" is now listening on 127.0.0.2:7002 for protocols iiops, t3s, ldaps, https.> <Jun 26, 2014 10:42:47 AM EST> <Notice> <WebLogicServer> <BEA-000331> <Started WebLogic Admin Server "examplesServer" for domain "wl_server" running in Development Mode> <Jun 26, 2014 10:42:47 AM EST> <Notice> <WebLogicServer> <BEA-000365> <Server state changed to RUNNING> <Jun 26, 2014 10:42:47 AM EST> <Notice> <WebLogicServer> <BEA-000360> <Server started in RUNNING mode> Could not invoke browser: No X11 DISPLAY variable was set, but this program performed an operation which requires it. |
UID
ibm11140070