Middle-tier database connection pooling

The IFS middle-tier makes use of three separate pools of database connections. Such connections are shared between multiple users. The three connection pools serves different use cases and have very different behavior.

Overview of IFS Applications three separate pools of database connections

Contents

• Data Sources
• PL/SQL Access

Data Sources

Two of the database connection pools are managed by the application server as Data Sources:

• fndbas
  This connection pool is used for middle-tier Activities and Services, such as bizAPIs, web services, some IFS Enterprise Explorer framework services, IFS Solution Manager, etc.
  All connections are created as user IFSSYS, but when a connection is to be used by a client call the current Foundation1 user identity is temporarily switched with a call to Login_SYS.Init_Fnd_Session (the Oracle database user is still IFSSYS). The connections are shared between all interactive clients and service consumers.
  When monitoring database sessions, connections from this pool are identified as category Activity/Service.
  The minimum and maximum size of the connection pool (number of database connections) can be configured using the Installer. The size of the connection pool will vary, depending on the current load, between these configured sizes.
• fndbasXA
  This connection pool is used only by the Application Server integration with Oracle Advanced Queuing.
  Both the Batch Processor and Streams use connections in this pool in order to subscribe to messages sent from the database to the application server. Such connections are never used to invoke business logic and are never used by interactive clients.
  All connections are created as user IFSSYS.
  When monitoring database sessions, connections from this pool are identified as category JMS-AQ integration.

Both connection pools can be monitored using IFS System Monitoring and configured using the Installer.

PL/SQL Access

This type of connection pool is used by interactive clients when accessing database business logic directly. The behavior of this connection pool is very different from the two application server managed connection pools. All connections are created as user IFSSYS, but rather than temporarily switching only the Foundation1 user identity when a connection is in use the Oracle database user is also switched using Oracle proxy authentication. Connections are shared between clients, but the pool will attempt to reuse an already switched connection (where the database user identity matches that of the current user) as a way to minimize the cost and impact of database user switching. If no such idle connection is available the database identity will be switched to that of the current user.

There are two scenarios where connections are reserved for a particular client session - when the client keeps an open cursor (this typically happens when scrolling in a table window with a large result set) or when the client keeps a database transaction active between client-server calls. In both these scenarios is absolutely vital that the same database connection is used for all database calls.

The state of the connection indicates whether the connection is currently:

• USED - Ongoing database call
• INITIALIZED - User identity switched to some database user
• ANONYMOUS - Not yet initialized connection, will have to be initialized before use
• RESERVED - The connection is dedicated to a particular client session, either because of an open cursor or an active transaction. The connection will go back to state INITIALIZED automatically after any of the two configurable timeouts explained below.

(Other transient connection states exists.)

There are three important configuration parameters which can be changed using the Installer:

• Maximum Number of Connections
  The connection pool will grow up to this many connections as needed, depending on the current load. Since connections can be shared, this value can be significantly less than the number of concurrent users.
• Cursor Timeout
  The time until an idle open cursor will be closed automatically.
• Transaction Wait Timeout
  The time until an active transaction, waiting for subsequent client-server call, will be rolled back automatically.
  This is basically the maximum user "think" time between server calls in the context of an ongoing transaction started by the client.

The behavior of this pool can be monitored using IFS System Monitoring and configured using the Installer.

When monitoring database sessions, connections from this pool are identified as category PL/SQL Access.