JDBC DATASOURCE ISSUES:

 Many times we may encounter Datasource overlosd issues.
At those times you may track below kind of errors in logs.

<BEA-001129> <Received exception while creating connection for pool "ARS213NotificDBConn": Cannot open database "ARS271TEST" requested by the login. The login failed. ClientConnectionId:5c6a702b-00ad-4d38-b031-2645f4a89563.>
<Mar 5, 2013 5:32:34 AM GMT> <Error> <JDBC> <BEA-001112> <Test "SELECT 1" set up for pool "SFA316NytificDBConn" failed with exception: "com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset".>
<Mar 5, 2013 5:32:35 AM GMT> <Error> <JDBC> <BEA-001112> <Test "SELECT 1" set up for pool "SFA271ARDBConn" failed with exception: "com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset".>
<Mar 5, 2013 5:32:45 AM GMT> <Error> <JDBC> <BEA-001112> <Test "SELECT 1" set up for pool "SFA315HeldDBConn" failed with exception: "com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset".>
<Mar 5, 2013 5:32:52 AM GMT> <Error> <JDBC> <BEA-001112> <Test "SELECT 1" set up for pool "SFA316ARDBConn" failed with exception: "com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset".>
<Mar 5, 2013 5:33:07 AM GMT> <Error> <JDBC> <BEA-001112> <Test "SELECT 1" set up for pool "SFA319ARDBConn" failed with exception: "com.microsoft.sqlserver.jdbc.SQLServerException: Connection reset".>


ERROR:

weblogic.common.resourcepool.ResourceDisabledException: Pool SFA213HeldDBConn is Suspended, cannot allocate resources to applications. 

Immediate Resolution:

login to your weblogic console:

left pane click on services->datasources->click on the datasource->control tab

Clear the statement cache / shutdown & start the datasource:

After the immdiate action lets now look at how we can avoid this reoccurance :

JDBC TIPS AND KNOWLEDGE:

 Tuning JDBC Connection Pool Initial Capacity

The InitialCapacity attribute of the JDBCConnectionPool element enables you to set the number of physical database connections to create when configuring the pool. If the server cannot create this number of connections, the creation of this connection pool will fail.
During development, it may be convenient to set the value of the InitialCapacity attribute to a low number to help the server start up faster. In production systems, consider setting the InitialCapacity value equal to the MaxCapacity attribute's default production mode setting of 25. This way, all database connections are acquired during server start-up. And if you need to tune the MaxCapacity value, make sure to set the InitialCapacity so that it equals the MaxCapacity value.
If InitialCapacity is less than MaxCapacity, the server needs to create additional database connections when its load is increased. When the server is under load, all resources should be working to complete requests as fast as possible, rather than creating new database connections.

Tuning JDBC Connection Pool Maximum Capacity

The MaxCapacity attribute of the JDBCConnectionPool element allows you to set the maximum number of physical database connections that a connection pool can contain. Different JDBC drivers and database servers might limit the number of possible physical connections.
The default settings for development and production mode are equal to the default number of execute threads: 15 for development mode; 25 for production mode. However, in production, it is advisable that the number of connections in the pool equal the number of concurrent client sessions that require JDBC connections. The pool capacity is independent of the number of execute threads in the server. There may be many more ongoing user sessions than there are execute threads.

  However in WEblogic 8:

It was adviced to Set the Maximum Capacity of the connection pool at least equal to the Execute Thread Count.

From WLS 9.0 series onwards,server has a single thread pool for requests from all applications. Similarly, all pending work is enqueued in a common priority-based queue. The thread count is automatically tuned to achieve maximum overall throughput. One of the major differences between execute queues and the new thread scheduling model is that the thread count does not need to be set. In earlier releases, customers defined new thread pools and configured their size to avoid deadlocks and provide differentiated service. It is quite difficult to determine the exact number of threads needed in production to achieve optimal throughput and avoid deadlocks. WebLogic Server 9.0 is self-tuned, dynamically adjusting the number of threads to avoid deadlocks and achieve optimal throughput subject to concurrency constraints. The maximum threads constraint can be used to limit the maximum number of concurrent threads given to all WorkManagers that share the constraint.This constraint should be used in very special cases and should not be confused with the thread count parameter in execute queues. The minimum threads constraint takes an integer value that specifies the number of threads that should be assigned to this constraint to prevent server-to-server deadlocks.

There is no Execute Queue concept from Weblogic server 9 version onwards.They have intoduced WORK MANAGERS instead of that.Now WebLogic Server uses a single thread pool, in which all types of work are executed. WebLogic Server prioritizes work based on rules you define, and run-time metrics, including the actual time it takes to execute a request and the rate at which requests are entering and leaving the pool.

Test Connections On Reserve(do not check)
Enables WebLogic Server to test a connection before giving it to a client. (Requires that you specify a Test Table Name.)
The test adds a small delay in serving the client's request for a connection from the pool, but ensures that the client receives a viable connection.
MBean Attribute (Does not apply to application modules) :
JDBCConnectionPoolParamsBean.TestConnectionsOnReserve



Statement Cache Size(SET to 10)
The number of prepared and callable statements stored in the cache. (This may increase server performance.)
WebLogic Server can reuse statements in the cache without reloading the statements, which can increase server performance. Each connection in the connection pool has its own cache of statements.
Setting the size of the statement cache to 0 turns off statement caching.
MBean Attribute (Does not apply to application modules) :
JDBCConnectionPoolParamsBean.StatementCacheSize
Minimum value: 0
Maximum value: 1024


When using DataSource objects for a connection pool, use the Honors Global Transaction option to create a TxDataSource.

 The only time you should use a non-Tx Data Source is when you want to do some work on the database that you do not want to include in the current transaction.



When configuring a connection pool to use with WebLogic JMS JDBC Store, use non-XA database drivers.