Connection Pool Maintenance

Here are instructions for maintaining the pool of connections between the database and application servers.

Every night, the Eclipse software recycles the pool of database connections, except connections marked as “in use” or “reserved” by Solar sessions. You can identify these connections by their creation date: anything older than 1:00 AM was from a previous business day. It is rare for connections to be marked as “in use” or “reserved” for more than a day under normal conditions, so it’s highly likely that any leftover connections were the result of a canceled database query or a disconnected user session. These connections consume database resources and should be closed.

Step 1: Closing Connections via the Connection Pool Manager Web Page

Browse to the Solar web start page, and then click on the link for the Connection Pool Manager. You will be prompted for your Eclipse user credentials when entering the secure area.

The Connection Pool Manager web page displays a table of all of the current connections between the database and application server, similar to the following example:

J2EE Phantoms 1

Before closing a connection, you may wish to review the “trace” output, which will show you the last database activity of the user’s connection. If a user shows up often, you may wish to contact them and discuss what they were doing that resulted in leftover database connections.

To close a connection, click the “Close Connection” button and wait a few seconds. The connection should disappear from the table. If it doesn’t, proceed to the next step below.

Step 2: Closing Connections from the Command Line

If you weren’t able to close a hung database connection from the web page, or if you’re looking to clean up connections from a previous application server instance that do not appear on the Connection Pool Manager web page, you may do so from the command line.

Log into the server as root, and run the following command:

ps -ef | grep SOLAR.J2EE.CONNECTION | grep -v "01:00"

This command will find any SOLAR.J2EE.CONNECTION phantoms that existed prior to the scheduled refresh at 1:00 AM. This is the quickest way to identify leftover connection phantoms.

Here’s an example of the above command being run on a server with leftover connections:

[root@eclipse ~]# ps -ef | grep SOLAR.J2EE.CONNECTION | grep -v "01:00"
eclipseB 7088 18276 0 Aug07 ? 00:07:52 phantom PHW SOLAR.J2EE.CONNECTION 51420 590
eclipseB 8182 18276 64 Aug07 ? 1-12:04:07 phantom PHW SOLAR.J2EE.CONNECTION 51420 685
eclipseB 8284 18276 0 Aug07 ? 00:05:27 phantom PHW SOLAR.J2EE.CONNECTION 51420 708
eclipseB 8473 18276 0 Aug06 ? 00:00:21 phantom PHW SOLAR.J2EE.CONNECTION 51420 492
eclipseB 21362 18276 0 Aug08 ? 00:00:37 phantom PHW SOLAR.J2EE.CONNECTION 51420 757
eclipseB 22168 18276 0 Aug06 ? 00:00:02 phantom PHW SOLAR.J2EE.CONNECTION 51420 510
root 23935 17186 0 08:43 pts/336 00:00:00 grep SOLAR.J2EE.CONNECTION
eclipseB 31961 18276 0 Aug06 ? 00:01:30 phantom PHW SOLAR.J2EE.CONNECTION 51420 466

As you can see, the query returned a number of phantoms. These can safely be terminated using the kill -4 PID command, where PID is the process ID of the phantom from the 2nd column of the output.