J2CA0045E: Connection not available

    J2CA0045E: Connection not available

    Error:

    [DATE DateXX GMT] 0000076d FreePool E J2CA0045E: Connection not available while invoking method createOrWaitForConnection for resource
     jdbc/XXX.
       Their is no change in the environment as well as on the application.

    Issue solution:

    J2CA0045E messages and from the javacore that the connection pool is full (50 threads). In order to diagnose the most efficient way is to run a trace and collect the connection leak logic data as per the following technote –

    How to troubleshoot J2CA0045E connection pooling problems

    http://www-01.ibm.com/support/docview.wss?uid=swg21385033

    One pre-requisite of this is to ensure you are on a fixpack level which includes the apar PK83902 which for V6.0.x is 6.0.2.37. This apar includes the diagnostic code necessary. I see that you are very back level at 6.0.2.19 ? Before we go any further please can you confirm to me whether it is going to be possible for you to upgrade to this later fixpack level and if not then please explain so we can progress this accordingly.

    J2CA0045E errors along with connection time out exceptions which happens normally when the JDBC connection pool is maxed out and all new incoming connection requests have to be queued waiting for a connection to become free on the JDBC connection pool. The javacore files showed upwards of 50 threads that were waiting for a datasource connection. This can typically occur for one of three reasons: either the maximum pool size value is too small, connections are not being closed by the application, or connections are not being returned to the free pool even after being closed by the application until the Local Transaction Containment (LTC) is completed.

    To remedy a pool size that is too small, increase the maximum pool size value. Keep in mind that the maximum pool size value is per jvm, so if the application is deployed to a cluster, each cluster member will maintain its own pool. Therefore, the database server will need to be able to receive a total number of connections which is calculated by the maximum pool size value times the number of jvm's in the cluster.

    http://publib.boulder.ibm.com/infocenter/wasinfo/v6r0/index.jsp?topic=/com.ibm.websphere.nd.multiplatform.doc/info/ae/ae/udat_conpoolset.html

    To remedy an application that is not closing connections, consult with you application developer to ensure the application is properly implementing the get/use/close scenario. An application has  to always call close() method on a jdbc conenction whenever its done using it for the connection to be returned back to the free pool. If an application fails to close a jdbc connection after done using it, it will not be returned to the pool and after a while a the connection pool will run out of connections and J2CA0045E will be logged into the systemout. For this, I highly recommend that you double check with your application developers that they do infact follow the get/use/close patter whereby a JDBC connection is retrieved from a jdbc conneciton pool, used and the closed upon being done used.

    To remedy connections that are not being returned to the free pool until the LTC is completed, the application should obtain an unshared connection instead of a shared connection. This can be accomplished in one of two ways. The common method is to program the application to perform an indirect lookup of the datasource via a resource reference (using the java:comp/env/name_of_resource_reference syntax). In addition to reprogramming the application to perform an indirect lookup, a resource reference will need to be created within the application. The jndi name of the datasource will be configured on the resource reference along with setting the sharing scope to unshared. The application will need to be redeployed after this is done. This is discussed further in the following document...

    How to change from shared to unshared connections

    http://www-01.ibm.com/support/docview.wss?uid=swg21223465

    The second means of setting the application to obtain an unshared connection would be to configure one of two new datasource custom properties: defaultConnectionTypeOverride or globalConnectionTypeOverride. However, these custom properties are not available until FixPack v6.1.0.23. You would need to upgrade WebSphere Application Server to at least v6.1.0.23 to be able to configure one of the custom properties. The benefit of the custom properties is that it alleviates the need to reconfigure the application to perform the indirect datasource lookup as discussed above. Following are two links to documents which describe the two custom properties...

    PK75717: ADD CUSTOM PROPERTIES TO CONTROL CONNECTION SHARING

    http://www-01.ibm.com/support/docview.wss?rs=180&uid=swg1PK75717

    Tuning connection pools (see the last section under "Control connection pool sharing")

    http://publib.boulder.ibm.com/infocenter/wasinfo/v6r1/index.jsp?topic=/com.ibm.websphere.nd.multiplatform.doc/info/ae/ae/tdat_conpoolman.html

    We can also obtain additional data by enabling connection pool tracing. This will help in clarifying which of the above recommendations is most appropriate. Following is a link to the connection pool MustGather document...MustGather: Connection pooling problems for WebSphere Application Server

    http://www-01.ibm.com/support/docview.wss?rs=180&uid=swg21254645

    Tracing can be enabled dynamically, which does not require a restart of the application server, by following the procedure under "Dynamic trace" in the following document...Setting up a trace

    http://www-01.ibm.com/support/docview.wss?rs=180&uid=swg21254706

    Set the maximum trace file size to 250MB and the number of historical files to 10.