IBM InfoSphere Streams Version 4.1.0

Operator ODBCAppend

SPL standard and specialized toolkits > com.ibm.streams.db 1.5.0 > com.ibm.streams.db > ODBCAppend

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

The ODBCAppend operator stores an input stream into a database management system table. A row is appended to the table for each input stream tuple, by using an SQL INSERT statement that is based on the information that is specified in the <table> element of the access specification that is named by the access parameter.

Behavior in a consistent region

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

In a consistent region, an ODBCAppend 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 ODBCAppend operator has successfully completed its checkpoint, it is possible for the same inserts to be attempted more than one time.

On drain: If there are any pending insert statements, they are run. If this results in any database errors, and the operator has an error output port, error tuples generated and submitted to the error output port

On checkpoint: A database commit is performed.

On reset: A rollback is performed.

Exceptions

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

Examples

() as mySink = ODBCAppend(persondata)
{   
param      
	connection         : "PersonDB";    
	access             : "PersonSink";  
	connectionDocument : "connections.xml"; 
}

Summary

Ports
This operator has 2 input ports and 1 output port.
Windowing
This operator does not accept any windowing configurations.
Parameters
This operator supports 11 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 ODBCAppend operator has one required input port. The names and types of tuple attributes on the required input port must correspond to the names and types of the <column> elements in the <native_schema> element of the access specification that is named by the access parameter. The <native_schema> column names must be the same as the names of the columns of the table that are being appended to. Also, the column types must be the SPL type that corresponds to the SQL type of the table column. The input port is non-mutating and its punctuation mode is Oblivious. Punctuation is used when the commitOnPunctuation parameter is specified.

Note: It is not required that all columns of a table be represented in the input stream schema and in the access specification <native_schema> element. Columns which have an automatically assigned or default value at insert time can be excluded.

Properties

Ports (1)

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

Properties

Output Ports

Assignments
This operator requires that assignments made to output attributes must evaluate at compile-time to a constant.
Ports (0)

The ODBCAppend operator has one optional output port. This output port submits a tuple when an insert error occurs when trying to insert a tuple record into the table.

Properties

Parameters

This operator supports 11 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 punctuation. The default 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 rowset is inserted.
  • If the number of rows that are inserted since the last transaction commit is greater than 0, the transaction is committed. 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 are inserted reaches the number that is specified in the transaction_batchsize attribute of the <table> element. For more information about the <table> element, see the connection specifications document.

This parameter is ignored if the transaction_batchsize attribute of the <table> element is set to a value of 1.

Properties

Metrics

droppedTuples - Counter

The number of input tuples that are dropped (not inserted into the table) because of an insert failure.

Libraries

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