JDBC Data Sources¶

JDBC data sources can be used for the following objectives:

• Use data from a relational database to obtain values for a variable in a parameterizable query of Individual Query jobs.

• Create a relational database exporter (see section Exporters Section).

To create a JDBC data source you need to specify the following parameters:

• Database adapter. Name of the JDBC adapter to be used to access the relational database. In section Extensions, the adapters distributed with Denodo Scheduler are discussed as well as how new ones can be added. When selecting an adapter, the connection URI, name of the driver class, and classpath fields are filled in automatically. In the case of a connection URI, a connection template for the database will appear which should be modified according to the remote server that you want to access.

• Connection URI. Database access URI.

• Driver class name. Name of the Java class of the JDBC adapter to be used.

• Classpath. Path for the folder that contains the JAR files with the implementation classes needed by the JDBC adapter.

• Username (optional). Username for accessing the external database.

• Password (optional). User password for accessing the external database.

• Enable pool (optional). It is possible to enable the use of a pool of connections against the database server by checking this check box. In this case, the following parameters can be specified for the pool.

  • Initial size of the pool (optional). Number of connections with which the pool is to be initialized. The specified number of connections are established and created in an “idle” state, ready to be used.

  • Maximum active connections in the pool (optional). Maximum number of active connections that can be managed by the pool at the same time (zero means no limit).

  • Maximum idle connections in the pool (optional). Maximum number of active connections that can remain idle in the pool without the need for additional connections to be disengaged (zero implies no limit).

  • Maximum time to wait for a connection from the pool (ms) (optional). Maximum amount of time to wait for an idle object when the pool is exhausted (-1 means no limit).

  • Validation query (optional). SQL query used by the pool to verify the status of the connections that are cached. The query should be simple, and the table in question should exist.

  • Maximum time to wait for the validation query (sec) (optional). Amount of time, in seconds, that connection validation will wait for a response from the database when executing a validation query (a value less than or equal to 0 means no timeout).

  • Test connections (optional). If you check this option, the pool will try to validate each connection before being returned. Where the connection is not valid (restart of the management system, closed connection, etc.), it will be eliminated from the pool and a new one will be created.

  The pool can be configured to periodically check that the connections in the pool are still valid and/or to close the connections that have sat idle in the pool for more than a certain period of time.

  To check if a connection is still valid, the pool executes the Validation query. If it fails, the pool discards the connection. The main reasons for the validation query to fail are:

  a. The database has a mechanism that closes connections after a certain period of inactivity.

  b. A firewall placed in between the Denodo server and the database automatically closes any connection after a certain period of inactivity.

  The main goals of enabling this option are:

  a. Make sure that the connections of the pool are valid at all times. That way, when a connection is needed, the connections of the pool are always valid.

  b. Close connections that have not been used in a while thus freeing resources in the database.

  The task of verifying connections and closing the idle ones is performed by the “connection eviction thread” (there is one per pool of connections). Periodically, this thread awakes and perform these checks.

  The parameters that control the connection eviction thread are:

  • Time between evictions (ms) (default value: -1). How long in milliseconds the eviction thread should sleep before “runs” of examining idle connections. If negative, the eviction thread is not launched.

    Eviction runs contend with the execution engine for access to the pool, so if this thread runs too frequently, performance issues may result.

  • Minimum time to be evictable (ms) (default value: 1800000 - 30 minutes). Minimum amount of time in milliseconds that a connection may sit idle in the pool before it is eligible for eviction.

    When is less than 1, no connections will be evicted from the pool due to idle time. In this case, the eviction thread will only discard connections that return an error when executing the validation query.

    This parameter has no effect if Time between evictions (ms) is less than 1.

  • Validated by the connection eviction thread (default value: false). If true and the parameter Validation query is not empty, the connections will be validated by the connection eviction thread. To validate a connection, the thread runs the validation query on the connection. If it fails, the pool drops the connection from the pool.

    This parameter has no effect if Time between evictions (ms) is less than 1.

  • Number of connections examined for eviction (default value: 3). Number of connections examined in each run of the connection eviction thread. The reason for not examining all the connections of the pool in each run of the thread is because while a connection is being examined, it cannot be used by a query.

    This parameter has no effect if Time between evictions (ms) is less than 1.