This pattern is used to write data to a database using a JDBC connection.
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 |
Property name | Value | Description | LocalJDBCReader | JDBCWriter |
---|---|---|---|---|
debug |
true or false (default is false) |
Enables 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 you 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 |
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); }
<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>
<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>
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 aborts 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. Since 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 aborts the step execution loop by throwing an exception.