DB2 DISPLAY commands

How DISPLAY works

Issue DISPLAY with your subsystem prefix from SDSF, a z/OS console, the DB2I Commands panel, a DSN session, IMS, CICS, or IFI. Abbreviation is usually -DIS. Authorization is typically the DISPLAY privilege or SYSOPR / SYSCTRL / SYSADM / system DBADM. Output is a set of DSN* messages ending with DSN9022I NORMAL COMPLETION. By the time you read a THREAD or PROCEDURE display, the status may already have changed—treat it as a snapshot, not a lock.

Wildcards follow the same patterns as other space-name commands: prefix*, *suffix, *contains*, and ranges name1:name2 in Unicode collation order. Prefer a specific database name over (*) in production.

-DISPLAY DATABASE

This is the workhorse. It shows whether databases and table/index spaces are RW, RO, UT, STOP, STOPP, RESTP, or in restrictive/advisory states. Options change the story:

• SPACENAM (SPACE, SP) — limit to named spaces; required for many USE/LOCKS/CLAIMERS reports
• PART — partition list or ranges (1,2,4:6)
• RESTRICT — only restricted objects (the -904 hunt)
• ADVISORY (ADV) — advisory states such as AREO* / AREOR / ARBDP that recommend REORG but still allow RW
• USE — who has the space open
• LOCKS — lock holders
• CLAIMERS — claimers (drain/claim model for utilities)
• LPL — logical page list entries (pages that need recovery from GBP or disk errors)
• WEPR — write-error page range
• OVERVIEW — space names and types only (not combined with most other keywords)
• LIMIT / AFTER — page the report (default LIMIT 50)

1
2
3
4
-DISPLAY DATABASE(PAYROLL) SPACENAM(*) LIMIT(*)
-DISPLAY DATABASE(PAYROLL) SPACENAM(TSACCT) LOCKS
-DISPLAY DATABASE(*) SPACENAM(*) RESTRICT LIMIT(*)
-DISPLAY DATABASE(DSNDB01) SPACENAM(*) LPL

Status RW means applications can read and write. RO is read-only. UT is utility-only. STOP means -STOP DATABASE completed; SQL cannot use the space until -START DATABASE. STOPP means stop pending (a STOP is in progress, often waiting for drain). RESTP is restart pending. RECP means recovery pending—usually COPY, RECOVER, or REBUILD INDEX is required. CHKP is check pending after a referential or check-constraint violation (CHECK DATA). COPY is copy pending, often after a LOG NO utility; you cannot update until an image copy (or an allowed bypass) clears it. GRECP is group buffer pool recovery pending in data sharing. LPL pages need recovery from a coupling facility or I/O error. Advisory AREO* / AREOR recommend REORG but still allow RW. Learn the codes; do not START FORCE as a first reflex.

USE, LOCKS, and CLAIMERS answer three different “who is in my way?” questions. USE shows which threads have the object open. LOCKS shows IRLM locks (including lock state and duration). CLAIMERS shows the drain/claim holders that block a utility from draining the space. BLOCKERS is a related but separate command that starts from a database name and lists lock and claim holders across objects—handy when DROP DATABASE hangs and you do not yet know the space name.

-DISPLAY THREAD

Shows allied (TSO, CICS, IMS, batch, RRS) and distributed threads. TYPE filters include ACTIVE, INACTIVE, INDOUBT, POSTPONED, PROCEDURE, SYSTEM, and * . LOCATION(*) adds remote conversation detail. DETAIL adds package, statement, accounting, and XID information. The TOKEN is what -CANCEL THREAD wants. Status PT marks parallel child tasks under an originating thread. During shutdown only TYPE(SYSTEM) remains useful; SCOPE(GROUP) is forced local.

1
2
3
-DISPLAY THREAD(*)
-DISPLAY THREAD(*) TYPE(INDOUBT)
-DISPLAY THREAD(*) LOCATION(*) DETAIL

-DISPLAY BUFFERPOOL and GROUPBUFFERPOOL

BUFFERPOOL reports local pools (VPSIZE, thresholds, getpage and I/O counters with DETAIL). Inactive pools still appear so you can see definitions that will apply when the pool becomes active. GROUPBUFFERPOOL reports coupling-facility structures: size, connections, castout, and directory/data entries. GBP failures show up here and in DISPLAY GROUP; recovery may require START DATABASE after the structure is rebuilt.

-DISPLAY LOG and -DISPLAY ARCHIVE

DISPLAY LOG answers “how full are the active logs, is offload stuck, when was the last checkpoint, what was RESTART RBA.” Dual logging shows COPY1 and COPY2. If percentages differ, the data sets are different sizes and Db2 still switches both at end-of-file—wasting space. Encrypted logs can show KEY LABEL. GDPS Continuous Availability environments add CDDS mode (source versus proxy) in DSNJ375I.

DISPLAY ARCHIVE shows how many archive tape units Db2 may use and how long they stay allocated (the values -SET ARCHIVE changes). ARCHIVE LOG SCOPE(GROUP) does not make DISPLAY ARCHIVE group-wide; issue it on each member.

-DISPLAY DDF and -DISPLAY LOCATION

DISPLAY DDF shows whether DDF is STARTD or STOPD, the IP and port, alias ports, and counts of connections versus active database-access threads. DETAIL breaks down statistics. DISPLAY LOCATION focuses on a named remote location: conversation queues, threads accessing that partner. Use them together when distributed SQL hangs: DDF up, location jammed, then DISPLAY THREAD LOCATION(*).

-DISPLAY UTILITY, TRACE, PROCEDURE, FUNCTION

DISPLAY UTILITY(*) lists UID, utility name, phase, percent, and member. STOPPED means the job left SYSUTILX occupied. DISPLAY TRACE lists trace numbers, types (PERFM, ACCTG, STAT, AUDIT, MONITOR), classes, and destinations (GTF, SMF, SRV, OPX). Start traces with -START TRACE; modify IFCIDs with -MODIFY TRACE; stop with -STOP TRACE—DISPLAY only shows what is on.

DISPLAY PROCEDURE shows stored procedures that applications have accessed: STOPQUE versus STOPREJ versus STARTED, queued requests, failures, WLM environment. Qualify with schema. DISPLAY PROCEDURE *.* needs SYSOPR or higher. DISPLAY FUNCTION SPECIFIC is the same idea for external UDFs (not sourced or built-in functions).

Group, blockers, stats, profile, RLIMIT, REST

• DISPLAY GROUP — member names, statuses (ACTIVE, QUIESCED), code level, catalog level, function level, SCA and lock structure health. First command after a data sharing incident.
• DISPLAY BLOCKERS — which threads hold locks or claims on DSNDB06, DSNDB01, or a user database when DROP or STOP waits.
• DISPLAY STATS — specialized counters such as index traverse counts for named indexes (DBNAME / SPACENAM / PART).
• DISPLAY PROFILE — whether START PROFILE loaded SYSIBM.DSN_PROFILE* tables into memory (filtering, monitoring, attributes).
• DISPLAY RLIMIT — governor on or off and which resource limit specification table (Authid.DSNRLSTxx) is active.
• DISPLAY RESTSVC — REST services by collection, started or stopped (pairs with START/STOP RESTSVC).
• DISPLAY DYNQUERYCAPTURE — monitors started by START DYNQUERYCAPTURE for stabilizing cached dynamic SQL.

Accelerator, ML, and OTEL

DISPLAY ACCEL lists accelerator servers and, with DETAIL, query and load statistics. ACCESS(MAINT) on START ACCEL shows up here as maintenance mode. DISPLAY ML reports IBM Db2 AI for z/OS: STOPPED versus STARTED, compatible versus current Db2ZAI level, and DETAIL daemon lines (MLZD, training, package automation). DISPLAY OTEL is the Db2 13 OpenTelemetry report (APAR PH67971): STATUS STARTED/STOPPED, EMIT YES/NO, successful versus failed SMF 1161 span records, and trace-parent sampling counts. Use DETAIL. Issue it on each member; there is no SCOPE(GROUP).

1
2
3
4
5
-DISPLAY ACCEL(*) DETAIL
-DISPLAY ML DETAIL
-DISPLAY OTEL DETAIL
-DISPLAY UTILITY(*)
-DISPLAY TRACE(*)

A practical order for incidents

1. DISPLAY GROUP (if data sharing) — is this member active?
2. DISPLAY LOG — are we about to hang on log-full?
3. DISPLAY THREAD TYPE(ACTIVE) — who is running?
4. DISPLAY DATABASE(...) RESTRICT — what is unavailable?
5. DISPLAY UTILITY(*) — is a STOPPED REORG the cause?
6. Then DDF, BUFFERPOOL, PROCEDURE, or ACCEL depending on the symptom.

DISPLAY never fixes the problem. It tells you whether the next command is START, STOP, CANCEL, TERM, RECOVER, or a utility restart.

Explain It Like I'm Five

DISPLAY is looking through the classroom window without opening the door. DISPLAY DATABASE is “are the lights on in each room?” DISPLAY THREAD is “who is sitting in which chair, and what is their ticket number?” DISPLAY LOG is “how full is the notebook?” DISPLAY UTILITY is “is the janitor still mopping?” You look first. You only use START, STOP, or CANCEL after you know which room has the problem.

Exercises

1. On a sandbox, issue -DIS DB(DSNDB06) SPACENAM(*) LIMIT(20) and identify RW versus any restricted status.
2. Issue -DIS THREAD(*) and write down one TOKEN. Do not cancel it; just match NAME and PLAN to a known connection.
3. Compare -DIS LOG and -DIS ARCHIVE output and list three facts each command gives that the other does not.
4. Issue -DIS UTIL(*) and explain what you would do if a COPY were STOPPED.
5. If your shop has data sharing, capture -DIS GROUP and name the function level shown.