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: