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.
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).
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.
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.
- 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.
- 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_TYPE=DB2 DRIVER=com.ibm.db2.jcc.DB2Driver URL=[jdbc:db2://]()<host>:<port50000>/<database>
DATABASE_TYPE=SQL_SERVER DRIVER=com.microsoft.sqlserver.jdbc.SQLServerDriver URL=jdbc:sqlserver://<host>:<port1433>;SelectMethod=cursor;DatabaseName=<database>;sendStringParametersAsUnicode=false
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.
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.
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.
DATABASE_TYPE=ORACLE DRIVER=oracle.jdbc.OracleDriver URL=jdbc:oracle:thin:@<host>:<port1521>:<database>
DATABASE_TYPE=MYSQL DRIVER=com.mysql.jdbc.Driver URL=jdbc:mysql://<host>:<port3306>/<database>
DATABASE_TYPE=POSTGRES DRIVER=org.postgresql.Driver URL=jdbc:postgresql://<host>:5432/<database>
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.