Operator ODBCSource
The ODBCSource operator generates an output stream from the result set of an SQL SELECT statement. 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 by assigning the values of the columns of the result set to the output stream attributes with the same name and data type. When a column of the result set contains null data, the corresponding output stream attribute is set to 0 for numeric data types and to the empty string for rstring and xml data types. The columns of the result set of the SELECT statement, as specified by the <column> elements of the <native_schema> element of the access specification, must be a superset of the attributes of the output stream. The values of the columns of the SELECT statement result set are assigned to output stream attributes by name.
If you want to stream the result set of your query into your application more than once, specify the number of times as the value of the replays attribute of the <query> element in the access specification. The operator runs the query that number of times. If the value of the replays attribute is 0, the ODBCSource operator runs the query repeatedly until the application is canceled. If the value of the replays attribute is not 0, a final punctuation is generated when all queries for the operator invocation complete.
The 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. For a usage example, see the description of the ODBCEnrich operator.
Behavior in a consistent region
An ODBCSource operator can be used as the start of a periodic or an operator-driven consistent region. If the region is operator-driven, ODBCSource initializes a drain after each query repetition is performed and all of its results are processed. If the operator is periodic, ODBCSource allows drains only after each query repetition and its results are processed, and waits for a drain at that time. Either way, there is a drain after each complete query execution, and only then.
In a consistent region, an ODBCSource operator cannot have a control input port.
An ODBCSource operator can be configured to run an SQL query either a limited number of times, or an unlimited number of times. If the number or repetitions is limited, the remaining number of repetitions is persisted in the checkpoint. On reset, the query is always executed and the processing of results restarts. As a result, a reset can cause part or all of the results of a query to be reported more times than the query was configured to run.
If the execution of a query fails, ODBCSource requests a reset of the consistent region.
On drain: no action is taken.
On checkpoint: If configured to run the query a fixed number of times, the number of remaining query runs is saved in the checkpoint.
On reset: If configured to run the query a fixed number of times, the number of remaining query runs is restored from the checkpoint.
Exceptions
If NoRetry is specified for the reconnectionPolicy parameter and the initial connection attempt fails while the ODBCSource operator is starting, it throws an ODBCOperatorShutdownException.
Examples
stream <int32 id, rstring fname, rstring lname>
MyPersonNamesStream = ODBCSource()
{
param
connection : "PersonDB";
access : "InfPersonIFL";
connectionDocument : "connections.xml";
initDelay : 3;
sleepTime : 6;
} Summary
- Ports
- This operator has 1 input port and 2 output ports.
- Windowing
- This operator does not accept any windowing configurations.
- Parameters
- This operator supports arbitrary parameters in addition to 12 specific parameters.
Required: connection, access
Optional: connectionDocument, connectionDatabase, connectionUser, connectionPassword, connectionPolicy, reconnectionPolicy, reconnectionBound, reconnectionInterval, initDelay, sleepTime
- Metrics
- This operator does not report any metrics.
Properties
- Implementation
- C++
- Threading
- Always - Operator always provides a single threaded execution context.
- Ports (0)
The ODBCSource operator has one optional input port. This port allows operator configuration to be changed at run time.
- Properties
-
- Optional: true
- ControlPort: true
- TupleMutationAllowed: false
- WindowingMode: NonWindowed
- WindowPunctuationInputMode: Oblivious
- Assignments
- This operator requires that assignments made to output attributes must evaluate at compile-time to a constant.
- Ports (0)
-
The ODBCSource operator has one required output port. This port submits a tuple for each row in the result set of the SELECT statement. The output port is mutating and its punctuation mode is Generating. A window punctuation is generated after all of the tuples are submitted as the result of a query. This behavior allows downstream operators to distinguish between tuples from different queries.
- Properties
-
- Optional: false
- TupleMutationAllowed: true
- WindowPunctuationOutputMode: Generating
- Ports (1)
-
The ODBCSource operator has one optional output port. This port submits a tuple when an error occurs on the SQL SELECT statement.
- Properties
-
- Optional: true
- TupleMutationAllowed: false
- WindowPunctuationOutputMode: Free
- 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
-
- Type: rstring
- Cardinality: 1
- Optional: false
- ExpressionMode: Constant
- 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
-
- Type: rstring
- Cardinality: 1
- Optional: false
- ExpressionMode: Constant
- 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
-
- Type: rstring
- Cardinality: 1
- Optional: true
- ExpressionMode: AttributeFree
- 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
-
- Type: rstring
- Cardinality: 1
- Optional: true
- ExpressionMode: AttributeFree
- 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
-
- Type: rstring
- Cardinality: 1
- Optional: true
- ExpressionMode: AttributeFree
- 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
-
- Type: rstring
- Cardinality: 1
- Optional: true
- ExpressionMode: AttributeFree
- 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
-
- Type: ConnectionPolicy (Immediate, Deferred)
- Cardinality: 1
- Optional: true
- ExpressionMode: CustomLiteral
- 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
-
- Type: ReconnectionPolicy (InfiniteRetry, NoRetry, BoundedRetry)
- Cardinality: 1
- Optional: true
- ExpressionMode: CustomLiteral
- 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
-
- Type: uint32
- Cardinality: 1
- Optional: true
- ExpressionMode: AttributeFree
- 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
-
- Type: float64
- Cardinality: 1
- Optional: true
- ExpressionMode: AttributeFree
- initDelay
This optional parameter specifies an initial processing delay, in seconds, before the operator begins emitting tuples. It is equivalent to the initDelay parameter of the FileSource or TCPSource operator in the IBM Streams Processing Language (SPL) standard toolkit. If this parameter is present, it must have exactly one value of type float64. If this parameter is not specified, no delay is used.
- Properties
-
- Type: float64
- Cardinality: 1
- Optional: true
- ExpressionMode: AttributeFree
- sleepTime
This optional parameter specifies the minimal time in seconds that the operator must wait before it can run a query again. It is equivalent to the sleepTime parameter of the DirectoryScan operator in the SPL standard toolkit. If this parameter is not specified, the operator does not wait between queries. If the difference between the time the last query was run and the current time is less than sleepTime seconds, the operator sleeps until the time since the last query was run is sleepTime seconds. If more than sleepTime seconds have passed, the query runs immediately.
This parameter is ignored if the replays attribute of the query element in the <access_specification> is set to 1.
- Properties
-
- Type: float64
- Cardinality: 1
- Optional: true
- ExpressionMode: AttributeFree