Enabling distributed database access threads to be pooled

You can enable distributed database access threads to be pooled to achieve certain performance advantages.

About this task

Enabling threads to be pooled provides the following advantages:

  • You can leave an application that is running on a workstation connected to Db2 from the time the application is first activated until the workstation is shut down and thus avoid the delay of repeated connections.
  • Db2 can support a larger number of distributed connections limited by the value of CONDBAT subsystem parameter, because the number of connections is not limited by the maximum number of threads.
  • Less storage is used for each DDF connection because a connection uses significantly less storage than a database access thread.
  • You get an accounting trace record each time that a thread is pooled rather than once for the entire time that you are connected. When a pooled thread becomes active, the accounting fields for that thread are re-initialized. As a result, the accounting record contains information about active threads only, which makes it easier to study how distributed applications are performing. If a pooled mode thread remains active because of sections that are specified with the KEEPDYNAMIC(YES) bind option, an accounting record is still written.
    Exception: If you employ account record accumulation, an accounting trace is not produced each time that a thread becomes pooled. Accounting records can be rolled up by concatenated combinations of the following values:
    • User ID
    • End transaction name
    • User workstation name
  • Each time that a thread is pooled, Workload Manager resets the information that it maintains on that thread. The next time that thread is activated, workload manager begins managing to the goals that you have set for the transactions that run in that service class. If you use multiple performance periods, it is possible to favor short-running units of work that use fewer resources while giving fewer resources over time to long running units of work.
  • You can use response time goals, which is not recommended when ACTIVE mode threads are used.
  • INACTIVE mode threads can better take advantage of the ability to time out idle active threads.
  • Thread reuse enables DDF to use a small pool of database access threads to support a large group of network clients.
  • The response times reported by RMF include periods between requests and within the same unit of work. These times are shown as idle delays.

Procedure

To enable distributed database access threads to be pooled:

Set the value of the CMTSTAT subsystem parameter to INACTIVE.
The recommendation is to use INACTIVE mode for database access threads instead of ACTIVE mode, whenever possible.

Results

Db2 always tries to pool database access threads, but in some cases such threads cannot be pooled. The conditions listed in the following table determine whether a thread can be pooled. When the conditions are true, the thread can be pooled when a COMMIT is issued.

Table 1. Requirements for pooled threads
If there is... Thread can be pooled?
A DRDA hop to another location with a pending request1 No
A package that is bound with RELEASE(COMMIT) Yes
A package that is bound with RELEASE(DEALLOCATE) Yes
A held cursor, a held LOB locator, or a package bound with KEEPDYNAMIC(YES) No
A declared temporary table that is active (the table was not explicitly dropped through the DROP TABLE statement or the ON COMMIT DROP TABLE clause on the DECLARE GLOBAL TEMPORARY TABLE statement) No
1A pending request occurs when a hop connection is created but no SQL statements are run at that location.

After a ROLLBACK, a thread can be pooled even if it had open cursors defined WITH HOLD or a held LOB locator because ROLLBACK closes all cursors and LOB locators. ROLLBACK is also the only way to use the KEEPDYNAMIC(YES) bind option to clear information about a dynamic SQL statement.