[Advanced] Bpm DB Lookup Providers (Conf)

Using this feature, users can add DB lookup providers, which are used by the Bpm node "db_lookup" to perform searches on external databases. For further details about the Bpm node "db_lookup", refer to the administration manual.


../../../../_images/bpmdb_lookup_providers.png

To configure a new provider, press the Add DB lookup button.

Enter the name of the provider, press the Save button, then define the provider configuration. For further details, refer to the Connection section.

Connection

Users can use default connection settings for different database types: PostgreSQL, Oracle and MS SQL Server.

Select a configuration pressing the < Preset > menu button and select the database type.

The description of the connection settings follows:

  • JDBC Driver: the JDBC Driver class to connect to the database.

  • JDBC URL: the complete JDBC Connection URL.

  • Database User: the username of the database.

  • Database Password: the password of the database.

  • Confirm Database Password: the password of the database entered in the upper field.

Datasource

  • Initial pool size: the number of connections a pool tries to acquire upon startup. The value should be between Min Pool Size and Max Pool Size.

  • Min pool size: the minimum number of connections a pool maintains at any given time.

  • Max pool size: the maximum number of connections a pool maintains at any given time.

  • Max idle time: in seconds. A connection can remain pooled but unused before it is discarded. Zero means idle connections never expire.

  • Checkout timeout: the number of milliseconds a client calling getConnection() waits for a connection to be checked-in or acquired when the pool is exhausted. Zero means to wait indefinitely. Setting any positive value causes the getConnection() call to time-out and break with an SQLException after the specified number of milliseconds.

  • Max Statements Per Connection: the number of Prepared Statements c3p0 caches a single pooled Connection. If both Max Statements and Max Statements Per Connection are zero, statement caching is not enabled. If Max Statements Per Connection is zero but Max Statements is a non-zero value, statement caching is enabled, and a global limit enforced, but otherwise no limits are set on the number of cached statements for a single Connection. If set, Max Statements Per Connection should be set to the number distinct prepared statements that are used frequently in your application, plus two or three extra so infrequently statements don't force the more common cached statements to be culled. Though Max Statements is the JDBC standard parameter for controlling statement caching, users may find Max Statements Per Connection more intuitive to use.

  • Idle connection test period: if it is a number greater than 0, every specified number of seconds, c3p0 tests all the idled, pooled but unchecked-out connections.

  • Acquire increment: it determines how many connections at a time c3p0 tries to acquire when the pool is exhausted.

  • Acquire Retry Attempts: it defines how many times c3p0 tries to acquire a new connection from the database before giving up. If this value is less than or equal to zero, c3p0 keeps trying to fetch a connection indefinitely.

  • Acquire Retry Delay: in milliseconds, time of waiting from an attempt to the next one.

  • Break After Acquire Failure: if checked, a pooled Data Source declares itself as broken and permanently closed if a Connection cannot be obtained from the database after making Acquire Retry Attempts to acquire one. If unchecked, the failure to obtain a Connection causes all threads waiting for the pool to acquire a Connection to throw an exception, but the data source remains valid, and attempts to acquire again a call to getConnection().

  • Max Connection age: the actual maximum time of connection, in seconds. After that period of time, the connection is destroyed and purged from the pool. This differs from Max Idle Time, which refers to the absolute age. Even a Connection which has not been much idle will be purged from the pool if it exceeds Max Connection Age. Zero means no maximum absolute age is enforced.

  • Max Idle Time Excess: the number of seconds during which connections in excess of Min Pool Size can remain idle in the pool before being culled. It is intended for those applications that aggressively minimize the number of open Connections, shrinking the pool back towards Min Pool Size if the load level diminishes and Connections acquired are no longer needed, following a spike. If Max Idle Time is set, Max Idle Time Excess Connections should be smaller if the parameter does not have any effect. Zero means no enforcement, excess Connections are not idled out.

  • Test Connection on Check-in: if checked, an operation is performed asynchronously at every connection checkin to verify that the connection is valid. Together with idle Connection Test Period for quite reliable, use always asynchronous Connection testing. Besides, setting an Automatic Test Table or Preferred Test Query usually speeds up all connection tests.

  • Test Connection on Check-out: check it only if necessary. It is expensive. If checked, an operation is performed at every connection checkout to verify that the connection is valid. Better choice: verify connections periodically using the Idle Connection Test Period. Besides, setting an Automatic Test Table or Preferred Test Query usually speeds up all connection tests.

  • Preferred Test Query: the query that is executed for all connection tests, when the default Connection Tester (or other implementations of the Query Connection Tester, or of the Full Query Connection Tester) is being used. Defining a preferred Test Query executed quickly in your database may dramatically speeds up Connection tests. If any Preferred Test Query is set, the default Connection Tester executes a getTables() call on the Connection's Database MetaData. On some databases, this maye be executed more slowly than a "normal" database query.

  • Unreturned Connection Timeout: if set, an application checks out but then it fails to check-in [i.e. close()] a Connection within the specified period of time, the pool unceremoniously destroys() the connection. This permits applications with occasional Connection leak to survive, rather than eventually exhausting the connection pool. Zero means no timeout, applications are expected to close() their own Connections. If a non-zero value is set, it should be a value longer than any connection, which should reasonably be checked out. Otherwise, the pool occasionally kills the connections in active use.

  • Debug Unreturned Connection: if checked, and if Unreturned Connection Timeout is set to a positive value, the pool captures the stack trace (through an Exception) of all connection checkouts, and the stack traces are printed at the unreturned checked out Connections timeout. This is intended to debug applications with connection leaks. Use it for applications that occasionally fail to return connections, which leads to the pool growth, and eventually exhaustion (when the pool hits Max Pool Size with all connections checked out and lost). This parameter should only be set while debugging, as capturing the stack trace slows down every connection check out.

  • Number of Helper Threads: c3p0 is very asynchronous. Slow JDBC operations are generally performed by helper threads that don not hold contended locks. Spreading these operations over multiple threads can significantly improve performances allowing multiple operations to be performed simultaneously.

Note

Further details about c3p0 are available at the link <http://www.mchange.com/projects/c3p0/>.

Connection Test

Using the test feature, users can perform a search for the database. To perform a test, click on Test Connection button.

If the configuration is correct, the test is successful. The test gives a feedback of the connection performance and the output message notifies information about Connection Status, Connection Time and Query Time. A comment referred to Time fields, which is wrote in parentheses, reveals the database performance.

Test Connection Status
Connection time (comment)
Query time (comment)

Comment refers the time spent to perform the action (milliseconds):
<50 ms: Fast
>= 50 ms, <=100 ms: Normal
> 100 ms: Slow

To save, press the Save Changes button and then Back.

Note

After configuration changes are made, restart the Genius Server.