StreamBase JDBC DataSource Configuration

Overview

This article provides a reference for writing a JDBC DataSource configuration file where the HOCON type is com.tibco.ep.streambase.configuration.jdbcdatasource.

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 three 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.

The top-level configuration object defines the configuration envelope the same way for all HOCON file types.

configuration

On a line below the header lines, enter the word configuration followed by an open brace. The configuration object is a sibling of the name, version, and type identifiers, and serves to define the configuration envelope around this type's objects as described on this page. The file must end with the matching close brace.

configuration = {
...
...
}

HOCON Properties Explained

Below shows the configuration's HOCON properties, usage, and syntax example, where applicable.

As of StreamBase 10.3.0, you can define multiple JDBC data sources in a single configuration file. Prior to 10.3.0, only one JDBC data source was configurable (and is still supported for backward compatibility). See here for the 10.3.0 and later HOCON syntax example, and here for a pre-10.3.0 example.

StreamBase 10.3.0 and later

JDBCDataSourceGroup

The JDBC data source group type defines a collection of JDBC data sources, indexed by name, of JDBC data sources used by the JDBC operator. JAR files and shared libraries needed by data source database drivers should be declared as application fragment dependencies.

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 array is optional and has no default value. If not present, the configuration is associated with all engines.

For example:

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

The JDBC dataSource type defines a connection to a server to be used by the Query operator. This object is required.

QAjdbc1

String. Example JDBC data source name. Names must be unique across all data source and data source group configurations.

serverURL

Data source URL. This required property is encryptable as part of the node-level secrecy system described in Encrypting Sensitive Configuration Data.

For example:

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

Data source driver. This property is required.

For example:

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

The database user name. This optional property is encryptable as part of the node-level secrecy system described in Encrypting Sensitive Configuration Data. If unspecified, then the property must appear in the data source URL.

For example:

userName = "fred"
password

The database user password. This optional property is encryptable as part of the node-level secrecy system described in Encrypting Sensitive Configuration Data. If unspecified, then the property 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 property 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 property 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 property 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 property 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 property is optional and its default value is 0, which disables disconnecting idle connections.

The connection timeout for connection pooling is useful if there is no maximum size to the connection pool. The pool can dynamically grow as needed and then the connections can be released over time when usage quiets down.

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 property 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 property 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 property is optional and has no default value.

For example, if operationBatchSize 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 property 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 Query operator assumes a communications failure 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 Query 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 property 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 Query 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 array 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.*" 
]
maxTypeCheckReconnectAttempts

This optional property controls the number of reconnections the StreamBase engine will attempt during type checking. The default value of 0 disables reconnection attempts. TIBCO strongly recommends supplying a nonzero value.

For example:

maxTypeCheckReconnectAttempts = 1
maxReconnectAttempts

Int. This property controls the number of reconnections the StreamBase engine will 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 property is optional and its default value is 0, which disables reconnection attempts. Supplying a nonzero value is therefore strongly recommended.

For example:

maxReconnectAttempts = 42
attemptTransactionIsolation

If set to true, the connection attempts to set its transaction isolation level to the JDBC TRANSACTION_READ_COMMITTED level. This property is optional and its default value is true.

For example:

attemptTransactionIsolation = true
transactionBoundary

The JDBC operator can integrate each JDBC transaction with the containing StreamBase Runtime transaction such that the JDBC transaction commits or rolls back when the Runtime transaction commits or rolls back.

The Runtime transaction completes when the tuple exits the parallel region. Set this property to PARALLEL_REGION to have the JDBC transaction commit when the tuple exits the parallel region. With this setting, you must also set the useConnectionPool property to true.

If this property is not set or is set to OPERATION, then each statement executed by the JDBC operator executes in its own JDBC transaction.

reconnectSleepMilliseconds

Int. The time in milliseconds to wait between execute attempts. This property 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 property 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 LiveView fragments, if this parameter is set to true, only one connection is opened for all tables the provider discovers and all queries go over this one long-lived connection. If this parameter is not set, there are no long-lived JDBC connections and a new JDBC connection is opened and then closed for each new query made.

For example:

shareConnection = true
useConnectionPool

If set to true, creates a pool of connections to this data source. This property is optional and its default value is false. If this property is set to true then the shareConnection field must be set to false.

For example:

useConnectionPool = false
maximumNumberOfConnections

If the useConnectionPool field is set to true, then this property defines the maximum number of connections to the data source that the connection pool will maintain at any given time. This property is optional and its default value is 0, meaning that the connection pool has no maximum size.

If a connection is requested for this data source and the maximum number of connections have already been created for the pool, then the requester waits for an existing existing connection to become available. See the connectionWaitMilliseconds property for additional information. Idle connections are removed from the pool per the setting of the connectionTimeoutMilliseconds field. TIBCO recommends setting a nonzero value for the connectionTimeoutMilliseconds field when there is no maximum size set for the connection pool.

For example:

maximumNumberOfConnections = 0
minimumNumberOfConnections

If the useConnectionPool property is set to true, then this field defines the minimum number of connections to the data source to maintain when idle connections are removed from the pool. This property is optional and its default value is 0. This property must be nonnegative and must be less than or equal to the value of the maximumNumberOfConnections property.

For example:

minimumNumberOfConnections = 0
initialNumberOfConnections

If the useConnectionPool property is set to true, then this property defines the number of connections to the data source that are created at startup. This property is optional and its default value is 0. This property must be nonnegative and must be less than or equal to the value of the maximumNumberOfConnections property.

For example:

initialNumberOfConnections = 0
connectionWaitMilliseconds

If the useConnectionPool property is set to true, then this property defines, how long, in milliseconds, a user of the connection pool will wait for a connection to become available. This property is optional and its default value is 0, meaning to wait forever.

For example:

connectionWaitMilliseconds = 10000
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 property is optional and its default value is false.

For example:

quoteStrings = false
zeroValues

Allows you to specify the exact zero value for a field, indexed by the replacement position in the SQL string. For example, if the SQL string is: select x from mytable where y={y} and z in ({list}), then the value of the string field list can be overridden by: zeroValues=[{ index=1, value="0"}]. This optional array has no default value.

For example:

zeroValues = [
  {
     index = 1
     value = "0"
  }
  {
     index = 2
     value = "0"
  }
]
additionalDriverArguments

String. An association of any other JDBC driver parameters. This optional property is encryptable as part of the node-level secrecy system described in Encrypting Sensitive Configuration Data.

For example:

additionalDriverArguments = {
  "com.oracle.someSpecificOracleJDBCSetting" = "my specific 
    oracle setting value"
  "com.other.setting" = "another specific setting"
}

HOCON Configuration File Samples

The following are examples of the jdbcdatasource type.

Multiple JDBC Data Sources Example (10.3.0 and Later)

In the example, two JDBC data sources are defined, Source1 and Source2.

name = "mydatasource"
version = "1.0.0"
type = "com.tibco.ep.streambase.configuration.jdbcdatasource"
configuration = {
  JDBCDataSourceGroup = {
    associatedWithEngines = [ "javaengine", "otherengine[0-9]" ]
    jdbcDataSources = {    
      Source1 = {    
        serverURL = "jdbc:mysql://mysqlserver:3306/test"
        driverClassName = "com.mysql.jdbc.Driver"
        userName = "user1"
        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.*"
        ]
        maxTypeCheckReconnectAttempts = 1
        maxReconnectAttempts = 42
        attemptTransactionIsolation = true
        reconnectSleepMilliseconds = 50
        shareConnection = true
        useConnectionPool = false
        maximumNumberOfConnections = 0
        minimumNumberOfConnections = 0
        initialNumberOfConnections = 0
        connectionWaitMilliseconds = 10000
        quoteStrings = false
        zeroValues = [
          {
            index = 1
            value = "0"
          }
          {
            index = 2
            value = "0"
          }
        ] 
        additionalDriverArguments = {
          "com.oracle.someSpecificOracleJDBCSetting" = "my specific oracle 
          setting value"
          "com.other.setting" = "another specific setting"
        }
      }
      Source2 = {
        serverURL = "jdbc:mysql://myoracleserver:3306/test"
        driverClassName = "com.oracle.jdbc.Driver"
        ...
      }
    }
  }
}

Single JDBC Data Source Example (pre-StreamBase 10.3.0)

The following pre-StreamBase 10.3.0 example defines a single JDBC data source. This version of the HOCON syntax is still supported for backward compatibility (StreamBase 10.3.0 and later also support single a single JDBC data source using the updated HOCON syntax).

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.*"
    ]
    maxTypeCheckReconnectAttempts = 1
    maxReconnectAttempts = 42
    attemptTransactionIsolation = true
    reconnectSleepMilliseconds = 50
    shareConnection = true
    quoteStrings = false
    zeroValues = [
      {
        index = 1
        value = "0"
      }
      {
        index = 2
        value = "0"
      }
    ] 
    additionalDriverArguments = {
      "com.oracle.someSpecificOracleJDBCSetting" = "my specific oracle 
        setting value"
      "com.other.setting" = "#!another specific setting"
    }
  }
}