DB2 DDF and distributed parameters

How DDF parameters fit together

A remote connection is not the same thing as a Db2 thread. The client first establishes a network connection to a Db2 location. That connection counts toward CONDBAT. When it sends SQL, it needs a DBAT, which counts toward MAXDBAT. With commit-time pooling, a quiet connection can remain open without owning a DBAT. This separation lets thousands of mostly idle application sessions share a much smaller set of execution threads.

DSNZPARMs are subsystem-level policy. They should be selected from workload evidence, memory and CPU capacity, security standards, and recovery requirements. A larger number is not automatically safer. An oversized MAXDBAT can admit enough simultaneous SQL to overwhelm CPU, storage, locks, or buffer pools. An undersized value creates avoidable queues. The goal is controlled concurrency, with visible back pressure before the whole subsystem becomes unresponsive.

DDF startup and core capacity parameters

DDF=NO, AUTO, or COMMAND

The DDF setting controls the startup posture. AUTOstarts DDF as part of Db2 startup, making distributed access available without a separate operator action. COMMAND initializes the environment but waits for an explicit START DDF command before accepting distributed work. NO keeps DDF unavailable. COMMAND can provide a deliberate operational gate during a controlled startup, while AUTO normally suits systems where remote access is a required service. NO is appropriate only when the subsystem is intentionally not a distributed server or requester.

Startup policy does not replace port, location, TCP/IP, RACF, TLS, or communications database setup. DDF can be started yet still reject a connection because the client used the wrong location name, port, security mechanism, or credentials. Likewise, a healthy listener says nothing about whether MAXDBAT has enough room to execute new SQL.

MAXDBAT and CONDBAT

MAXDBAT is the ceiling for allocated DBATs, including DBATs that are active or available in the pool. A DBAT is the Db2 execution context for distributed work. CONDBAT is the ceiling for inbound DDF connections and must be at least MAXDBAT. If CONDBAT is reached, new connections are rejected. If MAXDBAT is reached, requests that already have connections can wait for a DBAT, subject to the queue controls described below.

Capacity planning must total every possible client pool. Ten application-server instances with a maximum pool of 150 can establish 1,500 connections before REST tools, administrators, reporting products, or failover traffic are counted. Do not assume the client will open only its average number. Set client pool maxima deliberately, reserve headroom, and compare DDF high-water marks with transaction response time.

CMTSTAT, POOLINAC, and IDTHTOIN

CMTSTAT=INACTIVE allows an eligible DBAT to be disassociated from its client at commit and returned to the DBAT pool. The TCP connection remains, counts toward CONDBAT, and acquires a DBAT again when the next transaction begins. CMTSTAT=ACTIVE keeps the DBAT associated until disconnect. ACTIVE is therefore much less scalable and should have a documented compatibility reason.

Pooling is not guaranteed merely because INACTIVE is selected. Resources that must stay associated with the thread—such as certain held cursors, held locators, temporary objects, or package behaviors—can keep the DBAT tied to the connection. POOLINAC controls how long an unused pooled DBAT remains eligible for reuse before Db2 can terminate it. IDTHTOIN addresses an in-use remote thread that is idle, such as a client that stopped responding while still holding its context. It is not the pooled-DBAT retirement timer.

MAXCONQN and MAXCONQW

When all DBATs are occupied, unlimited waiting can turn one slow period into a huge backlog. MAXCONQN limits the number of connection requests queued for a DBAT. MAXCONQW limits the waiting time. Depending on the Db2 release, numeric and ON/OFF forms relate those limits to MAXDBAT, CONDBAT, or IDTHTOIN. Check the exact rules for the installed function level before changing them.

These controls are especially useful in data sharing. Closing requests that exceed a member's queue threshold gives a properly configured client an opportunity to redirect work to a healthier member. They do not cure slow SQL; they contain the blast radius while monitoring reveals whether the cause is a workload surge, lock wait, CPU shortage, or DBAT held too long.

TCP/IP and DDF compatibility parameters

TCPALVER and credential trust

TCPALVER governs whether Db2 accepts certain TCP/IP requests where the client claims that identity verification already occurred. That is a security boundary, not a performance switch. Modern configurations should protect credentials in transit and require a mechanism approved by the site, such as encrypted credentials, TLS, PassTickets, or Kerberos as applicable. Changing TCPALVER can break older clients that depend on a weaker assumption, so inventory connection mechanisms and test them before enforcing a stronger policy.

TCPKPALV and dead connection detection

TCPKPALV controls whether Db2 inherits the TCP/IP stack keepalive value, overrides it with a subsystem-specific interval, or disables probing. Keepalive packets help discover a peer that disappeared without closing its socket—for example, after a network interruption or machine failure. A very short interval creates needless network traffic, while a very long interval can leave dead sessions consuming connection slots. Coordinate this value with firewall idle limits, load balancers, driver validation, and application pool timeouts. TCP keepalive is not an SQL timeout and does not replace transaction timeout design.

DDF_COMPATIBILITY

DDF_COMPATIBILITY is a compatibility control for selected distributed behaviors introduced at particular Db2 levels. Its accepted values and effects are version-specific. Treat it as a migration switch: document why a compatibility option is enabled, test drivers and remote partners, and retire obsolete behavior when IBM's migration guidance permits. Never copy a value from another subsystem without checking its Db2 release and activated function level.

Distributed requester and server parameters

Db2 for z/OS can play both DRDA roles. As a server, it executes SQL for remote clients. As a requester, it sends work to another database location. Location definitions, aliases, communications database rows, security, and network routes determine which partner is reached. The DSNZPARMs below refine protocol flow and recovery; they do not create a missing location definition.

RESYNC for indoubt work

A distributed unit of work can involve more than one participant under two-phase commit. If communication fails after prepare but before the final outcome is confirmed, a participant can retain an indoubt unit of recovery. RESYNC controls periodic resynchronization activity used to resolve that work with the partner. It is recovery plumbing, not a normal JDBC retry interval.

Operators should monitor indoubt work and partner availability rather than simply shortening RESYNC. An unavailable coordinator, incorrect location information, or security failure cannot be repaired by polling more aggressively. Recovery procedures must preserve commit consistency; manually forcing outcomes requires careful knowledge of the coordinator's decision.

EXTRAREQ and EXTRASRV

DRDA query data moves in blocks. EXTRAREQ caps the extra query blocks used when this Db2 acts as a requester, while EXTRASRV applies when it acts as a server. Allowing extra blocks can return more rows in one network exchange, reducing round trips on higher-latency links. It can also increase data buffered or sent when the application consumes only a small portion of the result.

These parameters do not cap the total result-set size. They shape the amount transmitted in an exchange. Evaluate network latency, row size, fetch behavior, and driver settings together. SQL that returns millions of unnecessary rows should be corrected with better predicates and fetching patterns before protocol blocks are tuned.

REST parameters and capacity awareness

Native Db2 REST services are delivered through DDF. An HTTP request is mapped to a Db2 service, authenticated, and executed using distributed infrastructure. There is no magic REST-only execution engine: REST calls consume connection and DBAT capacity, participate in Db2 authorization and accounting, and can queue behind other DDF work. Include expected API concurrency in MAXDBAT, CONDBAT, queue, WLM, and timeout planning.

At a high level, BIND SERVICE creates or deploys the package that represents a REST service and its SQL. The service name, version, collection, owner, and package options become part of the operational contract. Package privileges and the service's execution authorization matter just as they do for other static SQL. Administrators also need the DDF REST support configured and an appropriate secure HTTP path available.

1
2
3
4
5
6
7
8
-- Conceptual only: options depend on service design and Db2 level
BIND SERVICE(MYCOLL.CUSTOMERLOOKUP)
     SQLDDNAME(INPUT)
     OWNER(APIADM)
     QUALIFIER(APPDATA)
 
-- Capacity path
HTTP request -> DDF -> authenticated connection -> DBAT -> service package -> SQL

REST observability should join the layers. HTTP status and response time reveal the client view; DDF statistics reveal connection, queue, and DBAT pressure; package and SQL accounting reveal database cost. Raising MAXDBAT because an API is slow can make matters worse if the real cause is an inefficient statement or lock contention.

ODBC and JDBC parameter interactions

JDBC type 4 over DDF

The IBM Data Server Driver for JDBC and SQLJ type 4 connection uses Java networking and DRDA directly to DDF. The URL identifies a host, port, and database or location. Driver properties select security, package collection, current schema, accounting information, timeout behavior, and other session choices. Each physical pooled connection counts toward CONDBAT even while idle; each transaction needs a DBAT while executing.

1
2
3
4
5
6
7
8
9
10
11
12
Conceptual JDBC type 4 settings
--------------------------------
jdbc:db2://db2.example.com:446/DB2P
currentPackageSet=APPV2
currentSchema=APPDATA
clientProgramName=OrderAPI
clientApplicationInformation=production
 
Important:
- Property names and support vary by driver level.
- CURRENT PACKAGESET must name an available, authorized collection.
- Client timeout, pool timeout, IDTHTOIN, and MAXCONQW solve different problems.

ODBC, CLI, DESCSTAT, and special registers

ODBC and CLI data sources expose similar choices through configuration keywords and connection attributes. A tool may need metadata for prepared or static statements, package collection selection, Unicode handling, or session identification. DESCSTAT controls Db2 support for describing static SQL metadata. It can help tools that need statement descriptions, but it should be enabled because a tested requirement exists—not as a generic performance setting.

Client properties can initialize special registers. For example, CURRENT PACKAGESET influences the collection searched for packages, while CURRENT SCHEMA influences qualification of unqualified object names. Other client information can improve accounting attribution. A property request does not override server authority: the collection and packages must exist, the ID must be authorized, and profiles or server rules can still constrain behavior. Keep driver versions aligned with the Db2 function level and test property spelling against official driver documentation.

Monitoring and safe tuning workflow

1
2
3
4
5
6
7
8
9
10
11
-DISPLAY DDF DETAIL
-DISPLAY THREAD (*) DETAIL
-DISPLAY THREAD (*) TYPE(INACTIVE)
 
Review together:
  active and pooled DBATs
  inactive and total connections
  DBAT queue depth and queue time
  timeouts and rejected connections
  REST/JDBC/ODBC accounting identifiers
  CPU, locks, response time, and SQL performance

DISPLAY DDF DETAIL is the first operational picture, but a single snapshot is not a capacity study. Capture statistics across normal peaks, batch overlap, deployments, and failover tests. Distinguish a connection high-water mark from an active DBAT high-water mark. Persistent DBAT queues with low CPU may point to held threads or slow SQL; persistent queues with saturated CPU may mean concurrency is already too high.

• Establish a baseline for connections, DBATs, queues, timeouts, and response time.
• Change one related policy set at a time, including matching application pool limits.
• Test startup, authentication, TLS, planned failover, dead-peer cleanup, and resync.
• Confirm whether the parameter is dynamic or requires a recycle for the installed Db2 level.
• Record the reason, expected result, rollback value, and measurements that prove the change worked.

Explain It Like I'm Five

Imagine a restaurant. DDF decides whether the front door opens by itself, waits for the manager, or stays closed. CONDBAT is the number of customers allowed to wait inside. MAXDBAT is the number of cooks. With CMTSTAT=INACTIVE, a cook can prepare one order, finish it, and help another customer while the first customer studies the dessert menu. POOLINAC sends extra cooks home after the rush. IDTHTOIN deals with a customer who has a cook assigned but never answers. MAXCONQN limits the waiting line, and MAXCONQW stops anyone waiting forever. REST, JDBC, and ODBC are different ways of placing an order, but all of them still use the same front door and cooks.

Exercises

1. Draw a diagram showing 1,000 JDBC connections sharing 200 DBATs. Label which number is controlled by CONDBAT and which is controlled by MAXDBAT.
2. Explain what changes at commit under CMTSTAT=ACTIVE versus CMTSTAT=INACTIVE, and list two reasons a DBAT might remain associated.
3. A service has eight application instances, each with a 100-connection pool. Add a reporting pool of 250 and propose a CONDBAT planning conversation with suitable headroom.
4. Compare POOLINAC, IDTHTOIN, MAXCONQW, and TCPKPALV. State which idle or waiting condition each setting addresses.
5. Describe how RESYNC differs from an application retry after an HTTP or JDBC timeout.
6. Create a monitoring checklist for a new REST service that includes HTTP, DDF, DBAT, package, SQL, and security evidence.