Resolution:
Environment
==========
ALL OS
ALL BW versions

Resolution
========
When BW executes a JDBC activity and catches an SQLException, BW tries to execute a Database TestStatement to figure out whether the connection is OK. If the test statement can be executed successfully, BW stops and throws the SQLException.

If the TestStatement cannot be executed, BW assumes this is a bad connection, closes the connection and creates a new connection. It then tries to execute the JDBC activity. The whole sequence will continue in a loop until one of the following conditions is met.

1). The loop will stop if there is a  JDBC Login Timeout.

2). The Loop will stop if the timeout specified for the JDBC Update activity in the input tab is reached.

3). The loop will continue 25 times after the first update statement fails. 25 is the default retryCount (if JDBC Update timeout is not specified). If a JDBC Update timeout is specified, a simple formula is used to calculate the number of retries.

 The following messages will be seen in BW log.

Getting connection from the connection pool...
Connection received
Connection established!
Job-183000: executing JDBC Update for connection /JDBC Connection.sharedjdbc.test-JDBC Update for 183000Job-183000 with timeout 0
Preparing to execute prepared statement
Setting statement parameters
Executing update.....
Exception ! java.sql.SQLException: [tibcosoftwareinc][Oracle JDBC Driver][Oracle]ORA-01950: no privileges on tablespace 'USERS'

Bad connection! Removing from pool to try again
Job-183000: connection retry = 1
Getting connection from the connection pool...
Connection received
Connection established!
Job-183000: executing JDBC Update for connection /JDBC Connection.sharedjdbc.test-JDBC Update for 183000Job-183000 with timeout 0

Bad connection! Removing from pool to try again
...

During this time, BW will close and re-create a few connections. However when BW closes a DB connection, the OS will not close the connection immediately. Instead, the connection will go to a TIME_WAIT state (you can still see the connection using the netstat command) and after a certain time, based on the OS, the connection will be removed. There is no connection leak in this situation, i.e., BW will close the connections immediately.

The "Maximum Connections" specified in the JDBC Connection Resource will still be honored in this situation. The "Maximum Connections" parameter specifies the size of the connection pool, which limits the number of ESTABLISHED connections from BW to the Database. The connections which BW has already closed, but the OS keeps in "TIME_WAIT" state, are not accounted for in the "Maximum Connections" set by BW. So, in the above situation, if you set the "Maximum Connections" to 5 you will never see more than 5 "ESTABLISHED" connections from BW to the Database, though the total number of connections ("ESTABLISHED" and "TIME_WAIT") may go beyond this value.

Note: TIME_WAIT sockets do not hold open file descriptors so you should not need to worry about a "too many open files" error with the FD limit being reached.

How does BW test bad JDBC connections?