IBM Cloud Authors: Yeshim Deniz, Elizabeth White, Zakia Bouachraoui, Pat Romanski, Liz McMillan

Related Topics: IBM Cloud

IBM Cloud: Article

Database Programming with Version 4.0

Database Programming with Version 4.0

The rich set of new features and functionalities in WebSphere Application Server 4.0 reduces some of the complexity usually inherent with database programming, especially with distributed database programming.

Before any application component attempts to retrieve data from a database it must first acquire a valid connection to the database. Most seasoned developers are well aware of the overhead incurred with getting a connection to a database. Not only must the application server set up a connection to the database, it must also maintain the open connection and perform any house-cleaning operations that must be done prior to releasing the connection and freeing up system resources again.

The prospect of performing these expensive operations each time a user request is submitted to the application is indecently unacceptable. Resources would be quickly drained and expensive hardware storage solutions would have to be implemented so applications can perform reliably and scale with user growth. With connection pooling the overhead of creating and releasing a connection is dramatically reduced since connections are now cached and reused by the application.

Connection Pooling
Web-based and standalone desktop applications can now benefit from this useful concept that’s been around for some time. Connection pooling mechanisms became more sophisticated with the introduction of improved middleware components such as Java servlets and Microsoft’s ASP technology. With improving middleware performance, RDMS and application servers needed a way to keep abreast of new customer expectations and application scalability.

IBM first introduced a connection pooling mechanism and datasource objects, in WebSphere Application Server version 3.02x. The nonstandard method used to create datasources and obtain connections to a database in versions 3.02x looked like this:

import com.ibm.db2.jdbc.app.stdext.javax.sql.*;

This must be replaced with:

import javax.sql.*;

in versions 3.5.x and later.

WebSphere’s connection pooling provides a layer of abstraction from databases that removes some of the burden of rewriting applications to trap vendor-specific exceptions whenever a database vendor change has occurred, thereby making your code more maintainable and portable.

Connection pooling provides several key benefits to two- and three-tier applications that access relational databases using JDBC 2.0 or later:

  • Improved application performance: Due to the reduced overhead of creating and destroying database connections. (Getting a new connection to a database is a very expensive operation.)

  • Improved resource management: Resources are no longer consumed unnecessarily, and are available to other application server components such as caching mechanisms and concurrent service requests.

  • Less code: Java classes and methods no longer need a separate mechanism for creating, obtaining, and managing database connections to relational back-end systems.

    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:




    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:


    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:

  • Version 3.5x

    must be changed to Version 4.0


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

  • Version 3.5x
    javax.sql.DataSource ds =

    must be changed to Version 4.0

    javax.sql.DataSource ds =

    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">




    <init-param id="InitParam_1">




    <init-param id="InitParam_2">




    <init-param id="InitParam_3">







    <servlet-mapping id="ServletMapping_1">




    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.

    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!

  • More Stories By Antonio Villafana

    Independent consultant with e-Mind Solutions. Has over 12 years experience in the computing industry. Served 10 years with the United States Army (whoooa) and since his expired term of service has worked with several leading technologies such as Messaging services, XML/XSL Transformations, J2EE, Workload Management and Load Balancing, Application Server implementation and monitoring, and RDMS development.

    Comments (0)

    Share your thoughts on this story.

    Add your comment
    You must be signed in to add a comment. Sign-in | Register

    In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.

    IoT & Smart Cities Stories
    With the introduction of IoT and Smart Living in every aspect of our lives, one question has become relevant: What are the security implications? To answer this, first we have to look and explore the security models of the technologies that IoT is founded upon. In his session at @ThingsExpo, Nevi Kaja, a Research Engineer at Ford Motor Company, discussed some of the security challenges of the IoT infrastructure and related how these aspects impact Smart Living. The material was delivered interac...
    CloudEXPO has been the M&A capital for Cloud companies for more than a decade with memorable acquisition news stories which came out of CloudEXPO expo floor. DevOpsSUMMIT New York faculty member Greg Bledsoe shared his views on IBM's Red Hat acquisition live from NASDAQ floor. Acquisition news was announced during CloudEXPO New York which took place November 12-13, 2019 in New York City.
    Atmosera delivers modern cloud services that maximize the advantages of cloud-based infrastructures. Offering private, hybrid, and public cloud solutions, Atmosera works closely with customers to engineer, deploy, and operate cloud architectures with advanced services that deliver strategic business outcomes. Atmosera's expertise simplifies the process of cloud transformation and our 20+ years of experience managing complex IT environments provides our customers with the confidence and trust tha...
    Intel is an American multinational corporation and technology company headquartered in Santa Clara, California, in the Silicon Valley. It is the world's second largest and second highest valued semiconductor chip maker based on revenue after being overtaken by Samsung, and is the inventor of the x86 series of microprocessors, the processors found in most personal computers (PCs). Intel supplies processors for computer system manufacturers such as Apple, Lenovo, HP, and Dell. Intel also manufactu...
    Darktrace is the world's leading AI company for cyber security. Created by mathematicians from the University of Cambridge, Darktrace's Enterprise Immune System is the first non-consumer application of machine learning to work at scale, across all network types, from physical, virtualized, and cloud, through to IoT and industrial control systems. Installed as a self-configuring cyber defense platform, Darktrace continuously learns what is ‘normal' for all devices and users, updating its understa...
    At CloudEXPO Silicon Valley, June 24-26, 2019, Digital Transformation (DX) is a major focus with expanded DevOpsSUMMIT and FinTechEXPO programs within the DXWorldEXPO agenda. Successful transformation requires a laser focus on being data-driven and on using all the tools available that enable transformation if they plan to survive over the long term. A total of 88% of Fortune 500 companies from a generation ago are now out of business. Only 12% still survive. Similar percentages are found throug...
    OpsRamp is an enterprise IT operation platform provided by US-based OpsRamp, Inc. It provides SaaS services through support for increasingly complex cloud and hybrid computing environments from system operation to service management. The OpsRamp platform is a SaaS-based, multi-tenant solution that enables enterprise IT organizations and cloud service providers like JBS the flexibility and control they need to manage and monitor today's hybrid, multi-cloud infrastructure, applications, and wor...
    Apptio fuels digital business transformation. Technology leaders use Apptio's machine learning to analyze and plan their technology spend so they can invest in products that increase the speed of business and deliver innovation. With Apptio, they translate raw costs, utilization, and billing data into business-centric views that help their organization optimize spending, plan strategically, and drive digital strategy that funds growth of the business. Technology leaders can gather instant recomm...
    The Master of Science in Artificial Intelligence (MSAI) provides a comprehensive framework of theory and practice in the emerging field of AI. The program delivers the foundational knowledge needed to explore both key contextual areas and complex technical applications of AI systems. Curriculum incorporates elements of data science, robotics, and machine learning-enabling you to pursue a holistic and interdisciplinary course of study while preparing for a position in AI research, operations, ...
    After years of investments and acquisitions, CloudBlue was created with the goal of building the world's only hyperscale digital platform with an increasingly infinite ecosystem and proven go-to-market services. The result? An unmatched platform that helps customers streamline cloud operations, save time and money, and revolutionize their businesses overnight. Today, the platform operates in more than 45 countries and powers more than 200 of the world's largest cloud marketplaces, managing mo...