Importance of Pool size Configuration of Data Server in SAP MII

Recently I have faced some issues in my project regarding pool size of the database. In my project we are using Oracle database to store all the tables and data as per requirement. But recently while we were trying to store data in the db tables then we got a very strange error.

The error message is like that,

com.sap.engine.services.dbpool.exceptions.BaseSQLException: ResourceException occurred in method ConnectionFactoryImpl.getConnection(): com.sap.engine.services.connector.exceptions.BaseResourceException: Cannot get connection for 0 seconds. Possible reasons: 1) Connections are cached within SystemThread(can be any server service or any code invoked within SystemThread in the SAP J2EE Engine) or they are allocated from a non transactional ConnectionFactory. In case of transactional ConnectionFactory used from Application Thread there is an automatic mechanism which detects unclosed connections, 2)The pool size of adapter “———” is not enough according to the current load of the system . In case 1) the solution is to check for cached connections using the Connector Service list_conns command or in case 2), to increase the size of the pool.

Day by day we were getting this message very frequently. Then we have contacted to DBA to discuss the issue. After discussion, they have increased the connection pool size in the database level. so the error got resolved for few days. But after some days it occurred again. In that way, DBA have increased the pool size 3 times, but still the error persist.

At this point, after debugging and crosscheck, we found that we have missed a basic configuration, which is occurring the issue.

Observation:

• While user configures any MII system, they need to configure the “Data Servers” from “Data Service” tab in the MII Admin menu.

• For a particular data server, while user set the connection details, there user needs to configure few details. To solve this issue user needs to point few fields among them.

• Pool Max: Pool max is the parameter where user can set the maximum pool size which will be allowed by the DB server based on the db server pool size. User can put the same value as in DB connection pool size. By defining this, MII can open maximum this no of connection at a time.
  

                       Default value for Pool max is 100

• Pool Size: Pool size is the parameter which indicate the no of connections MII can open at a time.

                       Default value for Pool size is 1

• Wait Time: Wait time is the parameter, which defines the max wait time for a connection if the pool is full.

Solution:

In our system, the pool size was 1 by default. So it creates a bottleneck in MII. When ever our code executes it tries to create multiple connections to the DB, but MII passes them one by one. and if any one of the connection takes more than 30 min then rest of the connections which are in waiting state there get terminated.

So we increase the pool max and the pool size to solve the issue. Only the point user need to remember while configuring,

Pool size <= Pool Max (always)