TdRedux Configuration for IBM WebSphere™ 4.0 Application Servers

As of TdRedux 1.20, IBM WebSphere 4.0 testing is performed as part of release test suite. The tests are based on a modified version of the sample code from the Enterprise JavaBeans, 3rd Ed., Richard Monson-Haefel, O'Reilly & Assoc. TdRedux can support both bean-managed persistence (BMP) and container managed persistence (CMP). The latter poses some important issues for application developers, which will be discussed below.

To setup IBM WebSphere to support the TdRedux JDBC driver, you must first update the server's CLASSPATH to include the path to the Jakarta ORO regular expression package via the Adminstrators Console Nodes-><nodename>->Application Servers->Default Server->Process Definition->JVM Settings form.

You then need to update the server's resource-provider-templates.xml file, located in the config directory. With WebSphere stopped, add the following text to the bottom of this XML file:

<!-- TdRedux Driver Templates -->

  <resources:JDBCDriver xmi:type="resources:JDBCDriver" xmi:id="resprovider_template_TdRedux" name="TdRedux JDBC Driver" description="TdRedux JDBC Driver" implementationClassName="com.presicient.tdredux.ConnectionPoolDataSource">
    <factories xmi:type="resources:DataSource" xmi:id="resfactory_template_TdRedux" name="" jndiName="jdbc/{Datasource JNDI Name}" description="a New TdRedux Datasource" category="" jtaEnabled="false" minimumPoolSize="1" maximumPoolSize="10" connectionTimeout="180" idleTimeout="1800" orphanTimeout="1800">
      <propertySet xmi:id="J2EEResourcePropertySet_TdRedux.1">
        <resourceProperties xmi:id="res_prop_template_TdRedux.1" name="serverName" type="java.lang.String" value="localhost" description="The name of the server where Teradata resides, such as 'localhost'"/>
        <resourceProperties xmi:id="res_prop_template_TdRedux.2" name="port" type="java.lang.String" value="1025" description="The TCP/IP port Teradata uses for communication. By default, Teradata uses port 1025."/>
        <resourceProperties xmi:id="res_prop_template_TdRedux.3" name="databaseName" type="java.lang.String" value="" description="Default logon database."/>
        <resourceProperties xmi:id="res_prop_template_TdRedux.4" name="sessmode" type="java.lang.String" value="ANSI" description="Default logon session mode."/>
        <resourceProperties xmi:id="res_prop_template_TdRedux.5" name="partition" type="java.lang.String" value="DBC/SQL" description="Default logon partition."/>
      </propertySet>
    </factories>
    <propertySet xmi:id="J2EEResourcePropertySet_TdRedux"/>
  </resources:JDBCDriver>

After updating the provider template, restart WebSphere. Then Open the administrative console, select Resources->JDBC Providers and create a new JDBC provider from TdRedux. You must specify the pathname to the TdRedux.jar file for your system, and set the type of driver to SQL92. Once you've configured the provider, you can then create a DataSource by opening Resources->JDBC Providers->TdRedux JDBC Driver and clicking on the Data Sources icon. This will display a list of all currently configured TdRedux data sources (none if none are configured). Click on the "New" button to display a form to enter all the appropriate configuration information. Remember to click the Properties button and configure the serverName property, as well as any other properties you might need. Most importantly, remember to save the configuration when done!

Notes on Using TdRedux (and Teradata) For Container Managed Persistent (CMP) Entity Beans

WebSphere does not have native support for Teradata within its automated CMP deployment tools, and thus presents some issues for using TdRedux as a persistent store with WebSphere's CMP container. Note the following:

1. WebSphere can produce SQL 92 level compatible SQL, which in most cases is compatible with Teradata SQL.
2. The DDL produced by WebSphere during bean deployment may need to be modified to be executable on Teradata.
3. Entity beans will produce SQL that is not fully Teradata-compliant, and which uses updatable cursors to operate on entity data. TdRedux performs a minimal modification to the cursor statements to provide compatibility; however, to use CMP with Teradata, with updatable cursors, you must set the data source sessmode property to ANSI, as updatable cursors are only supported in ANSI mode. Furthermore, the use of updatable cursors with Teradata is generally discouraged, due to the performance and resource contention issue related to "row-at-a-time" processing against very large datasets.
4. WebSphere attempts to mitigate the performance and resource issues of updatable cursors by setting the transaction isolation level to READ_UNCOMMITTED. Unfortunately, TdRedux is unable to support true transaction isolation levels, due to the manner in which Teradata implements isolation levels (ie, per-object, statement-level LOCKING clauses). Since queries may be arbitrarily complex, and may reference views which apply their own locking clauses, or reference nested, complex macros, supporting isolation levels programmatically within TdRedux is difficult, if not impossible. Therefore, TdRedux quietly ignores any request to change isolation levels. (Note that WebSphere currently does not query the DatabaseMetaData to determine if READ_UNCOMMITTED is actually supported by a JDBC driver before it attempts to set the READ_UNCOMMITTED level; TdRedux 1.20 has adapted to this behavior by suppressing the exception previously thrown in that case).
5. In light of the isolation level issue, application developers should consider converting the DDL generated by WebSphere into a base table, and a view with the appropriate LOCKING clause applied, with the view name set to the table name generated by WebSphere.