Bookmark and Share

Recent Posts

Controlling DDF Threads using Profiles

August 2, 2016

Recently I wrote about IBM's strategy to move customers from DB2 Connect Gateway to DB2 Connect Client. The customers I've heard from aren't happy with this development.

Much of the frustration stems from the belief that it's no longer possible to control the number of concurrent connections a given application can have. Say, for example, using DB2 Connect Gateway you limit the number of concurrent connections for an order entry application to 30 and the employee HR application is set to 10. If, at the end of the month, 100 employees are all simultaneously trying to access DB2, DB2 Connect Gateway will throttle this access to avoid maxing out the available connections and overloading the DB2 subsystem.

Using DB2 Connect Client, you could set the HR application to use all available connections, but of course this would cause the order entry application to fail. However, there is a viable solution. DB2 9  introduced profile tables that provide dynamic SQL statement level stabilization. These same tables can be used to monitor and control the number of connections and threads.

SYSIBM.DSN_PROFILE_TABLE is used to identify contexts in which DB2 takes particular actions such as resource monitoring, subsystem parameter customization and dynamic SQL stabilization. The contexts might identify statements, threads or connections that are based on information about the originating application, system or user. Learn more about this table here.

In addition to the profile table, you need to set values in the SYSIBM.DSN_PROFILE_ATTRIBUTES table that specify the monitoring thresholds and actions to take. Learn more about this table here.

The KEYWORD column is used to specify the function DB2 will perform. Several keywords are available to monitor connections and threads, including:

* MONITOR CONNECTIONS, which specifies that DB2 monitors the total number of remote connections from application servers, including active connections and live inactive connections. The connections are filtered only according to the IP address or domain name value that is specified in the LOCATION column of the DSN_PROFILE_TABLE table.

* MONITOR IDLE THREADS, which specifies that DB2 monitors the approximate time (in seconds) that an active server thread is allowed to remain idle. When the specified value is 0, threads are allowed to remain idle indefinitely. When the specified value is a negative number, this monitor function is disabled and a message is recorded in the profile attributes history table to indicate that the row is rejected.

* MONITOR THREADS, which specifies that DB2 monitors the total number of concurrent active threads. DB2 takes certain actions when the threshold is reached according to the value specified in the Attribute1 column. When the total number of threads being queued or suspended reaches the specified threshold, DB2 either issues a message if a WARNING value is specified, or it starts to fail the connection requests with SQLCODE -30041 if an EXCEPTION value is specified.

In the example of controlling the number of threads running for a given application, you would define the scope of the profile on the COLLECTION or PACKAGE name. This would cause the threads to queue and then execute when the next thread is available.

When EXCEPTION is specified, EXECUTION_DIAGLEVEL1 is used. If profile threshold is 0 and is exceeded, the thread is canceled. When the profile threshold is greater than 0, the action taken depends on the filtering scope of the profile as described below:


In the initial example where 30 threads are being allowed for the order entry system, you'd create a profile with these values inserted into SYSIBM.DSN_PROFILE_ATTRIBUTE table:


With this setting, the first 30 requests will run, and the next 30 requests will queue and wait for a thread to end. When the queued requests exceed 30, the next request will fail with a return SQLCODE of -30041.

These IBM Knowledge Center documents (here and here) have details about monitoring connections and threads using profiles. There are also examples to help you understand the interactions between the profile tables used to monitor system resources such as threads and connections.

Posted August 2, 2016| Permalink

comments powered by Disqus