Topic
9 replies Latest Post - ‏2011-05-30T01:34:16Z by SystemAdmin
SystemAdmin
SystemAdmin
462 Posts
ACCEPTED ANSWER

Pinned topic How to set multi-data source failover correctly (alternateResourceJNDIName)

‏2011-05-25T01:42:06Z |
I have a question about multi-data source failover.

1) I made two datasource which connect to different DBs which has SAMPLE databases - same name
A - db2(jdbc/db2) B - db2_2(jdbc/db2_2)

2) I made a custom properties in connection pool of A(db2)
alternateResourceJNDIName = jdbc/db2_2
failureThreshold = 5

3) I set MBean attributes and operations according to WAS v8.0 Beta Infocenter
(http://publib.boulder.ibm.com/infocenter/wasinfo/beta/topic/com.ibm.websphere.nd.doc/info/ae/ae/cdat_dsfailover.html?resultof=%22%66%61%69%6c%4f%76%65%72%54%6f%41%6c%74%65%72%6e%61%74%65%52%65%73%6f%75%72%63%65%22%20%22%66%61%69%6c%6f%76%65%72%74%6f%61%6c%74%65%72%6e%61%74%65%72%65%73%6f%75%72%63%22%20)

set varname $AdminControl queryNames :,node=kr050578Node04,type=DataSource,process=server1,name=db2
$AdminControl setAttribute $varname resourceFailOver True

$AdminControl invoke $varname failOverToAlternateResource {"automated"}

set varname $AdminControl queryNames :,node=kr050578Node04,type=DataSource,process=server1,name=db2_2
$AdminControl setAttribute $varname populateAlternateResource True

$AdminConfig save

4. call to servlet to query 'select' to Database using A(db2) datasource

5. stop DB2 instance which is connected with A(db2) datasource (db2stop)

6. call to servlet to query 'select' again...
The exception is occured

7. call to servlet to query 'select' again over 5 times...
However, it didn't work like what I expected......it means it didn't failover to alternative resource JNDI - 'jdbc/db2_2'....
Is my procedure I did wrong ?

Could you guide me what I will do more to set multi-data source failover correctly ?

Thanks,
Updated on 2011-05-30T01:34:16Z at 2011-05-30T01:34:16Z by SystemAdmin
  • ladydi953
    ladydi953
    3 Posts
    ACCEPTED ANSWER

    Re: How to set multi-data source failover correctly (alternateResourceJNDIName)

    ‏2011-05-25T11:39:07Z  in response to SystemAdmin
    Hi,
    I have a couple of questions for you.

    (1) > Are both the primary (A-db2(jdbc/db2) and alternate datasource (B-db2_2(jdbc/db2_2) defined on the same node? The primary and alternate data sources are required to be on the node.
    (1) > Is the primary (A-db2(jdbc/db2)) pointing to one DB2 instance and the alternate datasource (B-db2_2(jdbc/db2_2)) pointing to a difference DB2 instance that can access the same sample database?
    (1) > What driver type are the connectors? JDBC Type 2 or JDBC Type 4? Both types can be used. Type 2 however requires the database to be local to where the WebSphere server is running, which the Type 4 can access the database from anywhere in the sysplex.

    When you recycled your application server, make sure you do not see "Message J2CA0683E is issued to indicate that the failover feature has been disabled" in your logs. This message usually means that one of the data sources definition isn't setup correctly, or the connection using the DB2 instance isn't successful.

    I have used the connection custom property definition
    alternateResourceJNDIName = jdbc/db2_2 failureThreshold = 5

    but not the MBean.. I will check with the developer.
  • frowe
    frowe
    9 Posts
    ACCEPTED ANSWER

    Re: How to set multi-data source failover correctly (alternateResourceJNDIName)

    ‏2011-05-25T11:58:30Z  in response to SystemAdmin
    In the list of steps you described, I don't see where you restarted the server after configuring resource workload rerouting (also known as data source failover). You must restart the server after configuration.

    If you do so and failover is still not occurring, are there are any errors or warnings in the server log?

    Fred Rowe

    Senior Software Engineer
    WebSphere Architect
    IBM Software Group
  • frowe
    frowe
    9 Posts
    ACCEPTED ANSWER

    Re: How to set multi-data source failover correctly (alternateResourceJNDIName)

    ‏2011-05-25T12:07:20Z  in response to SystemAdmin
    A clarification of my last post is required. Once you have added the datasource custom property "alternateResourceJNDIName" to the primary resource connection pool and created the alternate resource, you must restart the server before failover will be enabled. After that restart,
    as described in the InfoCenter topic, if you are using the Datasource MBean to configure the resources, you do not have to restart the server for your changes to take effect. If instead you are using wsadmin or the admin console to config the other properties, you will need to restart the server for the changes to take effect.

    Fred Rowe

    Senior Software Engineer
    WebSphere Architect
    IBM Software Group
  • SystemAdmin
    SystemAdmin
    462 Posts
    ACCEPTED ANSWER

    Re: How to set multi-data source failover correctly (alternateResourceJNDIName)

    ‏2011-05-27T08:37:28Z  in response to SystemAdmin
    Let me answer to your questions...please check it and give me expert adviser

    (1) > Are both the primary (A-db2(jdbc/db2) and alternate datasource (B-db2_2(jdbc/db2_2) defined on the same node? The primary and alternate data sources are required to be on the node.
    -> yes, both datasource defined on the same node.

    (1) > Is the primary (A-db2(jdbc/db2)) pointing to one DB2 instance and the alternate datasource (B-db2_2(jdbc/db2_2)) pointing to a difference DB2 instance that can access the same sample database?
    -> yes...I made two DB2 instance to check this function...
    DB2 instance for jdbc/db2 on 50000 port, DB2 instance for jdbc/db2_2 on 60000 port
    two DB2 instance has sample database and table with same schema..

    (1) > What driver type are the connectors? JDBC Type 2 or JDBC Type 4? Both types can be used. Type 2 however requires the database to be local to where the WebSphere server is running, which the Type 4 can access the database from anywhere in the sysplex.
    -> JDBC Type 4

    In addition, I've tried many times this issue after restart WAS...
    but it failed...with below message

    11. 5. 27 17:30:20:278 KST 00000007 DataSourceCon A J2CA0680I: JNDI 이름이 jdbc/db2인 구성된 1차 자원을 사용할 수 없습니다. JNDI 이름이 jdbc/db2_2인 구성된 대체 자원으로 새 요청이 라우트됩니다.
    11. 5. 27 17:30:20:264 KST 00000007 FfdcProvider W com.ibm.ws.ffdc.impl.FfdcProvider logIncident FFDC1003I: FFDC 문제가 C:\IBM\WebSphere8\AppServer\profiles\AppSrv01\logs\ffdc\server1_20b3a1d_11.05.27_17.30.20.2372653140834179680653.txt com.ibm.ws.rsadapter.spi.InternalGenericDataStoreHelper.getPooledCon 1298에서 발생했습니다.
    server1_20b3a1d_11.05.27_17.30.20.2372653140834179680653.txt-----------
    11. 5. 27 17:30:20:239 KST FFDC Exception:com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException SourceId:com.ibm.ws.rsadapter.spi.InternalGenericDataStoreHelper.getPooledCon ProbeId:1298
    com.ibm.db2.jcc.am.DisconnectNonTransientConnectionException: jcct4204311550http://3.62.56 예외 java.net.ConnectException: 다음 메시지와 함께 localhost/127.0.0.1 서버에 대한 소켓을 50,000 포트에서 열기 오류: Connection refused: connect. ERRORCODE=-4499, SQLSTATE=08001
    at com.ibm.db2.jcc.am.fd.a(fd.java:319)
    at com.ibm.db2.jcc.am.fd.a(fd.java:337)
    at com.ibm.db2.jcc.t4.wb.a(wb.java:418)
    at com.ibm.db2.jcc.t4.wb.<init>(wb.java:90)
    at com.ibm.db2.jcc.t4.a.y(a.java:320)
    at com.ibm.db2.jcc.t4.b.a(b.java:1818)
    at com.ibm.db2.jcc.am.jb.a(jb.java:608)
    at com.ibm.db2.jcc.am.jb.<init>(jb.java:560)
    at com.ibm.db2.jcc.t4.b.<init>(b.java:316)
    at com.ibm.db2.jcc.DB2PooledConnection.<init>(DB2PooledConnection.java:84)
    at com.ibm.db2.jcc.DB2ConnectionPoolDataSource.getPooledConnectionX(DB2ConnectionPoolDataSource.java:411)
    at com.ibm.db2.jcc.DB2ConnectionPoolDataSource.getPooledConnection(DB2ConnectionPoolDataSource.java:220)
    at com.ibm.ws.rsadapter.spi.InternalGenericDataStoreHelper$1.run(InternalGenericDataStoreHelper.java:1223)
    at com.ibm.ws.security.util.AccessController.doPrivileged(AccessController.java:118)
    at com.ibm.ws.rsadapter.spi.InternalGenericDataStoreHelper.getPooledConnection(InternalGenericDataStoreHelper.java:1260)
    at com.ibm.ws.rsadapter.spi.InternalDB2UniversalDataStoreHelper.getPooledConnection(InternalDB2UniversalDataStoreHelper.java:1786)
    at com.ibm.ws.rsadapter.spi.WSRdbDataSource.getPooledConnection(WSRdbDataSource.java:2003)
    at com.ibm.ws.rsadapter.spi.WSManagedConnectionFactoryImpl.getConnection(WSManagedConnectionFactoryImpl.java:1746)
    at com.ibm.ws.rsadapter.spi.WSManagedConnectionFactoryImpl.createManagedConnection(WSManagedConnectionFactoryImpl.java:1498)
    at com.ibm.ejs.j2c.PoolManager.createManagedConnection(PoolManager.java:9761)
    at com.ibm.ejs.j2c.DataSourceConnectionFactoryFailoverTimer.testPrimaryResource(DataSourceConnectionFactoryFailoverTimer.java:347)
    at com.ibm.ejs.j2c.DataSourceConnectionFactoryFailoverTimer.alarm(DataSourceConnectionFactoryFailoverTimer.java:540)
    at com.ibm.ejs.util.am._Alarm.run(_Alarm.java:127)
    at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1620)
    Caused by: java.net.ConnectException: Connection refused: connect
    at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:383)
    at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:245)
    at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:232)
    at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:377)
    at java.net.Socket.connect(Socket.java:539)
    at com.ibm.db2.jcc.t4.x.run(x.java:34)
    at java.security.AccessController.doPrivileged(AccessController.java:254)
    at com.ibm.db2.jcc.t4.wb.a(wb.java:404)
    ... 21 more
    • frowe
      frowe
      9 Posts
      ACCEPTED ANSWER

      Re: How to set multi-data source failover correctly (alternateResourceJNDIName)

      ‏2011-05-27T11:33:21Z  in response to SystemAdmin
      Are you able to successfully test the connection to each resource jdbc/db2 and jdbc/db2_2 using the datasource test connection feature?

      Fred Rowe

      Senior Software Engineer
      WebSphere Architect
      IBM Software Group
      • SystemAdmin
        SystemAdmin
        462 Posts
        ACCEPTED ANSWER

        Re: How to set multi-data source failover correctly (alternateResourceJNDIName)

        ‏2011-05-27T14:08:13Z  in response to frowe
        Sure, Test connections to each resouce jdbc/db2 and jdbc/db2_2 have no issue...
        In other words, each resource can make a connection to Database without any problem
        when I use the datasource test connection feature.
        • SystemAdmin
          SystemAdmin
          462 Posts
          ACCEPTED ANSWER

          Re: How to set multi-data source failover correctly (alternateResourceJNDIName)

          ‏2011-05-27T14:57:31Z  in response to SystemAdmin
          Hi,


          11. 5. 27 23:38:50:814 KST 00000006 DataSourceCon A J2CA0680I: JNDI 이름이 jdbc/db2인 구성된 1차 자원을 사용할 수 없습니다. JNDI 이름이 jdbc/db2_2인 구성된 대체 자원으로 새 요청이 라우트됩니다.
          11. 5. 27 23:38:45:222 KST 00000019 FfdcProvider W com.ibm.ws.ffdc.impl.FfdcProvider logIncident FFDC1003I: FFDC 문제가 C:\IBM\WebSphere8\AppServer\profiles\AppSrv01\logs\ffdc\server1_1f1c8ec_11.05.27_23.38.45.2123804722915946390415.txt com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection 299에서 발생했습니다.
          11. 5. 27 23:38:50:829 KST 00000006 DataSourceCon A J2CA0681I: JNDI 이름이 jdbc/db2인 구성된 1차 자원과 JNDI 이름이 jdbc/db2_2인 구성된 대체 자원을 사용할 수 없습니다.


          server1_1f1c8ec_11.05.27_23.38.45.2123804722915946390415.txt
          11. 5. 27 23:38:45:214 KST FFDC Exception:javax.resource.spi.ResourceAllocationException SourceId:com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection ProbeId:299 Reporter:com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource@210081d
          javax.resource.spi.ResourceAllocationException: DSRA8100E: DataSource에서 PooledConnection을(를) 가져오지 못했습니다.
          at com.ibm.ejs.j2c.FreePool.createManagedConnectionWithMCWrapper(FreePool.java:2371)
          at com.ibm.ejs.j2c.FreePool.createOrWaitForConnection(FreePool.java:1724)
          at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:3101)
          at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:2473)
          at com.ibm.ejs.j2c.ConnectionManager.allocateMCWrapper(ConnectionManager.java:1340)
          at com.ibm.ejs.j2c.ConnectionManager.allocateConnection(ConnectionManager.java:961)
          at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:669)
          at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:636)
          at com.ibm.juwlee.servlet.DB2SelectServlet.doGet(DB2SelectServlet.java:45)
          at javax.servlet.http.HttpServlet.service(HttpServlet.java:575)
          at javax.servlet.http.HttpServlet.service(HttpServlet.java:668)
          at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1147)
          at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:722)
          at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:449)
          at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:178)
          at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1020)
          at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:87)
          at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:883)
          at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1625)
          at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:197)
          at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:445)
          at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:504)
          at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:301)
          at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:83)
          at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:165)
          at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
          at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
          at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
          at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
          at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
          at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
          at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1620)
          Caused by: com.ibm.websphere.ce.cm.StaleConnectionException: jcct4204311550http://3.62.56 예외 java.net.ConnectException: 다음 메시지와 함께 localhost/127.0.0.1 서버에 대한 소켓을 50,000 포트에서 열기 오류: Connection refused: connect. ERRORCODE=-4499, SQLSTATE=08001 DSRA0010E: SQL 상태 = 08001, 오류 코드 = -4,499
          at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
          at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:56)
          at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:39)
          at java.lang.reflect.Constructor.newInstance(Constructor.java:527)
          at com.ibm.websphere.rsadapter.GenericDataStoreHelper.mapExceptionHelper(GenericDataStoreHelper.java:597)
          at com.ibm.websphere.rsadapter.GenericDataStoreHelper.mapException(GenericDataStoreHelper.java:656)
          at com.ibm.ws.rsadapter.AdapterUtil.mapException(AdapterUtil.java:2270)
          at com.ibm.ws.rsadapter.spi.WSRdbDataSource.getPooledConnection(WSRdbDataSource.java:2007)
          at com.ibm.ws.rsadapter.spi.WSManagedConnectionFactoryImpl.getConnection(WSManagedConnectionFactoryImpl.java:1746)
          at com.ibm.ws.rsadapter.spi.WSManagedConnectionFactoryImpl.createManagedConnection(WSManagedConnectionFactoryImpl.java:1498)
          at com.ibm.ejs.j2c.FreePool.createManagedConnectionWithMCWrapper(FreePool.java:2047)
          ... 31 more


          db2 DataSource(jdbc/db2) is connect to DB2 instance which listen 50000 port
          and db2_2 DataSource(jdbc/db2_2) is connect to DB2 instance which listen 60000 port

          According to FFDC log above,it failed to connect to DB2 through 50,000 port...
          However, alternative datasource would have connected to DB2 though 60,000 port
          because I've already set custom property 'alternateResourceJNDIName = jdbc/db2_2'...

          it's a little bit weird....
          • frowe
            frowe
            9 Posts
            ACCEPTED ANSWER

            Re: How to set multi-data source failover correctly (alternateResourceJNDIName)

            ‏2011-05-27T16:11:18Z  in response to SystemAdmin
            What do you have configured for the hostname of both datasources? localhost? Are both db2 instances running locally?

            Fred Rowe

            Senior Software Engineer
            WebSphere Architect
            IBM Software Group
            • SystemAdmin
              SystemAdmin
              462 Posts
              ACCEPTED ANSWER

              Re: How to set multi-data source failover correctly (alternateResourceJNDIName)

              ‏2011-05-30T01:34:16Z  in response to frowe
              yes, both db2 instances running locally...it menas I've configured hostname of both datasources to 'localhost'...

              Thanks,