CONDBAT, on the other hand, defines the maximum number of remote connections that DDF will allow. The CONDBAT default is 10,000 (also up from the DB2 V7 default of 64) and its maximum possible value is 150,000. CONDBAT needs to be greater than or equal to MAXDBAT. If MAXDBAT is set to 0 (zero), then CONDBAT will be treated as 0 (zero). If the CONDBAT value is reached or is set to 0 (zero), connection requests are rejected. CONDBAT should be set to a value so connection requests aren’t rejected. Maintaining many connections isn’t that expensive.
Any inbound distributed access to DB2 for z/OS requires a DDF connection and a DB2 DBAT. The combination of CMTSTAT=INACTIVE, MAXDBAT > 0 and CONDBAT > MAXDBAT allow many connections to share fewer DBATs. Commit allows the DBAT to be returned to the pool. However, thread creation is an expressive process and a DBAT consumes approximately 200KB in the DBM1 address space as compared to a connection that uses only approximately 7.5KB of storage in the DDF address space. Reusing threads (DBATs) avoids the continuous creation and takedown of threads (DBATs), saving valuable DB2 CPU and storage resources and potentially improving performance.
If you’re running CMTSTAT=ACTIVE, then CONDBAT is ignored and MAXDBAT is the only value used to control both concurrent connections as well as concurrent active DBATs. In addition, regardless of CMTSTAT setting, cursors defined with WITH HOLD and packages bound with KEEPDYNAMIC (YES) won’t create an inactive connection or inactive DBAT.
Note: Both MAXDBAT and CONDBAT can be changed online using the –SET SYSPARM after an assembly and link of the subsystem parameter’s module (usually DSNZPARM). However, modifying CMTSTAT still requires the DB2 subsystem to be recycled to activate a changed setting.
Subsystem Parameter IDTHTOIN
IDTHTOIN, also on the DSN6FAC macro or the IDLE THREAD TIMEOUT field on the installation panels, controls how long an idle active thread will hang around before it’s canceled. IDTHTOIN is often misinterpreted. Occasionally, this DSNZPARM keyword will be associated with threads defined with CMTSTAT ACTIVE. This is inaccurate. All threads are active when they’re performing work whether CMTSTAT is set to ACTIVE or INACTIVE. IDTHTOIN won’t cancel an inactive thread. So now KEEPDYNAMIC YES forces a thread to remain active, and depending on the setting of IDTHTOIN, that thread may or may not be canceled.
Prior to DB2 V8, IDTHTOIN defaulted to 0 (zero). Setting IDTHTOIN to 0 (zero) tells DB2 to never cancel a thread no matter how long it hangs around idle. This setting is strongly suggested for an SAP implementation. However, in most environments, this keyword should usually be set to a value greater than 0 (zero). In DB2 V8, the default was changed to 120 seconds and remains at 120 seconds as the default in DB2 11. Not only does 120 seconds work well as the DB2 default, it’s also the lowest value that should be specified. DB2 checks to see if a thread is idle on average about every two minutes or 120 seconds. Therefore, it makes little sense to set this keyword to anything less than 120 seconds. In fact, it isn’t unusual to see IDTHTOIN set to values as high as 300 to 600 seconds, the range often suggested when setting up this keyword.
Note: IDTHTOIN can also be changed online using the –SET SYSPARM after an assembly and link of the subsystem parameter’s module.
Connection Management Improvements
Three connection management areas of concern are addressed in DB2 10 by APAR PM43293. First, DB2 needed a better way to manage and observe connection behavior over and above what’s currently provided using the DSNZPARM keywords MAXDBAT and CONDBAT. Next, the behavior of KEEPDYNAMIC (YES) must be adjusted for threads that could end up running for a longer period of time. Finally, workload manager (WLM) needed to be aware of how DBAT processing was proceeding, including additional messaging to avoid any “surprises.”
All three concerns were addressed in December 2012 by APAR PM43293, including the addition of two new DSNZPARM keywords on macro DSN6FAC: MAXCONQN and MAXCONQW. The challenge is placing controls on a connection waiting and queuing for a DBAT to address these concerns. Prior to applying the aforementioned APAR, these concerns couldn’t be addressed.
Why might it be important to have some control over queuing? It’s possible that if DBATs aren’t available to satisfy requests, DB2 could reach its CONDBAT (maximum number of remote connections) or MAXDBAT (maximum number of DBATs) limits, causing new connection requests to be rejected (00D31034). If MAXDBAT is reached before CONDBAT, the TCP/IP socket could be marked for clean up. Unfortunately, that requires a DBAT. With that, hopefully, the potential problem starts to become clearer.
The two subsystem parameters introduced at the end of 2012, MAXCONQN and MAXCONQW, were released to prevent a situation like this from occurring. The keyword MAXCONQN controls the number of connections that can be waiting on a DBAT to come available, whereas the DSNZPARM keyword MAXCONQW specifies the amount of time a connection can wait for a DBAT.
MAXCONQN and MAXCONQW can be set to ON, OFF or to a specific value; the default for both MAXCONQN and MAXCONQW is OFF, thus disabling the additional checking. When a value is used, MAXCONQN can be set to the maximum connections and MAXCONQW can be set to the maximum duration in seconds. If not specified or if set to their default, how queued connections are handled is unchanged from prior to applying APAR PM43293. These subsystem parameters only have an effect when defined to a member of a data sharing group.
When a connection is closed because one of the aforementioned subsystem parameters is exceeded, message DSNL030I is issued to the console with either reason code 00D31053 when MAXCONQN has been exceeded or 00D31054 when MAXCONQW has been exceeded. These messages are issued at no more than a five-minute interval to avoid flooding the z/OS console with an excessive number of messages.
Both MAXCONQN and MAXCONQW can be updated online using the assemble, link and –SET SYSPARM command process. The aforementioned keywords also won’t work for a DB2 subsystem that still uses the DSNZPARM setting CMTSTAT = ACTIVE.
The behavior of all the keywords discussed so far doesn’t have to be a mystery. There are invaluable details available about these keywords from a source that’s easy to access: the
–DISPLAY DDF DETAIL command (see Figure 2).
The highlighted lines in the output are for messages DSNL090I and DSNL091I. DSNL090I describes the current settings in the DSNZPARM member for CONDBAT and MAXDBAT. Message DSNL091I has the current settings for MAXCONQN, labeled MCONQN, and MAXCONQW, labeled MCONQW.