Hi,
Could u pls,tell anybody that
1.When we r creating JDBC Connection pool,we have an option to choose that Min & Max Connection resides in connection pool.My question is How many connections will be in connection pool by default.Is it 0 or 1? If it is 0 what will happen?
2.How to update values from one database into another database?

Thanks in Advance

by default, minimum connection which will be open a time with db will be zero, this is directly govern by the paramter you set in your pool configuration…

You should read this article about connection pooling: [url]http://en.wikipedia.org/wiki/Connection_pool[/url]

After reading the article, you will notice having a Min: 0 in terms of performance is ridiculous, but for dev environments is fine.

You should fine tune connection pooling by stress testing your applications and having DBA monitor connections, then fine tune Min/Max connections.

The more Min connections you keep in the pool, the better performance, but also the more threads will be used. It make no sense either to set a 200 Min value.

Fine tune is a funny world, and there is no “magic” numbers, it will depend for every installation.

In my experience, the “magic” number to use for JDBC minimum connections is 0. I’ve never seen a case where something other than 0 was useful/necessary. DevNull, you’re right that 0 will have a negative impact on performance–when a new integration starts up and the pool is empty. But for IS instances with steady traffic, this will not be the case.

It has been long known that anything other than 0 will result in stale connections and failed integrations. This is because the pool tries to hold on to connections even if the DB has killed them.

The general rules of thumb I follow:

• Set minimum connections to 0.
• Set the expire timeout to a few minutes shorter than what the DB uses to quiesce inactive connections. E.g. if the DB uses 120 minutes, set the JDBC adapter expiration to [STRIKE]155[/STRIKE] 115minutes.
• Set the block timeout to something other than 1000 milliseconds (the default when defining a new connection pool). One second is far too short. 30-60 may be more appropriate, depending upon the integration and the traffic volume.

A connection pool does not use threads when they are idle. They only consume memory.

I think you are now mixing JDBC adapter with Internal JDBC pools.
It “was” true setting something different to Min: 0 resulted in pool not been able to recycle, and that was the recommended value, however that was fixed.

About threads we need to know the difference betwen System and Java threads wM concept.
Any JDBC pool will use a JVM thread, but it’s true it will not be displayed as a “System” thread.

When talking about expiration you are right, is good to set expiration time “lower” than DB expiration, so if Db expiration is 120 min, set JDBC expiration to 110 not 155… or maybe I’m wrong here.

Again the best option is to fine tune this setting monitoring server usage and DB.

The “fix” with which I’m familiar is that the pool gets flushed when a stale connection is detected. But the service that hits the stale connection will still fail (depending on various factors). Is there another fix you’re referring to?

The settings apply to both JDBC adapter and internal JDBC pools. The issue is the same.

A connection doesn’t use a thread, system or otherwise. Neither does the pool. Both are simply objects within the JVM memory.

A thread, from the thread pool, when executing to run a service will request a connection from a connection pool. After the thread is finished, the connection is returned to the pool. Idle connections do not have threads associated with them.

Threads use connections, not vice versa.

Oops. Thanks for catching that typo. It was supposed to 115, not 155. (110 would be good too)

I agree about tuning. My starting point is always min connections = 0 for JDBC connections (adapter or internal pools). And I’ve never encountered a need to use something other than 0. If the pool gets up to 5 connections during an active period, they will stay in the pool until the expire time. IMO, there is no point trying to keep connections that are idle beyond the expire time. Sure, there is a delay to get the connections established when the pool is empty but for most integrations (IME, all integrations), the pool startup delay is immaterial.

Yes I’m refering to that one.

I would need to check, but I trust you however what I’m sure is the “Thread Pool” uses a thread, at least when taking a Thread Dump you will see the name displayed. When a service requires a connection, it will query the “Connection Thread Pool” which will return the “object”.

You welcome, it looked like a typo

Well I agree, however if you have average 5 connections in the pool, but you know your implementation has some kind of batch will will fire 100 concurrent services which require a connection, having Min:50 and Max:200 will increase performance. Of course you can live with 0 since the time of creating a connection is not really high… all depends on your needs.

Fine tune to suit your needs is the way to go, default 0 should fot for most environments.

Nice topic “thread”

Thanks for ur reply.
How to update Table values in one database as well as in another database at the same time?

Hi Rajesh,

You did specify that both the Database are exist in same server or different. please post the details of ur requirement.

Use XA JDBC connections and execute the adapter services within a single transaction. Probably best to use explicit transaction control. Refer to the JDBC Adapter user’s guide.

Rajesh, when you say to update in two different database then you have to use two different JDBC pools. Now if the question is the make them into one logical transaction then the answer is to use XA JDBC connections.