Configuring Flux to Run with Databases

This guide provides instructions on setting up Flux with any of its supported databases.

Keeping Current with JDBC Drivers

Customers occasionally report issues with Flux and their databases, e.g., database exceptions, problems with reindexing, reorganizing their database, or database issues with performance and reliability. In many instances the database itself has been upgraded (new version or service packs and patches applied) but the JDBC driver that Flux is using has not. It is the customer’s responsibility to ensure that the database driver in use with Flux is current in accordance with the database vendor’s documentation. Do not use a database driver that predates the database version in use (e.g., do not use an Oracle Driver for Oracle 9 if your database is running Oracle 11).

Advanced Users (Java knowledge required)

This guide assumes that you are configuring Flux from a file (this would be the case if you are starting Flux from a service or a script). If you have instead created your Flux engine using the Java API, refer to Configuring Flux to Run With Databases Using the Java API for more details about configuring the engine through code.

Preparing the Database for Flux to Connect

The first thing you’ll need to do is select the database you will want Flux to use. Flux supports any of the five database servers listed above. If you have more than one database server available and you are unsure which database will work best for Flux, see the Technical Specifications for performance metrics for each server, or see Databases for an in-depth look at how Flux works on each server.

Once you’ve chosen a database, you’ll need to set up a schema for Flux to use. If you’re not sure how to create a new schema on your database, the links below contain more information for each supported server:

For example, on MySQL, this might look like:

CREATE SCHEMA Flux;

Where “Flux” is the name of the schema you want to create.

After your schema is created, you’ll need to populate it with the Flux database tables. Flux includes .sql scripts for each database server type to automatically create the tables it requires – to set up the tables, you’ll first just need to navigate to <Flux home>/doc and find the SQL script for your database (on MySQL, for example, this would be mysql.sql). Once you’ve located the correct script for your database, just execute that script using standard script execution methods on your server (to avoid permissions problems later on, be sure to run the script using the same user account that you will want Flux itself to use when connecting to the database).

Flux database permissions

Make sure the Flux database user has the permissions set as described in Database and File System Permissions.

If you’re not familiar with the process for executing a script, you can find detailed instructions for each server type below:

On MySQL, for example, you might run the following command after connecting to the MySQL database using the command-line tool:

source <Flux home>/doc/mysql.sql

Where “<Flux home>” is the full path to the directory where you’ve installed Flux.

Additional Database Setup Details

For additional specifics regarding database setup details, refer to Databases for your specific database. This page contains details on additional commands or features that must be enabled or executed in order for Flux to perform as expected.

Advanced Users (SQL knowledge required)

NOTE: The SQL scripts provided impose size restrictions on some columns in the Flux tables. If you find that the data you need to store consistently exceeds these limitations, you may want to increase the size of those columns to match your needs.

Configuring Flux to Use the Database

  1. To get started, you will first need to download a JDBC driver for your database (see “Database Configuration and Driver Settings” below for a link to download your driver class if you do not already have one available). Once you’ve downloaded the driver, place the driver .jar file into your /lib directory. Placing the file in this directory will allow the engine to easily locate the driver.
  2. Edit the file engine-config.properties (which should be located in the <Flux home>/config directory) add the following lines:
DATABASE_TYPE="Look below for your database information"
DRIVER="Look below for your database information"
URL="Look below for your database information"
JDBC_USERNAME=userName
JDBC_PASSWORD=password
MAX_CONNECTIONS=20
CONCURRENCY_LEVEL=15

MAX_CONNECTIONS and CONCURRENCY_LEVEL

You can change the MAX_CONNECTIONS and CONCURRENCY_LEVEL settings to match your needs. Be careful to ensure that MAX_CONNECTIONS is at least 5 higher than CONCURRENCY_LEVEL. To read more about these properties, what they do, and how they relate to one another, see Max Connections and Concurrency Level.

Database Configuration and Driver Settings

DB2 

DATABASE_TYPE=DB2
DRIVER=com.ibm.db2.jcc.DB2Driver
URL=[jdbc:db2://]()<host>:<port50000>/<database>

Download Driver for DB2

SQL Server 2005 or 2008 

DATABASE_TYPE=SQL_SERVER
DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver
URL=jdbc:sqlserver://<host>:<port1433>;SelectMethod=cursor;DatabaseName=<database>;sendStringParametersAsUnicode=false

Download Driver for SQL Server

SQL Server Options sendStringParametersAsUnicode and SelectMethod

SENDSTRINGPARAMETERASUNICODE: JAVA CLIENTS, INCLUDING FLUX, CAN EXPERIENCE PERFORMANCE PROBLEMS WHEN COMMUNICATING TO SQL SERVER USING JDBC DRIVERS. MOST OF THE JDBC DRIVERS PASS STRING PARAMETERS TO SQL SERVER AS UNICODE BY DEFAULT. THIS CAN CAUSE THE UNICODE PARAMETERS TO FAIL TO RECOGNIZE APPROPRIATE INDEXES FOR VARCHAR KEY COLUMNS, INCREASING UNWANTED TABLE SCANS. SETTING THIS PARAMETER TO FALSE, AS ABOVE, CORRECTS THIS PROBLEM.

SelectMethod=cursor: This connection string parameter was required in older versions of SQL Server. In all SQL Server versions supported in Flux, however, the parameter is only required if large results are returned from database queries (typically more than 100 records at a time) and Flux does not have sufficient memory to handle the result set. If this method is turned on, it avoids this problem by returning result sets at 100 rows per call, but at the expense of server performance.

You may disable this property in Flux to achieve significant performance gains. Testing shows that in a typical environment, performance gains of up to 40% are achieved by disabling this property alone.

Choosing the SQL Server JDBC JAR file

SQL Server offers two versions of the database driver. For more information on choosing which JDBC driver is right for your installation, see System Requirements for the JDBC Driver from Microsoft.

SQL Server — Integrated Security

If your SQL server database will reuse the security credentials of the user who starts Flux / starts the Flux engine service, you can run in integrated security mode. In this case, the parameters would look like this:

DATABASE_TYPE=SQL_SERVER
DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver
URL=jdbc:sqlserver://<host>:<port1433>;SelectMethod=cursor;DatabaseName=<database>;sendStringParametersAsUnicode=false;integratedSecurity=true

With integrated security enabled, the JDBC_USERNAME and JDBC_PASSWORD parameters should not be set, as these will indicate to Flux that SQL authentication rather than Windows authentication will be used.

Finally, it is necessary to install the file sqljdbc_auth.dll into the Java installation. Typically, this can be done by copying the file into <Java home>/lib. For more information, see Building the Connection String from Microsoft.

Oracle 

DATABASE_TYPE=ORACLE
DRIVER=oracle.jdbc.OracleDriver
URL=jdbc:oracle:thin:@<host>:<port1521>:<database>

Download Driver for Oracle

MySQL 

DATABASE_TYPE=MYSQL
DRIVER=com.mysql.jdbc.Driver
URL=jdbc:mysql://<host>:<port3306>/<database>

Download Driver for MySQL

PostgreSQL 

DATABASE_TYPE=POSTGRES
DRIVER=org.postgresql.Driver
URL=jdbc:postgresql://<host>:5432/<database>

Download Driver for PostgreSQL

Configuring Flux to use a Data Source from an Application Server

If Flux is running within the same application server as your data source, you can easily configure Flux to use a data source from that server by setting the DATA_SOURCE engine configuration property. You can also specify a username and password to connect to the data source if required, as well as other settings to control how Flux interacts with the data source (see the previous link for a full explanation of each data source property).

If Flux is running from outside your application server, you can still access the data source. You will just need to set the PROVIDER_URL and INITIAL_CONTEXT_FACTORY engine configuration properties (and, if your initial context factory requires it, the username and password for the factory). The provider URL and context factory settings instruct Flux on where to find the application server hosting the data source, and how it should connect to that data source.