Is it a good practice to set max connection to 0 in connection settings of data source in WAS which will enable infinite physical connections to database?
My web application is always running out of connections at some of time as there is long running operation which is repeated at regular intervals...
All connections are properly closed in finally block.
No, that is a bad idea. Unless you have a resource leak there must be some value that is large enough - use that, possibly with some margin just in case. No database can handle an infinite number of connections, so with that approach you are only pushing the problem from the connection pool to the database.
No, this is very bad idea. You will crush either application server or database server, or even both.
You have to diagnose which operations are taking too long and optimize them. Either on the application or database. Even one long operation shouldn't exhaust your pool, so it might be connection leak or design flow.
If they are not user initiated, like some kind of daily report generation, very long and database intensive, you can create timer beans or async calls for them, and use separate datasource, so they can be queued, but not affect your primary users and online operations. I
