Migrating to Version 4.0
Any database access code that uses WebSphere’s datasources must be modified to use the standard packages, including all J2EE objects such as servlets, session beans, and entity beans. The packages:

com.ibm.db2.jdbc.app.stdext.javax.sql

and

com.ibm.ejs.dbm.jdbcext

have been deprecated as of version 4.0. Existing applications can use already created datasources, however – but they can’t create new datasource objects, since new datasource objects created using the deprecated packages can’t be bound into JNDI in version 4.0. In version 4.0 all new datasources must be created using the com.ibm.websphere.advanced.cm.factory.DataSourceFactory.

WebSphere versions 3.0x ran on the JDK 1.1 level, and IBM provided the necessary transaction support features now found in the JDK 1.2.x level and later JDKs. In version 4.0 the proprietary transaction support provided by IBM is in the following package:

com.ibm.db2.jdbc.app.jta.javax.transac tion.*;

Which must be replaced with the standard package:

javax.transaction.*;

After changing the above import statements, Java sources should be recompiled using the new JDK 1.3 package.

Before migrating your applications to the new supported transaction API, as a WebSphere developer you should be aware of several important transaction-management principles, one of which includes database connection usage and availability. If an application component obtains a connection to a database and then executes a transaction on that connection, once the transaction is flagged as completed by the database server and the transaction manager component of the transaction API, the connection is closed automatically and returned to the connection pool. A somewhat smaller and obviously overlooked setting is the transaction timeout setting. If you’re involved with fine-tuning WebSphere, then this little tidbit should be of some concern to you, since improper numbers will degrade performance and scalability.

Default Username and Password
Previous versions of WebSphere’s connection pooling didn’t allow for default username and password properties, which meant that developers had to figure out the best method of storing this sensitive information without complicating application architecture and compromising database server security. However, in Version 4.0 connection pooling datasource object now allows developers to use the default username and password set by the administrator, using one of WebSphere’s administrative tools.

Application Development
WebSphere has implemented the J2EE 1.2-compliant method of performing lookups. The syntax used by both versions 3.5 and 4.0 will work, but all new applications should be written using Version 4.0 syntax.

One of these changes involves the initial context lookup method:

must be changed to Version 4.0

com.ibm.websphere.naming.WsInitialContextFactory

Another change involves the string argument for the ctx.lookup() method:

must be changed to Version 4.0

javax.sql.DataSource ds =
(javax.sql.DataSource)
ctx.lookup("java:comp/env/jdbc/SampleDB");

Datasources and Connection Pooling
Datasources are normally created by a system administrator using one of WebSphere’s GUI-based admin consoles. To obtain a datasource from the factory and then bind the datasource into JNDI is a relatively simple process. Depending on your maintainability requirements you may decide to place the username, password, and datasource name in a plaintext properties file or, if using the HttpServlet interface, retrieve them using the getInitParameters() method. Listing 1 (go to www.sys-con.com/websphere/sourcec.cfm for complete listings) illustrates the retrieval and JNDI binding process:

One property that’s been introduced on the com.ibm.websphere.advanced.cm.factory.DataFactory, is the DISABLE_AUTO_CONN_CLEANUP property. This new property setting specifies whether or not the connection should be automatically released and returned to the connection pool at the end of each transaction (see Figure 1). The default value is false, which indicates that WebSphere’s connection pooling should automatically close a connection at the end of a transaction – thus releasing it back to the pool (see Figure 1).

Statement Cache Feature
I often used the “roommate from hell” story to get junior developers to fully understand the purpose of performing trivial, but very important, housekeeping routines such as closing resultsets, statements, and connections. Imagine living in an apartment with someone who loves to eat, but never cleans the kitchen: after a week, the energy and time required to restore the kitchen back to its once-pristine state is very resource-intensive. The same concept applies to database programming. The rule’s simple: if you’re the one who opened it – then close it.

One of the really outstanding features that can certainly improve your application’s performance is WebSphere’s statement cache feature. WebSphere has provided a caching mechanism that caches prepared statements, thus allowing for reuse. With statement caching the overhead of creating a new PreparedStatement each time one is needed is now noticeably reduced, thus improving performance by reducing the overall response times of the application.

WebSphere Exceptions
In version 3.02x the ConnectionPreemptionException was introduced, but no longer exists in version 3.5.x. ConnectionPreemptionException, in all cases, has been replaced with StaleConnectionException in versions 3.5.2 or later. com.ibm.ejs.cm.portability.StaleConnectionException has been deprecated in version 4.0, but applications using this class will still function as expected. As expected, IBM recommends writing new applications using the new com.ibm.websphere.ce.cm.StaleConnectionException class.

Connections that are no longer consistent because of momentarily disabled database servers or network failures are called stale connections. Other factors that can cause a connection to be invalid include:

1.   Trying to use an orphaned connection.

2.   Trying to use a connection on which close() was already called.

When this occurs, all connections in the pool must be flushed and recreated. In version 4.0 the StaleConnectionEx-ception detection and recovery mechanism has been improved. StaleConnectionException extends the ever-popular SQLException, so you may be asking “why should I even bother trapping this exception?” By explicitly handling the StaleConnectionException, you can now perform a specified number of retries, thus giving the application a chance to recover from a noncritical network or database failure.

Some SQLExceptions are now mapped to specific WebSphere exceptions. WebSphere’s connection pooling mechanism monitors these exceptions, thus making it easier for developers to trap and recover from these exceptions. In version 4.0 more vendor-specific codes are mapped in the abstraction layer, thus enabling better support and recovery from noncritical database errors. Database programmers no longer have to know each database-specific exception and can now write easily adaptable code without worrying about database specific SQLExceptions.

Listing 2 shows one method of handling a StaleConnectionException. If a request for a connection throws a StaleConnectionException, the application makes three additional attempts at getting a new database connection. If all attempts fail then close() is called on the java.sql.stat[ment and java.sql.connection objects, and the application responds with a critical error.

A ConnectionWaitTimeoutException indicates that the application has requested a connection from the connection pool but that no connection has been issued in the allotted time. The allotted time is the number of seconds specified in the CONN_TIMEOUT setting. The following code sample shows one acceptable way to handle this exception in WebSphere. One of the biggest real-world causes of ConnectionWaitTimeoutException is incorrect username and/or passwords (did you read all your e-mails today). System administrators usually sets this parameter according to the application workload requirements and/or expectations.

…

java.sql.Connection conn = null;

javax.sql.DataSource ds = null;

…

try {

…

// get initial context properties here and create initial naming context.

…

ds = (javax.sql.DataSource) ctx.lookup("java:comp/env/jdbc/sample");

conn = ds.getConnection();

} catch (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException cwte) {

// perform recovery operations – there’s no reason to retry getting a connection here.

} catch (java.sql.SQLException sqle) {

// let the world know!

}

Improving Application Performance
Improving application performance benefits not only the user of the system but also the creators of the system. Since developers are by nature problem solvers at some level, their constant desire to improve performance and solve other related issues such as quicker development turnaround time is quite understandable.

JNDI Lookups
One very important aspect of improving J2EE application performance is the caching of JNDI lookups. Imagine what it would be like if nobody remembered your name, or worse yet, who you are – they’d be forced to ask you the same questions each time you met. Well, the same analogy applies to the process and reasoning for caching (remembering) JNDI lookups. By implementing caching of JNDI lookups you’ll reduce the response times of your application(s). JNDI lookups are expensive operations, and should only be done once by the application. As a best practice lookups should be done in the servlet’s init() method or, if using EJBs, from the ejbActivate() method.

Freeing Up Resources
A very common mistake in database programming is waiting until the finalize{} block to close() opened resultsets, statements, and connections. The assumption here is that the resources on which the close() method is called are actually freed, and are available for reuse. This isn’t the case, though: resources are merely flagged for cleanup by the JVM, and until this happens valuable resources are still being held by the application. The result is an application that performs well under normal conditions, but won’t scale and is very difficult to debug. As a rule of thumb, database resources should be freed as soon as they are no longer needed.

Data Access Beans
WebSphere programmers can benefit a great deal by using data access beans provided by IBM. These data access beans provide additional functionalities and features beyond the benefits of connection pooling. Websphere’s data access beans provide a tight coupling between its connection pooling mechanism and standard JDBC best practices. To use these data access beans in your code and appreciate the advantage of doing so over doing it yourself, you must first incorporate some changes to your code.

Any application component that will use WebSphere’s data access beans must:

1.   Import the com.ibm.db package. The package can be found in “databeans.jar” located in the <WAS_HOME>\lib folder. As with any other package, it must be made available to your servlets, preferably in your application server classpath.

2.   For servlets, declare a class-level variable for the “StatementMetaData” class that will be fully initialized once the init() method has completed. All subsequent requests serviced by the servlet will be using the same instance.

Listing 3 shows a sample implementation of some the features and portability you can obtain by using IBM’s data access beans.

The init() method of the DBServlet performs a one-time initialization of the StatementMetaData (mData) object and the InitialContext lookup. The username and password credentials required to access the database, along with a datasource name, are set as init parameters and retrieved using the getInitParameter() method of the ServletConfig. A DataSource object is created using the datasource name provided to the InitialContext lookup method. This datasource object will now be available to service all subsequent requests to the servlet, without performing the expensive operation of looking up a DataSource object.

Now let’s discuss some of the operations performed by the servlet’s setStatementMetaData() method and by the performTask()(service) method, which makes it possible to access our data using IBM’s data access beans. In the setStatementMetaData() method, a new Statement MetaData (mData) object is created. Several methods on the mData object are called to set the database metadata information. The addParameter() method allows you to delegate the task of converting between Java datatypes and the underlying relational datatype to the mData object. Notice that the argument list of the addParameter() method includes both the regular Java datatype (Integer.class) and the relational datatype (Types.SMALLINT). Once created, the mData object becomes responsible for automatically converting the servlet’s Java datatype so as to make it consistent with relational datatypes. Because our servlet will be updating the relational table, we must also include the addColumn() method, which includes the same argument list as the addParameter() method. The addTable() method is also necessary for updating the relational table.

A connection object (conn) is created for every user request being serviced by the servlet, which allows for multiple concurrent users. The link between the database access bean and the actual database connection is the DatabaseConnection (dbConn) bean object, which provides an expanded set of functionalities and features. The SelectStatement (selectStatement) and SelectResult (result) objects act as an abstraction layer between the already established mData and the user data passed to the servlet. The selectStatement object is associated with a connection object, the mData object, and any parameters required by the underlying SQL statement.

After calling the execute() method on the selectStatement object, the result of the query is captured in a SelectResult (result) object. Once the result object is created, the connection object associated with the selectStatement can be returned to the connection pool by calling the dbConn disconnect() method. This is possible since the result object caches the query results one layer closer to client, thus making it available to the rest of the application, without the application holding on to much-needed system resources.

Testing the DataAccessBean (DBServlet)
To test the DBServlet sample in WebSphere 4.0 you will need, if you haven’t already done so, to complete the database configuration steps for using WebSphere sample applications. To access the configuration instructions, open your browser to http://localhost/WSsamples/index.html. The DBServlet locates all the employees for the specified department (dept) and, for demonstration purposes only, updates/increments the value of the employee’s salary by the amount specified, for the first employee in the list only. To add the servlet to WebSphere’s default_app application you’ll need to perform the following steps:

1.   Ensure that application server reloading is enabled. Otherwise the server will have to be restarted.

2.   Open the dbSample.jar file and extract the DBServlet.class file to <WAS_HOME>\installedApps\Sample_Application.ear\ default_app.war\WEB-INF\classes.

3.   Add the following to the <WAS_HOME> \installedApps\Sample_Application.ear\default_app.war\WEB-INF\web.xml file, making sure that the five attributes (ids) highlighted in blue are unique within the current web.xml file:

<servlet id="Servlet_1">

<servlet-name>DBServlet</servlet-name>

<display-name>DBServlet</display-name>

<servlet-class>com.mic.sample.DBServlet
</servlet-class>

<init-param id="InitParam_1">

<param-name>username</param-name>

<param-value>wsdemo</param-value>

</init-param>

<init-param id="InitParam_2">

<param-name>password</param-name>

<param-value>wsdemo1</param-value>

</init-param>

<init-param id="InitParam_3">

<param-name>datasource</param-name>

<param-value>jdbc/sample</param-

value>

</init-param>

<load-on-startup>1</load-on-startup>

</servlet>

…

<servlet-mapping id="ServletMapping_1">

<servlet-name>DBServlet</servlet-name>

<url-pattern>/dbSample</url-pattern>

</servlet-mapping>

4.   Open your browser to http://localhost/servlet/dbSample?dept=E01&amount=12345.

Try changing the value of “amount” in the query string, and you’ll be able to see the salary increment each time the page is submitted.

Summary
IBM’s WebSphere Application Server provides an abstraction layer for better data exception handling and data access beans for working with relational data. These new features reduce some of the complexity usually inherent with database programming, especially with distributed database programming. WebSphere provides a rich set of features and functionalities that most development teams aren’t familiar with or simply ignore. So, don’t be a statistic – maximize your resources!