DB Connection timeouts intermittently cause tasks to fail.
The customer's IBM WebSphere Support advises that IM is not closing connections properly and is causing the connection pool to become exhausted.
We see many of the following errors which seem to always be followed by "PxSystemException:Error building data for matching events "
J2CA1010E: Connection not available; timed out waiting for 10 seconds.
at com.netegrity.llsdk6.imsimpl.jdbcmanagedobject.JDBCManagedObjectProvider.find(JDBCManagedObjectProvider.java:655)
at com.netegrity.llsdk6.imsimpl.jdbcmanagedobject.JDBCManagedObjectProvider.find(JDBCManagedObjectProvider.java:752)
at com.netegrity.llsdk6.imsimpl.jdbcmanagedobject.JDBCManagedObjectProvider.find(JDBCManagedObjectProvider.java:547)
at com.ca.identitymanager.policyxpress.engine.PxProvider.getPoliciesForEvent(PxProvider.java:93)
CA IDM to 14.3 CP2
Application Server: IBM WebSphere 8.5.5.17
Application: IDM 14.3 CP2
Database: SQL server 2017
DB Connection timeouts intermittently cause tasks to fail.
Customer's IBM WebSphere Support advises that IM is not closing connections properly and is causing the connection pool to become exhausted.
We see many of the following errors which seem to always be followed by "PxSystemException:Error building data for matching events "
J2CA1010E: Connection not available; timed out waiting for 10 seconds.
at com.netegrity.llsdk6.imsimpl.jdbcmanagedobject.JDBCManagedObjectProvider.find(JDBCManagedObjectProvider.java:655)
at com.netegrity.llsdk6.imsimpl.jdbcmanagedobject.JDBCManagedObjectProvider.find(JDBCManagedObjectProvider.java:752)
at com.netegrity.llsdk6.imsimpl.jdbcmanagedobject.JDBCManagedObjectProvider.find(JDBCManagedObjectProvider.java:547)
at com.ca.identitymanager.policyxpress.engine.PxProvider.getPoliciesForEvent(PxProvider.java:93)
... 23 more
Caused by: com.ibm.websphere.ce.j2c.ConnectionWaitTimeoutException: J2CA1010E: Connection not available; timed out waiting for 10 seconds.
at com.ibm.ejs.j2c.FreePool.createOrWaitForConnection(FreePool.java:1782)
at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:3907)
at com.ibm.ejs.j2c.PoolManager.reserve(PoolManager.java:3085)
at com.ibm.ejs.j2c.ConnectionManager.allocateMCWrapper(ConnectionManager.java:1554)
at com.ibm.ejs.j2c.ConnectionManager.allocateConnection(ConnectionManager.java:1033)
at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:644)
at com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource.getConnection(WSJdbcDataSource.java:611)
at com.netegrity.llsdk6.imsimpl.jdbcmanagedobject.JDBCManagedObjectProvider.find(JDBCManagedObjectProvider.java:557)
... 26 more
[11/11/20 12:40:28:782 PST] 000000f0 FreePool E J2CA0045E: Connection not available while invoking method createOrWaitForConnection for resource iam/im/jdbc/jdbc/objectstore....
IBM WAS is reporting "Connection reset by peer" intermittently for connection to the object store DB.
One of the Xpress Policies executes a stored procedure. The PX includes just a simple sql statement--nothing unusual. The customer's DB team provided their input where they were seeing Db connections get maxed out on the DB side. We have increased DB pool settings from max 200 to 350, which only increases the number of connection and task failures as the connections do not get returned to the pool.
Then we asked them to engage IBM WebSphere Support for some DB tracing on the WAS side. IBM 's analysis is that IDM is not closing connections properly.
IBM advises that we need to close all connections by invoking connection.close() method, we already perform. They also recommend using an "unshareable" DB connection setting,
Here is IBM's WAS input on root cause of the J2CA0045E issues:
"For server2, the last time when J2C trace enabled and the datasource was accessed, you can find one webcontainer 6 thread requested and allocated many connections as shared connection which indicates that it requested connections many times with different parameters everytime, please contact your development team to fix the issue, there is no the application's information in the trace: If it do need to get connections from the pool with different parameters, from trace, it is in local transaction, it is best to let the application use connections as unshared, so the connections can be returned to free pool after application invoke connection.close() method when use them as shared connection, only after the local transaction/servlet end, the connections can be returned to free pool and cause the connection pool usage inefficiently and easy to trigger J2CA0045E issue.
How to change from Shareable to Unshareable Connections
Implement IBM WebSphere Support's recommendation to make the db connection "unshareable."
https://www.ibm.com/support/pages/node/1080489?lang=en
From the IBM Administrative Console , navigate to the following path:
Resource >JDBC > Data sources > < IDM datasource (ex. objectstore)> Connection pool properties > Connection pool custom properties
set
Name: defaultConnectionTypeOverride
Value: unshare