How to adjust JDBC/Oracle application's connection pool

  • 7011109
  • 22-Mar-2011
  • 27-Mar-2013

Environment

NetIQ Access Governance Suite

Situation

During a refresh with refreshLinks enabled on identities with accounts on a JDBC application, multiple SQL stmts execute on the JDBC application. To avoid the overhead in repeatedly creating/destroying identitical connections to the same application,Access Governance Suite provides a per-application connection pool.

The per-application "connection pool" logic manages a hashtable of key/value pairs. The key concatenates the application's loginname
and URL (host, port and database) strings.  From this key, the logic then associates a pool of JDBC connections.

If the hashing logic computes a (new) key that needs a new pool, then the logic checks the application object map for creation time settings ("pool.maxActive""pool.maxWait").

As an example, for an application with logon "spUser" and URL "production.mybank:1521:DBTBL", then the hashtable key string becomes:

"spUser@production.mybank:1521:DBTBL"

All SQL operations that map to the same key (whether from same or different applications) get a connection from the same pool. When SQL operations map to different keys, then they get connections from different pools.

As a general rule, a JDBC aggregation task uses (at least) 2 JDBC connections, one to step thru the list of entries and another to query for each entry's values. Other connections might be necessary for rule evaluation and such.

To disable pooling on an aggregation/application, set "pool.disablePooling=true" in the application object. Similarly for "pool.maxActive" setting.  The current option list follows. Please plan carefully before making any adjustments:

"pool.disablePooling" (false; flag to disable)
"pool.maxWait" (60000; msec wait time for an idle connection)
"pool.maxActive" (10; max number of connections)
"pool.maxIdle" (5; max number of idle connections)
"pool.evictRuns" (300000; msec wait time btwn closing idle connections)
"pool.minEvictIdle" (600000; msec min idle time to close connection)

While Access Governance Suite contains similar logic that connects to a RDBMS to persist the in-memory (java) object instances, yet the "repository" cfg is separate from the per-application cfg.  The "iiq.properties" file contains cfg info only for the "repository" RDBMS.