Data Virtualization connection properties

<< Click to Display Table of Contents >>

Navigation:  Automation Server for JEE > Automation Server JEE configuration and administration > Automation Server for JEE installation > Automation Server JEE platform configuration > Setting up the system architecture > Configuring Data Replication or Data Virtualization >

Data Virtualization connection properties


When configuring the use of Data Virtualization for a JEE Test or Production Bizagi environment, you register all data sources in an XML file, as guided according to your JEE Application server (Data provider configuration in Weblogic).


This section presents a technical reference sheet of options available as connection properties to the different data sources you register in such XML file.


Adding properties

To add any property, ensure these are contained inside the <customProperties> element in an <entry>, as paired <key> and <value> as shown below.

Note that some properties are mandatory, such as those described in the links above (e.g, url, user, password, and driverclass).


Add a new property by including the following code, ensuring you replace [PROPERTY] and [VALUE] accordingly.



<key xmlns:xs="" xmlns:xsi="" xsi:type="xs:string">[PROPERTY]</key>

<value xmlns:xs="" xmlns:xsi="" xsi:type="xs:string">[VALUE]</value>




Note that properties are listed as supported by the C3P0 component, which is employed by Bizagi drivers connecting to databases.

For complete information about this component, refer to



The following table describes connection properties available.






Determines how many connections at a time c3p0 will try to acquire when the pool is exhausted.



Defines how many times c3p0 will try to acquire a new Connection from the database before giving up. If this value is less than or equal to zero, c3p0 will keep trying to fetch a Connection indefinitely.



Milliseconds, time c3p0 will wait between acquire attempts.



The JDBC spec is unforgivably silent on what should happen to unresolved, pending transactions on Connection close. C3P0's default policy is to rollback any uncommitted, pending work. (I think this is absolutely, undeniably the right policy, but there is no consensus among JDBC driver vendors.) Setting autoCommitOnClose to true causes uncommitted pending work to be committed, rather than rolled back on Connection close.



If provided, c3p0 will create an empty table of the specified name, and use queries against that table to test the Connection. If automaticTestTable is provided, c3p0 will generate its own test query, therefore any preferredTestQuery set will be ignored. You should not work with the named table after c3p0 creates it; it should be strictly for c3p0's use in testing your Connection.



If true, a pooled DataSource will declare itself broken and be permanently closed if a Connection cannot be obtained from the database after making acquireRetryAttempts to acquire one. If false, failure to obtain a Connection will cause all Threads waiting for the pool to acquire a Connection to throw an Exception, but the DataSource will remain valid, and will attempt to acquire again following a call to getConnection().



The number of milliseconds a client calling getConnection() will wait for a Connection to be checked-in or acquired when the pool is exhausted. Zero means wait indefinitely. Setting any positive value will cause the getConnection() call to time-out and break with an SQLException after the specified number of milliseconds.



The fully qualified class-name of an implementation of the ConnectionCustomizer interface, which users can implement to set up Connections when they are acquired from the database, or on check-out, and potentially to clean things up on check-in and Connection destruction. If standard Connection properties (holdability, readOnly, or transactionIsolation) are set in the ConnectionCustomizer's onAcquire() method, these will override the Connection default values.



The fully qualified class-name of an implememtation of the ConnectionTester interface, or QueryConnectionTester if you would like instances to have access to a user-configured preferredTestQuery. This can be used to customize how c3p0 DataSources test Connections, but with the introduction of automaticTestTable and preferredTestQuery configuration parameters, "rolling your own" should be overkill for most users.



Must be one of caller, library, or none. Determines how the contextClassLoader (see java.lang.Thread) of c3p0-spawned Threads is determined. If caller, c3p0-spawned Threads (helper threads, java.util.Timer threads) inherit their contextClassLoader from the client Thread that provokes initialization of the pool. If library, the contextClassLoader will be the class that loaded c3p0 classes. If none, no contextClassLoader will be set (the property will be null), which in practice means the system ClassLoader will be used.




If true, and if unreturnedConnectionTimeout is set to a positive value, then the pool will capture the stack trace (via an Exception) of all Connection checkouts, and the stack traces will be printed when unreturned checked-out Connections timeout. This is intended to debug applications with Connection leaks, that is applications that occasionally fail to return Connections, leading to pool growth, and eventually exhaustion (when the pool hits maxPoolSize with all Connections checked-out and lost). This parameter should only be set while debugging, as capturing the stack trace will slow down every Connection check-out.



DataSources that will be bound by JNDI and use that API's Referenceable interface to store themselves may specify a URL from which the class capable of dereferencing a them may be loaded. If (as is usually the case) the c3p0 libraries will be locally available to the JNDI service, leave this set as null.



Setting this to true forces Connections to be checked-in synchronously, which under some circumstances may improve performance. Ordinarily Connections are checked-in asynchronously so that clients avoid any overhead of testing or custom check-in logic. However, asynchronous check-in contributes to thread pool congestion, and very busy pools might find clients delayed waiting for check-ins to complete. Expanding numHelperThreads can help manage Thread pool congestion, but memory footprint and switching costs put limits on practical thread pool size. To reduce thread pool load, you can set forceSynchronousCheckins to true.



Setting the parameter driverClass causes that class to preload and register with java.sql.DriverManager. However, it does not on its own ensure that the driver used will be an instance of driverClass, as DriverManager may (in unusual cases) know of other driver classes which can handle the specified jdbcUrl. Setting this parameter to true causes c3p0 to ignore DriverManager and simply instantiate driverClass directly.



If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds.



Number of Connections a pool will try to acquire upon startup. Should be between minPoolSize and maxPoolSize.



Seconds before c3p0's thread pool will try to interrupt an apparently hung task. Rarely useful. Many of c3p0's functions are not performed by client threads, but asynchronously by an internal thread pool. c3p0's asynchrony enhances client performance directly, and minimizes the length of time that critical locks are held by ensuring that slow jdbc operations are performed in non-lock-holding threads. If, however, some of these tasks "hang", that is they neither succeed nor fail with an Exception for a prolonged period of time, c3p0's thread pool can become exhausted and administrative tasks backed up. If the tasks are simply slow, the best way to resolve the problem is to increase the number of threads, via numHelperThreads. But if tasks sometimes hang indefinitely, you can use this parameter to force a call to the task thread's interrupt() method if a task exceeds a set time limit.



Seconds, effectively a time to live. A Connection older than maxConnectionAge will be destroyed and purged from the pool. This differs from maxIdleTime in that it refers to absolute age. Even a Connection which has not been much idle will be purged from the pool if it exceeds maxConnectionAge. Zero means no maximum absolute age is enforced.



Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.



Number of seconds that Connections in excess of minPoolSize should be permitted to remain idle in the pool before being culled. Intended for applications that wish to aggressively minimize the number of open Connections, shrinking the pool back towards minPoolSize if, following a spike, the load level diminishes and Connections acquired are no longer needed. If maxIdleTime is set, maxIdleTimeExcessConnections should be smaller if the parameter is to have any effect. Zero means no enforcement, excess Connections are not idled out.



Maximum number of Connections a pool will maintain at any given time.



The size of c3p0's global PreparedStatement cache. If both maxStatements and maxStatementsPerConnection are zero, statement caching will not be enabled. If maxStatements is zero but maxStatementsPerConnection is a non-zero value, statement caching will be enabled, but no global limit will be enforced, only the per-connection maximum. maxStatements controls the total number of Statements cached, for all Connections. If set, it should be a fairly large number, as each pooled Connection requires its own, distinct flock of cached statements. As a guide, consider how many distinct PreparedStatements are used frequently in your application, and multiply that number by maxPoolSize to arrive at an appropriate value. Though maxStatements is the JDBC standard parameter for controlling statement caching, users may find c3p0's alternative maxStatementsPerConnectionmore intuitive to use.



The number of PreparedStatements c3p0 will cache for a single pooled Connection. If both maxStatements and maxStatementsPerConnection are zero, statement caching will not be enabled. If maxStatementsPerConnection is zero but maxStatements is a non-zero value, statement caching will be enabled, and a global limit enforced, but otherwise no limit will be set on the number of cached statements for a single Connection. If set, maxStatementsPerConnection should be set to about the number distinct PreparedStatements 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 maxStatements is the JDBC standard parameter for controlling statement caching, users may find maxStatementsPerConnection more intuitive to use.



Minimum number of Connections a pool will maintain at any given time.



C3p0 is very asynchronous. Slow JDBC operations are generally performed by helper threads that don't hold contended locks. Spreading these operations over multiple threads can significantly improve performance by allowing multiple operations to be performed simultaneously.



Forces the username that should by PooledDataSources when a user calls the default getConnection() method. This is primarily useful when applications are pooling Connections from a non-c3p0 unpooled DataSource. Applications that use ComboPooledDataSource, or that wrap any c3p0-implemented unpooled DataSource can use the simple user property.



Forces the password that should by PooledDataSources when a user calls the default getConnection() method. This is primarily useful when applications are pooling Connections from a non-c3p0 unpooled DataSource. Applications that use ComboPooledDataSource, or that wrap any c3p0-implemented unpooled DataSource can use the simple password property.



For applications using ComboPooledDataSource or any c3p0-implemented unpooled DataSources — DriverManagerDataSource or the DataSource returned by DataSources.unpooledDataSource( ... ) — defines the password that will be used for the DataSource's default getConnection() method.



If true, c3p0-spawned Threads will have the associated with c3p0 library classes. By default, c3p0-spawned Threads (helper threads, java.util.Timer threads) inherit their AccessControlContext from the client Thread that provokes initialization of the pool. This can sometimes be a problem, especially in application servers that support hot redeployment of client apps. If c3p0's Threads hold a reference to an AccessControlContext from the first client that hits them, it may be impossible to garbage collect a ClassLoader associated with that client when it is undeployed in a running VM.



Maximum time in seconds before user configuration constraints are enforced. Determines how frequently maxConnectionAge, maxIdleTime, maxIdleTimeExcessConnections, unreturnedConnectionTimeout are enforced. c3p0 periodically checks the age of Connections to see whether they've timed out. This parameter determines the period. Zero means automatic: A suitable period will be determined by c3p0.




If set to a value greater than 0, the statement cache will track when Connections are in use, and only destroy Statements when their parent Connections are not otherwise in use. Although closing of a Statement while the parent Connection is in use is formally within spec, some databases and/or JDBC drivers, most notably Oracle, do not handle the case well and freeze, leading to deadlocks. Setting this parameter to a positive value should eliminate the issue. This parameter should only be set if you observe that attempts by c3p0 to close() cached statements freeze (usually you'll see APPARENT DEADLOCKS in your logs). If set, this parameter should almost always be set to 1. Basically, if you need more than one Thread dedicated solely to destroying cached Statements, you should set maxStatements and/or maxStatementsPerConnection so that you don't churn through Statements so quickly.



If true, an operation will be performed asynchronously at every connection checkin to verify that the connection is valid. Use in combination with idleConnectionTestPeriod for quite reliable, always asynchronous Connection testing. Also, setting an automaticTestTable or preferredTestQuery will usually speed up all connection tests.



If true, an operation will be performed at every connection checkout to verify that the connection is valid. Be sure to set an efficientpreferredTestQuery or automaticTestTable if you set this to true. Performing the (expensive) default Connection test on every client checkout will harm client performance. Testing Connections in checkout is the simplest and most reliable form of Connection testing, but for better performance, consider verifying connections periodically using idleConnectionTestPeriod.