Does your Db2 system hit its specified MAXDBAT limit regularly? If it does, you might need to evaluate whether your MAXDBAT and CONDBAT settings are configured to allow your Db2 subsystem to run with optimal performance.

The MAXDBAT subsystem parameter controls the maximum number of database access threads (DBATs) that are allowed to be concurrently active in a Db2 system. In combination with the CTHREAD subsystem parameter setting, MAXDBAT controls total number of threads allowed to access data concurrently in the Db2 system. The maximum supported value for this sum is 20000.

The major constraint of MAXDBAT is limited by the amount of ECSA allocated to your z/OS system. Let’s examine a few ways to determine how many concurrent database access threads (DBATs) can be running in your system. We'll also briefly review the steps you can use to validate your CONDBAT setting.

Validating MAXDBAT

In Db2 13, the storage footprint for a DBAT is reduced to the same amount of ECSA storage for processing a local thread, which is 4KB. This change applies to remote TCP/IP connections accessing Db2. To find the ECSA usage in your Db2 system, you can use the IFCID 225 statistics trace, SHARED and COMMON storage summary data.

Calculating average thread footprint

The average amount of ECSA storage a Db2 thread consumes is calculated as follows:

average thread footprint = QW0225_ECSA_ALLOC ÷ (QW0225DB + QW0225AT)

QW0225_ECSA_ALLOC = total ECSA currently GETMAINed

QW0225DB = current number of active and pooled DBATs

QW0225AT = current number of active allied (local) threads

Calculating maximum number of possible threads

Once you know the average thread footprint, you can calculate the maximum number of possible threads (DBATs and local) that your system can support. To calculate the maximum number of possible threads, do the following:

QW0225EC = ECSA size

Now that you know the maximum number of possible threads, you subtract the CTHREAD value from the maximum number of possible threads, and the result is the optimum value to set for MAXDBAT in your subsystem.

Here are some other suggested questions that you can consider when validating your MAXDBAT setting.

• Do your applications enable Sysplex Workload Balancing?
• Do your applications release database resources (hold cursors, DGTT, etc.) when they are no longer needed?
• Do your applications have connection limits or circuit breakers?
• Do your application transactions meet their WLM performance goals?
• During peak periods, what does the thread and connection activity look like?

Validating CONDBAT

The CONDBAT subsystem parameter specifies the maximum number of concurrent connections that your Db2 subsystem can support. The maximum value for CONDBAT is 150,000 which is achievable when CMTSTAT=INACTIVE. However, to determine a more suitable CONDBAT value for your Db2 subsystem, you need to know the maximum possible number of remote client applications that accesses the Db2 server. This number includes direct connections from workstations, middleware servers, web servers, and so on.

For example, consider 500 application connections that access a six-way Db2 data sharing group. CONDBAT should be set to a minimum of 3000 (500 per Db2 member) to support the 500 application connections.

To determine the number of connections accessing your Db2 subsystem, you can use the following statistics trace records:

• IFCID 365 – collects statistics by IP addresses
• IFCID 411 – collects statistics by client application name
• IFCID 412 – collects statistics by client user ID

Alternatively, you can also issue -DISPLAY LOCATION DETAIL command periodically to determine the number of remote connections by IP address.

MAXCONQN and MAXCONQW

If your Db2 system reaches the MAXDBAT limit, connection requests that arrive during this period are queued by Db2. The MAXCONQN subsystem parameter controls the number of connection requests that can be queued, and the MAXCONQW subsystem parameter  controls amount of time allowed for each connection to wait in the queue. If the MAXCONQN limit is reached subsequent connections are terminated by Db2. If a connection is queued but it exceeds the MAXCONQW limit the connection is timed out.

 #Db2Znews #Db2zos