Idle Database Connections in Advanced authentication

Document ID : KB000056958
Last Modified Date : 14/02/2018
Show Technical Document Details

The database connections made from Advanced Authentication (formerly Arcot) depend on two database driver types, JDBC and ODBC regardless of which database engine is used. With both drivers, the product relies on these driver's connection pooling to create and release database connections. Database connection pooling is the concept where connections are reused instead of requiring each transaction to create a new connection. This reduces the computing overhead  of creating and releasing connections for both our application as well as the database engine itself. Since we are using 2 different drivers, our methods and configurations for these pools differ and require individual management for each. This document will identify the configurations  to allow the administrator to customize these settings for the best performance in their environment.

It is important to understand where these are all configured from and how each component will use these connections due to the impact it will have on the local component, as well as the database engine itself. Each connection pool created will have its own min and max setting. For example, if I have 2 riskfort services, 2 webfort, 1 casemanagemnent, 2 uds, 2 statemanagers, and 1 arcotadmin and assume the minimum values are all set to 4. The total number of database connections would be (2x4, 2x4, 1x4, 2x4, 2x4, 1x4) = 40 database connections simply by starting the applications. Assuming the max value for each was 32; the maximum number of connections would be (2x32, 2x32, 1x32, 2x32, 2x32, 1x32) = 320 connections. If we are not cleaning up idle connections this can have resource implications on the database engine especially when using a shared database engine. If the Web application components (WARs) are all installed in a single instance it is possible for these application to share the application database pool effectively reducing the amount of total and idle connections to the database.

The JDBC database driver covers all of the application components that require database connectivity and run on Tomcat, Websphere, Jboss, or other Java based application servers. This includes arcotadmin, UDS, and the statemanager. All three applications use the JDBC drivers connection pooling technology but the statemanager does use its own configuration method.

 

The statemanager pool is created in the application server directly (for Tomcat this would be defined in the server.xml or context.xml) and is directly controlled by the web application server as opposed to the Arcot application or the arcotcommon.ini. This is true for all settings required to create the database connection pool in this manner. It is possible when using the Web Application initiated database connection pools that all applications can use the same connection pool but this is not automatically generated through the Advance Authentication install. Changes to this pool can only be made in the Application Server itself.

The Arcotadmin and UDS applications will initiate the creation of the database connection pool through the configuration set in the arcotcommon.ini. The connection pool does not differ from the type of connection pool defined in the Application servers except that the pool will be initialized when the application starts up.

 

On Windows, edit the %ARCOT_HOME%\conf\arcotcommon.ini

On Linux, edit the $ARCOT_HOME/conf/arcotcommon.ini

     [arcot/db/dbconfig]

MaxIdleConnections=64       # in number of connections, if MaxConnections is same or lower it effectively will never release idle connections

 

Setting this too low may have negative application performance.  In deployment where heavy load is not expected, this value may be decreased to 16.  Default: 64.

Note: Other Java Web application components like the AFM, AFMwizard and assorted Sample applications do not connect to the Advanced Authentication database and therefore have no impact on open connections.

 

The ODBC database driver covers the Webfort, Riskfort and Case Management services. These services will create connection pools based on the configuration options from DataDirect Connect for ODBC (32 bit driver). By default the connection pool will not remove idle database connections without an actual network or connection issue. Since the odbc.ini is a shared configuration, all three will have the same configuration but each tool will create and use its own individual connection pools. If this value is set to 0 then the only way to clear these connections is to recycle these services.

 

On Windows, modify the Registry

     [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\arcotdb]

          "Pooling"="0"     #  set to 1 to enable pooling and LoadBalanceTimeout

"LoadBalanceTimeout"="0"  # in seconds, 0 is unlimited amount of time

       Or use the 32 bit GIU %windir%\SysWOW64\odbcad32.exe to modifiy the "Pooling Tab" to

              set a check box in the Connection Pooling and

              set a value in seconds in the LoadBalance Timeout field

 

              odbc3.png

 

On Linux, edit the $ARCOT_HOME/odbc32v714wf/odbc.ini

      [YourSpecificDSN_name]

LoadBalanceTimeout=0  # in seconds, 0 is unlimited amount of time

 

Setting this too short  may have negative application performance. Set this to the number of seconds the driver will try to clean up the idle connection (ex 300 sec is  5 min).

This process will not remove any idle connections below the min connections set.