IBM InfoSphere Streams Version 4.1.0

Operator ODBCEnrich

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

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

The ODBCEnrich operator generates a stream from an input tuple and the result set of an SQL SELECT statement.

The ODBCEnrich operator runs an SQL SELECT statement for each input tuple, and submits an output tuple for each record in the result set of the SELECT statement. If an invocation of the SELECT statement does not result in any records, no output tuples are submitted for that invocation.

The SELECT statement is specified as the value of the query attribute of the <query> element of the access specification that is named by the access parameter. Any valid SELECT statement for the database that is specified in the connection specification that is named by the connection parameter can be used. For each row in the result set, the operator produces a tuple. The tuple automatically assigns the values of the columns of the result set to the output stream attribute with the same name and data type. Null data in a result set column produces 0 for numeric attributes and an empty string for the rstring attributes. These automatic assignments do not override an explicit assignment to an output attribute or an auto-assignment of an input stream attribute with the same name.

The names and data types of the columns of the result set of the SELECT statement are specified by the <column> elements of the <native_schema> element of the access specification. However, no native schema column can have the same name as an input stream attribute, since there is no syntactic means to indicate whether such a name refers to the input stream attribute or the native schema column. Additionally, the operator checks that every output stream attribute either has an explicit assignment, has the same name as an input stream attribute, or has the same name as a native schema column.

In addition to the set of common operator parameters, the ODBCEnrich operator supports query-specific parameters that permit the parameterization of the SQL SELECT statement. In the access specification that is named by the access parameter, a <parameter> element is associated with each ODBC parameter marker in the SELECT statement. The operator declaration must specify a parameter with the same name as each of the <parameter> elements in its access specification. The values of a query-specific parameter must have the data type specified by its <parameter> element and the specified number of values. In the following example, the access specification PersonIAGST has these <query> and <parameter> elements:

stream <int32 id, rstring fname, rstring lname,   
int16 age, rstring gender, float32 score, float64 total>
MyCompletePersonStream = ODBCEnrich ( MyPersonNamesStream ) 
{                        
	param                 
		connection : "PersonDB";             
		access : "PersonIAGST";         
		connectionDocument : "connections.xml";     
		pid : id;   
}                               
<query query="SELECT id, age, gender, score, total FROM personsrc WHERE ID = ?" />
<parameters>
	<parameter name="pid" type="int32" />
</parameters>

Note that the SELECT statement has an ODBC parameter marker (the "?") in its WHERE clause. The <parameter> element named pid is associated with that parameter marker. In the operator declaration above, the corresponding operator parameter named pid has a single value, id, which denotes the input stream attribute with that name. The data type of the value of the pid parameter in the operator declaration, int32, matches that of the pid <parameter> element. For each incoming tuple, the operator runs this SELECT statement, providing the current value of the input stream attribute id as the value of that ODBC parameter marker.

A SELECT statement can have multiple ODBC parameter markers ("?"). The number of parameter markers must match the number of <parameter> elements. ODBC parameter markers are processed in the order in which they occur in the SELECT statement, and correspond to the order of the <parameter> elements in the configuration file.

Behavior in a consistent region

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

In a consistent region, an ODBCEnrich operator cannot have a control port.

On drain: No action is done.

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 ODBCEnrich 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 10 specific parameters.

Required: connection, access

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

Metrics
This operator reports 1 metrics.

Properties

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

Input Ports

Ports (0)

The ODBCEnrich operator has one required input port. The port tuples must contain attributes corresponding to the columns of the table that is being queried. The input port is non-mutating and its punctuation mode is Oblivious.

Properties

Ports (1)

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

Properties

Output Ports

Ports (0)

The ODBCEnrich operator has one required output port. This port submits a tuple for each row in the result set of the SELECT statement that is run for an input tuple. The resulting tuple contains the input tuple attributes, plus additional attributes corresponding to columns in the result set. The output port is mutating and its punctuation mode is Preserving.

Assignments
This operator allows any SPL expression of the correct type to be assigned to output attributes.

Properties

Ports (1)

The ODBCEnrich operator has one optional output port. This port submits a tuple when an error occurs while the operator is running the SELECT 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 10 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. This parameter is required for all operators that require a database connection. 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 on 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. For example, if the ODBCEnrich operator specifies a reconnectionPolicy of BoundedRetry and a reconnectionBound of 3, up to three connection attempts are made at operator start up time, and, if a connection was not made, up to three connection attempts are made before each SQL statement attempts to 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

Metrics

droppedTuples - Counter

The number of input tuples that are dropped (not processed) because a query failure occurred.

Libraries

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