Operator NetezzaPrepareLoad

SPL standard and specialized toolkits > com.ibm.streams.db 2.1.0 > com.ibm.streams.db.netezza > NetezzaPrepareLoad

The NetezzaPrepareLoad operator converts an incoming tuple to a delimited string, which can be written to Netezza with its external table interface. The output tuple is then typically sent to the NetezzaLoad operator. For information about external tables, see the Netezza documentation about data loading.

The <table> element specifies information to build a delimited string that is used by a NetezzaLoad operator instance. The <native_schema> element contains a list of <column> elements that describe the columns within the Netezza table. The NetezzaPrepareLoad operator uses the description of these elements to build the delimited string.

The following is an example of an access specification that contains a <table> element that can be used by the NetezzaPrepareLoad operator:

<access_specifications>
	<access_specification name="myLoad">
	  <table tablename="table1" transaction_batchsize="500000"/>
	  <uses_connection connection="conn1" />
	  <native_schema>
		<column name="f1" type="INTEGER"/>
		<column name="f2" type="CHAR" length="32"/>
	  </native_schema>
	</access_specification>
</access_specifications>

Behavior in a consistent region

A NetezzaPrepareLoad operator can be used in a consistent region. It cannot be the start operator of a consistent region.

On drain: no action is performed.

On commit: no action is performed.

On reset: no action is performed.

Exceptions

The NetezzaPrepareLoad operator throws a run time exception when a column is not large enough to store an xml value that is converted to an rstring. This behavior is different from storing rstring attributes, where rstring data is truncated by the NetezzaPrepareLoad operator. Throwing the exception for xml occurs because truncated xml data results in useless xml data. Any other potential problems where tuple attribute types do not match types in the access specification are caught at compile time.

Character Encoding

Examples

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 5 parameters.

Required: access

Optional: connection, connectionDocument, delimiter, escapeCharList

Metrics

This operator reports 1 metric.

Properties

Implementation

C++

Threading

Always - Operator always provides a single threaded execution context.

Input Ports

Ports (0)

The NetezzaPrepareLoad operator is configurable with one required input port. The input port is non-mutable and its punctuation mode is Oblivious. A delimited string that is valid for loading into Netezza is formed each time that a tuple is received on the input port and submitted to the output port.

Properties

• Optional: false

• ControlPort: false
• TupleMutationAllowed: false
• WindowingMode: NonWindowed
• WindowPunctuationInputMode: Oblivious

Output Ports

Assignments

This operator does not allow assignments to output attributes.

Ports (0)

The NetezzaPrepareLoad operator is configured with one required output port. The port is non-mutating. The output stream can contain only one rstring attribute.

Properties

• Optional: false

• TupleMutationAllowed: false
• WindowPunctuationOutputMode: Preserving

Ports (1)

The NetezzaPrepareLoad operator is also configurable with one optional error output port. The output port is non-mutating. This optional error port submits one tuple when an error occurs, and it converts that tuple to a delimited string. The only such error occurs when a field is not large enough to hold a serialized xml attribute (such as CHAR, VARCHAR, NCHAR, or NVARCHAR). The resulting tuple contains one attribute that is an embedded tuple that contains all of the attributes from the input tuple.

Properties

• Optional: true

• TupleMutationAllowed: false
• WindowPunctuationOutputMode: Preserving

Parameters

Required: access

Optional: connection, connectionDocument, delimiter, escapeCharList

access

This mandatory parameter specifies the name of an access specification element in the connection specifications document. The access specification indicates 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

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

• Type: rstring
• Cardinality: 1
• Optional: true
• 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 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 runtime 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

delimiter

This optional parameter contains a single character to use as a delimiter in the delimited string that is created by the operator. The default value is the pipe (|) character. This same value must be used as a delimiter on the NetezzaLoad operator or the operator is not able to differentiate between fields in the input record.

The delimiter parameter is used in cases where the pipe character is found in string values and when the escapeChar external table option on the NetezzaPrepareLoad operator is not set. In that case, a different delimiter character that does not exist in any other string fields can be used.

Properties

• Type: rstring
• Cardinality: 1
• Optional: true
• ExpressionMode: AttributeFree

escapeCharList

This optional parameter contains a list of rstring values that contain characters that are preceded by an escape character (a backslash '') before they are written to Netezza. The values for the parameter can be specified in one of the following ways:

• The parameter can contain one rstring with multiple characters. In this case, each of these individual characters is escaped.
• The parameter can contain a list of one-character rstring values. In this case, each individual character is escaped.
• The parameter can contain a combination of the previous two methods: multiple rstring values with multiple characters. In this case, all the characters that are found in all of the rstrings are escaped.

An empty string indicates that nothing is escaped, and it results in slightly faster performance. Use this option if you know that the characters that need escape characters never appear in any string fields. The escapeCharList parameter is optional and the default value is an empty string which results in no escape characters added to the string. If the EscapeChar external table option that is used with the corresponding NetezzaLoad operator is not set, there is usually no reason for you to specify a value for the escapeCharList parameter. If escaped characters exist in the rstring and the EscapeChar external table option is not set, the escape character (backslash) is stored in the database.

Properties

• Type: list<rstring>
• Cardinality: 1
• Optional: true
• ExpressionMode: Constant

Metrics

droppedTuples - Counter

The number of input tuples which could not be prepared successfully.

Libraries

No description for library.

Command: ../../Common/ODBCLibInfo.pl