IBM Support

50 DB2 Nuggets #39: Tech Tip - Configuring pureScale Client Affinity with WebLogic

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 )


image

 

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'.


image

 

 



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.

--------

image

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

---------

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
%>

 

image

 

 


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.

 

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

UID

ibm11140070