The output of the Db2 for z/OS command -DISPLAY DDF DETAIL provides a lot of very useful information in a compact form. That said, I've found that quite a number of Db2 people are not so clear on the meaning of various fields in -DISPLAY DDF DETAIL output. In this blog entry I will aim to shed light on key information items provided via -DISPLAY DDF DETAIL, and explain how that information can be put to use.
To begin with, a sample of -DISPLAY DDF DETAIL output is shown below (I have anonymized identifiers such as IP addresses and subsystem names). The fields highlighted in red are those that I have found to be the most useful - they will be subsequently explained.
DSNL080I -DBP2 DSNLTDDF DISPLAY DDF REPORT FOLLOWS:
DSNL081I STATUS=STARTD
DSNL082I LOCATION LUNAME GENERICLU
DSNL083I DBP2 ABCD1234.DBP2 ABCD1234.DBP0GLU
DSNL084I TCPPORT=4100 SECPORT=0 RESPORT=4102 IPNAME=-NONE
DSNL085I IPADDR=::1.2.3.4
DSNL086I SQL DOMAIN=b4vipa.acme.com
DSNL086I RESYNC DOMAIN=b4vipa.acme.com
DSNL089I MEMBER IPADDR=::1.2.3.4
DSNL090I DT=I CONDBAT= 5000 MDBAT= 550
DSNL091I MCONQN= 0 MCONQW= 0
DSNL092I ADBAT= 67 QUEDBAT= 0 INADBAT= 0 CONQUED= 0
DSNL093I DSCDBAT= 60 INACONN= 1786 IUDBAT= 7
DSNL094I WLMHEALTH=100 CLSDCONQN= 0 CLSDCONQW= 0
DSNL100I LOCATION SERVER LIST:
DSNL101I WT IPADDR IPADDR
DSNL102I 32 ::1.2.3.4
DSNL102I 32 ::1.2.3.4
DSNL105I CURRENT DDF OPTIONS ARE:
DSNL106I PKGREL = COMMIT
DSNL106I SESSIDLE = 001440
DSNL099I DSNLTDDF DISPLAY DDF REPORT COMPLETE
OK, starting from the top and working down through the sample command output, following are my explanations of the fields highlighted in red:
- TCPPORT, SECPORT and RESPORT - The first of these fields is the Db2 subsystem's "standard" SQL listener port. A client application (a DRDA requester or a REST client) that will interact with Db2 over a TCP/IP link will specify this port in a connection request (or in a REST request) if there is not a need for SSL encryption for the connection (i.e., encryption of data "on the wire"). If SSL encryption is required, the client specifies the Db2 subsystem's secure port. In the command output shown above, the value of 0 for SECPORT indicates that a secure SQL port has not been defined for the subsystem. That doesn't mean that SSL encryption cannot be used for a connection to the subsystem - that can still happen even when the standard SQL listener port is specified for the connection request. The difference is this: when the secure SQL listener port is specified at connection time then Db2 requires the use of SSL encryption, and rejects the connection request when SSL encryption is not used. Note that with the fix for APAR PH08188 applied (it came out in April of 2019), a Db2 subsystem's one and only SQL listener port can be its secure port - if that is made so then any application that would access the Db2 subsystem via DDF (whether a DRDA requester or a REST client) cannot do so unless SSL encryption is used for the application's connection(s) to the subsystem.
RESPORT is short for re-sync port. What is the purpose of this port? Well, in a DDF two-phase commit situation (i.e., when an application that is DDF-connected to a Db2 subsystem changes data at that location and at another Db2 location in the same unit of work), a failure (application-side or data server-side) could leave one or more associated DDF transactions in an in-doubt state. For those transactions to be resolved the application needs to tell the Db2 subsystem to either commit or abort the transactions, and when the application communicates to the Db2 subsystem the action it is to take for these transactions (commit or abort), it uses the subsystem's re-sync port. This is particularly important in a Db2 data sharing environment, in which all member subsystems have the same SQL listener port (and the same secure port, if applicable). When a DDF transaction is in an in-doubt state in that kind of environment, the associated application needs to communicate the commit or abort action to the particular member subsystem that was servicing the transaction - the member's unique re-sync port makes that possible.
- CONDBAT and MDBAT - These refer to two parameters, CONDBAT and MAXDBAT, in the Db2 subsystem's ZPARM module (think of the ZPARMs as Db2 for z/OS configuration parameters). CONDBAT specifies the maximum number of connections that DDF-using applications can have with the Db2 subsystem. MAXDBAT is the maximum number of those connections that can be active at one time. It is typical for a subsystem's CONDBAT value to be much higher than its MAXDBAT value. Why is this so? Because at any given time, it's likely that most connections to the Db2 subsystem are not active, and an inactive connection doesn't need a DBAT (DBAT, by the way, is short for database access thread - that's what we call a DDF thread). Having a bunch of inactive connections is no big deal - the virtual storage "footprint" of a Db2 inactive connection is very small, and the CPU cost of switching an inactive connection to active (when a new transaction associated with the connection comes along) and back again (when said transaction has completed) is very small, as well. Generally speaking, you want the CONDBAT value to be high enough to avoid reaching the connection limit, because once that limit has been reached then the next attempt by an application to connect to the subsystem via DDF will be rejected by Db2. A statistics long report generated by your Db2 monitor (depending on the monitor product, that may be called a statistics detail report) will show you if the CONDBAT limit has been reached for a subsystem.
The MAXDBAT value, as stated previously, is the limit on the number of DDF connections that can be concurrently active for the subsystem. How so? Because by definition an active connection is one that is paired with a DBAT (a transaction that comes in by way of a connection needs a DBAT in order to be processed). It's long been thought that you want the MAXDBAT value for a subsystem to be large enough so as not to be reached, and that's still the case for most Db2 systems that I've seen, but there could be a situation in which you WANT the MAXDBAT value to be reached. Suppose, for example, that there are occasional huge surges of transactions that come in from DDF-using applications. It's possible that letting all of those transactions into the system at once would overwhelm the z/OS LPAR's processing capacity, leading to big-time performance problems. In that case, a MAXDBAT value that would be hit would cause some DDF transactions to queue up waiting for a DBAT to come free, but by keeping the system's processing capacity from being overwhelmed, the MAXDBAT limit would preserve a high level of transaction throughput, so a DDF transaction waiting for a DBAT to come free shouldn't have to wait long.
Another MAXDBAT-related matter to consider: high-performance DBATs. A high-performance DBAT, once instantiated, stays dedicated to the instantiating connection until it has been used to process 200 transactions. That means that a larger number of high-performance DBATs leads to fewer DBATs in the DBAT pool, all other things being equal, and THAT means you should almost certainly increase a Db2 subsystem's MAXDBAT value prior to implementing high-performance DBAT functionality.
- ADBAT - ADBAT is short for "active DBATs," and it indicates the total number of DBATs that exist at the time of the issuance of the -DISPLAY DDF DETAIL command. It indicates the total number of DBATs because ALL DBATs - whether in the DBAT pool or currently paired with connections - are "active."
- QUEDBAT - This field shows the number of times that the subsystem's MAXDBAT limit has been reached since DDF was last started (which was probably when the Db2 subsystem was last started). See the information about MAXDBAT, above, to get an idea as to how one should interpret a non-zero value in this field.
- DSCDBAT - This is short for "disconnected DBATs," and it indicates the number of DBATs in the pool. DBATs in the DBAT pool are active, as previously noted, but they are in a disconnected state - a DBAT in the pool is there because it is not tied to a particular connection. When a connection needs a DBAT (for the servicing of a transaction associated with the connection), it gets one from the DBAT pool.
- INACONN - This is short for "inactive connections." As previously mentioned, it is very common to see a large number of DDF connections in the inactive state - it just means that there are no currently in-flight transactions associated with the connections.
- IUDBAT - This is short for "in-use" DBATs. It's a relatively new field in the output of -DISPLAY DDF DETAIL, added via the fix for APAR PH30222, which came out about a year ago. In-use DBATs are those that are currently being used to process transactions. A DBAT is also considered to be in-use when it is a high-performance DBAT.
- PKGREL - This is a DDF configuration setting, and it can be thought of as the high-performance DBAT "on/off switch." When PKGREL is set to COMMIT, as seen in the example command output shown above, high-performance DBAT functionality is "off" for the Db2 subsystem. When PKGREL is set to BNDOPT, high-performance DBAT functionality is "on," in the sense that it is enabled, but for a DBAT to become the high-performance type it has to be used to execute at least one package that has been bound with RELEASE(DEALLOCATE). There is a third PKGREL specification, BNDPOOL, that in my experience is not commonly used - it can be a reasonable choice when an application that uses high-performance DBATs does not stay connected for very long to the Db2 subsystem (typically, a DDF-using application will connect to a Db2 for z/OS server and stay connected to that Db2 system for a long time).
The PKGREL setting can be changed using the Db2 command -MODIFY DDF.
And that's that. I hope that this explanation of -DISPLAY DDF DETAIL output will be useful for you.
