What is an ESB

The ESB term is confusing and perhaps over-hyped – but at its core – it is a messaging system (such as ActiveMQ, RabbitMQ or MQSeries) along with an integration framework such as Apache Camel or Mule – and it is a way to declaratively connect up web and other services.    So, if you had multiple web-services – you could write XML to query one, plug the results into another then insert the results into a database.    So, you can be rid of the ‘donkey’ work of generating Java classes from the WSDL using AXIS2 – then the code within your app to use the service, handle errors etc.

Also, it is a way to decouple the applications from their integrations – so e.g., when a new employee is hired, the HR app can post a message to the ESB, then the ESB can route it to all other interested apps, either the message itself or via a web-service call.  So the HR app is not concerned with who is interested in this info.  The other app – especially if it is called via a Web-Service call from the ESB, is not concerned about the HR app – thus they are ‘decoupled’.

Another use to allow a ‘service provider’ to upgrade their service to meet new requirements – but allow (via the ESB) older clients to keep using the same service.   The ESB would allow you to declaratively map/adapt the call from the old client to the new one – and vice-versa – i.e. the response could be mapped/adapted to the XML the older client is expecting.

So, you are not stuck with having to maintain multiple versions of a service.

Camel comes out of the box with about 80 connectors to end-points other than SOAP – HTML scraping e.g.   Mule has even more and even more third party ones.

Then, on top of all this, you can also use products that will allow you to declaratively setup and control business processes such as approvals etc.

File Upload Design Pattern - I

One of my recent projects added a requirement to insert and or update records from a file rather than from the UI.   Of course, all the business logic and validation that was being done for the UI had to be done on the records being updated or inserted from the file.

Good news was that  we had kept all the business and validation logic out of the controller.  So at least we were not faced with either refactoring the controller or duplicating the logic in another section of the program.

It was a good lesson for the junior programmers on the project -- as they had been chaffing at my insistence to keep business logic out of the control layer.

The file could contain thousands of records.

Mulling this over, I came up with the following design considerations.

1. Have the file be processed asynchronously. 
2. Have one transaction per record 
3. Re-use all the business logic encoded while developing the interactive screen.
4. Not have any business logic specific to the file upload that was not directly relevant to the file-upload process itself.  I.e. I did not want to introduce any business logic in the file-upload that was different than in the interactive screen.
5. Have the process that reads and writes excel files be only loosely coupled with the upload process.
6. Have the process be robust -- and stand up to server failures/restarts etc.
7. Build in some support function to cancel/suspend the process.

The process I designed was as follows:

1. All uploads will from a file downloaded by the application.
2. End User fills out a form to request a file download that he will update.
3. End User will be directed to a status page for file being processed -- it will show the file in a process of 'Preparing'.
4. The download will be processed asynchronously and on its completion and user will see a change of status on a page from preparing to 'Ready for download'.
5. End User will download the file, update it and then upload it back into the system.  The status of the file will be changed 'Validating'.
6. System will validate the file asynchronously.
7. End-user will see the results of the validation.  If the validation failed, they can download the file with the validation errors in a new column in the spreadsheet.  Correct them and re-upload.
8. Once the file clears validation, they will be allowed to submit it for processing.
9. The file changes/adds will be applied to the system.   A final status of either successful, partially successful or failed will be returned.

Generic process involved in selecting packaged software

Here is a generic process to follow when you are tasked with selecting utility packaged software.

The first thing to do is to determine the mission of the software.  E.g. it might be to 'lower time between lead generation and the close of a sale.'.

Then:

1. Identify Alternatives
2. Collect a feature list that these products support.   There are likely features to the product that you are not even aware of.
3. Identify features that are or might be of interest to you -- perhaps even prioritize them.
4. Evaluate/Compare three (you can choose a smaller or larger number) of the alternatives -- (these are not in any order):
1. Cost
2. Licensing terms
3. Performance
1. Identify one or more metrics -- say 'Transactions per second'.
2. Graph CPU, Memory, Latency and Disk I/O against the metric.
3. Platforms (e.g. Windows/Linux) supported
4. Databases required/supported (Oracle, DB2, MS-SQL Server etc.)
5. Viability of the product and the selling organization
6. Clustering: Distribution and Failover including rejoining by failed servers
7. Ease of install, support, upgrade etc.
8. Security
9. Audit Logging
10. Support for performance and network monitoring tools (e.g. SNMP).
11. Any other specific to your requirements.  E.g. you may have some need to have the software be stateless.
5. Summary and Conclusion/Recommendation, taking special and specific care to document how your selection supports the mission.

Saving checked out clear case files.

Do you use Clearcase for source code control? If so, you likely have a lot of work in your snapshot view that is not backed up. So, if your hard drive fails or your PC is stolen, you would lose a lot of work.

Here is a little script for Windows that I run at the end of each day that will copy all checked-out files to a location of your choice.

c:

cd snapshotViewFolder
del networkLocation\*.*
FOR /F "usebackQ" %%i in (`cleartool lsc -short -cview -me -r`) do copy %%i "networkLocation"

create a .bat file with these commands. then run it. all files checked out in the folder = snapshotViewFolder will be copied to networkLocation. 

In practice, I put this batch file in c:\windows\system32, and append the following command at the end of it.

shutdown -s -t 10



then, at the end of the day, I just run the batch file from the run-command window.


If you want to run the FOR command directly from the command line, replace each %% with just one %.

Protecting your Application from other Applications

Scenario: While servicing a HTTP request, you call an external Application via a plain old HTTP Post of an XML String.

During testing etc. everything goes swimmingly. However in production, this other service gets super slow -- and worse yet, hangs -- so -- in effect the Thread servicing your HTTP request is hung. Your end users' browsers are just spinning away. They happen to be a persistent bunch, they close their browser and retry again and again -- until your server simply ups and dies :(

You'll probably agree that is not a good scenario. Particularly in today's world with its emphasis on availability.

(Question: What is the timeout in IE before it times out? Answer at the end of the post.)

You can/should take precaution against this scenario. First, investigate the possibility of setting a timeout on your HTTP Post. If you are using java.net.HttpURLConnection you can set timeouts in Java 1.5 and on.

Second -- consider limiting the number of threads engaged at any one time in this activity. Here is the outline of a simple technique to do this --Assuming you are using the same multi-threaded instance to do your service call. Again, this is not meant to be working code:

public class ServiceImpl {
ConcurrentHashMap map = new ConcurrentHashMap();

public String send(....) {
if (map.size() > 25) throw new RuntimeException("Max Threads exceeded");
try {
map.put(Thread.currentThread(),start);
... call service..
return response;
}
finally {
map.remove(Thread.currentThread());
}
}

In practice you could also keep counts on the number of times the service was called, threw an exception etc. and use a restricted jsp to examine these values etc. If you 'practice' AOP, this would be a a great application of it.

If you are wondering how to arrive at the max number of threads to allow for a specific service -- ask this question -- what is the maximum percentage of total threads am I willing to dedicate to this part of my application? If the max number of threads in your container is configured at say 500 and your answer is 5%, then this number would be 25. In practice you would make this value configurable.

I actually implemented some counters in the above algorithm, including a 'max concurrent request' counter -- so I did get an idea of what the number could be for a specific back-end service call.

(Answer to the question posed earlier -- Five Minutes)

Low priority high volume database writes killing you?

Scenario: Your application has some very high volume -- and perhaps large -- writes to the database that are low priority. A perfect example would be a log that contains a CLOB.

Perhaps this is clogging up your database manager to the point it is affecting the performance of your app itself. Your instinct would be to perhaps defer the write of these log records to a background app perhaps via a JMS queue.

However, Oracle 10g+ has a most excellent feature that lets you implement similar functionality just by modifying your 'COMMIT' statement at the end of your insert.

Instead of doing a 'COMMIT', you do a 'COMMIT WRITE BATCH NOWAIT'. BATCH grants the database manager the permission to batch up the writes and write them out at its convenience -- and the NOWAIT instructs to return control to your app immediately. So, by adding just a few words to your 'COMMIT', you would have effectually implemented a queuing solution!

However, nothing is ever that simple. java.sql.Connection().commit(); is not going to accomplish this. You would have to do something like:

Connection c = DataSource.getConnection();
c.setAutocommit(false);
...prepare statement, insert, close prepared statement;
Statement s = java.Sql.Connection.createStatement();
s.execute("COMMIT WRITE BATCH NOWAIT");
s.close();
c.close();

Just double check to see if perhaps your DBA changed the system wide parameter COMMIT_WRITE . Its default is 'IMMEDIATE,WAIT'. Leave it that way.

A db trigger that executes java code inside your J2EE app

Yes, this is possible, at least in Oracle Enterprise Edition.

Do you need to take some action each time a database record is inserted/updated. The answer is Triggers of course, but then you are locked into either SQL or a Java Stored Proc -- with their attendant deployment issues. These may not solve your problem if you have to communicate with your JVM, say, to update a cache you are maintaining in your heap.

Ideally, you'd just like some code in your J2EE code to be executed. This gives you the ability to leverage your software infrastructure (code reuse) and not really limit you in what can or cannot be done.

Oracle has this very nice feature called Advanced Queueing. You can simply configure a JMS queue on your Oracle server -- no JVM necessary. Then you can write a trigger to put -- say the primary key of the record that was inserted/updated onto it.

Then within your J2EE code, you can simply service this queue like any other JMS queue. This has the added advantage of you being able to have multiple (clustered) receivers on the queue. Other Enterprise Integration patterns, such as selective receivers etc. are also supported.

Oracle does seem to live in its own world -- so I could not find a lot of documentation on this to implement in a non-Oracle App Server like Websphere. I don't think that you can configure such a queue on Websphere and use a Message Driven Bean.. you have to service this queue directly within your J2EE code base, perhaps via a Quartz job.

The steps you have to do are:

Start a background job via Quartz, then within it get a standard connection to the database. It has to be an Oracle Connection, not the WSJdbcConnection you would get via a DataSource.getConnection(). I later found out that you could use WSJdbcUtil.getNativeConnection((WSJdbcConnection)DataSource.getConnection()); to get this, but too late to implement in production, so I cannot vouch for it.

Then AQjmsQueueConnectionFactory.createQueueConnection(connection); will get you a JMS queueConnection.

There is more to this -- your DBA will provide you with the Java Object that the message is going to be packaged in. But, from here on it is not that different from servicing any other JMS queue.

I did implement this for a intensive mission critical application and it worked quite well.

Storing dates/timestamps in the database

One principle I like to follow is to have the database stand on its own as much as possible. So, if you use the standard date or even timestamp in Oracle at least, you have no means to tell what the time zone for it.

One would think that if you used the Oracle Date with Timestamp datatype, it would resolve your problem. However, jdbc (unless I missed something) does not recognize the timezone in this field. It is simply ignored. And, all times are assumed to be in the time zone of the JVM.

At least in one project I was involved with, I saw code where the programmer had misunderstood how it all works and introduced a bug where the time when it was read in was two hours earlier than the time that was written out.

If you have multiple app servers in different time zones updating the database, this would be a real issue. Ideally of course, your code/database should be independent of the timezone of the JVM.

As you can imagine, archiving the data or feeding it to secondary systems would be more problematic if one had to guess at or otherwise determine the timezone of the field.

I have two solutions to this problem. One is a little more cumbersome than the other -- but a little more database browsing friendly.

First you need to understand java.util.Calendar. My understanding of it is that the core value of this object is the time in milliseconds since the epoch in GMT. This is a cardinal value -- it is completely unaffected by the time zone of the JVM etc.

And java.util.Calendar is very sophisticated. It knows all the time zones in the world, but even more impressively knows whether or when daylight savings is observed in that time zone, further -- it also knows if and when the dates of the switchovers were changed.

So, once you have the cardinal value from the database, you can create a new java.util.Calendar object, setTimeInMillis(thatValue), and you are in business. Say you want to display the time in Central. You set the timezone of the Calendar object by something like setTimeZone(new TimeZone("America/Chicago")); and now you can display it using either SimpleDateFormat or FastDateFormat, using a formatter like "MM/dd/yyyy HH:mm:ss Z" -- and can rest assured that the correct time zone for that specific date will be displayed -- CDT or CST.

In summary:

Store the time in the database as a long integer -- from java.util.Calendar.getTimeInMillis(). Then when you read it back:

Calendar cal = Calendar.createInstance();
cal.setTimeInMillis(rs.getLong("fieldName");

Now, if and when you want to display this -- I think you should always show the timezone -- and you should pick one and not let it default to the timezone of the JVM.

so, then

TimeZone tz = TimeZone.getTimeZone("America/Chicago");
cal.setTimeZone(tz);
org.apache.commons.lang.time.FastDateFormat fdf = FastDateFormat.getInstance("MM/dd/yyyy hh:mm a z",Locale.US);
String displayValue = fdf.format(cal);

One drawback of this scheme is that you don't know the timezone of the original timezone. If you needed to know it for some reason, you could store it in a separate field. I recommend a GMT-HH:MM format, as not all timezones translate easily to something like "America/Chicago";

The second option is a little more complex. You can use the Oracle DATETIME with TIMEZONE data type. But now you cannot use the standard java.sql.PreparedStatement.setDate(java.sql.Date) or java.sql.ResultSet.getDate() functions.

Instead you would need to do something like this:

Update tablename set fieldName = TO_TIMESTAMP(?,"YYYY-MM-DD HH24:MI:SS.FF TZR") where primaryKeyFieldName = ?

you'd prepare a statement with the above string then:
FastDateFormat fdf = FastDateFormat.getInstance("yyyy-MM-dd HH:mm:ss:SSS Z");
ps.setString(1, fdf.format(cal));
ps.setString(2, keyValue);
ps.executeUpdate();

When reading it, you'd do the reverse:

select to_char(fieldname,"YYYY-MM-DD HH24:MI:SS.FF TZR") from tableName where keyFieldName = keyValue;

then String charValue = rs.getString(1);
Calendar cal = Calendar.getInstance();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss:SSS Z");
java.util.Date date = sdf.parse(charValue);
cal.setTime(date);

The nice part of this technique is that the database contains the relevant information in one field and it is easy to read.

This is not meant to be working code--I've typed this mostly from memory. But should give you enough information to use the technique in your code. Note that SimpleDateFormat.parse() is not threadsafe. It would need further refinement if i18n.

Character Set Encoding Issues.

Character Set encoding can become an issue in some scenarios. Migrating an App from one OS to another with different default character set encoding would need some examining.

Developing/Compiling on an OS with a different encoding than the one you are running your app on would be another.

This should be a consideration anytime you are communicating with an OS with a different character set encoding. Either if you are receiving files, or perhaps communicating via a message queue or just HTTP.

The default character set encoding on common OSs (Windows, AIX, Linux) may be different but they are mostly compatible and most likely this doesn't lead to issues.

This is a red flag for me. Anytime I see the potential for subtle and hard to recreate issues -- I like to be proactive in making sure they do not occur to begin with.

To specify the character set encoding in Java -- wrap the java.io.InputStream with java.io.InputStreamReader.

Charset cs = Charset.forName("ISO-8859-1");
InputStreamReader reader = new InputStreamReader(inputStream,cs);

e.g. if you are reading a file: -- inputStream would be:

InputStream inputStream = new FileInputStream(fileName);

In practice you would probably have the file encoding somehow configurable, so if the source system for your file changes, you could just make a config change and not have to make a coding change.

----------------------

If developing on one system and deploying on another with a different character set encoding -- you will need to learn up on Java's handling of character set encoding. There is easily found and good information on this on the internet. The point of this post is to simply alert you that this is/could be an issue.

My instinct would be to compile/build on the target system and specify the character set encoding of the source files to the javac compiler.

The issue isn't so much with the compiled code itself, but with String constants and the like.

A java I/o anti-pattern

A server (Websphere AS) crashed with an 'Out of Memory' error. Asked to investigate it I somehow came to this piece of code..(I think it was either or both the heap and thread dumps).

HttpURLConnection conn = new URL("some url").openConnection();
.. post some data to conn.. then..
StringBuffer sb = new StringBuffer();
InputStream is = conn.getInputStream();
while (true) {
int i = is.read();
if (i == -1) break;
sb.append(i);
}
clean up etc..

I saw two problems with this code:
1) If the system we were reading from went into some kind of loop, and kept feeding us data, we would keep reading it into our heap and ultimately crash when we ran out of heap-memory.

2) Reading one char at a time is not particularly efficient. A bufferedInputStream would be more appropriate. I ran some benchmarks and found that to read a 2240 bytes, the time fell from about 140ms to about 40ms with a 1024 byte readbuffer. This is pretty much all raw CPU time. So, if you are doing a lot of this, it would make a difference.

So, to prevent the crash put in some limit (whatever is reasonable in your case) into your read loop (break and throw an exception if the read exceeds a certain size), and to improve performance -- use BufferedInputStream to do your reading. Use 1024 bytes to start with, but the size of your buffer would depend on your application of course.

Websphere -- maximum # of sessions

Have you ever wondered what happens if, for whatever reason, there is a runaway amount of logins into your application.

The answer probably is -- not good things. Especially if you have any amount of data in your session, your server is going to struggle with managing the memory, i/o for session persistence etc.

So, on the assumption you agree with the premise: 'Do three things well -- instead of ten things badly'. You'll want to do something about this.

For Websphere at least there is a setting in the administrative console to set the maximum number of sessions on the server.

First of all you'll have to decide what this number should be. Only you really know the answer. Set this (or have this set) via the administrative console. 'Application Servers > serverName > Web Container > Session Management'. The 'Allow overflow' checkbox needs to be unchecked of course.

Your job is not done. You should be asking, what happens when this maximum is reached. Perhaps 'request.getSession(true);' will return null, or throw an exception? The answer is neither of those. Websphere will return you a session, but it will be an invalid one.

So, you will have to add this code to wherever you do a getSession(true) or a getSession(). Hopefully (presumably?) this isn't in more than a few places in your code.

HttpSession session = request.getSession(true);
IBMSession ibmSession = (IBMSession)session;
if (ibmSession.isOverflow()) {
... appropriate action ..

} else {

.. continue normally ..

}

Most likely you are going to want to simply redirect them to a simple html page with a message to the effect 'Server Busy, please retry later'.

Conserving Sessions

Sessions are somewhat precious. And it is a good idea to conserve them. If you agree with that premise -- and your site requires logging in just to enter it at all -- read on.

Are you aware of this JSP directive?

<%@ page session="false"%>

This instructs the JSP not to automatically create a session when this JSP is invoked. In the absence of this directive, a session will be created.

So, as you can imagine, if you have a page such as a Log In page that is a JSP, this directive needs be included in that JSP.

Otherwise, someone simply getting to this page is going to create a session on your server. And, if they choose to not Log In --- this session is going to live on your server until it reaches the session timeout value.

Corollaries:

1) The page they land on after you do a 'session.invalidate()' needs to have this directive

2) If the log in fails, make sure you have not created a session while processing the log in request, or if you have, invalidate it.

3) The 'Session Expired' page would need this too.

4) Make sure that you do not create a session while processing a 'Log Out' request from an expired session.

To Cache or not to Cache

Caches are seductive. For some reason Programmers love to write/use them. Though, in my opinion, they should only be used after careful thought. I am talking about the home-made caches written with the J2EE application itself -- not the ones written within system software such as database managers.

Here are my thoughts on the subject.

a)Make sure that the puts/gets from/to the cache are thread safe. java.util.HashMap is not thread safe. java.util.Hashtable is, but it is slow. Here is an excellent article that recommends the use of java.util.ConcurrentHashMap.

http://www.ibm.com/developerworks/java/library/j-jtp07233.html

b)Make sure that the cache cannot grow indefinitely. You need to somehow limit the maximum number of entries in the cache - lest it crash your server due to an 'out of memory error' from the JVM.

You'll have to decide how to manage the situation when the max number of entries is reached. Some entries will need to be discarded. Least Recently Used is one option, oldest is another option. This of course means you will have to track that somehow.

c)More likely than not, the cache entries are going to get stale at some point. Ideally you can somehow get notified of this event and remove the corresponding entry from the cache. If not, you may decide that you can live with say a four hour age. If you find an entry in your cache and determine it older than that age, you will discard it and refresh the value. This alogrithm has a number of disadvantages:

i) You could of course be working against a stale entry.

ii) If your servers are in a cluster, you could have different values for a particular key in each server -- This alone has a smell to it.

iii) You need to somehow delete entries in your cache that are stale or just be carrying deadweight in your JVM.

d) Related to b) but worth emphasizing. You'll be using up valuable space in your Heap for your cache.

If you've covered all the bases described above -- weigh the cost of all that, the additional risk from the complexity and the use of the very valuable Heap space against the performance improvements.

Websphere plugin:Counter intiutive behavior in clustered environments

If you are not using session persistence, but getting by just turning on Session Affinity in the Websphere Plug-in -- you may believe that if you restart just one server out of your cluster -- you would only affect the sessions on that server.

However you would be wrong. If you restart one server, say out of four -- all end-users on that server would of course lose their sessions. However, when the server comes back on line, the Websphere plug-in suspends its 'session affinity' and will direct sessions from other servers to the new server.

This, of course, would be no particular big deal if you had session persistence across your cluster, but if you didn't -- some of the end-users on the other servers will lose their sessions.

The plug-in just assumes you have session persistence and values balancing the load over the cluster over and above affinity when a new server comes on line.

Websphere and Commons Logging: Oil & Water

Are you using Apache's Commons logging and are perplexed as to why you cannot control this logging when running inside a Websphere server? The reason is that Commons is just a thin wrapper around (in theory) a logging system of your choice. Many people choose the log4j logger of course.

However, IBM sees fit to direct Commons to its own logger - so, you can configure commons logging all you want, IBM forces it to its own implementation -- the one used to log to the console.

This behavior was impossible to change in 5.1 and it is supposed to be able to change it in 6.x and beyond -- though I don't believe it is simple to do so. I spent a lot of time trying to get around it when our organization was on Websphere 5.1 -- and we started using the log4j logger directly -- so I haven't spent much time investigating it in 6.1.

I don't see any particular advantage to using Commons.. Unless there are very specialized requirements -- don't see any real argument against log4j.

J2EE Application improving performance.

Here is a little anti-pattern -- that depending on your application -- could be costing you.

While learning up on my legacy J2EE app. I noticed a lot of:

SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
String dateString = sdf.format(someDate);
and/or
Date date = sdf.parse(someString);

since these statements were inside the execute method of a Struts action, each HttpRequest was creating an instance of SimpleDateFormat -- and shortly thereafter discarding it..

Now SimpleDateFormat.parse() is not threadSafe.. so this is not all bad if you ARE parsing -- however, nine times out of ten, you will be just formatting. in which case you should create the SimpleDateFormat as an instance variable like so:

private static final SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");

If you already have apache-commons in your project, use FastDateFormat -- like so:

private static final FastDateFormat basicDateFormat = FastDateFormat.getInstance( "MM/dd/yyyy", Locale.US );

Now this leads to an important question. If you are not already using apache-commons -- should you?

Not for just this one option. In my opinion, unless a) you intend to use more than a few functions b) everyone in your project is familiar and willing to use them and most important c) someone takes ownership of maintaining this 'third party library'. i.e. resolving issues, downloading fixes, updates as and when necessary etc.

And, keep in mind that you WILL be introducing a dependency -- so if and when you migrate your appserver say from one that uses Java 1.5 to Java 1.6, it will be one more thing to check and confirm and perhaps upgrade and test. So, though it is easy enough to download and throw this library into WEB-INF/lib, there is an ongoing cost to use it -- make sure it is worth it.

Websphere -- shared class cache by the JVMs

If you are running multiple JVMs inside your Websphere 6.x+ server -- you may be surprised to learn that the JVMs share Classes in a common Class cache. Before you freak -- note that only System classes, i.e. those that are part of the jre itself are the ones that are shared. Your Classes are not.

This is actually a feature of the IBM version of the jre/jdk 1.5 and onwards.

To turn this off: set the JVM argument -Xshareclasses:none

If you are getting weird class loading errors -- and cannot figure out why -- try shutting off this cache.

Application server connection pool settings that are of concern to the Software Architect:Shared Connections

IBM Websphere: 'Shared Connections' is the setting in Websphere Application Server by default and, at least in my experience, is a setting that is little known about -- and, depending on your application -- can have a major impact on your design.

What it means is that the same connection is shared by all J2EE components during an HTTP request. At least that is what I think it means. However, I do know for sure the practical result of this setting..

Say you have a Servlet/Struts action or whatever server side component that gets executed to service an HTTP request: (not intended to be working code).

public void doGet(HttpServletRequest req, HttpServletResponse resp) {

java.sql.Connection conn = null;
try {
dataSource.getConnection();
... do stuff...
}
finally {
conn.close():
}
.. do some other stuff, perhaps a call to a backend service..then
req.getRequestDispatcher("some.jsp").forward(req,resp);
}

You may (understandably) believe that the connection is returned to the pool, right after you called Connection.close(). However, if the Connection Pool is set to 'Shared' (the default), you would be wrong. Websphere will hold onto the connection through the call to the backend service and until it finishes servicing the http request.

So, if you have an Application that has the above pattern -- you should definitely consider changing the datasource setting from 'Shareable' to 'Unshareable'.

You do this in the web deployment descriptor. Reference tab in the Websphere Administrative console.. or:




jdbc/ds1
javax.sql.DataSource
Container
Unshareable

Application server connection pool settings that are of concern to the Software Architect:Aged Timeout

Aged Timeout: default value is zero which implies no timeout.This is the maximum time that Websphere will use a given connection.  So, if a connection stays in use past this time, without an intervening idle window >= the Unused timeout -- Websphere will simply recycle this connection.

Again this setting depends on your application, but 9000 seconds or 2.5 hours is a good starting point.  No specific reason, it just feels good to know that connections are getting closed every so often so any memory leak in the connection either in Websphere or in Oracle is getting cleared. 

It also flags any connections on the db server that are older than this timeout for further investigation.

I am presuming you are well aware that ResultSets, Prepared Statements. Callable Statements are to be closed after use.  Closing the connection will not necessarily close them.  These timeouts will not protect you in the event you are not doing this.  I don't even mention the vanilla Statement, on the assumption that you are savvy enough to never use it in your J2EE application.

Application server connection pool settings that are of concern to the Software Architect:Unused Timeout

Unused Timeout: This is how long Websphere will keep a connection idle in the pool. That is, say there is a sudden demand for connections and the total # of connections in the pool climbs to say 100. Now, there is a decrease in web traffic. The connections in use at any time are now about 50 and stay there for a while. With the default timeout of 1800, Websphere will wait a half hour before releasing the excess 50 connections.

Another way to look at it is that if any one connection is in the pool and idle for 1800 seconds or 30 minutes -- it will be closed and removed from the pool.

I think 30 minutes is excessive. I recommend 300 seconds or five minutes as a starting point. Adjust depending on your application and other constraints. Closing the connection will ensure all associated result sets etc. will get cleared and Garbage Collected. The db server will be able to clear up the memory at its end as well.

If you have multiple applications or application servers (as in a cluster) connecting to the same database, this may result in a lower maximum # of connections in use at the database server over a given period of time. This would provide relief to your database server and, if it is licensed by max # of connections, may save you money as well.

There are two drawbacks to these connections being closed. It will clear the 'Prepared Statement Cache' and there will be some overhead of re-acquiring this connection if and when demand rises again. So you don't want to set this to too low a value.

Application server connection pool settings that are of concern to the Software Architect:Connection Timeout

If you have any responsibility for the stability/performance of your application -- there are some settings you need to be concerned about. I am familiar with Websphere and most likely there are comparable settings for its competitors. In Websphere's case at least, these settings are not very well documented and it is easy to misunderstand them. In this post, I will address 'Connection Timeout'.

Connection Timeout:If you do a javax.sql.DataSource.getConnection() and there are no connections available -- this is how long Websphere will wait until it throws a timeout exception. The default value is 180 seconds and this is a bad setting.

Why? Because while you are waiting, you are using up a thread in the web-container and this thread will stay active and waiting in line for the next available connection even if the end-user abandons the transaction by hitting stop, back buttons on his browser or even just closing the browser.

You and I may be patient folks (particularly with our own applications) -- but the rest of the world is not.

For a B2C application, and depending on how invested the end-user is in the transaction -- I wouldn't invest a thread past 3 seconds, five at the very most. For Intranet or B2B applications you can increase this -- but I wouldn't go past 15 seconds.

Chances are that the end-user is going to abandon his transaction after waiting the above mentioned times.

What can exacerbate the situation is that after abandoning the transaction, the end-user can keep repeating/retrying -- just chewing up threads on your server. Worse yet, these threads are going to live at least 180 seconds on your server!!

You can easily imagine that with enough users, this can snowball to the point of bringing your server to a complete halt.

You can/should, of course, trap this exception and display some appropriate message to the end-user -- like 'System is busy, please retry later'.. You need to log this event of course. Goes without saying that this logging should in no way be directed to the same database :)

Summary: Will depend on your application -- but set it as low as you can get away with. Don't exceed 3, 5 at the very most for a B2C application -- 15 for Intranet/B2B applications.