STOP commands in DB2 for z/OS

How STOP commands are issued

On z/OS, a Db2 command is the verb plus keywords, preceded by the command prefix defined for that subsystem (often a hyphen, sometimes a unique prefix such as -DB2A). In IBM manuals the hyphen is written as -STOP DATABASE so you remember it is an operator command, not SQL. Abbreviation examples include -STO DB for STOP DATABASE, -STO DB2 for STOP DB2, -STO DDF, -STO PROC, -STO RLIM, and -STO TRA.

You can issue these commands from:

• A logged-on z/OS console or SDSF (the user ID must be defined to RACF or an equivalent security product so Db2 can check primary and secondary authorization IDs)
• A DSN session under TSO
• The DB2I COMMANDS panel
• An IMS or CICS terminal (where the attachment allows Db2 commands)
• A program using the instrumentation facility interface (IFI) COMMAND function

DSN9022I reports normal command completion. DSN9023I reports abnormal completion. For asynchronous STOP DATABASE, DSN9022I can appear before the objects actually stop; watch DSNT736I and DISPLAY DATABASE status STOPP versus STOP.

The STOP command family

Think of STOP as “make this unavailable.” The object you name decides the blast radius: one partition, one procedure, all traces, or the whole Db2 address spaces.

-STOP DATABASE

-STOP DATABASE makes specified objects unavailable for applications and closes their data sets. You can name databases, table spaces, index spaces, physical partitions of partitioned spaces (including data-partitioned secondary indexes), or logical partitions of nonpartitioned secondary indexes. Stopping a logical partition does not close the index data set. In data sharing the command has group scope: every member sees the stop. If a GBP-dependent object is stopped, Db2 does the processing needed so the object is no longer GBP-dependent.

Authorization typically includes the STOPDB privilege, database authorities (DBMAINT, DBCTRL, DBADM), System DBADM, SYSCTRL, or SYSADM. Implicit databases can be authorized on the implicit database or on DSNDB04. When data definition control is active, installation SYSOPR or installation SYSADM is required to stop spaces that contain registration tables. If you stop catalog spaces in DSNDB06 that are needed to check authorization, you need installation SYSADM to start them again.

DATABASE and SPACENAM name forms

You must specify a database list (or *). Patterns IBM documents:

• (name) — one or more names, comma-separated
• (*) — all databases you are authorized to stop. DSNDB01, DSNDB06, and work file databases (for example DSNDB07) are not included unless you name them explicitly
• dbname1:dbname2 — Unicode collating range
• dbname*, *dbname, *dbname*, *dbstring1*dbstring2* — prefix, suffix, contains, or two-string contains patterns (length limits apply as in the Command Reference)

SPACENAM (abbreviations SPACE or SP) names table spaces or index spaces inside those databases. The same list, range, and asterisk patterns apply. SPACENAM(*) stops all spaces in the named databases. PART(integer) stops one or more partitions (lists and ranges). PART is valid for partitioned table spaces, partitioned indexes, and nonpartitioned type 2 indexes on partitioned table spaces. CLONE processes clone objects only; omit CLONE to process base objects only.

AT(COMMIT)

Without AT(COMMIT), STOP DATABASE takes drain-all locks and exclusive locks as documented (partitioned versus nonpartitioned). With AT(COMMIT), Db2 marks the object so new requesters cannot get in. Applications already running continue until their next commit; after that commit they cannot come back. The object actually reaches STOP when all jobs release claims and all utilities release drain locks. Use AT(COMMIT) to break in on threads bound with RELEASE(DEALLOCATE) when there is heavy thread reuse. The option is ignored for declared temporary databases.

1
2
3
4
-STOP DATABASE(DSN8D81A) SPACENAM(DSN8S81E)
-STOP DATABASE(*) AT(COMMIT)
-STOP DATABASE(DSN8D81A) SPACENAM(XEMP2) PART(1)
-STOP DATABASE(MYDB*) SPACENAM(MYDB*SP) CLONE

If you stop a space with SPACENAM, you must START DATABASE that space explicitly later—starting only the database does not restart spaces you stopped by name. Index spaces for declared temporary tables cannot be stopped. The CDB and resource limit database cannot be stopped while they are active. From a TSO or z/OS console the command runs asynchronously; from batch it runs synchronously so later job steps can depend on the stop. The command retries drain locks and fails if it times out more than 15 times or hits a serious deadlock.

-STOP DB2

-STOP DB2 stops the Db2 subsystem (this data sharing member). You need STOPALL privilege, SYSOPR, SYSCTRL, or SYSADM.

• MODE(QUIESCE) — currently executing programs may complete; no new program may start. If a utility starts a subtask after the quiesce, DSNU006I can appear and the utility might abend. Connected address spaces must disconnect before Db2 actually stops. Use DISPLAY THREAD, then CANCEL THREAD or z/OS CANCEL on leftovers.
• MODE(FORCE) — terminate currently executing programs, including utilities. No new work starts. FORCE often creates indoubt situations. Stored-procedure and service tasks may abend with dumps. FORCE is not an instant abend: work outside Db2 is posted to stop accessing Db2; work inside Db2 stops at the next suspend or execution-unit switch, which can take a while.
• CASTOUT(YES|NO) — data sharing only. YES (usual) allows group buffer pool castout for page sets this member last updated. NO skips castout to speed a maintenance shutdown. After CASTOUT(NO) the member can show QC status on DISPLAY GROUP, retain IX page-set P-locks, and leave GBP connections failed-persistent. Do not use CASTOUT(NO) if you need consistent data on disk (for example an offsite copy of the whole group).

1
2
3
-STOP DB2 MODE(QUIESCE)
-STOP DB2 MODE(QUIESCE) CASTOUT(NO)
-STOP DB2 MODE(FORCE)

-STOP DDF

-STOP DDF stops the distributed data facility if it is already started, ending the DDF interface to VTAM or TCP/IP. Authorization is SYSOPR, SYSCTRL, or SYSADM. Member scope.

With MODE(SUSPEND) you can add CANCEL(n) to cancel remaining active DBATs if suspend has not finished in n seconds (0–9999), or WAIT(n) to resume DDF if suspend does not complete in n seconds. After a successful SUSPEND, other non-DDF holders might still exist—use CANCEL THREAD for those. If CMTSTAT is ACTIVE, all DDF threads are treated as active for QUIESCE. DISPLAY DDF status values include STOPDQ (normal stop), STOPDF (forced stop), and SUSPND (suspended).

1
2
3
-STOP DDF
-STOP DDF MODE(FORCE)
-STOP DDF MODE(SUSPEND) CANCEL(600)

-STOP PROCEDURE and -STOP FUNCTION SPECIFIC

These twins stop new work, not work already queued or scheduled. -STOP PROCEDURE stops SQL CALL for stored procedures. -STOP FUNCTION SPECIFIC stops SQL that invokes specified functions. You cannot stop built-in functions, inline SQL functions, or UDFs sourced on another function. Db2 itself issues STOP … ACTION(REJECT) when a routine exceeds the installation MAX ABEND COUNT (DSNTIPX).

• Names: *.* (all), schema.name, schema.partial*. Unqualified procedure names are in SYSPROC. Functions must use the specific name from SYSIBM.SYSROUTINES, not the SQL function name.
• ACTION(QUEUE) — wait until timeout or a matching START command (default behavior when you omit ACTION on many systems’ examples)
• ACTION(REJECT) — fail new CALL or function invocations immediately
• SCOPE(LOCAL|GROUP) — this member or the whole data sharing group

A stopped routine does not stay stopped across a Db2 restart. To disable permanently, ALTER or DROP the routine, or remove the z/OS load module. STOP PROCEDURE and STOP FUNCTION SPECIFIC affect all versions of native SQL procedures / SQL functions that you name.

1
2
3
-STOP PROCEDURE ACTION(REJECT)
-STOP PROCEDURE(PAYROLL.CALC_TAX,USERPRC3) SCOPE(GROUP)
-STOP FUNCTION SPECIFIC(PAYROLL.USERFN1,PAYROLL.USERFN3) ACTION(REJECT)

-STOP TRACE

-STOP TRACE stops tracing. You need TRACE privilege, SQLADM, System DBADM, SYSOPR, SYSCTRL, SYSADM, or SECADM. SCOPE can be LOCAL (default) or GROUP.

• Types: PERFM (performance), ACCTG (accounting), STAT (statistics), AUDIT, MONITOR, or * for all
• DEST: GTF, SMF, SRV, or OPn (an OP buffer used by IFI readers)
• Constraint and filter blocks match START TRACE: PLAN, AUTHID, LOCATION, CLASS, TNO (trace numbers 1–32), USERID, APPNAME, and exclude forms such as XPLAN
• AUDTPLCY applies to AUDIT traces and cannot be combined with CLASS or IFCID

Stopping traces does not undo SMF already written. If a monitor program allocated an OP buffer, stop the corresponding trace before the program ends so the buffer is released cleanly.

1
2
3
-STOP TRACE(*)
-STOP TRACE(PERFM) DEST(SMF)
-STOP TRACE(MONITOR) TNO(1,2) SCOPE(LOCAL)

-STOP RLIMIT and -STOP PROFILE

-STOP RLIMIT stops the resource limit facility (governor). Previously set limits return to infinity and accumulated governed time resets. Dynamic SQL that was limited (SELECT, INSERT, UPDATE, DELETE through PREPARE or EXECUTE IMMEDIATE) then runs with no governor limit until you START RLIMIT again. SYSOPR, SYSCTRL, or SYSADM.

-STOP PROFILE disables the profile function that reads SYSIBM.DSN_PROFILE_TABLE and DSN_PROFILE_ATTRIBUTES (idle-thread monitors, connection thresholds, special registers, and related profile actions). SQLADM, System DBADM, SYSOPR, SYSCTRL, or SYSADM. Member scope. START PROFILE reloads and re-enables profiles.

1
2
-STOP RLIMIT
-STOP PROFILE

Accelerators, CDDS, REST, capture, ML, and OTEL

-STOP ACCEL

Tells this Db2 subsystem to stop using the named IBM Db2 Analytics Accelerator servers. Queries already running on an accelerator are not the same as “Db2 will not route new work.” Pair with DISPLAY ACCEL and START ACCEL when you bounce an accelerator appliance or take a server out of the configuration.

-STOP CDDS

Directs all members of a data sharing group to close and deallocate the compression dictionary data set (CDDS) used with replication products that need a shared dictionary. Use when the CDDS must be moved or recovered; START CDDS afterward.

-STOP RESTSVC

Prevents Db2 from accepting new discover or invoke requests for one or more native REST services. You can qualify names with a collection ID (the REST package collection, often SYSIBMSERVICE). Existing in-flight invokes are not the same as new HTTP requests; DISPLAY RESTSVC shows status. START RESTSVC enables the service definition again.

-STOP DYNQUERYCAPTURE

Stops capture of dynamic SQL statements by the monitors you started with START DYNQUERYCAPTURE (used with dynamic statement stabilization and related capture into catalog structures such as SYSIBM.SYSDYNQRY). Specify the same monitor identification you used on START.

-STOP ML

Stops Db2 functions used by IBM Db2 AI for z/OS if that product has already been started with START ML. DISPLAY ML shows whether ML is active on the member.

-STOP OTEL

On Db2 13 (APAR PH67971 and related PTFs), -STOP OTEL stops all Db2 OpenTelemetry functions: W3C trace-context propagation and writing of extended SMF type 1161 subtype 1 span records. There is no SCOPE(GROUP)—issue STOP OTEL on each data sharing member. START OTEL EMIT(YES|NO) starts processing; DISPLAY OTEL DETAIL shows emit counts. If OTEL is already stopped, DSNW303I reports that the command is ignored.

1
2
3
4
5
-STOP ACCEL(*)
-STOP CDDS
-STOP RESTSVC(SYSIBMSERVICE.GetEmployee)
-STOP ML
-STOP OTEL

A practical STOP sequence

Operators rarely issue a random STOP. A typical maintenance window looks like this:

• DISPLAY THREAD, DISPLAY UTILITY, and DISPLAY DATABASE so you know what is running
• STOP DDF MODE(QUIESCE) if you must drain remote clients without stopping the whole subsystem
• STOP DATABASE … AT(COMMIT) for the spaces you will REORG, MOVE, or recover
• DISPLAY DATABASE until status is STOP, not STOPP
• Do the work, then START DATABASE ACCESS(RW) (or RO/UT as required)
• For a subsystem outage: STOP DDF QUIESCE, then STOP DB2 MODE(QUIESCE), and only FORCE if something will not drain

Never confuse STOP DATABASE with the SQL DROP statement, and never confuse STOP DB2 with cancelling one batch job. STOP DATABASE is reversible with START DATABASE. STOP DB2 takes down MSTR, DBM1, DIST (if up), IRLM identification, and allied connections for that member.

Explain It Like I'm Five

Imagine Db2 is a busy library. STOP DATABASE is putting a “closed” sign on one shelf so nobody can take those books, while the rest of the library stays open. STOP DDF is locking the front door that remote visitors use. STOP PROCEDURE is telling the librarian not to take any new phone requests for a special service. STOP DB2 is turning out the lights and sending everyone home. QUIESCE means “finish the chapter you are reading, then leave.” FORCE means “the fire alarm—out now,” which can leave bookmarks (indoubt work) in a messy place.

Exercises

1. Write the command to stop table space PAYTS in database PAYDB and explain when you would add AT(COMMIT).
2. A DISPLAY DATABASE shows STOPP for hours. List three things you would DISPLAY next and why STOPP is not the same as STOP.
3. Compare STOP DDF MODE(QUIESCE), MODE(FORCE), and MODE(SUSPEND) for a change that only needs to freeze inbound SQL while DDL runs.
4. A stored procedure is looping. Why might STOP PROCEDURE ACTION(REJECT) not stop the current CALL, and which command would you use for the thread?
5. Your shop uses data sharing and you need a copy of DASD that is consistent on disk. Why is STOP DB2 CASTOUT(NO) the wrong choice for that copy?