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.

Deploying the resource adaptor

Deploying the resource adaptor involves setting up a build environment, DU, and SLEE Library JARs.

Note

The download package includes:

du/ - deployable units
lib/ - compilation dependencies
docs/ - documentation
examples/ - an example service
etc/ - library JAR descriptors and library-ref XML fragments
createradu.macro.xml - a reusable Ant macrodef for assembling an RA DU
build.xml - build files
build.properties - build properties

Below are instructions for setting up a local build environment, creating a deployable unit, and building JDBC driver SLEE Library JARs.

Setting up a local build environment configuration

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.

Creating the resource adaptor deployable unit

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.

Building JDBC driver SLEE Library JARs

Note
Supported databases

The resource adaptor should work with any JDBC DataSource that uses standard configuration methods. A SLEE Library JAR must be created for the driver and the resource adaptor’s deployment descriptor updated to reference that library. The package contains Ant build scripts to perform this task.

The resource adaptor has been tested with recent versions of these databases:

  • Oracle (Thin Client Driver)

  • MySQL

  • PostgreSQL.

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.

Configuring the resource adaptor

DB Query resource adaptor configuration

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.

Warning You should set a network timeout to avoid possible long or indefinite waiting for a response from the database in the event of a network or database failure. See Network timeout configuration.

Resource adaptor configuration properties

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

Name Type Description

ConfigProfile

String

The name of the profile to configure this resource adaptor entity

ConfigProfileAddNodeSuffix

boolean

Flag to indicate that per-node configuration profiles should be used (see details below)

CacheNonTransactionQueryResultSets

false

Enables improved RA behaviour under overload conditions by caching results and closing all resources immediately

Resource adaptor configuration profile

The DbQueryConfigProfile profile has the following attributes:

Name Type Description

DataSourceProfileIDs

ProfileID[]

List of DataSource profiles. These will always be tried in the order given.

TestSql

String

An SQL statement to be executed against the datasources to test for availability

ProfilePollTime

long (ms)

Time between polling for changes to datasource profiles

MaxSyncTransactionAge

long (ms)

Maximum time a synchronous transaction can exist before assuming the service has neglected to end it and it is marked as 'lost'

MaxLostTransactionAge

long (ms)

Maximum time a transaction will stay in the 'lost' transaction list before being forcibly ended

TransactionScanTime

long (ms)

Time between scanning sync and lost transactions

Datasource configuration profiles

The DbQueryDataSourceProfile profile has the following attributes:

Name Type (units) Description

Description

String

A description for this datasource, will be used in log messages and statistics parameter set names

AdminEnable

boolean

Flag to enable/disable this datasource administratively

DataSourceClass

String

The Java class name for the datasource, will be instantiated via reflection

DataSourceName

String

Value for the DataSourceName property, if needed by the datasource (typically not needed)

Url

String

Value for the Url property if used by this datasource (not needed if ServerName/PortNumber/DatabaseName are specified)

URL

String

Value for the URL property if used by this datasource (not needed if ServerName/PortNumber/DatabaseName are specified)

ServerName

String

Value for the ServerName property if used by this datasource (not needed if Url or URL is specified)

PortNumber

int

Value for the PortNumber property if used by this datasource (not needed if Url or URL is specified)

User

String

Value to be passed to the setUser() method on the datasource

Password

String

Value to be passed to the setPassword() method on the datasource

DatabaseName

String

Value for the DatabaseName property if used by this datasource (not needed if Url or URL is specified)

DataSourceProperties

String[]

Arbitrary values to be passed to set PropertyName() methods on the datasource (see details below)

ConnectionProperties

String

Properties to be passed to the setConnectionProperties method on the datasource (see details below)

QueryTimeout

long (ms)

Timeout for a query, including getting a connection, preparing and executing the statement. On DatabaseFutureResult method calls the shorter timeout will always be in effect (no default, must be specified)

AvailabilityPollInterval

long (ms)

How frequently to poll for availability when a datasource is marked unavailable (default: 2000ms)

WorkerThreads

int

The number of threads in the worker thread pool used to invoke JDBC operations (default: 30)

WorkQueueMaxSize

int

The maximum number of work items in a queue before they are rejected (default: 600)

ConnPoolGetConnectionTimeout

long (ms)

Timeout for retrieving a connection from the pool (default: 5000ms)

ConnPoolMaxConnectionAge

long (ms)

The maximum time a physical connection may be used before it will be closed instead of being returned to the pool (default: 60000ms)

ConnPoolMaxConns

int

The maximum sum of connections currently in use and available in pool (default: 30)

ConnPoolSize

int

The maximum number of available connections kept in pool (default: 30)

ConnPoolMinSize

int

The minimum number of available connections that a pool maintains

ConnPoolMaxIdle

long (ms)

Maximum idle time for a connection before it is closed and removed from the pool (default: 30000ms)

ConnPoolIdleInterval

long (ms)

How frequently to check for idle connections (default: 500ms)

IdleCheckTime

long (ms)

How frequently to scan the connection pool for idle connections (default: 35000ms)

WarningAlarmDuration

long (ms)

How long overload alarms will stay active before being cleared (default: 30000ms)
Note The Url/URL attribute exists with both title case and upper case because some datasources use one form and some use the other.

Datasource properties profile attribute

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

Name/Type/Value

where

  • Name is the DataSource property name

  • Type is the property type (a Java primitive or a Java class which has a constructor that takes a single java.lang.String argument)

  • Value is the String representation of the value.

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 &#xa;.

Oracle connection properties

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 &#xa;.

Per-node configuration

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:

  • ConfigProfile __ NodeID

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:

  • MySQLConfig__101

  • MySQLConfig__102

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.

Configuration example

The following Ant commands would create one MySQL DataSource and one Oracle DataSource.

To use this configuration, the resource adaptor entity would be created with the configuration properties ConfigProfile=dbquery-config/MySQL-Ora-Config,ConfigProfileAddNodeSuffix=false.

oracle-timesten.build.xml 
<?xml version="1.0"?>

<project name="dbquery-ra-oracle-mysql-examples" default="deploy-oracle-mysql-example" basedir="./..">

    <import file="${basedir}/examples/common.build.xml"/>
    <import file="${basedir}/examples/oracle.build.xml"/>
    <import file="${basedir}/examples/mysql.build.xml"/>

    <target name="deploy-oracle-mysql" depends="oracle, deploy-oracle-library, deploy-mysql, deploy-mysql-library, deploy-dbquery-ra, create-oracle-mysql-dbquery-profiles, create-dbquery-ra-entity"
            description="Deploy Oracle+MySQL RA"/>

    <target name="create-oracle-mysql-dbquery-profiles" depends="init, login" description="Create profiles for Oracle/MySQL configuration">
        <slee-management>
            <createprofile tablename="dbquery-ds" profilename="MysqlDataSource">
                <profilevalue name="Description" value="${mysql.host} (MySQL)"/>
                <profilevalue name="DataSourceClass" value="com.mysql.jdbc.jdbc2.optional.MysqlDataSource"/>
                <profilevalue name="Url" value="jdbc:mysql://${mysql.host}:3306/${mysql.database}"/>
                <profilevalue name="User" value="${mysql.user}"/>
                <profilevalue name="Password" value="${mysql.password}"/>
                <profilevalue name="AdminEnable" value="true"/>
                <profilevalue name="WorkerThreads" value="5"/>
                <profilevalue name="QueryTimeout" value="10000"/> <!-- milliseconds -->
            </createprofile>
            <createprofile tablename="dbquery-ds" profilename="OraDataSource">
                <profilevalue name="Description" value="${oracle.host} (Oracle)"/>
                <profilevalue name="DataSourceClass" value="oracle.jdbc.pool.OracleConnectionPoolDataSource"/>
                <profilevalue name="ConnectionProperties" value="oracle.jdbc.ReadTimeout: 30000&#xa;"/>
                <profilevalue name="URL" value="jdbc:oracle:thin:@//${oracle.host}:1521/${oracle.database}"/>
                <profilevalue name="User" value="appuser"/>
                <profilevalue name="Password" value="appuser"/>
                <profilevalue name="AdminEnable" value="true"/>
                <profilevalue name="WorkerThreads" value="5"/>
                <profilevalue name="QueryTimeout" value="10000"/> <!-- milliseconds -->
            </createprofile>
            <createprofile tablename="dbquery-config" profilename="MySQL-Ora-Config">
                <profilevalue name="DataSourceProfileIDs" value="[dbquery-ds/MysqlDataSource,dbquery-ds/OraDataSource]"/>
                <profilevalue name="TestSql" value="SELECT short_code FROM mappings WHERE short_code IS NULL"/>
                <profilevalue name="ProfilePollTime" value="5000"/> <!-- milliseconds -->
                <profilevalue name="MaxSyncTransactionAge" value="120000"/> <!-- milliseconds -->
                <profilevalue name="MaxLostTransactionAge" value="120000"/> <!-- milliseconds -->
            </createprofile>
        </slee-management>
        <property name="dbqueryra.properties" value="ConfigProfile=dbquery-config/TT-Ora-Config,ConfigProfileAddNodeSuffix=false"/>
    </target>

    <target name="undeploy-oracle-mysql" depends="undeploy-dbquery-ra, undeploy-oracle-library, undeploy-mysql-library"
            description="Undeploy Oracle+MySQL RA"/>

    <target name="deploy-oracle-mysql-example" depends="deploy-oracle-mysql, deploy-example-service"
            description="Deploy Oracle+MySQL RA with example"/>

    <target name="undeploy-oracle-mysql-example" depends="undeploy-example-service, undeploy-oracle-mysql"
            description="Undeploy Oracle+MySQL RA with example"/>

</project>

MySQL Configuration

Later MySQL databases and drivers (8.x +) will use TLS connections by default. However there can be difficulties getting the connections to work due to interactions with other aspects of Rhino.

If you get the following or similar Exceptions, in particular Caused by: javax.net.ssl.SSLHandshakeException: Received fatal alert: unknown_ca this page provides some solutions.

Exception in thread "main" com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:836)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:197)
at com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:416)
at com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:128)
at com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:113)
at com.mysql.cj.jdbc.MysqlConnectionPoolDataSource.getPooledConnection(MysqlConnectionPoolDataSource.java:48)
<snip>
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:538)
at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:702)
at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:679)
at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:128)
at com.mysql.cj.protocol.a.NativeAuthenticationProvider.proceedHandshakeWithPluggableAuthentication(NativeAuthenticationProvider.java:466)
at com.mysql.cj.protocol.a.NativeAuthenticationProvider.connect(NativeAuthenticationProvider.java:171)
at com.mysql.cj.protocol.a.NativeProtocol.connect(NativeProtocol.java:1342)
at com.mysql.cj.NativeSession.connect(NativeSession.java:157)
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:956)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:826)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:456)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:246)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:197)
at com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:416)
at com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:128)
at com.mysql.cj.jdbc.MysqlDataSource.getConnection(MysqlDataSource.java:113)
at com.mysql.cj.jdbc.MysqlConnectionPoolDataSource.getPooledConnection(MysqlConnectionPoolDataSource.java:48)
<snip>
Caused by: javax.net.ssl.SSLHandshakeException: Received fatal alert: unknown_ca
at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:131)
at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:117)
at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:313)
at java.base/sun.security.ssl.Alert$AlertConsumer.consume(Alert.java:293)
at java.base/sun.security.ssl.TransportContext.dispatch(TransportContext.java:186)
at java.base/sun.security.ssl.SSLTransport.decode(SSLTransport.java:171)
at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1406)
at java.base/sun.security.ssl.SSLSocketImpl.readApplicationRecord(SSLSocketImpl.java:1371)
at java.base/sun.security.ssl.SSLSocketImpl$AppInputStream.read(SSLSocketImpl.java:958)
at java.base/java.io.FilterInputStream.read(FilterInputStream.java:133)
at com.mysql.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:64)
at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63)
at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45)
at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:532)
... 24 more

Cause

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.

Solution 1 - Disable TLS connections

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

<profilevalue
    name="DataSourceProperties"
    value="[sslMode/java.lang.String/DISABLED,allowPublicKeyRetrieval/java.lang.Boolean/true]"
/>

Solution 2 - Supply MySQL with an empty keystore

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.

keytool -genkeypair -storetype pkcs12 -alias dummy -storepass secret -keypass secret \
  -keystore empty.keystore \
  -dname "CN=IDontCare, OU=someunit, O=someorg, L=somecity, ST=less, C=WORLD"
keytool -delete -alias dummy -storepass secret -keystore empty.keystore

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.

<profilevalue name="DataSourceProperties"
    value="[clientCertificateKeyStoreUrl/java.lang.String/${empty.keystore.url},clientCertificateKeyStorePassword/java.lang.String/${empty.keystore.pass},clientCertificateKeyStoreType/java.lang.String/JKS]"
/>

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.

Solution 3 - Enable 2 way authentication - untested

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.

Other untested potential solutions

  • Modify the MySQL server to accept the RSA certificate in the rhino-server.keystore.

  • Modify the MySQL server to not ask for a client certificate.

  • Generate rhino keystores with DSA signing algorithm (note there are security implications).

  • A very similar problem was observed with 8.0.18 driver, with DSA keys also, but that was fixed in 8.0.21, so maybe this problem will also disappear some time.

Network timeout configuration

Set a network timeout to avoid possible long or indefinite waiting for a response from the database in the event of a network or database failure.

The query timeout limits execution time of a query; the network timeout controls the underlying communication channel between the JDBC driver and the database.

Full support for timeouts requires the network timeout to be configured on the JDBC driver. This feature is JDBC driver dependent.

For example, when a query timeout is detected and the JDBC driver sends a notification to the database, a communication timeout may occur. This will not be detected (or the detection can take arbitrary time) unless the network timeout is explicitly configured.

Warning

Not configuring the network timeout causes either:

  • a default value (between minutes and hours) to be used, usually provided by operating system, or

  • indefinite blocking of query execution, waiting for a database answer.

Below are details for configuring the network timeout for some common database drivers.

Database, JDBC driver Where to configure Property name Unit Example

Oracle, Thin driver, v10.1.0.5+

DbQueryDataSourceProfile ConnectionProperties

oracle.jdbc.ReadTimeout

ms

oracle.jdbc.ReadTimeout: 60000

Oracle, Thin driver, older

DbQueryDataSourceProfile ConnectionProperties

oracle.net.READ_TIMEOUT

ms

oracle.net.READ_TIMEOUT: 60000

Oracle, OCI driver

Not supported.

MySQL, v3.0.1+

DbQueryDataSourceProfile ConnectionProperties [1]

socketTimeout

ms

socketTimeout: 60000

PostgreSQL, v8.4+

DbQueryDataSourceProfile ConnectionProperties [1]

socketTimeout

s

socketTimeout: 60
Tip Always check the vendor’s documentation for the right property name, place of configuration, and any other restrictions or guidelines.

As a general guideline: the value for network timeout should be greater than maximum time of a query or transaction. The resource adaptor internally sets the query timeout for the JDBC driver (more precisely, for each issued query statement) equal to 1.5 times DbQueryDataSourceProfile QueryTimeout. The network timeout should not be less than 1.5 times the QueryTimeout, as otherwise the JDBC driver may end a query prematurely with a failure.

Managing the resource adaptor

Enabling, disabling, reconfiguring, and monitoring

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

Administratively enabling or disable a datasource

The profile attribute AdminEnable can be used to disable a configured datasource. If this attribute is set to false using a management interface (such as the Rhino command-line console), then the data source will be treated as though it is not available. The profile is polled for a change to this attribute with a period specified by attribute ProfilePollTime in the main DbQueryConfigProfile profile.

Reconfiguring datasources

Most aspects of datasource configuration can be modified while a datasource has been administratively disabled. When the datasource is re-enabled, the actual DataSource implementation class will be re-instantiated and reconfigured according to the new profile values.

Enabling or disabling tracing

The DB Query Resource Adaptor has several tracers that behave in the usual way, and whose trace levels can be modified using the Rhino management tools.

There is one special case. The trace level of the tracer dsm.driver determines whether or not setLogWriter() will be called on the DataSource class. If JDBC driver tracing is required, this tracer’s (or a parent’s) trace level should be set to FINEST. If JDBC driver tracing is no longer required, this tracer’s trace level should be unset.

Warning
Tracing can affect performance

  • The level check of this special tracer is made only once, when the datasource instance is created. A datasource instance may be created by either activating a resource adaptor entity or by administratively enabling the datasource (as described above).

  • If the tracer level is unset or set to a level other than FINEST, the tracing will be filtered by that trace level as usual; but the LogWriter will still be set on the driver, generating trace messages which are not displayed. This will have a performance impact.

  • If JDBC driver tracing is no longer needed, the tracer level should be unset or changed, and then the datasource forced to be re-instantiated by deactivating and activating the resource adaptor, or you can administratively disable then enable the datasource.
Tip See also Monitoring the resource adaptor statistics

Monitoring resource adaptor statistics

The DB Query Resource Adaptor creates several statistic parameter sets that can be monitored using the Rhino Stats tool.

Statistics parameter sets

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:

Query parameter set

Parameter Set: DatabaseQuery.dbquery-0.Query
Parameter Set Type: ResourceAdaptorID[name=Database Query,vendor=OpenCloud,version=1.1].DatabaseQueryStats
Description: Database Query RA statistics

Counter type statistics:
  Id: Name:            Label:         Description:
  0   asyncQueries     asyncQ         Total async queries
  1   asyncTxnQueries  asyncTxnQ      Total transactional async queries
  2   syncQueries      syncQ          Total sync queries
  3   syncTxnQueries   syncTxnQ       Total transactional sync queries
  4   asyncUpdates     asyncU         Total async updates
  5   asyncTxnUpdates  asyncTxnU      Total transactional async updates
  6   syncUpdates      syncU          Total sync updates
  7   syncTxnUpdates   syncTxnU       Total transactional sync updates
  8   failed           fail           Failures
  9   activeSyncTxn    activeSyncTxn  Active sync transactions

Sample type statistics: (none defined)

DataSource parameter set

Parameter Set: DatabaseQuery.dbquery-0.DataSource
Parameter Set Type: ResourceAdaptorID[name=Database Query,vendor=OpenCloud,version=1.1].DataSourceStats
Description: Database Query RA DataSource statistics

Counter type statistics:
  Id: Name:                 Label:       Description:
  0   queueTimeout          qTimeout     Timeout while waiting for worker thread
  1   poolWaitTimeout       pTimeout     Timeout while waiting for pooled connection
  2   databaseTimeout       dbTimeout    Timeout while waiting for database execution
  3   resultTooLate         late         ResultSet received too late
  4   inPool                inPool       Connections in the pool
  5   inUse                 inUse        Connections in use
  6   newConnectionCreated  created      Created
  7   pooledConnectionUsed  used         Used
  8   connectionTimeout     connTimeout  Connection timeout

Sample type statistics:
  Id: Name:                 Label:     Description:
  9   connectionWait        connWait   Time waiting for a connection
  10  queueTime             queueTime  Time waiting in executor queue
  11  databaseTimeQuery     dbTimeQ    Time creating and executing PreparedStatement for query
  12  databaseTimeUpdate    dbTimeU    Time creating and executing PreparedStatement for update
  13  databaseTimeCommit    dbTimeC    Time calling commit
  14  databaseTimeRollback  dbTimeR    Time calling rollback

WorkerPool parameter set

Parameter Set: DatabaseQuery.dbquery-0.WorkerPool
Parameter Set Type: WorkerPoolStats
Description: Database Query RA worker pool statistics

Counter type statistics:
  Id: Name:             Label:      Description:
  0   threadsTotal      totalThrd   Total number of worker threads
  1   threadsAvailable  availThrd   Number of available worker threads
  2   tasksExecuted     tasksTotal  Tasks executed
  3   queueSize         qsize       Number of messages waiting in worker pool's queue
  4   rejects           rejects     Number of messages rejected because queue was full

Sample type statistics:
  Id: Name:           Label:  Description:
  5   processingTime  procT   Time spent in RA processing a message
  6   queueTime       queueT  Time spent on queue waiting for a thread

Running the example service

To configure and deploy the example, please refer to MySQL setup instructions. Once all the steps are completed, the following scenario can be followed to test it.

Note

If building and deploying the example on Rhino version 2.6 and onwards, the log4j jar required in the example/common.build.xml doesn’t exist in Rhino client library folder. Hence, it should be added manually using the following steps:

  1. Download the log4j-1.2.17 from https://logging.apache.org/log4j/1.2/download.html

  2. Unzip or untar the downloaded file and copy the log4j-1.2.17.jar file to examples/lib/antlib

Running the example

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

Then try these commands:

Enter: To…​

q-s <shortcode>

send a query using the synchronous API (if a transaction exists, it will be used)

q-a <shortcode>

send a query using the asynchronous API (if a transactional activity exists, it will be used)

up-s <shortcode> <longcode>

start or use an existing transaction and send an update using the synchronous API

up-a <shortcode> <longcode>

start or use an existing transactional activity and send an update using the asynchronous API

p-s <shortcode> <prefix>

start or use an existing transaction and execute a stored procedure synchronously to extend the mapping with the prefix

p-a <shortcode> <prefix>

start or use an existing transactional activity and execute a stored procedure asynchronously to extend the mapping with the prefix

co

commit the transaction

ro

rollback the transaction

close

close the Telnet connection

help

see brief help
Example output 
$ telnet localhost 9999
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
help

 COMMANDS:
   q-s [short] - query short code synchronously (using current transaction if one exists)
   q-a [short] - query short code asynchronously (using current transaction if one exists)
   up-s [short] [long] - update short -> long mapping synchronously (starting a transaction if needed)
   up-a [short] [long] - update short -> long mapping asynchronously (starting a transaction if needed)
   p-s [short] [prefix] - execute stored procedure synchronously to extend a mapping with the prefix (starting a transaction if needed)
   p-a [short] [prefix] - execute stored procedure asynchronously to extend a mapping with the prefix (starting a transaction if needed)
   co - commit current transaction
   ro - rollback current transaction
   close - close the telnet connection

q-s 0000
 Short code '0000' maps to long code '11111111111'

q-a 0000
 Submitted SELECT long_code FROM mappings WHERE short_code = '0000' on non-transactional activity
 Short code '0000' maps to long code '11111111111'

up-s 0000 66666666666
 Transaction for synchronous requests created: DatabaseTransaction[101.100003,age=0ms,conn=none]
 Updated mapping of short code '0000' to long code '66666666666'
 (There is an open transaction, it must be committed or rolled back)

q-s 0000
 Short code '0000' maps to long code '66666666666'
 (There is an open transaction, it must be committed or rolled back)

ro
 Rolled back DatabaseTransaction[101.100003,age=9940ms,conn=DbConnectionHandle[id=4,state=CLOSED,transactional=true,connection=com.mysql.jdbc.ProxyConnection@608148fe]]

q-s 0000
 Short code '0000' maps to long code '11111111111'

up-s 0000 555555555555
 Transaction for synchronous requests created: DatabaseTransaction[101.100005,age=0ms,conn=none]
 Updated mapping of short code '0000' to long code '555555555555'
 (There is an open transaction, it must be committed or rolled back)

co
 Committed DatabaseTransaction[101.100005,age=4698ms,conn=DbConnectionHandle[id=6,state=CLOSED,transactional=true,connection=com.mysql.jdbc.ProxyConnection@6cbd7ea6]]

q-s 0000
 Short code '0000' maps to long code '555555555555'

q-a 0000
 Submitted SELECT long_code FROM mappings WHERE short_code = '0000' on non-transactional activity
 Short code '0000' maps to long code '555555555555'

up-a 0000 333333333333
 Transactional activity created: DatabaseQueryActivity[101.100008],I
 Submitted UPDATE mappings SET long_code = '333333333333' WHERE short_code = '0000' on activity DatabaseQueryActivity[101.100008],P
 (There is an open transaction, it must be committed or rolled back)
 Updated mapping of short code '0000' to long code '333333333333'
 (There is an open transaction, it must be committed or rolled back)

q-s 0000
 Short code '0000' maps to long code '555555555555'
 (There is an open transaction, it must be committed or rolled back)

q-a 0000
 Submitted SELECT long_code FROM mappings WHERE short_code = '0000' on activity DatabaseQueryActivity[101.100008],P
 (There is an open transaction, it must be committed or rolled back)
 Short code '0000' maps to long code '333333333333'
 (There is an open transaction, it must be committed or rolled back)

co
 Submitted commit request on transactional activity DatabaseQueryActivity[101.100008],P
 Transaction committed

q-a 0000
 Submitted SELECT long_code FROM mappings WHERE short_code = '0000' on non-transactional activity
 Short code '0000' maps to long code '333333333333'

up-a 0000 44444444444
 Transactional activity created: DatabaseQueryActivity[101.100011],I
 Submitted UPDATE mappings SET long_code = '44444444444' WHERE short_code = '0000' on activity DatabaseQueryActivity[101.100011],P
 (There is an open transaction, it must be committed or rolled back)
 Updated mapping of short code '0000' to long code '44444444444'
 (There is an open transaction, it must be committed or rolled back)

ro
 Submitted rollback request on transactional activity DatabaseQueryActivity[101.100011],P
 Transaction rolled back

q-a 0000
 Submitted SELECT long_code FROM mappings WHERE short_code = '0000' on non-transactional activity
 Short code '0000' maps to long code '333333333333'

p-s 0000 0
 Transaction for synchronous requests created: DatabaseTransaction[101.100013,age=0ms,conn=none]
 Updated mapping of short code '0000' to long code '0333333333333'
 (There is an open transaction, it must be committed or rolled back)

q-s 0000
 Short code '0000' maps to long code '0333333333333'
 (There is an open transaction, it must be committed or rolled back)

q-a 0000
 Submitted SELECT long_code FROM mappings WHERE short_code = '0000' on non-transactional activity
 (There is an open transaction, it must be committed or rolled back)
 Short code '0000' maps to long code '333333333333'
 (There is an open transaction, it must be committed or rolled back)

ro
 Rolled back DatabaseTransaction[101.100013,age=16806ms,conn=DbConnectionHandle[id=14,state=CLOSED,transactional=true,connection=com.mysql.jdbc.ProxyConnection@441e8828]]

p-a 0000 00
 Transactional activity created: DatabaseQueryActivity[101.100015],I
 Submitted CALL EXTEND_LONG_CODE_WITH_PREFIX(short_code = '0000' and prefix = '00') on activity DatabaseQueryActivity[101.100015],P
 (There is an open transaction, it must be committed or rolled back)
 Updated mapping of short code '0000' to long code '00333333333333'
 (There is an open transaction, it must be committed or rolled back)

q-a 0000
 Submitted SELECT long_code FROM mappings WHERE short_code = '0000' on activity DatabaseQueryActivity[101.100015],P
 (There is an open transaction, it must be committed or rolled back)
 Short code '0000' maps to long code '00333333333333'
 (There is an open transaction, it must be committed or rolled back)

q-s 0000
 Short code '0000' maps to long code '333333333333'
 (There is an open transaction, it must be committed or rolled back)

co
 Submitted commit request on transactional activity DatabaseQueryActivity[101.100015],P
 Transaction committed

q-s 0000
 Short code '0000' maps to long code '00333333333333'

close
 Goodbye!
Connection closed by foreign host.

MySQL setup

Below are the steps required to set up the MySQL example: create and fill a test database, and deploy and undeploy the service.

Creating and filling in the database

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:

Configuring MySQL 
$ mysql -u root -p < examples/src/com/opencloud/slee/example/dbquery/MySqlTestDB.sql
examples/src/com/opencloud/slee/test/dbquery/MySqlTestDB.sql 
CREATE DATABASE mappings;
CREATE USER 'appuser' IDENTIFIED BY 'appuser';
GRANT ALL PRIVILEGES ON mappings.* TO 'appuser';
FLUSH PRIVILEGES;
USE mappings;

DROP TABLE IF EXISTS mappings;

CREATE TABLE mappings (
  short_code VARCHAR(10) PRIMARY KEY,
  long_code VARCHAR(64) NOT NULL
);

INSERT INTO mappings (short_code, long_code) VALUES ('0000', '11111111111');
COMMIT;

DROP PROCEDURE IF EXISTS extend_long_code_with_prefix;

DELIMITER //
CREATE PROCEDURE EXTEND_LONG_CODE_WITH_PREFIX(
    OUT p_concat VARCHAR(64),
    IN p_short_code VARCHAR(10),
    IN p_prefix VARCHAR(64))
BEGIN
    SELECT concat(p_prefix, long_code)
      INTO p_concat
      FROM mappings
     WHERE short_code = p_short_code;

    UPDATE mappings
       SET long_code = p_concat
     WHERE short_code = p_short_code;
END //
DELIMITER ;
Warning The same host, database, user, and password must be set in examples/build.properties.

Deploying the example service

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.

Undeploying 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.

1. Setting the network timeout by means of URL parameters is also possible. However, it is discouraged as it makes the configuration easily lost when the URL is modified.