CA API Gateway 9.1

9.4 9.1

JDBC Connection Properties

When creating or viewing details about a JDBC connection, the JDBC Connection Properties appear.
gateway
When creating or viewing details about a JDBC connection, the JDBC Connection Properties appear.
CA Technologies recommends setting the following properties for improved JDBC connection performance:
  • maxIdleTime = 0
  • maxConnectionAge = 0
  • idleConnectionTestPeriod = 600
Set these properties in the "Additional Properties" section of the connection properties.
Understanding the Driver Classes
By default, the following driver classes are white-listed for support by the
CA API Gateway
.
For more details on the databases that are 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 
DB2
Description
Supports IBM DB2 DB
Driver class
com.l7tech.jdbc.db2.DB2Driver
URL
jdbc:l7tech:db2://hostname:port;DatabaseName=value[;property=value[;...]]
Examples
jdbc:l7tech:db2://10.0.0.1:50000;DatabaseName=SAMPLE
jdbc:l7tech:db2://10.0.0.1:50000;Database=SAMPLE;User=test;Password=secret
MySQL Enterprise
Description
Supports MySQL Enterprise Edition
Driver class
com.l7tech.jdbc.mysql.MySQLDriver
URL
jdbc:l7tech:mysql://hostname:[port];DatabaseName=value[;property=value[;...]]
Examples
jdbc:l7tech:mysql://localhost:3306;DatabaseName=test
jdbc:l7tech:mysql://10.0.0.1:3306;Database=test;User=test;Password=secret
MS SQL Server
Description
Supports Microsoft SQL Server
Driver class
com.l7tech.jdbc.sqlserver.SQLServerDriver
URL
jdbc:l7tech:sqlserver://hostname:port;DatabaseName=value[;property=value[;...]]
Examples
jdbc:l7tech:sqlserver://10.0.0.1:1433;DatabaseName=test
jdbc:l7tech:sqlserver://10.0.0.1:1433;Database=test;User=test;Password=secret
Special note
For Microsoft MS SQL server, the JDBC driver requires "Bouncy Castle" to be the JCE provider. To use this, set the following Gateway system property:
com.l7tech.common.security.jceProviderEngineName=bc
Restart the Gateway for changes to take effect.
MySQL Community
Description
Supports MySQL Community Edition
Driver class
com.mysql.jdbc.Driver
URL
jdbc:mysql://hostname:[port]/<database name>
Example
jdbc:mysql://localhost:3306/test
Oracle
Description
Supports Oracle Database
Driver class
com.l7tech.jdbc.oracle.OracleDriver
URL
jdbc:l7tech:oracle://hostname:port;DatabaseName=value[;property=value[;...]]
Example
jdbc:l7tech:oracle://10.0.0.1:1521;DatabaseName=XE
jdbc:l7tech:oracle://10.0.0.1:1521;ServiceName=XE;
jdbc:l7tech:oracle://10.0.0.1:1521;SID=XE;User=test;Password=secret
Oracle RAC Server
Description
Supports Oracle Real Application Clusters (Oracle RAC)
Driver class
com.l7tech.jdbc.oracle.OracleDriver
URL
jdbc:l7tech:oracle://host1.XXXXX.com:1521;AlternateServers=(host2.XXXXX.com:1521);LoadBalancing=true
Other
The serviceName in RAC in part of your Oracle setup and not under control of the Gateway. Consult the DataDirect documentation for more information about this value.
Custom JDBC
To configure a custom JDBC driver, do the following
:
  1. Install the JDBC driver into the
    /opt/SecureSpan/Gateway/runtime/lib/ext/
     directory on the Gateway.
  2. Add the driver to the white list the driver (see "Updating the White List" below).
  3. Optionally configure the driver class in the
    jdbcConnection.driverClass.defaultList
     cluster property so that it is available in the drop-down list of JDBC drivers.
Updating the White List
By default, the following driver classes are white-listed:
  • com.mysql.jdbc.Driver
  • com.l7tech.jdbc.mysql.MySQLDriver  
  • com.l7tech.jdbc.db2.DB2Driver
  • com.l7tech.jdbc.oracle.OracleDrivercom.l7tech.jdbc.sqlserver.SQLServerDriver
To modify the list of white-listed driver classes, add this system property with a list of all drivers to be enabled:
com.l7tech.server.jdbcDriver = <driver class A>\n<driver class B>\n...<driver class N>
All the drivers must be listed in a single line, delimited with "\n".
Restart the Gateway for changes to take effect.
When
com.l7tech.server.jdbcDriver
 is defined, the Gateway falls back to recognizing the default driver classes.
To control which drivers are available in the Driver Class drop-down list, modify this cluster property:
jdbcConnection.driverClass.defaultList
 
Driver classes added to the
com.l7tech.server.jdbcDriver
 system property but not to the
jdbcConnection.driverClass.defaultList
 cluster property do not appear in the drop-down list. You can still use the class by typing in the driver class name manually. However, driver classes added only to the cluster property but not to the system property are not available for use.
Accessing the Properties
To access the properties for JDBC connection
:
  1. Run the Manage JDBC Connection task.
  2. Select a connection from the list and then click [
    Properties
    ]. You can also click [
    Add
    ] to define a new connection.
    The JDBC Connection Properties appear. 
  3. Configure the properties as follows:
    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 appropriate JDBC driver class from the drop-down menu. If the driver class you need is not listed, you may be able to enter it manually (depending on whether that class has been white-listed).
    For more information about the driver classes, see "Understanding the Driver Classes" above.
    It is possible that depending on the JDBC drivers installed on the 
    CA API Gateway
     that the driver class found for a JDBC URL does not match the configured Driver Class. This may cause the connection to fail as the Driver Class must be validated against the white list. If this happens, it is likely that the incorrect driver class was configured. Ensure that you are not referencing a deprecated driver class.
    JDBC URL
    Enter the URL for the JDBC connection. The URL format differs depending on the driver type. The URLs for the default white-listed drivers are listed under "Understanding the Driver Classes" above.
    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. To do this, define your password using the Manage Stored Passwords task and then reference it here using the
    ${secpass.<name>.plaintext}
     context variable.
    C3P0 Pool Configuration
    Modify the pool sizes if necessary. The default sizes should work well in most instances.
    • Minimum Pool Size
      : Minimum number of JDBC connections a pool maintains at any given time. The default is
      3
      .
    • Maximum Pool Size
      : Maximum number of JDBC connections a pool maintains at any given time. The default is
      15
      .
    The pool size defaults are stored in the cluster properties:
    jdbcConnection.pooling.minPoolSize.defaultValue
     and
    jdbcConnection.pooling.maxPoolSize.defaultValue,
     respectively. Additional C3P0 configuration may be made in the Additional Properties section.
    Additional Properties
    Configure additional properties as required by the JDBC connection. See the tip at the beginning of this topic for settings that can improve performance.
    By default, the property
    EnableCancelTimeout = true
     is added. This property ensures that requests to cancel a query are handled correctly. It is recommended that you do not remove or change this property for DataDirect driver unless you are certain of the consequences.  
    To add a new property:
    1. Click [
      Add
      ].
    2. Enter the Property Name and Property Value.
    3. Select the
      To set a C3P0 pooling property
       check box if this property applies to C3P0 pooling. When set, the Policy Manager adds the prefix "c3p0" to the property name to avoid naming collisions.
    4. Click [
      OK
      ].
    To modify a property:
    1. Select the property to change and then click [
      Edit
      ].
    2. Make the necessary modifications.
    3.  Click [
      OK
      ].
    To remove a property:
    • Select the property and then click [
      Remove
      ].
    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
    Click [
    Test
    ] to validate 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.
    (1) The [
    Test
    ] button will not operate if you used context variables in any of the settings. (2) Due to a limitation in a third-party library, the [
    Test
    ] button may fail to detect incorrect JDBC URLs in certain conditions. Refer to the URLs under "Understanding the Driver Classes" for examples.
    Security Zone
    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 Understanding Security Zones.
    This control is hidden if either: (a) no security zones have been defined, or (b) you do not have Read access to any security zone (regardless of whether you have Read access to entities inside the zones).
    Context variables cannot be used in the JDBC Connection Properties, since the variables cannot be evaluated until run-time. Using context variables will cause the JDBC connection to fail.
  4. Click [
    OK
    ] when done.
 
 

Content feedback and comments