JDBCReaderPattern

This pattern is used to retrieve data from a database using a Java Database Connectivity (JDBC) connection.

Supporting classes

1. CursorHoldableJDBCReader

   This class is referenced when the usage pattern of our JDBC input stream retrieves a set of results at the beginning of the step, and then iterates over them throughout the step-processing logic. The CursorHoldableJDBCReader uses a stateful session bean with a cursor holdable, non-XA data source. A cursor holdable JDBCReader is a pattern implemented in such a way that the cursor is not lost when the transaction is committed. As a result, ResultSets do not need to be repopulated after every checkpoint, which improves performance. To use CursorHoldableJDBCReader, package the CursorHoldableSessionBean in the application. To create the package, add the nonxadsjndiname=jndi_name_of_a_non-XA_data_source_to_database property to the properties file used by the BatchPackager. For example, nonxadsjndiname=jdbc/nonxads. To add multiple non-XA data sources enter the following: nonxadsjndiname=<jndi name1>;<jndi name2>...

   Restriction: Currently, the resource reference name of the JDBC data source is the same as the JNDI name.

2. JDBCReader

   This class is referenced when the usage pattern of the JDBC input stream retrieves a single result from a query, which is used and discarded after every iteration of the step.

3. LocalJDBCReader

   This class is referenced when data is read from a local database.

Required properties

The following properties are required for the pattern.

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

Optional properties

The following properties are optional for the pattern.

Property name	Value	Description	LocalJDBCReader	CursorHoldableJDCReader	JDBCReader
CursorHoldabilityEnabled	true or false (default is false)	Enable Cursor Holdability for this BDS Reader	Applicable	Applicable	Applicable
debug	true or false (default is false)	Enable detailed tracing on this batch datastream.	Applicable	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	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	Applicable

Interface definition

public interface JDBCReaderPattern {

	/**
	 * This method is invoked during the job setup phase. 
	 * 
	 * @param props properties provided in the xJCL 
	 */
	
	public void initialize(Properties props);
	
	/**
	 * This method should retrieve values for the various columns for the current row from the given resultset
   * object. Typically this data would be used to populate an intermediate object which would be returned
	 * @param resultSet
	 * @return
	 */
	public Object fetchRecord(ResultSet resultSet);
	
	/**
	 * This method should return a SQL query that will be used during setup of the stream to retrieve all
   * relevant data that would be processed part of the job steps
	 * @return object to be used during process step.
	 */
	public String getInitialLookupQuery();
	
	/**
	 * This method gets called during Job Restart. The restart token should be used to create an SQL query
   * that will retrieve previously unprocessed records. Typically the restart token would be the primary
   * key in the table and the query would get all rows with 
	 * primary key value > restarttoken
	 * @param restartToken
	 * @return The restart query
	 */
	public String getRestartQuery(String restartToken);
	
	/**
	 * This method gets called just before a checkpoint is taken.
	 * @return The method should return a string value identifying the last record read by the stream.
	 */
	public String getRestartTokens();
		
}

CursorHoldableJDBCReader xJCL example

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

LocalJDBCReader xJCL example

<batch-data-streams>
<bds>
<logical-name>inputStream</logical-name>
<props>
<prop name="PATTERN_IMPL_CLASS" value="com.ibm.websphere.batch.samples.tests.bds.EchoReader"/>
<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.LocalJDBCReader</impl-class>
</bds>
</batch-data-streams>

Related:

Batch data stream framework and patterns

Use the batch data stream framework

JDBCWriterPattern

ByteReaderPattern

ByteWriterPattern

FileReaderPattern

FileWriterPattern

(ZOS) RecordOrientedDatasetReaderPattern

(ZOS) RecordOrientedDataSetWriterPattern

JPAReaderPattern

JPAWriterPattern