JDBCWriterPattern

The JDBCWriterPattern pattern is used to write data to a database using a JDBC connection.

Supporting classes

• JDBCWriter
• LocalJDBCWriter

Required properties

The following properties are required for the pattern.

Property name	Value	LocalJDBCWriter	JDBCWriter
PATTERN_IMPL_CLASS	Class implementing JDBCWriterPattern interface	Applicable	Applicable
ds_jndi_name	Datasource JNDI name.	Applicable	Not applicable
jdbc_url	The JDBC URL. For example, jdbc:derby:C:\\mysample\\CREDITREPORT.	Applicable	Not applicable
jdbc_driver	The JDBC driver. For example, org.apache.derby.jdbc.EmbeddedDriver	Applicable	Not applicable
user_id	The user ID for the database. For example, Myid	Applicable	Not applicable
pswd	User password. For example, mypwd. LocalJDBCReader only.	Applicable	Not applicable

Optional properties

The following properties are optional for the pattern.

Property name	Value	Description	LocalJDBCReader	JDBCWriter
debug	true or false (default is false)	Enable detailed tracing on this batch datastream.	Applicable	Applicable
EnablePerformanceMeasurement	true or false (default is false)	Calculates the total time spent in the batch data-streams and the processRecord method, if we are using the GenericXDBatchStep.	Applicable	Applicable
EnableDetailedPerformanceMeasurement	true or false (default is false)	Provides a more detailed breakdown of time spent in each method of the batch data-streams.	Applicable	Applicable
batch_interval	Default value is 20. Make the value less than the checkpoint interval for record-based checkpointing.	Denotes the number of SQL updates to batch before committing.	Applicable	Applicable

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.

To provide the closest mapping to the skip-record processing for non-batching writers:

• Call the skip handler method onSkippedWrite(Object o, Throwable t) once passing record #2, and the SQLIntegrityConstraintViolationException instance.
• Call the skip handler method onSkippedWrite(Object o, Throwable t) a second time passing record #9, and the DataTruncation instance.
• Increment the skip count by two (assuming both exceptions are explicitly or implicitly part of the include list).

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.

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.

Related:

Batch data stream framework and patterns

Use the batch data stream framework

JDBCReaderPattern

ByteReaderPattern

ByteWriterPattern

FileReaderPattern

FileWriterPattern

(ZOS) RecordOrientedDatasetReaderPattern

(ZOS) RecordOrientedDataSetWriterPattern

JPAReaderPattern

JPAWriterPattern