IBM InfoSphere Streams Version 4.1.1

Operator ODBCRun

SPL standard and specialized toolkits > com.ibm.streams.db 2.0.0 > com.ibm.streams.db > ODBCRun

Primitive operator image not displayed. Problem loading file: ../../image/tk$com.ibm.streams.db/op$com.ibm.streams.db$ODBCRun.svg

The ODBCRun 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 ODBCRun operator is commonly used to update, merge, and delete database management system (DBMS) records. This operator is also used to create and drop tables, and to call stored procedures.

The ODBCRun operator supports statement-specific parameters that establish the framework of the user-defined statement that is run. In the access specification that is named by the access parameter, a <parameter> element is associated with each ODBC parameter marker in the statement. The operator declaration must specify a parameter with the same name as each of the <parameter> elements in its access specification. The value of a statement-specific parameter must have the data type specified by its <parameter> element and the specified number of values. For a usage example, see the description of the ODBCEnrich operator.

Behavior in a consistent region

An ODBCRun operator can be used in a consistent region. It cannot be the start operator of a consistent region.

In a consistent region, an ODBCRun operator cannot have its commitOnPunct parameter set to true. It cannot have a control port. The configured value of the transaction_batchsize 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. If a consistent region is reset and tuples are replayed after an ODBCRun operator has successfully completed its checkpoint, it is possible for the same statements to be issued more than one time.

On Aster databases, SELECT statements in an ODBCRun operator do not work in a consistent region. ODBCSource and ODBCEnrich operators can be used instead.

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.

Exceptions

If NoRetry is specified for the reconnectionPolicy parameter and the initial connection attempt fails while the ODBCRun operator is starting, it throws an ODBCOperatorShutdownException.

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 arbitrary parameters in addition to 11 specific parameters.

Required: connection, access

Optional: connectionDocument, connectionDatabase, connectionUser, connectionPassword, connectionPolicy, reconnectionPolicy, reconnectionBound, reconnectionInterval, commitOnPunctuation

Metrics
This operator reports 1 metrics.

Properties

Implementation
C++
Threading
Always - Operator always provides a single threaded execution context.

Input Ports

Ports (0)

The ODBCRun operator has one required input port.

When a tuple is received on the required input port, the operator runs an SQL statement. In particular, the operator runs the SQL statement that is specified as the value of the statement attribute in the <statement> element of the access specification. The required input port is non-mutable and its punctuation mode is Oblivious. Punctuation is used when the commitOnPunctuation parameter is specified.

Properties

Ports (1)

The ODBCRun operator has one optional input port. This port allows operator configuration to be changed at run time.

Properties

Output Ports

Ports (0)

The ODBCRun operator has one required output port. This port is mutating and its punctuation mode is Generating. A window punctuation is submitted each time that the statement is run.

The output port submits a tuple for each row in the result set of the user-defined SQL statement, if the statement produces a result set. The output tuple values are assigned in the following order:
  1. Output assignments that are explicitly configured in the operator.
  2. Auto-assigned attributes of the same name and type from the input tuple.
  3. Columns that are returned in the result set that are specified in the <native_schema> element of the <access_specification> in the connection document.
Assignments
This operator allows any SPL expression of the correct type to be assigned to output attributes.

Properties

Ports (1)

The ODBCRun operator has one optional output port. This port submits one or more tuples when an error occurs while the operator is running the user-defined SQL statement.

Note: If the <statement> element of the <access_specification> element is configured with a transaction_batchsize value greater than 1, the error output port submits a tuple for each record in the rowset that is processed by the statement.

Assignments
This port set requires that assignments made to output attributes must evaluate at compile-time to a constant.

Properties

Parameters

This operator supports arbitrary parameters in addition to 11 specific parameters.
connection

This parameter specifies the name of a connection specification element in the connection specifications document that identifies the external service to which this operator connects. The connection parameter must have exactly one value of type rstring.

Properties

access

This parameter specifies the name of an access specification element in the connection specifications document that specifies how this operator accesses specific data in the external service that is identified by the connection parameter.

Properties

connectionDocument

This optional parameter specifies the path name of the file that contains the connection and access specifications that are identified by the connection and access parameters.

If the connectionDocument parameter is specified, it must have exactly one value of type rstring. If the parameter is not specified, the operator looks for a file that is called connections.xml in the etc subdirectory of the SPL application directory. The SPL application directory is the current working directory where the sc command is run. For example, if you run the sc command from the /home/myapp directory, the compiler looks for a connection document in the /home/myapp/etc directory.

Important:
  • The SPL compiler, in conjunction with the Database Toolkit, checks that the document named by the connectionDocument parameter is semantically valid XML. The compiler also checks that the values for the connection and access parameters have corresponding sections in this connection XML document.
  • The SPL compiler cannot check at compile time that the operator can connect to the external data service and access data as configured. The operators have internal checks for correct configuration of the external data service that might result in a run time failure. Any failures are captured in the processing element logs. You can also use the odbchelper and db2helper programs that are included with the Database Toolkit to help you find setup and configuration issues.
Properties

connectionDatabase

This optional parameter specifies the data source name (DSN) of the target database. If this parameter is specified, any value specified in the database attribute of the <ODBC> element of the <connection_specification> element in the connection.xml document is ignored.

Properties

connectionUser

This optional parameter specifies the user name or identifier that is used to connect to the target database. If this parameter is specified, any value specified in the user attribute of the <ODBC> element of the <connection_specification> element in the connection.xml document is ignored.

Properties

connectionPassword

This optional parameter specifies the password that is used to connect to the target database. If this parameter is specified, any value specified in the password attribute of the <ODBC> element of the <connection_specification> element in the connection.xml document is ignored.

Properties

connectionPolicy

This optional parameter specifies the policy that is used to determine when a database connection, or subsequent reconnection after a database connection failure, occurs. The valid values are Immediate and Deferred. The default value is Immediate.

If Immediate is specified, the connection to the database is attempted when the operator is started. If a connection fails while an operator is running, a reconnection is attempted immediately when the disconnection is detected. This connection behavior minimizes delays in tuple processing.

If 'Deferred` is specified, the initial connection to the database is not attempted until a connection is needed, which is usually the first time that an SQL statement is run. If a connection fails while an operator is running, the operator does not try to connect to the database until the next time that a connection is needed. This connection behavior is useful if an application has many instances of an ODBC operator and you need to minimize the number of connections that occur immediately.

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

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

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

commitOnPunctuation
This optional parameter specifies whether transactions are committed when the operator receives a window punctuation. The default value is false. If the parameter is set to true, the operator performs the following actions when a window punctuation is received:
  • If the current rowset is not empty, the statement that pertains to the rowset is run.
  • If the number of rows that were inserted since the last transaction commit is greater than 0, the transaction is committed and the uncommitted row counter is reset to 0.

If no window punctuation is received, a commit continues to occur if the number of rows that were inserted reaches the number that is specified in the transaction_batchsize attribute of the <table> element.

This parameter is ignored if the transaction_batchsize attribute of the <statement> element is set to a value of 1 or if the statement is not transactional in nature.

Properties

Metrics

droppedTuples - Counter

The number of input tuples for which an SQL statement failure occurred.

Libraries

No description for library.
Command: ../Common/ODBCLibInfo.pl