DB Query Resource Adaptor Guide

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

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.

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.

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.

The file build.properties must be edited to provide values for the properties specific to the local environment. The required properties are marked at the top of the file.

A deployable unit for the resource adaptor implementation is not included pre-assembled in the distribution. This is because the resource-adaptor-jar.xml descriptor within the component JAR must be updated to reference the appropriate library JARs via <library-ref> elements before the DU can be deployed.

The included build.xml contains targets (oracle, mysql, and postgresql) to build the database-specific deployable units. They use the <createradu> macro to update the resource-adaptor-jar.xml descriptor within the dbquery-ra-x.y.z.jar, and then create dbquery-ra-x.y.z.du.jar.

The create-dbquery-ra macro in build.xml creates a DU using the supplied library-ref fragments.

Database JDBC drivers are not distributed with this package. The libraries will be built when the targets mentioned above are run (oracle, mysql, and postgresql). The JDBC driver locations must be specified by the properties at the top of build.properties. If the JDBC driver is distributed as a native library, then the path to the driver must be specified in the LD_LIBRARY_PATH environment variable.

You configure the DB Query resource adaptor using SLEE profiles. A single configuration property is used to point to a master configuration profile for the resource adaptor entity. This configuration profile in turn references a number of other profiles that configure the datasources. For each datasource configuration profile specified, the resource adaptor will create a DataSource object using the given class name, then set the other profile attributes as datasource properties.

To configure DB Query resource adaptor entities, use the following types of resource adaptor configuration:

And see the configuration example, which sets up two different datasource configurations.

The DB Query resource adaptor has the following resource adaptor configuration properties:

The DbQueryConfigProfile profile has the following attributes:

The DbQueryDataSourceProfile profile has the following attributes:

The elements of the array for the DataSourceProperties profile attribute have the following format:

Name/Type/Value

where

For example, to set the loginTimeout property on an Oracle datasource to 10, the array element would be:

loginTimeout/java.lang.Integer/10

Type can be java.util.Properties, in which case the Value must be a java.lang.String that can be parsed by the java.util.Properties.load(InputStream) method ("name: value" separated by line breaks). Line breaks in Ant build files can be specified by using the XML entity 
.

The Oracle datasource has another means of setting connection properties: the setConnectionProperties(java.util.Properties) method. These can be specified by the ConnectionProperties attribute.

The value of this property must be a java.lang.String that can be parsed by the java.util.Properties.load(InputStream) method ("name: value" separated by line breaks). Line breaks in Ant build files can be specified by using the XML entity 
.

Per-node configuration (when nodes in a cluster require different configurations) is supported by having separate DbQueryConfigProfile profiles for each node. The resource adaptor must be configured with the configuration property ConfigProfileAddNodeSuffix set to true and a profile created for each node, named as follows:

For example, if the resource adaptor configuration specified ConfigProfile=MySQLConfig and ConfigProfileAddNodeSuffix=true on a two-node cluster with nodes 101 and 102, then two profiles would be needed:

If a new node is added to the cluster and the resource adaptor is using per-node configuration, an appropriately named profile must be created before activating the resource adaptor entity on the new node.

This happens when MySQL JDBC driver and MySQL database connect in their default connection mode, AND the client’s JVM has a PrivateKeyEntry using RSA Public Key Algorithm (and probably no other PrivateKeyEntry - but we haven’t verified that) in the keystore used by the SSL protocol stack.

Although two way authentication is not configured, the MySQL server asks the client for a certificate anyway. If the client returns an RSA Certificate then the connection is closed by the server, resulting in the above Exception chain. If the client sends no certificate, or a DSA Certificate the connection succeeds.

Unfortunately, Rhino’s rhino-server.keystore, which is configured as the SSL keystore, has a single PrivateKeyEntry using RSA Public Key Algorithm.

To disable TLS, set the following connection properties in the datasource profile’s DataSourceProperties

An empty keystore can be used specifically for the MySQL connection which overrides the default one Rhino installs for the SSL protocol.

To create an empty keystore, you create a keystore with a single private key then delete that key.

using you own password to replace secret.

Now put this in rhino home directory adjacent to the rhino-server.keystore file.

Finally, set the following connection properties in the datasource profile’s DataSourceProperties to tell MySQL to use this keystore.

setting or replacing ${empty.keystore.pass} with the keystore password, and ${empty.keystore.url} with the absolute path to the empty.keystore file as a URL.

Follow MySQL documentation to enable two way authentication, and get a client certificate compatible with the MySQL server.

Import that certificate into an empty.keystore file, and install that as explained for solution 2. You must either use the empty.keystore file name, or modify the dbquery-ra permissions to allow dbquery-ra and the packaged driver to read another keystore. Other DataSourceProperties will need to be added to configure the client for two-way authentication. This may also work if the certificate is imported into the default rhino-server.keystore without setting an explicit keystore in the DataSourceProperties.

The DB Query resource adaptor supports management procedures to enable, disable, reconfigure, and monitor datasources.

The root parameter set is named DatabaseQuery.entity-name and has three child parameter sets:

The Query parameter set has general statistics that are not specific to a given datasource. The DataSource and WorkerPool parameter sets each have child parameter sets named for each datasource configured in the profiles, and contain statistics specific to the datasource. The child parameter set uses the value of the Description profile attribute as its name.

A list of statistics with descriptions can be displayed with the rhino-stats -l command:

To run the example, Telnet to the machine running Rhino, using port 9999.

Then try these commands:

To run the example, a test database should be created in your MySQL installation, containing the correct schema and data. One way of doing this is to execute the SQL script included in the examples:

First, mysql.jdbc.dir and client.home must be set in the build.properties and examples/build.properties files respectively.

The build target deploy-mysql-example of examples/mysql.build.xml will deploy the DB Query RA, the Example RA, and the example service.

The build target undeploy-mysql-example of examples/mysql.build.xml will undeploy the example service, the DB Query RA, and the Example RA.