DRAFT

Omnidex JDBC Intelligent Router

Overview

The Omnidex JDBC Intelligent Router is a front-end JDBC Driver that allows queries to be evaluated and then directed to other JDBC drivers based on a set of business rules.

This allows companies to distribute statements across a variety of drivers and servers. Statements can be directed to specific servers based on the type of statement, and even the syntax of the SQL. Statements can also be directed to specific servers based on whether they are fully optimized using Omnidex indexing, or whether they have timed out on the primary server. Statements with proprietary syntax can either be modified or simply directed to the underlying databases JDBC driver. These features allow companies to tune their server environments to insure the best performance for each class of statement.

The Omnidex JDBC Intelligent Router also deploys connection pooling using the Apache Jakarta’s Common DBCP objects for Connection Pooling. This standard and universal interface allows the Intelligent Router to maintain connection pools for all of the underlying JDBC drivers, greatly reducing the overhead of establishing connections.

The Omnidex JDBC Intelligent Router is simple in design. The Intelligent Router is simply another standard JDBC driver, and is accessed in JAVA applications using the same approach as any other JDBC driver. When a JAVA application connects to the Intelligent Router, it provides a simple connection string that identifies a configuration file. This configuration file contains the business rules and the information used to connect to other JDBC drivers.

Connecting to the Intelligent Router

Connecting to the Intelligent Router is simply a matter of declaring the appropriate connection string in the JAVA application. The syntax of connection strings varies between JDBC Drivers. For the Intelligent Router, the connection string syntax is:

jdbc:omnidex:router:config_filename[/rule]

The config_filename is the name of the configuration file containing the business rules and information used to connect to other JDBC drivers. The rule is an optional parameter that instructs the Intelligent Router to execute the named rule rather than the default rule.

The Intelligent Router exists in the Omnidex.jar file that ships with Omnidex. This file must reside in your CLASSPATH, and this is done automatically when Omnidex is installed.

Creating a configuration file

The configuration file is a simple XML file containing the business rules and information used to connect to other JDBC drivers. This file can be edited in any XML editor, or even a standard text editor such as Notepad on Microsoft Windows or vi on UNIX platforms.

The XML file has three sections: global settings, rules and servers. The global section governs the overall behavior of the router, including the default configuration for connection pooling. The rules section declares the rules that will be processed by the router for every query. The servers section declares the servers and their respective JDBC driver and database that will be called from the router, with each rule referencing a server from this section. Each driver may also have independent settings governing connection pooling.

The basic structure of the configuration file is show in the example below:

<?xml version="1.0" encoding="utf-8"?>
<config xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="OmnidexJDBCIntelligentRouter"
        xsi:schemaLocation="OmnidexJDBCIntelligentRouter d:\oadev\omnidex\config\OmnidexJDBCIntelligentRouter.xsd">
 
  <global></global>
 
  <servers>
    <server></server>
    <server></server>
  </servers>
 
  <rules>
    <rule></rule>
    <rule></rule>
    <initialRule></initialRule>
  </rules>
</config>

Declaring servers

A server declaration in the configuration file consists of a name, a JDBC driver and a connection string. It may also include a description and settings for connection pooling. The specific settings that are available are:

name

– an arbitrary name used to identify the server.

description

– an optional description of the server.

jdbcDriver

– the JDBC driver as it would be referenced in a call to Class.forName in Java.

connectionString

– the connection string as it would be referenced in a call to DriverManager.getConnection in Java.

The following settings are available for controlling the connection pooling:

maxActive – the maximum number of active connections allowed in the pool. A value of -1 indicates that an unlimited number of active connections are allowed. The default value is 8.

whenExhaustedAction - the action to take when all available connections in the pool have been exhausted. The choices are shown below, with the default action being BLOCK:

BLOCK – Block the connection and wait until a connection in the pool becomes available
FAIL – Fail the connection and return an error condition.
GROW – Accept the connection and allow the connection pool to grow as needed.

maxWait - the maximum number of milliseconds to wait before returning an error while a connection is being blocked. A value of -1 indicates that an unlimited number of active connections are allowed. The default value is -1.

maxIdle - the maximum number of idle connections allowed in the connection pool. Once this value is reached, idle connections are closed to reduce the number of resources being used. A value of -1 indicates that an unlimited number of idle connections are allowed. The default value is 8.

The following examples show how servers are declared in the configuration file:

<server>
  <name>Omnidex_Server1_STAR</name>
  <description>Omnidex JDBC Driver on SERVER1 accessing the STAR database</description>
  <jdbcDriver>omnidex.jdbc.OdxJDBCDriver</jdbcDriver>
  <connectionString>jdbc:omnidex:driver:c:/databases/server1_star.dsn</connectionString>
</server>
 
<server>
  <name>JTDS_Server2_STAR</name>
  <description>JTDS JDBC Driver on SERVER2 accessing the STAR database</description>
  <jdbcDriver>net.sourceforge.jtds.jdbc.Driver</jdbcDriver>
    <connectionString>jdbc:jtds:sqlserver://Server2:1433/STAR; user=ABCD;password=AbCd</connectionString>
  <connectionPooling>
    <maxActive>20</maxActive>
    <maxIdle>20</maxIdle>
  </connectionPooling>
</server>

Declaring business rules

The business rules can be declared using one of two approaches. The simplest approach is to declare a series of conditions using regular expressions, and once a condition tests positive, then the corresponding server is used. The more powerful approach is to use Java code to create a rule that evaluates the statement, choose the server and customizes the connection settings. This is done by extended a base class that is provided by Omnidex. This extended class has access to the complete SQL statement and all server connection information. The extended class can then evaluate the statement, alter the statement, choose a server and modify the connection information as needed.

Declaring business rules using regular expressions

Business rules that use regular expressions are declared in the configuration file using these settings:

name – an arbitrary name for the business rule.

description – an optional description for the business rule.

type – the type of business rule being declared. The available choices are REGEXP, JAVA and DEFAULTSERVER. In this case, the type should be set to REGEXP or DEFAULTSERVER. REGEXP indicates a rule that declares regular expressions. DEFAULTSERVER indicates a rule that simply declares a server to be used.

defaultServer – the name of a server declared in the servers section that should be used if none of the conditions in the rule test positive.

conditions – the conditions to be evaluated in this rule. Each condition is evaluated in sequence, and the first condition to test positive will determine the server to use. Each condition uses the following setting:

description – an optional description of the regular expression

regexp – the regular expression to be used. If the regular expression uses angle brackets (< or >), then the expression must be wrapped in a CDATA construct to avoid interfering with the XML parsing. The CDATA syntax is as follows:

        <regexp><![CDATA[Enter regular expression here]]></regexp>

server – the name of the server declared in the servers section that should be used if this condition tests positive.

onErrorRule – the name of a rule in the rules section that should be used if the statement returns an error condition.

onTimeoutRule – the name of a rule in the rules section that should be used if the statement returns an timeout condition.

This example shows how rules that use regular expressions are declared in the configuration file:

<rules>
  <rule>
    <name>Omnidex Counts</name>
    <description>
      Direct counts and aggregations to Omnidex on Server1, 
      retrievals to Omnidex on Server2, and updates to JTDS on Server3
    </description>
    <type>REGEXP</type>
    <defaultServer>JTDS_Server3_STAR</defaultServer>
    <conditions>
      <condition>
        <description>Test for counts and aggregations</description>
        <regexp><![CDATA[Enter regular expression here]]></regexp>
        <server>Omnidex_Server1_STAR</server>
      </condition>
      <condition>
        <description>Test for retrievals</description>
        <regexp><![CDATA[Enter regular expression here]]></regexp>
        <server>Omnidex_Server2_STAR</server>
      </condition>
    </conditions>
    <onErrorRule>onErrorRule</onErrorRule>
    <onTimeoutRule>onTimeoutRule</onTimeoutRule>
   </rule>
 
   <rule>
    <name>onErrorRule</name>
    <type>DEFAULTSERVER</type>
    <defaultServer>JTDS_Server3_STAR</defaultServer>
  </rule>
 
  <rule>
    <name>onTimeoutRule</name>
    <type>DEFAULTSERVER</type>
    <defaultServer>JTDS_Server3_STAR</defaultServer>
  </rule>
 
  <initialRule>Omnidex Counts</initialRule>
</rules>

Declaring business rules using Java

Business rules that use Java are declared in the configuration file using the following settings:

name – an name for the business rule. This name must be identical to the name of the extended class that has been written in Java. For more information about writing extended classes in Java, see the sectioned named “Writing Extended Classes in Java” below.

description – an optional description for the business rule.

type – the type of business rule being declared. The available choices are REGEXP, JAVA and DEFAULTSERVER. In this case, the type should be set to JAVA or DEFAULTSERVER. JAVA indicates a rule that uses an extended class written in Java. DEFAULTSERVER indicates a rule that simply declares a server to be used.

defaultServer – the name of a server declared in the servers section that should be used if none of the conditions in the rule test positive.

onErrorRule – the name of a rule in the rules section that should be used if the statement returns an error condition.

onTimeoutRule – the name of a rule in the rules section that should be used if the statement returns an timeout condition.

The following examples show how rules that use Java are declared in the configuration file:

<rules>
  <rule>
    <name>org.mycompany.omnidex.routeQueries</name>
    <type>JAVA</type>
    <description>Evaluate query and direct to appropriate server</description>
    <onErrorRule>org.mycompany.omnidex.onErrorRule</onErrorRule>
    <onTimeoutRule>org.mycompany.omnidex.onTimeoutRule</onTimeoutRule>
  </rule>
 
  <rule>
    <name>org.mycompany.omnidex.onErrorRule</name>
    <type>JAVA</type>
    <description>Evaluate error conditions and direct to appropriate server</description>
  </rule>
 
  <rule>
    <name>org.mycompany.omnidex.onTimeoutRule</name>
    <type>JAVA</type>
    <description>Evaluate timeout conditions and direct to appropriate server</description>
  </rule>
 
  <initialRule>org.mycompany.omnidex.routeQueries</initialRule>
</rules>

Writing Extended Classes in Java

The Omnidex JDBC Intelligent Router provides a set of base classes that can be extended for each Java-based rule declared in the configuration file. From within this class, the author can obtain the SQL Statement, evaluate the SQL statement using whatever approach they wish, and even alter the SQL statement. The class returns an OdxJDBCRouterServer object indicating which server should be used. This server object may be looked up in the configuration file, and the connection string and connection pooling information may then be modified if needed. It is also possible to create an OdxJDBCRouterServer object dynamically without first looking it up in the configuration file.

The following methods may be called within the OdxJDBCRouterRule class: getSql – retrieve the SQL statement that is being routed. setSql – submit a modified SQL statement as needed. getServer – retrieve the information about a specific server. This information can be altered before it is returned to the Intelligent Router.

The example shows extending the OdxJDBCRouterRule class for the routeQueries rule:

package org.mycompany.omnidex;
 
import omnidex.jdbc.OdxJDBCRouterRule;
import omnidex.jdbc.OdxJDBCRouterServer;
 
public class routeQueries extends OdxJDBCRouterRule
{
  /**
   * Process the rule, which returns a single
   * OdxJDBCRouterServer object.
   *
   * @return a OdxJDBCRouterServer object that contains the server
   *         information or null object if the rule is not passed.
   * @exception Exception if an error occurs.
   */
  public OdxJDBCRouterServer process() throws Exception
  {
    // prepare the returned object
    OdxJDBCRouterServer server = null;
 
    // get the sql to process
    String sql = getSql();
 
    // check if the sql starts with SELECT COUNT(*)
    // if it is, then call Omnidex JDBC driver
    // otherwise call jTDS JDBC driver
    if (sql.trim().toUpperCase().startsWith( "SELECT COUNT(*)"))
    {
      // get the server where Omnidex is running
      server = getServer( "Omnidex_Server1_STAR");
 
      // modify the sql if necessary
      String modifiedSql = sql;
 
      // save the modified sql
      setSql( modifiedSql);
    }
    else
    {
      // get the server where jTDS is running
      server = getServer( "JTDS_Server2_STAR");
    }
 
    return server;
  }
}

Declaring the Rule to be Evaluated

When the router is called, the rule to be executed is declared in the initialRule element of the rules section. Alternatively, the rule to be executed can be sent in the connection string using the following syntax:

jdbc:omnidex:router:config_filename[/rule]

Calling the Omnidex JDBC Intelligent Router

The following Java snippet shows an example of calling the Omnidex JDBC Intelligent Router. Some of the methods that display to the screen or close the database are not shown, but they are available on the Omnidex software distribution if needed.

import java.io.*;
import java.sql.*;
 
public class jdbcDemo
{
  public static void main( String[] args)
  {
    jdbcDemo obj = new jdbcDemo();
    try
    {
      obj.execute();
    }
    catch (Exception err)
    {
      err.printStackTrace();
    }
  }
 
  private Connection  _conn;
  private Statement   _stmt;
  private ResultSet   _rs;
  private int         _count;
  private String      _header;
  private String      _headerLine;
 
  private void printString( String str)
  { System.out.println( str); }
 
  private void skipLine()
  { System.out.println( ""); }
 
  public void execute() throws Exception
  {
    printString( "Omnidex JDBC Intellegent Router Test program.");
    printString( "Dynamic Information Systems Corporation (c) 2007.");
    skipLine();
    skipLine();
 
    // load the class
    Class.forName( "omnidex.jdbc.OdxJDBCDriver");
 
    // connect to the config file
    String url = "jdbc:omnidex:router:OdxJDBCIR.xml/org.mycompany.omnidex.routeQueries";
    _conn = DriverManager.getConnection( url, null, null);
 
    // create Statement object
    _stmt = _conn.createStatement();
 
    // execute SQL and get the result set.
    // this one should use Omnidex driver
    _rs = _stmt.executeQuery( "select count(*) from PRODUCTS where DEPT=10");
 
    displayResult();
 
    // execute another SQL and get the result
    // this one should use the jTDS driver
    _rs = _stmt.executeQuery( "select * from PRODUCTS where DEPT=10");
 
    displayResult();
 
    closeDatabase();
  }
 
Back to top
dev/jdbc/router.txt · Last modified: 2016/06/28 22:38 (external edit)