Reference for StreamBase JDBC DataSource Configuration

Overview

This article provides a reference for writing a StreamBase JDBC DataSource HOCON configuration file.

This configuration contains settings for JDBC data sources. It is separate from the base engine configuration and can therefore be managed separately without having to recreate an application archive and redeploy the application whenever for example a port number changes.

Required Header Lines

Each configuration file must contain the following header lines, typically found at the beginning of each file:

name

Specifies an arbitrary, case-sensitive string to name this configuration, which must be unique among other files with the same type, if any. Configuration files can refer to each other by this name. Select a name that reminds you of this configuration's type and purpose. For example:

name = "mydatasource"
version

Specifies an arbitrary version number that you can use to keep track of file versions for this configuration type in your development project. The maintenance of version numbers is under user control; StreamBase does not compare versions when loading configuration files during the fragment launch process. The version number is a string value, and can contain any combination of characters and numbers. For example:

version = "1.0.0"
type

This essential setting specifies the unique HOCON configuration type described on this page.

type = "com.tibco.ep.streambase.configuration.jdbcdatasource"

The header lines taken together constitute a unique signature for each HOCON file in a project's configurations folder. Each project's configurations folder can contain only one file with the same signature.

In addition, the configuration top-level element is the same for all HOCON file types.

configuration

On a line below the header element lines, enter the word configuration followed by an open brace. The configuration element is a sibling of the name, version, and type elements, and serves as a wrapper around this type's elements described below. The file must end with the matching close brace.

configuration = {
...
...
}

HOCON Elements Explained

Below shows the configuration's HOCON elements, its name-values, usage, and syntax example, where applicable.

JDBCDataSource

The JDBC dataSource type defines a connection to a server to be used by the JDBC operator. JAR files and shared libraries required by the database driver should be put either into the fragment class or dependency folders, or the engine configuration externalClassPath array.

associatedWithEngines

If you want to restrict this object to be associated with specific engines, do so here. Each entry can be a specific engine name or a regular expression that can applies to more than one engine. This name-value pair is optional and has no default value. If not present, the configuration is associated with all engines.

For example:

associatedWithEngines = [ "javaengine", "otherengine[0-9]" ]
serverURL

Data source URL. You can provide an enciphered string for the value; generate the enciphered string with the sbcipher command and prefix #! to the generated string. This name-value pair is required.

For example:

serverURL = "jdbc:mysql://mysqlserver:3306/test"
driverClassName

Data source driver. This name-value pair is required.

For example:

driverClassName = "com.mysql.jdbc.Driver"
userName

The database user name. You can provide an enciphered string for the value; generate the enciphered string with the sbcipher command and prefix #! to the generated string. This name-value pair is optional and has no default value. If it is not specified, then it must appear in the data source URL.

For example:

userName = "fred"
password

The database user password. You can provide an enciphered string for the value; generate the enciphered string with the sbcipher command and prefix #! to the generated string. This name-value pair is optional and has no default value. If it is not specified, then it must appear in the data source URL.

For example:

password = "secret"
fetchSize

Int. Sets the number of rows that will be read at a time. This is a hint to the JDBC driver and may be interpreted differently by each driver. A value of 0 means use the driver default. This name-value pair is optional and its default value is 0. Most drivers throw a SQLException if the value is less than 0.

Consider limiting the fetch size when your StreamBase application experiences memory problems related to large result sets returned from queries to the JDBC data source. Consider increasing fetch size if deadlocks occur because you are performing multiple queries against the same JDBC data source and query result sets are larger than the current database fetch size.

MySQL, however, will allocate and read the entire query result into memory unless the fetchSize is set to the magic value -2147483648 (Integer.MIN_VALUE). In the case where a SELECT returns a large amount of data, memory will be exhausted unless the fetchSize is set to the magic value.

A positive integer serves as a hint to non-MySQL JDBC drivers for the size to allocate for result set buffers. The actual size allocated depends on how a given driver interprets it, which may vary among drivers. A positive integer is ignored by MySQL drivers.

For example:

fetchSize = 123
operationTimeoutMilliseconds

Int. The timeout in milliseconds of background JDBC operations in StreamBase Studio and at runtime. This parameter can affect typechecking efficiency. StreamBase Studio communicates with the data source server in order to typecheck an application that has a JDBC data source connection. If the database server does not respond during this interval, the typecheck fails.

The default value of 15 seconds is adequate for normal local area network connections. Consider increasing this value if you experience typecheck failures due to a slow network connection. This name-value pair is optional and its default value is 15000.

For example:

operationTimeoutMilliseconds = 12000
queryTimeoutMilliseconds

Int. The timeout in milliseconds that the StreamBase engine is to wait for JDBC Query operations to execute. This name-value pair is optional and its default value is 0, which means it waits forever.

For example:

queryTimeoutMilliseconds = 321
connectionTimeoutMilliseconds

Int. The amount of idle time in milliseconds to keep a connection open, after which it is automatically disconnected. This prevents the application from holding a connection indefinitely. This name-value pair is optional and its default value is 0.

For example:

connectionTimeoutMilliseconds = 222
maxColumnSizeBytes

Long. The maximum column size expected in a JDBC table. Columns that are larger than this value will cause a typecheck exception. You can use HOCON power-of-ten or power-of-two suffixes like kB, MB, K, or M as an abbreviation.

This name-value pair is optional and its default value is 2048.

For example:

maxColumnSizeBytes = 4K
retrySQLStateValues

String. Retry any select, insert, or update JDBC operation that returns the specified SQLSTATE for this database driver. The operation is retried until it succeeds. This name-value pair is optional and has no default value.

For example:

retrySQLStateValues = ["001", "002"]
operationBatchSize

Int. Allows a Query operator to perform an insert or update operation in batch mode, and sets the number of statements that will be executed at one time. This name-value pair is optional and has no default value.

For example, if jdbc-batch-size is set to 10, then when the first nine tuples arrive at the Query operator, they are held by the JDBC connection instead of being executed immediately. When the tenth tuple arrives and is added to the batch of statements, the entire batch is executed at once.

If an update is interrupted normally (for example, by stopping the sbd server), the Query operator updates the JDBC table using all the accumulated statements, even if there are fewer than the specified number. However, if the application is stopped abnormally, the updates may not occur.

The value must be an integer greater than 1 (a value of 1 is valid, but not meaningful for a batch operation).

Note that your application may require more memory when you perform query updates in batch mode.

For example:

operationBatchSize = 10
batchTimeoutMilliseconds

Int. If specified, a batch is executed at the specified timeout in milliseconds, even if it is not currently full. This is useful for making sure that the batch is executed if data stops coming into the operator (for example, the end of a trading day).

Tuples may be written sooner than this number, or more frequently, for any number of reasons. This name-value pair is optional and its default value is 60000 (60 seconds).

For example:

batchTimeoutMilliseconds = 1000
reconnectRegexps

String. If a specified string is matched in the text of any error message, the JDBC operator assumes a communications failure has occurred, and attempts to open a new connection to the database server in order to re-execute the current command. If the error text is not matched in the error strings, and the JDBC operator has an error port, an error tuple is sent to the error port. If there is no error port, a tuple is sent on the error stream.

You can specify more than one jdbc-reconnect-regexp parameters to specify several error message strings to be considered communications failures. Each specified string is compared in order to the received error message text.

This name-value pair is optional. If you do not specify a reconnectRegexps value, the pattern .* (period-asterisk) is used as the pattern string, which means any received error causes a reconnection attempt.

For example, see the following array:

reconnectRegexps = [
".*duplicate private key.*"
".*some other temporary failure.*"
]
dontReconnectRegexps

String. An array of regular expressions which, if matched, cause the JDBC operator to NOT assume a communication failure and attempt to open a new connection to the database server to re-execute the command. This array is checked BEFORE the jdbcReconnectRegexp array. This name-value pair is optional and has no default value.

For example, see the following array:

dontReconnectRegexps = [ 
".*a fatal error for which we don't want to reconnect.*" 
]
maxReconnectAttempts

Int. This name-value pair controls the number of reconnections the StreamBase engine is to attempt. If the connection to the database server is lost, a value for this parameter of five attempts to establish a connection to the database server five times. If five connection attempts fail, an error message is printed and the StreamBase engine exits with an error status.

If a connection is reestablished to the database server, and then the connection is lost some time thereafter, the reconnection count is reset so that five more attempts to reconnect are made again.

The retry count is reset for each SQL exception, not for the lifetime of the connection. A value of -1 specifies that the server is to continue reconnection attempts without limits. This name-value pair is optional and its default value is 0, which disables reconnection attempts. Supplying a nonzero value is therefore strongly recommended.

For example:

maxReconnectAttempts = 42
reconnectSleepMilliseconds

Int. The time in milliseconds to wait between execute attempts. This name-value pair is optional and its default value is 500; a value of 0 means an immediate reconnect.

For example:

reconnectSleepMilliseconds = 50
shareConnection

Bool. If set to true, connections using the same <datasource> name are shared. This option can serve as an optimization that reduces the time to fetch and release a connection, but it can lead to blocks if updates and selects occur in the same application.

This name-value pair is optional and its default value is false, which means that, in an application with two or more JDBC operators, each JDBC operator gets its own connection from the StreamBase Server to the specified data source.

For example:

shareConnection = true
quoteStrings

Bool. Some databases (such as Vertica) require the strings passed into a PreparedStatement to be enclosed in single quotes ('Dallas'). Set this to true if the database vendor documentation specifies quoting the strings passed into PreparedStatement.setString.

This name-value pair is optional and its default value is false.

For example:

quoteStrings = false
additionalDriverArguments

String. An associative array of any other JDBC driver parameters. You can provide an enciphered string for the value; generate the enciphered string with the sbcipher command and prefix #! to the generated string.

For example:

additionalDriverArguments =
"com.oracle.someSpecificOracleJDBCSetting" = "my specific oracle setting value"
"com.other.setting" = "#!another specific setting"

HOCON Configuration File Sample

The following is an example of the com.tibco.ep.streambase.configuration.jdbcdatasource type.

name = "mydatasource"
version = "1.0.0"
type = "com.tibco.ep.streambase.configuration.jdbcdatasource"
configuration = {

  JDBCDataSource = {
    associatedWithEngines = [ "javaengine", "otherengine[0-9]" ]
    serverURL = "jdbc:mysql://mysqlserver:3306/test"
    driverClassName = "com.mysql.jdbc.Driver"
    userName = "fred"
    password = "secret"
    fetchSize = 123
    operationTimeoutMilliseconds = 12000
    queryTimeoutMilliseconds = 321
    connectionTimeoutMilliseconds = 222
    maxColumnSizeBytes = 4K
    retrySQLStateValues = ["001"]
    operationBatchSize = 10
    batchTimeoutMilliseconds = 1000
    reconnectRegexps = [
      ".*duplicate private key.*"
      ".*some other temporary failure.*"
    ]
    dontReconnectRegexps = [
      ".*a fatal error for which we don't want to reconnect.*"
    ]
    maxReconnectAttempts = 42
    reconnectSleepMilliseconds = 50
    shareConnection = true
    quoteStrings = false
    additionalDriverArguments = {
      "com.oracle.someSpecificOracleJDBCSetting" = "my specific oracle setting value"
      "com.other.setting" = "#!another specific setting"
    }
  }
}