JDBCWriterPattern

Supporting classes

• JDBCWriter
• LocalJDBCWriter

Required properties

Optional properties

Interface definition

public interface JDBCWriterPattern {

	public void initialize(Properties props);
	
	/**
	 * This is typically an Update query used to write data into the DB
	 * @return
	 */
	public String getSQLQuery();
	
	/**
	 * The parent class BDSJDBCWriter creates a new preparedstatement and
	 * passes it to this method. This method populates the preparedstatement
	 * with appropriate values and returns it to the parent class for execution
	 * @param pstmt
	 * @param record
	 * @return
	 */
	public PreparedStatement writeRecord(PreparedStatement pstmt, Object record);
}

JDBCWriter xJCL example

<batch-data-streams>
<bds>
<logical-name>outputStream</logical-name>
<props>
<prop name="PATTERN_IMPL_CLASS" value="com.ibm.websphere.batch.samples.tests.bds.EchoWriter"/>
<prop name="ds_jndi_name" value="jdbc/fvtdb"/>
<prop name="debug" value="true"/>
</props>
<impl-class>com.ibm.websphere.batch.devframework.datastreams.patterns.JDBCWriter</impl-class>
</bds>
</batch-data-streams> 

LocalJDCBWriter xJCL example

<batch-data-streams>
<bds>
<logical-name>outputStream</logical-name>
<props>
<prop name="PATTERN_IMPL_CLASS" value="com.ibm.websphere.batch.samples.tests.bds.EchoWriter"/>
<prop name="jdbc_url" value="jdbc:derby:C:\\mysample\\CREDITREPORT"/>
<prop name="jdbc_driver" value="org.apache.derby.jdbc.EmbeddedDriver"/>
<prop name="user_id" value="myuserid"/>
<prop name="pswd" value="mypswd"/>
<prop name="debug" value="true"/>
</props>
<impl-class>com.ibm.websphere.batch.devframework.datastreams.patterns.LocalJDBCWriter</impl-class>
</bds>
</batch-data-streams> 

Skip-record processing with the JDBCWriterPattern

The JDBCWriter/LocalJDBCWriter presents a special case when used with skip-record processing. The writer functions by batching a sequence of SQL statements and executing them all at once against a database, which throws a single BatchUpdateException if a problem occurs on execution. To match the granularity of the general skip-record processing, an attempt is made to parse a single BatchUpdateException to determine which statement's executions failed with which exceptions, as well as which individual and corresponding records are passed to the writers.

For example, if there are a batch of ten records, for record #2 the user's JDBCWriterPattern produces an SQL statement which results in java.sql.SQLIntegrityConstraintViolationException, and for record #9 the corresponding SQL statement results in java.sql.DataTruncation.

The process for parsing a BatchUpdateException to extract chained SQLExceptions for the comprising statements is not standardized across JDBC drivers and configurations. Additionally, some drivers abort on first failure without executing the full batch.

Note whether your driver configuration (some drivers behave differently in this area across different configurations) performs an atomic or a non-atomic batch execution style. Generally for the atomic style, the batch execution ends on the first failure, while for the non-atomic style the rest of the batch is executed.

In the case of the atomic style, the runtime aborts the step execution loop, (throws an exception rolling back the current transaction). In this instance, there is no meaningful exception to associate with the records of the statements not executed. Because they are never executed, they are not skipped.

In the case of the non-atomic style, a runtime heuristic is used to pick apart the BatchUpdateException, looking at the chained SQLException(s) . It tries to determine which statements within the batch execution resulted in errors and maps that back to the corresponding record, calling the skip listener with the corresponding record and the corresponding chained SQLException. It also increments the skip count once for each record, resulting in an exception within the batch, not just once for the entire batch.

If this mapping cannot be made (for non-atomic style), and a skip listener has been configured, the runtime calls the skip listener once for each problem record, but by passing the top-level BatchUpdateException. If the mapping cannot be made, and a skip listener has not been configured, the runtime ends the step execution loop by throwing an exception.