The DB Query Resource Adaptor lets SLEE services send SQL queries to an RDBMS and receive the results as SLEE events or return values.

The resource adaptor:

  • allows both synchronous and asynchronous operations

  • continuously checks connections are valid and fails fast if no connections are available

  • has been tested with Oracle, MySQL, and PostreSQL databases

  • includes scripts and examples for these databases

  • should work with any other JDBC datasource.

JDBC drivers are deployed as JAIN SLEE Libraries. The resource adaptor references the libraries in its deployment descriptor.

Topics

Deploying the resource adaptor

How to set up a build environment, deploy the resource adaptor and dependencies

Configuring the resource adaptor

Configuring the resource adaptor using profiles and properties

Managing the resource adaptor

Enable and disable a datasource, enable and disable tracing, monitor statistics

Running the example service

Install and run the example services for the DB Query Resource Adaptor

Other documentation for the DB Query Resource Adaptor can be found on the DB Query Resource Adaptor product page.

Usage and execution model

The DB Query Resource Adaptor receives SQL queries from the invoking service as QueryInfo objects containing the query and associated metadata, including the type of query and if and how it may be retried. These are added to the worker pool queue for the primary datasource and control is returned to the service. Synchronous queries return a future for the service to wait for a result, asynchronous queries deliver the result in a SLEE event. The sample statistics in the "Query" parameter set measure times from query submission to return of a result. They provide an overview of query performance in the RA. Statistics in the "DataSource" parameter sets contain finer timing data for execution of queries against individual data sources.

When a query is added to the worker queue the submitting thread starts the query timeout timer. Measurement of the query performance in the datasource starts at this point.

  1. The worker threads for the data source take queries from the queue and execute them against the database. The time in the queue is measured in both the WorkerPool.queueTime and DataSource.queueTime sample statistics. If the query has timed out in the queue, this is recorded in the DataSource.queueTimeout counter statistic.

  2. The thread attempts to obtain a connection from the current transaction, or from the connection pool, creating one if the pool is empty and the connection limit is not yet reached. This is measured using the DataSource.connectionWait sample statistic. Within this time, the DataSource.poolWait and DataSource.newConnectionTime statistics record the time spent waiting for a connection to be available in the pool or creating a new one. If the query has timed out while getting a connection, this is recorded in the DataSource.poolWaitTimeout counter statistic.

  3. The query is submitted to the database for execution. The time spent creating the prepared statement and executing it is recorded in one of the DataSource.databaseTimeX sample statistics, whichever is appropriate for the task type (query, update, commit, rollback). If the query has timed out while the statement was executing, this is recorded in the DataSource.resultTooLate counter statistic and the time taken to execute the query is recorded in the DataSource.databaseTimeLate statistic.

  4. When the result is received the query timeout timer is cancelled.

  5. The result is checked and, if an error occurred, the connection is released.

  6. If one of these steps fails and the query may be retried, the execution task calls back to the RA to reschedule the query on the next available datasource.

  7. The query timeout timer is restarted for the next datasource.

  8. This repeats until the query completes or all datasources have been tried.

  9. When the query has completed, or no more datasources are available to try, the result returned is sent to the service.

If the query timeout timer fired at any point in this process, or, for synchronous queries the service stopped waiting on the result future, the result set is released and, the enclosing transaction is rolled back if applicable.

Connection pooling

The DB Query Resource Adaptor implements a connection pool to reduce the connection load on the database and minimise the time spent waiting for a connection. The pool may be configured with minimum and maximum sizes and is pre-filled to the minimum size. A separate connection limit may be configured to limit the maximum number of connections in use. The pool shrinks when connections have been idle for a configured period. Connections are replaced automatically when they exceed a configured age.

When a query is processed a connection is taken from the pool if present, otherwise a new connection is created unless the maximum number of connections has already been made. The connection remains in use until both the service has released the database result set and the transaction enclosing the query has completed. Once released the connection is added to the pool unless the pool already contains the configured maximum number of connections.

Next page