Today’s Fortune 500 companies are embracing distributed techniques to access mainframe data on DB2. Although online transaction processing (OLTP) workloads still dominate DB2’s local processing, more applications, such as data warehousing analytics, run remotely as distributed tasks. The interesting part of distributed DB2 is the terminology; it has its own set. If you spend time dealing only with the local side, some of these terms may be new, maybe even confusing in some rare cases. Here’s a distributed terminology refresher using a couple of subsystem parameters that affect distributed data facility (DDF) processing: MAXDBAT and CONDBAT on the DSN6SYSP macro and CMTSTAT on the DSN6FAC macro.
The DDF allows distributed relational database architecture (DRDA)-supported applications to access DB2 for z/OS data. When a thread is created in support of this type of access, it’s referred to as a DBAT, or a database access thread.
DDF runs in its own address space (ssidDIST) that’s usually started along with the other three primary DB2 address spaces: database services (ssidDBM1), system services (ssidMSTR) and internal resource lock manager (ssidIRLM). The four address spaces combined are often referred to as a DB2 subsystem, or member, if data sharing is involved.
Note: Local access to DB2’s data is via an allied thread. Allied threads are usually associated with work originating with TSO, CICS, IMS, CAF, etc.
Distributed threads come in two flavors, active and inactive. A subsystem parameter, or what’s often referred to as DSNZPARM keyword, called CMTSTAT on the DSN6FAC macro, controls which distributed thread is in effect. CMTSTAT can also be set using the “DDF THREADS” field on the DSNTIPR installation panel. This subsystem parameter can be defined as either CMTSTAT=INACTIVE (the recommended choice and the default setting for DSNZPARM since DB2 Version 8) or CMTSTAT=ACTIVE (an option now highly frowned upon because of the amount of system resources that could be consumed). Once CMTSTAT is set, it can’t be changed without recycling the DB2 subsystem. This is not one of the DSNZPARM keywords that can be modified using the –SET SYSPARM command process.
Note: Back in DB2 V8, DB2 stopped using the terms type 1 and type 2 inactive threads. Since V8, “inactive DBAT” is used in place of a type 1 inactive thread and “inactive connection” is used rather than the term type 2 inactive thread.
With CMTSTAT=INACTIVE, DBATs are pooled and associated to connections as needed. CMTSTAT=INACTIVE is sometimes referred to as inactive thread processing or thread pooling. When CMTSTAT=INACTIVE is specified, up to a maximum of 150,000 concurrent inbound connections (a value set on CONDBAT keyword on the DSN6SYSP macro) can be defined to any one DB2 subsystem. This also allows incoming requests for DBATs that exceed the MAXDBAT maximum to be processed when a DBAT becomes available. With ACTIVE, work has to wait (queue) until a DBAT terminates. However, if CMTSTAT=ACTIVE is specified, every connection is a DBAT until it’s disconnected (its maximum is controlled with the MAXDBAT keyword) and there’s no pooling.
Note: All DBATs run as enclaves (pre-emptible service request blocks [SRBs]), making them System z Integrated Information Processor (zIIP)-eligible. The qualifying percentage of DRDA workloads eligible for zIIP processing increased to 60 percent with the introduction of APAR PM12256.
The DSNZPARM keywords MAXDBAT and CONDBAT, as previously mentioned, are used to manage two distributed thresholds in DB2. MAXDBAT defines the maximum number of concurrent remote DDF threads (DBATs) allowed in DB2’s DBM1 address space, also referred to as the database services address space or advanced database management facility (ADMF). Its default is 200 (up from the very old DB2 V7 default of 64) and its maximum possible value is 19,999 (an increase from the DB2 9 maximum of 1,999).
The value specified for MAXDBAT when combined with the value specified for CTHREAD (max users) must have a sum less than or equal to 20,000 (2,000 in DB2 9). When DB2 receives a connection request, how that request is satisfied is dependent on the setting of CMTSTAT (described previously). For CMTSTAT=INACTIVE, the request is satisfied by a DBAT from the pool. When the thread goes inactive (normally at a commit), the DBAT is returned to the pool for use by another connection. Note that setting MAXDBAT=0 is a good way to restrict distributed transactions to a particular data sharing member. Remember, each data sharing member has its own unique DSNZPARM member, or at a minimum, the ability exists to set up each member with its own unique DSNZPARM member. A unique ZPARM member isn’t a DB2 requirement. Note that none of these defaults and maximums change in DB2 11. Figure 1 provides a comparison of the min, max and defaults for the keywords discussed here and how they will change when moving between DB2 9, DB2 10 and DB2 11.