MODIFY commands in DB2 for z/OS

What MODIFY means in Db2

Db2 operator commands use the subsystem recognition character (often -DISPLAY, -STOP, -MODIFY). You issue them from a z/OS console, SDSF, a DSN session, DB2I COMMANDS, IMS/CICS, or IFI. They are not SQL. They do not run under an application plan.

Authorization for -MODIFY TRACE is the TRACE privilege, SYSOPR, SYSCTRL, or SYSADM. -MODIFY DDF requires a privilege set that can operate DDF (typically SYSOPR, SYSCTRL, or SYSADM). Commands from a logged-on console are checked with primary and secondary authorization IDs.

-MODIFY DDF

-MODIFY DDF changes the status and configuration of the Distributed Data Facility while DDF is up. The two jobs beginners meet first are controlling high-performance DBATs with PKGREL, and managing dynamic location aliases with ALIAS. Confirm current values with -DISPLAY DDF DETAIL (message DSNL106I shows PKGREL).

PKGREL

A DBAT (database access thread) that runs packages bound RELEASE(DEALLOCATE) can stay allocated across commits—a high-performance DBAT. That is great for CPU on a chatty DRDA application and painful when you need to BIND REPLACE, idle-thread break-in, or drain the package. PKGREL is the server-wide switch DDF consults.

1
2
3
-DISPLAY DDF DETAIL
-MODIFY DDF PKGREL(COMMIT)
-MODIFY DDF PKGREL(BNDOPT)

IBM documents that if MODIFY DDF PKGREL(COMMIT) is in effect at the server, RELEASE(DEALLOCATE) has no effect on packages executed through a client DRDA connection. Use COMMIT when you need BIND/REBIND or utilities to break in; switch back to BNDOPT when the change window ends. BNDPOOL is the less common choice when the client does not stay connected for a long time.

ALIAS — dynamic location aliases

A location alias is an extra LOCATION name and optional TCP/IP port that clients can use to reach a member or a subset of a data sharing group. Two families exist:

• STATIC — defined in the BSDS with DSNJU003 (DDF ALIAS=...). DDF treats them as static. You cannot ADD/START/STOP them with MODIFY DDF
• Dynamic — defined with -MODIFY DDF ALIAS(...). DISPLAY DDF shows STARTD, STOPD, CANCLD, and similar alias states

Typical dynamic-alias verbs:

• ADD — define the alias; specify PORT and optionally a secure port and IP
• START — listen for new connections on the alias port
• STOP — stop accepting new alias-port connections (group SQL port may still accept the alias name depending on status)
• CANCEL — cancel existing DBATs that were using the alias, then stop new alias-port connections
• DELETE / DROP (the documented operand on your function level) — remove a dynamic alias that is stopped

1
2
3
4
5
-MODIFY DDF ALIAS(PAYROLL) ADD PORT(4461)
-MODIFY DDF ALIAS(PAYROLL) START
-DISPLAY DDF DETAIL
-MODIFY DDF ALIAS(PAYROLL) STOP
-MODIFY DDF ALIAS(PAYROLL) CANCEL

MAXDBAT must be greater than 0 for an alias to start usefully. Starting an alias registers it with z/OS WLM in data sharing so Sysplex Distributor can route to members that belong to that subset. Wrong PORT values collide with the main SQL port or with another alias—DISPLAY DDF before you ADD.

Other DDF knobs you will see on DISPLAY

DISPLAY DDF DETAIL also reports session idle time (SESSIDLE), CONDBAT/MDBAT, and SQL/secure ports. Later function levels add more MODIFY DDF operands (for example session-idle overrides). Always match the Command Reference for your function level; do not copy a blog example that names an operand your member does not support.

-MODIFY DDF is not -START DDF or -STOP DDF. START/STOP bring the DIST address space listeners up or down. MODIFY changes options of an already-started DDF.

-MODIFY TRACE

Db2 tracing is START TRACE, DISPLAY TRACE, STOP TRACE, and -MODIFY TRACE. START creates a trace with a type (PERFM, ACCTG, STAT, AUDIT, MONITOR, GLOBAL), CLASS list, optional IFCID list, DEST (SMF, GTF, OPn, OPX, SRV), and filters (PLAN, AUTHID, LOCATION, and the rest). Each active trace gets a trace number (TNO) from 1 to 32.

-MODIFY TRACE changes the IFCIDs (trace events) associated with one of those active traces. You point at the trace with TNO. You typically specify the trace type, CLASS, and/or IFCID list you want now. COMMENT can document why the trace was changed.

1
2
3
-DISPLAY TRACE(*)
-MODIFY TRACE(PERFM) TNO(4) CLASS(3) IFCID(22,23)
-DISPLAY TRACE(PERFM) TNO(4)

• TNO — required in practice; DISPLAY TRACE prints it in the TNO column
• CLASS — trace classes for that type. Performance classes 30–32 are empty buckets for IFCID-only traces
• IFCID — extra or replacement event numbers (maximum per trace is documented, historically 156)
• DEST — set at START time; to move from SMF to GTF, STOP TRACE and START TRACE with the new destination

Performance traces are expensive. Modify toward a smaller IFCID list when you have diagnosed the problem rather than leaving CLASS(1,2,3) running. GLOBAL traces are almost never what you want on a production member. Audit traces that use AUDTPLCY follow audit-policy rules; you cannot mix CLASS/IFCID with AUDTPLCY on START, and MODIFY must stay consistent with how the trace was started.

MODIFY irlmproc (not a Db2 -MODIFY)

Operators also type MODIFY irlmproc,STATUS and related IRLM commands (SET, ABDUMP, DIAG). Those go to the IRLM started task, not to Db2’s command processor. They do not take the Db2 subsystem prefix in the same way, and they do not change DDF or IFCIDs. If lock timeout diagnosis is the goal, use IRLM STATUS and Db2 DISPLAY THREAD / DISPLAY DATABASE LOCKS together—not -MODIFY DDF.

Explain It Like I'm Five

-MODIFY DDF is adjusting the front door of the library while the library stays open: you can tell the door to forget “keep this book checked out until you leave” (PKGREL COMMIT), or you can hang an extra doorbell with its own number (an alias port). -MODIFY TRACE is telling one security camera (TNO 4) to watch different rooms without buying a new camera. MODIFY irlmproc is talking to the lock-box in the basement, which is a different building.

Exercises

1. DISPLAY DDF DETAIL and record PKGREL. Write the MODIFY DDF command to force COMMIT for a bind window, then the command to restore BNDOPT.
2. Explain why a BIND REPLACE of a RELEASE(DEALLOCATE) package can wait while PKGREL is BNDOPT and DDF threads stay allocated.
3. DISPLAY TRACE(*) and identify one TNO. Write MODIFY TRACE to add IFCID 22 on that trace without changing DEST.
4. A colleague wants to MODIFY TRACE to send an SMF trace to GTF. What should they do instead?
5. Distinguish a STATIC DSNJU003 alias from a dynamic MODIFY DDF alias using DISPLAY DDF status text.