DB@SoL - DataBase at Speed Of Light

DB@SoL Reference Documentation

1.0a


SourceForge.net Logo

Table of Contents

Preface
1. Installation
2. Drivers
3. Usage
3.1. Properties
3.1.1. db@sol.logging.prefered.logger
3.1.2. db@sol.logging.level
3.1.3. db@sol.statement.execution.time.threshold
4. Production driver features
4.1. Self result set fetch size tuning
4.2. Too long query run
5. Development driver features
5.1. Automatic unclosed connection handling
6. Pronunciation

Preface

DB@SoL is a JDBC driver that wraps other JDBC drivers. This simple architecture allows to perform operations to enhance database auditing, database performance or to make easier application development.

Chapter 1. Installation

DB@SoL is a JDBC driver that wraps other JDBC drivers. This simple architecture allows to perform operations to enhance database auditing, database performance or to make easier application development.

Chapter 2. Drivers

Currently, DB@SoL proposes six different drivers for three different RDBMS:

  • org.dbatsol.hsqldb.AdaptativeSpeedOfLightJDBCDriver
(PRODUCTION)
  • org.dbatsol.hsqldb.dev.AdaptativeSpeedOfLightJDBCDriver
(DEVELOPMENT)
  • org.dbatsol.oracle.AdaptativeSpeedOfLightJDBCDriver
(PRODUCTION)
  • org.dbatsol.oracle.dev.AdaptativeSpeedOfLightJDBCDriver
(DEVELOPMENT)
  • org.dbatsol.postgresql.AdaptativeSpeedOfLightJDBCDriver
(PRODUCTION)
  • org.dbatsol.postgresql.dev.AdaptativeSpeedOfLightJDBCDriver
(DEVELOPMENT)

As you can see, there is two types of driver for each supported RDBMS, one for production and one for development.

Production drivers focus on performance issues and database auditing.

Development drivers offer the same functionnalities as production ones plus development features like report for unclosed connection, statement and result set (and automatically close) that help the developer.

Chapter 3. Usage

Currently, DB@SoL can not be used by application servers through DataSource. The common way is to use Driver#connect( url, properties ).

The parameters are the same as usual:

3.1. Properties

Custom properties can be passed to the DB@SoL driver.

3.1.1. db@sol.logging.prefered.logger

Provides to use one of the following Logger API for message logging:

  • Commons-logging
  • Log4J
  • Java logging

Accepted values are:

  • commons-logging
  • log4J
  • java

3.1.2. db@sol.logging.level

Indicates which minimal level to use when logging.

Accepted values are:

  • trace
  • debug
  • info
  • warn
  • error
  • fatal

3.1.3. db@sol.statement.execution.time.threshold

If a statement execution lasts more than this values (in milli seconds), then an alert is generated with the SQL statement.

Default value: 30000ms (30 seconds)

4. Production driver features

4.1. Self result set fetch size tuning

As you probably know, once a query has been executed, the data must be retrieved (fetched) by the client. Durng this operation, a certain amount of rows are sent by the RDBMS to the client. For example, the Oracle thin driver sends 10 rows to the client in a buffer area of the JDBC driver. Then with each ResultSet#next call, the client retrieves these rows. When the tenth row has been retrieved by the client, the driver retrieves the 10 next rows from the RDBMS. This ensures a lot of network traffic if your query produces a lot of rows.

To enhance performance, the DB@SoL driver tends to increase the fetch size to reach a balance point where the time to retrieve one row is optimal.

On the other hand, the fetch size is limited to 1,000 to avoid out of memory errors!

4.2. Too long query run

If a statement execution is too long, it might be because of query design, lack of indexes, locks or other performance related issues.

DB@SoL will log all the statements if their execution duration has reached a threshold (which can be parametrized).

See db@sol.statement.execution.time.threshold.

5. Development driver features

5.1. Automatic unclosed connection handling

Unclosed connection can bring a lot of problem to a RDBMS: lock, lack of ressources, bad response time.

To avoid this problem, DB@SoL registers each created connection in a WeakHashMap object. It permits to detect when a connection can't be used anymore (no more referenced). If such connection is not closed, DB@SoL generates an alert in the log file; and closes all the possible statements or result set not closed then it closes the connection.

Reporting of such alert tells where the connection has been retrieved in the developer source code from the driver. If the -g option has been used for code compilation, it even tells the line where the connection has been instanciated!

6. Pronunciation

DB@SoL stands for DataBase at Speed Of Light but also means DBA SOLution!