Set JDBC Connection Properties

When creating or viewing details about a Java Database Connectivity (JDBC) connection, the JDBC Connection Properties appear.
gateway90
When creating or viewing details about a Java Database Connectivity (JDBC) connection, the JDBC Connection Properties appear.
 
 
2
 
2
 
 
Configure Properties for JDBC Connections 
To configure a JDBC connection:
  1. In the Policy Manager, go to 
    Tasks
     >
     Data Sources
     > 
    Manage JDBC Connections
    .
  2. Select a connection from the list and then click 
    Edit
    . You can also click 
    Add 
    to define a new connection.
    The JDBC Connection Properties appear. 
  3. Modify the properties, 
    Test
     the connection, then click 
    OK
    .
JDBC Connection Properties 
The JDBC connection properties are described in the following table.
Setting
Description
 
Connection Name
 
Enter a unique name to describe the JDBC connection.
This name is used to select a connection in the Perform JDBC Query assertion.
 
Driver Class
 
Choose the JDBC driver class that corresponds to your data source from the drop-down menu. If the driver class you need is not listed, you may be able to enter it manually. All driver classes must be referenced in the system.properties file.
For more information, see the Driver Classes table.
To add support for new driver classes, or deprecate existing driver classes, see Install the JDBC Interface.
 
JDBC URL
 
Enter the URL for the JDBC connection. The URL format will differ depending on the driver type. The URLs for the default white-listed drivers are listed under Understand the Driver Classes.
IPv6 literals are currently not supported for the JDBC URL.
 
User Name /
 
 
Password
 
Enter the login information for the connection, if required.
Although you may enter the actual password here, it is recommended that you use a secure password reference instead. Define your password using the Manage Stored Passwords task and then reference it here using the ${secpass.<name>.plaintext} context variable.
 
Pool Configuration
 
Set the pool size to constrict the number of JDBC connections supported.
  •  
    Minimum Pool Size
    : Minimum number of JDBC connections a pool will maintain at any given time. The default is 
    3
    .
    For best performance, set the minimum at the expected number of connections.
  •  
    Maximum Pool Size
    : Maximum number of JDBC connections a pool will maintain at any given time. The default is 
    15
    .
To modify the pool size default values, edit the cluster properties: 
jdbcConnection.pooling.minPoolSize.defaultValue
 and 
jdbcConnection.pooling.maxPoolSize.defaultValue
.
 
Additional Properties
 
By default, when you add a new connection, the property 
EnableCancelTimeout = true
 is set.
This property ensures that requests to cancel a query are handled correctly. Do not remove or change this property for a DataDirect driver.   
Configure additional properties as required by the JDBC connection.
Select the 
To set a C3P0 pooling property check box
 if the additional property applies to C3P0 pooling. The Policy Manager automatically adds the prefix "c3p0" to the property name to avoid naming collisions.
 
Disable JDBC Connection
 
By default, the Policy Manager will immediately attempt to establish a JDBC connection after the properties dialog is closed. Select this check box if you want to keep the connection disabled.
When a new connection is started, any old connection is stopped automatically.
 
Test
 
Validates the settings as configured for the JDBC connection. If the test is not successful, the Policy Manager displays error messages to help you correct the problem.
The following limitations apply:
  • The 
    Test
     button does not work if you used context variables in any of the settings.
  • Due to a limitation in a third-party library, the 
    Test
     button can fail to detect incorrect JDBC URLs in certain conditions.
 
Security Zone
 
This control is hidden if no security zones have been defined, or you do not have Read access to any security zone (regardless of whether you have Read access to entities inside the zones).
Optionally choose a security zone. To remove this entity from a security zone (security role permitting), choose "No security zone".
For more information about security zones, see Understand Security Zones.
Default Driver Classes
Select the driver class appropriate for your JDBC database connection. 
For more details on the databases supported by each driver class, refer to the Progress DataDirect data sheet at this location: https://www.progress.com/docs/default-source/default-document-library/Progress/Documents/Papers/dd-for-jdbc.pdf 
Database
 
Driver class
 
Notes
DB2
com.l7tech.jdbc.db2.DB2Driver
 
MySQL Enterprise
com.l7tech.jdbc.mysql.MySQLDriver
MS SQL Server
com.l7tech.jdbc.sqlserver.SQLServerDriver
Supports Microsoft SQL Server
 
The JDBC driver requires "Bouncy Castle" to be the JCE provider. Set the following system property, then restart the Gateway:
 
com.l7tech.common.security.jceProviderEngineName=bc
 
MySQL Community
com.mysql.jdbc.Driver
Supports MySQL Community Edition
Oracle
com.l7tech.jdbc.oracle.OracleDriver
Oracle RAC Server
com.l7tech.jdbc.oracle.OracleDriver
Supports Oracle Real Application Clusters (Oracle RAC)
Improve JDBC Connection Performance
Adding additional properties with explicit values can improve JDBC connection performance.
To improve JDBC connection performance:
  1. In the Policy Manager, go to 
    Tasks
     > 
    Data Sources
     > 
    Manage JDBC Connections
    .
  2. Either select an existing JDBC connection and click 
    Edit
    , or click 
    Add
     to create a new connection.
    The JDBC Connection Properties panel appears.
  3. In the Additional Properties section, click 
    Add
    .
  4. Create the following properties one at a time by providing the Property Name, Property Value, and clicking the C3P0 Pooling checkbox:
    • maxIdleTime = 0
    • maxConnectionAge = 0
    • idleConnectionTestPeriod = 600
Selecting the 
To set a C3P0 pooling property
 checkbox applies these new properties and values to the available pool of JDBC connections.
Update the List of Supported Driver Classes
The JDBC drivers provide the classes required to connect to a specific data source. When you select the driver class for your JDBC connection, the list of supported data sources appears.
By default, only the following JDBC driver classes are supported: 
  • com.mysql.jdbc.Driver
  • com.l7tech.jdbc.mysql.MySQLDriver  
  • com.l7tech.jdbc.db2.DB2Driver
  • com.l7tech.jdbc.oracle.OracleDrivercom.l7tech.jdbc.sqlserver.SQLServerDriver
  • com.l7tech.jdbc.oracle.OracleDriver
To add a new JDBC driver class, or modify the existing list see Install the JDBC Interface