Operator JDBCRun

Specialized toolkits - release 4.3.1.0-prod20190605 > com.ibm.streamsx.jdbc 1.6.0 > com.ibm.streamsx.jdbc > JDBCRun

Primitive operator image not displayed. Problem loading file: ../../image/tk$com.ibm.streamsx.jdbc/op$com.ibm.streamsx.jdbc$JDBCRun.svg

The JDBCRun operator runs a user-defined SQL statement that is based on an input tuple.

The statement is run once for each input tuple received.

Result sets that are produced by the statement are emitted as output stream tuples.

The JDBCRun operator is commonly used to update, merge, and delete database management system (DBMS) records.

This operator is also used to retrieve records, create and drop tables, and to call stored procedures.

it is strongly recommended before you begin with the implementation of a SPL application on JDBCRun operator, read the documentation of database vendors.

Every database vendor delivers one or more JAR libraries as jdbc driver. Please check the version of your database server and the version of JDBC driver libraries.

Behavior in a consistent region:

The JDBCRun operator can be used in a consistent region. It cannot be the start operator of a consistent region.

In a consistent region, the configured value of the transactionSize is ignored. Instead, database commits are performed (when supported by the DBMS) on consistent region checkpoints, and database rollbacks are performed on consistent region resets.

On drain: If there are any pending statements, they are run. If the statement generates a result set and the operator has an output port, tuples are generated from the results and submitted to the output port. If the operator has an error output port and the statement generates any errors, tuples are generated from the errors and submitted to the error output port.

On checkpoint: A database commit is performed.

On reset: Any pending statements are discarded. A rollback is performed.

The new version of toolkit 1.3.x. supports also optional type.

The SPL applications based on new JDBC toolkit and created with a new Streams that supports optional type are able to write/read 'null' to/from a nullable column in a table.

Summary

Ports
This operator has 2 input ports and 2 output ports.
Windowing
This operator does not accept any windowing configurations.
Parameters
This operator supports 32 parameters.

Required: jdbcClassName, jdbcDriverLib

Optional: appConfigName, batchSize, checkConnection, commitInterval, commitPolicy, credentials, hasResultSetAttr, isolationLevel, jdbcPassword, jdbcProperties, jdbcUrl, jdbcUser, keyStore, keyStorePassword, keyStoreType, pluginName, reconnectionBound, reconnectionInterval, reconnectionPolicy, securityMechanism, sqlFailureAction, sqlStatusAttr, sslConnection, statement, statementAttr, statementParamAttrs, transactionSize, trustStore, trustStorePassword, trustStoreType

Metrics
This operator does not report any metrics.

Properties

Implementation
Java

Input Ports

Ports (0)

The JDBCRun operator has one required input port. When a tuple is received on the required input port, the operator runs an SQL statement.

Properties
Ports (1)

The JDBCRun operator has one optional input port. This port allows operator to change jdbc connection information at run time.

Properties

Output Ports

Assignments
Java operators do not support output assignments.
Ports (0)

The JDBCRun operator has one required output port. The output port submits a tuple for each row in the result set of the SQL statement if the statement produces a result set. The output tuple values are assigned in the following order: 1. Columns that are returned in the result set that have same name from the output tuple 2. Auto-assigned attributes of the same name from the input tuple

Properties

Ports (1)

The JDBCRun operator has one optional output port. This port submits tuples when an error occurs while the operator is running the SQL statement. The tuples deliver sqlCode, sqlStatus and sqlMessage.

Properties

Parameters

This operator supports 32 parameters.

Required: jdbcClassName, jdbcDriverLib

Optional: appConfigName, batchSize, checkConnection, commitInterval, commitPolicy, credentials, hasResultSetAttr, isolationLevel, jdbcPassword, jdbcProperties, jdbcUrl, jdbcUser, keyStore, keyStorePassword, keyStoreType, pluginName, reconnectionBound, reconnectionInterval, reconnectionPolicy, securityMechanism, sqlFailureAction, sqlStatusAttr, sslConnection, statement, statementAttr, statementParamAttrs, transactionSize, trustStore, trustStorePassword, trustStoreType

appConfigName

Specifies the name of the application configuration that contains JDBC connection related configuration parameters. The 'credentials' parameter can be set in an application configuration. If a value is specified in the application configuration and as operator parameter, the application configuration parameter value takes precedence.

Properties
batchSize

This optional parameter specifies the number of statement to execute as a batch. The default batch size is 1.

Properties
checkConnection

This optional parameter specifies whether a checkConnection thread should be start. The thread checks periodically the status of JDBC connection. The JDBCRun sends in case of any connection failure a SqlCode and a message to SPL application.The default value is false.

Properties
commitInterval

This parameter sets a commit interval for the sql statements that are being processed and overrides the batchSize and transactionSize parameters.

Properties
commitPolicy

This parameter specifies the commit policy that should be used when the operator is in a consistent region.

If set to OnCheckpoint, then commits will only occur during checkpointing.

If set to OnTransactionAndCheckpoint, commits will occur during checkpointing as well as whenever the transactionCount or commitInterval are reached.

The default value is OnCheckpoint.

It is recommended that the OnTransactionAndCheckpoint value be set if the tables that the statements are being executed against can tolerate duplicate entries as these parameter value may cause the same statements to be executed if the operator is reset.

It is also highly recommended that the transactionCount parameter not be set to a value greater than 1 when the policy is onTransactionAndCheckpoint, as this can lead to some statements not being executed in the event of a reset.

This parameter is ignored if the operator is not in a consistent region. The default value for this parameter is OnCheckpoint.

Properties
credentials

This optional parameter specifies the JSON string that contains the jdbc credentials: username, password and jdbcurl or jdbcUrl.

This parameter can also be specified in an application configuration.

Properties
hasResultSetAttr

This parameter points to an output attribute and returns true if the statement produces result sets, otherwise, returns false

Properties
isolationLevel

This optional parameter specifies the transaction isolation level at which statement runs. If omitted, the statement runs at level READ_UNCOMMITTED.

Properties
jdbcClassName

This required parameter specifies the class name for jdbc driver and it must have exactly one value of type rstring.

The jdbc class names are defined by database vendors:

For example:

DB2 com.ibm.db2.jcc.DB2Driver

ORACLE oracle.jdbc.driver.OracleDriver

PostgreSQL org.postgresql.Driver

Properties
jdbcDriverLib

This required parameter of type rstring specifies the path and the file name of jdbc driver librarirs with comma separated in one string. It is recommended to set the value of this parameter without slash at begin, like 'opt/db2jcc4.jar'. In this case the SAB file will contain the driver libraries.

Please check the documentation of database vendors and download the latest version of jdbc drivers.

Properties
jdbcPassword

This optional parameter specifies the user’s password. If the jdbcPassword parameter is specified, it must have exactly one value of type rstring. . This parameter can be overwritten by the credentials and jdbcProperties parameters.

Properties
jdbcProperties

This optional parameter specifies the path name of the file that contains the jdbc connection properties: user, password and jdbcUrl.

It supports also 'username' or 'jdbcUser' as 'user' and 'jdbcPassword' as 'password' and 'jdbcurl' as 'jdbcUrl'.

Properties
jdbcUrl

This parameter specifies the database url that JDBC driver uses to connect to a database and it must have exactly one value of type rstring. The syntax of jdbc url is specified by database vendors. For example, jdbc:db2://<server>:<port>/<database>

jdbc:db2 indicates that the connection is to a DB2 for z/OS, DB2 for Linux, UNIX, and Windows.

server, the domain name or IP address of the data source.

port, the TCP/IP server port number that is assigned to the data source.

database, a name for the data source.

For details about the jdbcUrl string please check the documentation of database vendors

This parameter can be overwritten by the credentials and jdbcProperties parameters.

Properties
jdbcUser

This optional parameter specifies the database user on whose behalf the connection is being made. If the jdbcUser parameter is specified, it must have exactly one value of type rstring.

This parameter can be overwritten by the credentials and jdbcProperties parameters.

Properties
keyStore

This optional parameter specifies the path to the keyStore. If a relative path is specified, the path is relative to the application directory. The sslConnection parameter must be set to true for this parameter to have any effect.

Properties
keyStorePassword

This parameter specifies the password for the keyStore given by the keyStore parameter. The sslConnection parameter must be set to true for this parameter to have any effect.

Properties
keyStoreType

This optional parameter specifies the type of the keyStore file, for example 'PKCS12'. The sslConnection parameter must be set to true for this parameter to have any effect.

Properties
pluginName

Specifies the name of security plugin. The sslConnection parameter must be set to true for this parameter to have any effect.

Properties
reconnectionBound

This optional parameter specifies the number of successive connection attempts that occur when a connection fails or a disconnect occurs. It is used only when the reconnectionPolicy parameter is set to BoundedRetry; otherwise, it is ignored. The default value is 5.

Properties
reconnectionInterval

This optional parameter specifies the amount of time (in seconds) that the operator waits between successive connection attempts. It is used only when the reconnectionPolicy parameter is set to BoundedRetry or InfiniteRetry; othewise, it is ignored. The default value is 10.

Properties
reconnectionPolicy

This optional parameter specifies the policy that is used by the operator to handle database connection failures. The valid values are: NoRetry, InfiniteRetry, and BoundedRetry.

The default value is BoundedRetry. If NoRetry is specified and a database connection failure occurs, the operator does not try to connect to the database again.

The operator shuts down at startup time if the initial connection attempt fails. If BoundedRetry is specified and a database connection failure occurs, the operator tries to connect to the database again up to a maximum number of times.

The maximum number of connection attempts is specified in the reconnectionBound parameter. The sequence of connection attempts occurs at startup time. If a connection does not exist, the sequence of connection attempts also occurs before each operator is run.

If InfiniteRetry is specified, the operator continues to try and connect indefinitely until a connection is made. This behavior blocks all other operator operations while a connection is not successful.

For example, if an incorrect connection password is specified in the connection configuration document, the operator remains in an infinite startup loop until a shutdown is requested.

Properties
securityMechanism

Specifies the value of securityMechanism as Integer. The sslConnection parameter must be set to true for this parameter to have any effect.

Properties
sqlFailureAction

This optional parameter has values of log, rollback and terminate. If not specified, log is assumed.

If sqlFailureAction is log, the error is logged, and the error condition is cleared.

If sqlFailureAction is rollback, the error is logged, the transaction rolls back.

If sqlFailureAction is terminate, the error is logged, the transaction rolls back and the operator terminates.

Properties
sqlStatusAttr

This parameter points to one or more output attributes and returns the SQL status information, including SQL code (the error number associated with the SQLException) and SQL state (the five-digit XOPEN SQLState code for a database error)

Properties
sslConnection

This optional parameter specifies whether an SSL connection should be made to the database. When set to true, the keyStore, keyStorePassword, trustStore and trustStorePassword parameters can be used to specify the locations and passwords of the keyStore and trustStore. The default value is false.

Properties
statement

This parameter specifies the value of any valid SQL or stored procedure statement. The statement can contain parameter markers

Properties
statementAttr

This parameter specifies the value of complete SQL or stored procedure statement that is from stream attribute (no parameter markers).

Properties
statementParamAttrs

This optional parameter specifies the value of statement parameters. The statementParameter value and SQL statement parameter markers are associated in lexicographic order. For example, the first parameter marker in the SQL statement is associated with the first statementParameter value.

Properties
transactionSize

This optional parameter specifies the number of executions to commit per transaction. The default transaction size is 1 and transactions are automatically committed.

Properties
trustStore

This optional parameter specifies the path to the trustStore. If a relative path is specified, the path is relative to the application directory. The sslConnection parameter must be set to true for this parameter to have any effect.

Properties
trustStorePassword

This parameter specifies the password for the trustStore given by the trustStore parameter. The sslConnection parameter must be set to true for this parameter to have any effect.

Properties
trustStoreType

This optional parameter specifies the type of the trustStore file, for example 'PKCS12'. The sslConnection parameter must be set to true for this parameter to have any effect.

Properties

Libraries

Operator class library
Library Path: ../../impl/lib/com.ibm.streamsx.jdbc.jar