JDBCReaderPattern
This pattern is used to retrieve data from a database using a Java™ Database Connectivity (JDBC) connection.
Supporting classes
- CursorHoldableJDBCReaderThis class is referenced when the usage pattern of your 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 that is 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 your application. To create the package, add the
nonxadsjndiname=jndi_name_of_a_non-XA_data_source_to_database
property to the properties file that is used by the BatchPackager. For example,nonxadsjndiname=jdbc/nonxads
. If you want 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 Java Naming and Directory Interface (JNDI) name. - JDBCReader
This class is referenced when the usage pattern of your JDBC input stream retrieves a single result from a query, which is used and discarded after every iteration of the step.
- 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) | Enables Cursor Holdability for this BDS Reader | Applicable | Applicable | Applicable |
debug | true or false (default is false) | Enables 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 you 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>