StreamBase JDBC DataSource Configuration

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"

Data source driver. This property is required.

For example:

driverClassName = "com.mysql.jdbc.Driver"

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"

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"

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

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

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

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

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

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"]

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 StreamBase engine), 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

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

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.*"
]

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 do not want to reconnect.*" 
]

This optional property controls the number of reconnections the StreamBase engine will attempt during type checking. The default value of 0 disables reconnection attempts. It is a best practice to supply a nonzero value.

For example:

maxTypeCheckReconnectAttempts = 1

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

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

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.

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

For example:

reconnectSleepMilliseconds = 50

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 engine 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

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

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. It is a best practice setting a nonzero value for the connectionTimeoutMilliseconds field when there is no maximum size set for the connection pool.

For example:

maximumNumberOfConnections = 0

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

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

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

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

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"
  }
]

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"
}