App service connecting to DB2 - Singleton (Best Practice)

Question

All - When we connect to Cosmos db, the recommended practice is to create one lifetime connection instance and use it across application for every request (and that too without closing the connection) basically a Singleton kind of.

Now, I would like to know the best practice of establishing a Connection from Azure App services to DB2 database (which is at on-premises data center).

1. Do we need to create a connection once, put it in singleton and use it?
2. Do we need to Close the connection after serving every request or we should not be closing it at all ?
3. Do we need to do set any properties in DB2 (Connection related) so that it can serve multiple requests something like connection pooling etc etc ?

Please advise...!!

Thanks!
-Mathew James

Answer 1

Below are the answers for your 3 questions :

1. At your application service , no need to do singleton for connection to DB2 database engine. You could create connection for each request. There is no use in keeping a long-lived connection, it unnecessarily hogs the database engine's resources. Leave it to the DB2 server to handle efficiently the thousands of concurrent connections.
2. It is preferable to close your connections at the end of servicing your request. The connection will return to the connection pool maintained by your DB2 client sdk, as well as there is connection pooling support at the DB2 server side as well.
3. Yes, you need to follow the DB2 connection string specification to ensure that connection pooling is enabled. Follow the DB2 advice here

Answer 2

Mathew, Following -up on this,

Just to highlight some of the key aspects:

What a ‘connection’ means connection is the physical connection. Just to highlight, Developers might think of the Connection objects, like SqlConnection on dotnet for SQL Server, and this one is not a physical one but a proxy to one that is retrieved by the pool, so the object can be short-lived (and should) and the library will automatically lease/release physical ones as needed. 

Depending on the DB2 provider you are leveraging.

Typically, it is recommended to create a singleton instance of the DB2 connection and reuse it across the application for every request. This is because creating a new connection for every request can be expensive and can lead to performance issues. Hence, the aim is to maximize connection reuse whenever feasible.

As Anand highlighted, regarding closing the connection, it is generally a good practice to close the connection after serving every request. This ensures that the connection is released and can be reused by other requests.
Yes, the Connection pooling allows you to reuse connections instead of creating new ones for every request. This can improve performance and reduce the overhead of creating new connections.

To enable connection pooling in DB2, you may set the "Max Pool Size" property in the connection string. This property specifies the maximum number of connections that can be created in the pool. You may also set other properties such as "Min Pool Size", "Connection Timeout", and "Pooling" to configure the behavior of the connection pool.

Answer 3

Mathew James, Apologies for a long post:

Adding some more concepts to augments the above points, you may have already considered these factors, just sharing more info:

The setup of database connections can be time-consuming, potentially taking hundreds of milliseconds or even seconds. However, queries utilizing these connections tend to be significantly faster, particularly on-site (in milliseconds), although remote connections might entail longer durations. Hence, the aim is to maximize connection reuse whenever feasible.

 

Consider this scenario: running 1000 minor queries on a single connection would compete within a few seconds. However, if each query had its own separate connection, the collective time might extend to 30 minutes or more.

 

Nevertheless, there are instances where reusing connections isn't viable. Certain databases might restrict multi-threading on a connection or issuing multiple concurrent requests, necessitating individual connections for distinct execution threads. Additionally, when employing explicit transactions, the transaction's scope generally aligns with the active connection, allowing only one active transaction per connection.

 

Moreover, a dropped connection necessitates the establishment of a new one. 

The favored strategy involves connection pooling, where the code requests a connection, utilizes it exclusively for its required duration, and subsequently returns it to the pool.

 

Although specific details may differ across various databases, the fundamental concepts and rationale remain largely consistent. Depending on what DB2 provider you are using.

Check out these doc sections for more info:

• Modify the application to use connection pooling
• More guidance specific to App Service

Answer 4

Thanks for the sending the details.

In fact we tried Minpool & Maxpool too. An IBM expert did some changes in Connection User Profile (was something new to me. Basically created a Profile just for our application and it solved all the problems. Some hidden magic)

Another Question I have is one of your statement looks contradictory.

"Typically, it is recommended to create a singleton instance of the DB2 connection and reuse it across the application for every request. This is because creating a new connection for every request can be expensive and can lead to performance issues. Hence, the aim is to maximize connection reuse whenever feasible."

So singleton of Connection instance is recommended.

But as a next statement you say "it is generally a good practice to close the connection after serving every request".

If we close connection after every request, then for a new request if he tries to get from Singleton instance, you need to open the connection again - right ? Or are you saying Connection instance is created one time, but Open & Close every time ? I am Slightly confused.

Thanks in Advance!

-Mathew