SQL Batch Row Insert Service (com.ibi.agents.XDSQLInsert)

Syntax:

com.ibi.agents.XDSQLInsert

iIT Service Object:

SQL: SQL Batch Row Insert

Description:

The SQL Batch Row Insert service is designed to be used within an iteration process or a process that is being run from a splitting preparser. The batch inserter uses SQL batch operations where supported to update the database efficiently. The actual performance can be influenced by the blocking factors configured for this service and the blocking factors implemented on the database.

The inserts will be queued and issued to the database in groups, depending on the configuration. Any outstanding inserts will be issued upon successful completion of the process flow. The process flow must be configured to operate under local channel transaction control.

When the SQL Batch Row Insert service is used in an iteration process, your application process flow must inform the service that the iteration has completed. Otherwise, the SQL Batch Row Insert service will not know that the (current) batch must be sent to the database. This is true regardless of whether your configuration requests sub-batches. The iterators (for example, XML Split Iterator) provide a special service that must be configured to cause this notification to be made. This service takes the form of an end of iteration message that passes out of the iterator on the end_iteration edge. This is a custom edge that you must configure in your process flow. It should pass into the SQL Batch Row Insert service through a Junction object. The example in this section shows how to configure such a process flow. Failure to provide an end of iteration message in the insert loop will result in a loss of insertions into the database.

Parameters:

Parameter	Description
Output document type	Determines whether this service generates a status document or passes the input document to the process flow. Select one of the following options from the drop-down list: status input By default, status is selected.
Destination
Output Provider *	The name of a database provider that has been configured. The insert operation will be performed on this database.
Output SQL *	The Data Manipulation Language (DML) statement used to perform the insert into the database. Use the ?name parameter format that is used for the SQL service.
Batch Size	The number of statements in a batch. Enter zero (0) if all inserts are to be considered as one batch. If a value is entered, sub-batches may result, where commits are performed on the batch when the number of inserts reaches the specified value. The default batch size is zero (0).
Commit Sub-batches	If sub-batches are requested, this parameter determines whether they should be committed as they update. The default value is set to false.
Fail First	If set to true and the first row of the first batch fails, then the operation is terminated with a fail_insert edge. This usually indicates that the database cannot be reached or an insert cannot be performed for some reason. If this is omitted, then the generated status document will indicate which rows were successfully inserted. By default, false is selected.
Fail Count	If greater than 0, if the specified number of inserts fail, the operation terminates on the fail_insert edge. The default value is 0.
Omit Test	Performs the specified iFL operation. If the operation returns a result as true, then the row is not inserted into the database.
Fail Key	A specified key to save for inserts to identify failing insert rows. This value should consist of an iWay Functional Language (iFL) statement. Note: This setting consumes system resources to save the key and time to extract the key.
Out Encoding	The Internet Assigned Numbers Authority (IANA) character set to be used for the output database. Select one of the following character sets from the drop-down list or enter an encoding name: Leave Platform US-ASCII CP037/EBCDIC ISO-8859-1 UTF-8 UTF-16BE UTF-16LE UTF-16 By default, Leave is selected.

Edges:

The edges returned by the SQL batch row insert service are listed in the following table.

Edge	Description
success	The insert was batched for execution. If the intermediate batch size was reached, then the batch operation was completed successfully.
cancelled	A cancel was recognized.
fail_parse	The parameters could not be parsed.
fail_connect	A connection to the provider failed.
fail_connect_destination	A connection to the output database failed.
fail_operation	The insert operation failed to execute for some reason other than the error that is reported by the database. For example, the insert statement could not be formatted.
fail_insert	Failed on the actual commit if the number of invalid inserts exceeded a threshold value, or if configured, the first row fails.
fail_nullability	The database reported a nullability failure.

In addition to these edges, SQL Code edges are available as described in SQL Service (com.ibi.agents.XDSQLAgent).

Example:

In this scenario, a database must be updated with one row for each item in an incoming XML message. An example might be an EDI message denoting shipped packages.

The iterator could be an XML Split Iterator to present a single set of values further into the process flow. The SQL Insert operation formats an insert statement and adds it to the batch. The operation then passes control to the loop (a move service) that passes control back for the next row. The SQL insert service could have anchored the loop, but using a move service is a common idiom used to make the process flow easier to read.

When the last row being extracted by the iterator has been passed into the loop, the iterator sends the end of iteration message on the end_iteration edge. This edge can be wired into the Junction object that leads to the inserter. Naturally, other logic can also appear on either edge from the iterator. When control returns to the iterator, control is passed to the End node. As the process flow ends, the database commit is performed to insert the rows into the database. If the process is running in transactional mode, then the commit will be performed on a successful completion of the process flow. If the process is not running in a transactional mode, then the commit will be performed immediately.

If you have configured to commit on sub-batches, then it is possible that a transactional rollback will not avoid updating the database. Also, the behavior in the event of an error may differ amongst databases and drivers themselves.

For very simple types of insert operations, where no processing is to be done on the message other than simply inserting it, you can wire the end_iteration and the success edge to the same relation edge. The SQL Batch Row Insert service will sort out the messages.

If you intend to pass the message through an operation before the insert, you must separate the edges so that the intermediate services (for example, the Transform object in the following image) need not see the end of the iteration message.

The status document includes the error information. The type and specific error descriptions vary depending upon the capabilities of the specific JDBC driver currently in use.

<emitstatus status="0">
   <protocol>XDSQLInsert</protocol>
   <parms>
      <parm name="col1">100</parm>
      <parm name="col2">100</parm>
      <parm name="col3">aa</parm>
      <parm name="col4">02/01/2013</parm>
      <parm name="failcount">3</parm>
      <parm name="failfirst">false</parm>
      <parm name="failkey">100</parm>
      <parm name="outbatchsize">2</parm>
      <parm name="outencoding">leave</parm>
      <parm name="outomit"/>
      <parm name="outprov">LocalSQLServer</parm>
      <parm name="outsql">insert into TestHWM3 values (?col1, ?col2, ?col3, ?col4)</parm>
      <parm name="return">status</parm>
      <parm name="subcommit">false</parm>
   </parms>
   <timestamp>2013-02-06T17:02:11.179Z</timestamp>
   <status>0</status>
   <msg>after final batch</msg>
   <channel>ExecProcess</channel>
   <nodename>agent_Service</nodename>
   <batchstatus rowsread="3" batches="2" failures="1" omits="0">
      <fails count="1">
         <fail row="-1" key="100" text="SQLException while executing batch.  Unable to determine which row caused it: Error converting data type nvarchar to bigint."/>
      </fails>
   </batchstatus>
</emitstatus>