Total Visitors

Saturday, December 7, 2013

Auto Connection after Network Failure in JBoss 7.1

Put the following inside <datasource> .... </datasource> :- 
  <validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
<validate-on-match>true</validate-on-match>
<background-validation>false</background-validation>
<stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
  </validation>
<timeout>
<blocking-timeout-millis>5000</blocking-timeout-millis>
<idle-timeout-minutes>1</idle-timeout-minutes>
</timeout>

----- OR -----

<validation>
<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
<validate-on-match>true</validate-on-match>
<background-validation>false</background-validation>
</validation>
<timeout>
<blocking-timeout-millis>5000</blocking-timeout-millis>
<idle-timeout-minutes>5</idle-timeout-minutes>
</timeout>

----- OR -----
<validation>
<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
<validate-on-match>true</validate-on-match>
<background-validation>false</background-validation>
</validation>


----------EXPLAINATIONS-------------

--> <valid-connection-checker-class-name> :- a class that can check whether a connection is valid using a vendor specific mechanism

--> <check-valid-connection-sql> :- an sql statement that is executed before it is checked out from the pool (see <validate-on-match>) to make sure
it is still valid.If the sql fails, the connection is closed and new ones created. Also it will be used by <background-validation>

--> <validate-on-match> :- whether to validate the connection when the JCA layer matches a managed connection (i.e. when the connection is checked out
of the pool). With the addition of <background-validation> this is not necessarily required.
Note: Specifying "true" for <validate-on-match> is typically not done in conjunction with specifying "true" for <background-validation> as
this would be overkill in most scenarios.  Default is true.

--> <background-validation> :- In JBoss 4.0.5 background connection validation was added to help reduce the overall load on the RDBMS system  when validating a connection. When using this feature, JBoss will attempt to validate the current connections in the pool is a separate thread (ConnectionValidator). This must be set to true for <background-validation-minutes> to take effect.  Default is false.

--> <stale-connection-checker-class-name> :- An implementation of org.jboss.resource.adapter.jdbc.StateConnectionChecker that will decide whether
SQLExceptions that notify of bad connections throw org.jboss.resource.adapter.jdbc.StateConnectionException (from JBoss5)

--> <exception-sorter-class-name> :- a class that looks at vendor specific messages to determine whether sql errors are fatal and thus the connection  should be destroyed.  If none specified, no errors will be treated as fatal.

--> <blocking-timeout-millis> :- the length of time to wait for a connection to become available when all the connections are checked out. Default is 30000 (30 seconds).

--> <idle-timeout-minutes> :- indicates the maximum time a connection may be idle before being closed. Setting to 0 disables it.  Default is 15 minutes.


3 comments:

  1. Is it mandatory that tag should be used in conjunction with . Because I have just provided check-valid-connection-sql in my standalone.xml with a invalid query and the datasource seems to be returning a connection without any error on JBOSS 6.2 EAP.

    ReplyDelete
    Replies
    1. Is it mandatory that "check-valid-connection-sql" tag should be used in conjunction with "validate-on-match" tag . Because I have just provided check-valid-connection-sql in my standalone.xml with a invalid query and the datasource seems to be returning a connection without any error on JBOSS 6.2 EAP.

      Delete
  2. Also in case of network outage the JBOSS runs into infinate loop to create a connection. Do we have any attributes to state how many max retry should it try and at what interval.

    Thanks in advance

    ReplyDelete