-STOP DATABASE command (Db2)

The Db2 command STOP DATABASE makes the specified objects unavailable for applications and closes their data sets.

The objects that can be designated are:

Databases
Table spaces
Index spaces
Physical partitions of partitioned table spaces or index spaces (including index spaces that contains data-partitioned secondary indexes)
Logical partitions of nonpartitioned secondary indexes

When used to stop a logical partition of a secondary index, the command does not close any data sets that are associated with the index.

In a data sharing environment, the command applies to every member of the data sharing group. If a GBP-dependent object is stopped with the command STOP DATABASE, Db2 performs the necessary processing to make the object no longer GBP-dependent.

Abbreviation: -STO DB

Environment

This command can be issued from a z/OS® console, a DSN session under TSO, a DB2I panel (DB2 COMMANDS), an IMS or CICS® terminal, or a program using the instrumentation facility interface (IFI).

Data sharing scope: Group

Authorization

To execute this command, you must use a privilege set of the process that includes one of the following privileges or authorities:

STOPDB privilege
DBMAINT authority
DBCTRL authority
DBADM authority
System DBADM authority
SYSCTRL authority
SYSADM authority

Error messages are produced for those specified databases for which this set does not have the STOPDB privilege.

For implicitly created databases, the database privilege or authority can be held on the implicitly created database or on DSNDB04. If the STOP DATABASE command is issued on specific table spaces or index spaces in an implicitly created database, ownership of the table spaces is sufficient to stop them. This means that the owner can display information about an implicitly created table space or index space if the command explicitly specifies that table space or index space name.

Db2 commands that are issued from a logged-on z/OS console or TSO SDSF can be checked by Db2 authorization using primary and secondary authorization IDs. Start of change A logged-on z/OS user ID must be defined in RACF or a similar security server. End of change

When data definition control is active, installation SYSOPR or installation SYSADM authority is required to stop the database, a table space, or an index space that contains a registration table or index.

Database DSNDB06 contains the table spaces and index spaces that are required to check authorization. If you stop any table space or index space that is required for the START DATABASE authorization check, installation SYSADM authority is required to restart it.

Syntax

Option descriptions

One of the following two options is required.

( database-name , …)

Specifies the names of the database, or database for the table spaces or index spaces to stop. The following variations are accepted:

(database-name, ...): Identifies one or more database names, separated by commas or blanks.
(*): All databases that are defined to the Db2 subsystem for which the privilege set of the process has the required authorization.
(dbname1:dbname2): All databases whose names, in UNICODE, are between dbname1 and dbname2 inclusive.
(dbname*): All databases whose names begin with the string dbname that contains 1–7 characters.
(*dbname): All databases whose names end with the string dbname that contains 1–7 characters.
(*dbname*): All databases whose names contain the string dbname,where dbname that contains 1–6 characters.
(*dbstring1*dbstring2*): All databases whose names contain the strings dbstring1 and dbstring2 that together contain a total of 2–5 characters.

SPACENAM( space-name , …)

Indicates names of table spaces or indexes within the specified database to stop.

Abbreviation: SPACE, SP

space-name

Is the name of one or more table spaces or index spaces to stop. The following variations are accepted: spacename and spacestring can have any of the forms in the following list (where spacename1 and spacename2 represent any strings of 1–8 characters, and spacename represents any string of 1–7 characters):

(spacename, ...): One ore more index space names, separated by commas or blanks.
(*): All table spaces or index spaces that are defined to the Db2 subsystem for which the privilege set of the process has the required authorization.
(spacename1:spacename2): All table spaces or index spaces whose names, in UNICODE, are between spacename1 and spacename2 inclusive
(spacename*): All table spaces or index spaces whose names begin with the string spacename that contains 1–7 characters.
(*spacename): All table spaces or index spaces whose names end with the string spacename that contains 1–7 characters.
(*spacename*): All table spaces or index spaces whose names contain the string spacename that contains 1–6 characters.
(*spacestring1*spacestring2*): All table spaces or index spaces whose names contain the strings spacestring1 and spacestring2 that together contain a total of 2–5 characters.

PART ( integer , ...)

Indicates the partition number of one or more partitions, within the specified table space or index, that are to be stopped. The START or STOP state of other partitions does not change.

The integer specified must identify a valid partition number for the corresponding space name and database name. If you specify nonvalid partition numbers, you receive an error message for each nonvalid number, but all valid partitions that you specified are stopped.

integer can be written to designate one of the following specifications:

A list of one or more partitions
A range of all partition numbers that collate greater than or equal to integer1 and less than or equal to integer2
A combination of lists and ranges

PART is valid with partitioned table spaces, partitioned indexes, and nonpartitioned type 2 indexes of partitioned table spaces. If you specify PART with a nonpartitioned table space or index on a nonpartitioned table space, you receive an error message, and the nonpartitioned space is not stopped. When a logical partition is stopped, the index is not closed. A nonpartitioning index must be stopped without the use of PART to close the index.

CLONE

Stops clone objects. In the absence of the CLONE keyword, base table objects are stopped and clone table objects are not processed. If you specify the CLONE keyword then only clone objects will be processed.

AT(COMMIT)

Marks the specified object as being in STOP status to prevent access from new requesters. Currently running applications are allowed to continue access until their next commit. After commit, further access by the committing application is prohibited. The object is actually stopped and put in STOP status when all jobs release their claims on it and all utilities release their drain locks on it. Specify AT(COMMIT) to break in on threads that are bound with RELEASE(DEALLOCATE), especially in situations where there is high thread reuse.

The option is ignored for declared temporary databases and table spaces within it.

Usage notes

Explicitly stopped databases

If table spaces and indexes are stopped explicitly (using the STOP DATABASE command with the SPACENAM option), they must be started explicitly using the START DATABASE command. Starting the database does not start table spaces or indexes that have been stopped explicitly.

Stopped table spaces, indexes, and partitions

Table spaces, indexes, and partitions are physically closed when the STOP DATABASE command is issued, except for logical partitions of a nonpartitioning index of a partitioned table space. Index spaces for declared temporary tables cannot be stopped or started.

Operation in TSO, z/OS, and batch

When the STOP DATABASE command is issued from a TSO or a z/OS console, the command operates asynchronously to keep the terminal free. When the command is issued from a batch job, it operates synchronously in case later steps depend on the database being stopped. The STOP DATABASE command drains work in progress on the database before stopping it. If it cannot get the drain locks on the first request, it repeatedly tries again. The command fails if it times out more than 15 times trying to get the locks or if a serious deadlock situation occurs.

Ensuring that all databases are stopped

When the STOP DATABASE command is processing asynchronously, message DSN9022I might be issued before the command completes. Message DSNT736I is issued to indicate that the asynchronous processing of the STOP DATABASE command is complete.

Use the DISPLAY DATABASE command to check the stopped status of table spaces and indexes in a database. A status of STOPP indicates that the object is in the process of being stopped. A status of STOP indicates that the stop has completed and the object is in a stopped state. An object is not stopped until all currently active threads accessing the object are quiesced.

An object might remain in the STOP pending (STOPP) status if the STOP DATABASE command does not successfully complete processing.

Stopping the communication database and the resource limit database

f the communication database (CDB) and the resource limit database (RLST) are active, they cannot be stopped. Those databases are active when created and are activated by Db2.

Stopping the SYSCONTX catalog table space or indexes on tables in the SYSCONTX catalog table space

If trusted contexts are in use when you stop SYSCONTX or the associated indexes, you can continue to use any trusted contexts that are already defined.

Stopping DSNDB01

If you try to stop the DSNDB01 database while an application plan or package is executing, you might receive a time out because of locking contention on DSNDB01. This is most likely to occur when an application plan or package is executing for the first time since Db2 was started, or if the skeleton cursor table (SKCT) for the plan or the skeleton package table (SKPT) for the package was swapped out of the EDM pool.

Table space in a restrictive status

If an application process requests a transaction lock on a table space that is in a restrictive status (RECP) or has a required index in a restrictive status, Db2 acquires the lock and does not detect the status until the application tries to access the table space or index. The application then receives SQLCODE -904 (resource not available) and should release the lock, either by committing or rolling back (if the value of the RELEASE option is COMMIT) or by ending (if the value of RELEASE is DEALLOCATE). If you issue the command STOP DATABASE for either the table space or the index space while a transaction lock is in effect, the command is suspended. It repeatedly tries to get the locks needed to drain the work in progress before stopping the database. If the command times out more than 15 times trying to get the locks, it fails.

Stopping a table space partition in PRO restricted status

When a table space that is in Persistent Read Only (PRO) restricted status is stopped, the partition remains in PRO restricted status.

After a disk failure

Issuing the STOP DATABASE command before interrupting the I/O interface between the failed device and Db2 can result in incomplete I/O requests. To prevent this hang situation, create an interruption either by forcing the device offline using the z/OS command VARY with the FORCE option, or by setting the I/O timing interval for the device before any failures. You can set the I/O timing interval through the IECIOSxx z/OS parmlib member or by issuing the z/OS command:

SETIOS MIH,DEV=dddd,IOTIMING=mm:ss

Stopping a LOB table space

The following table summarizes the locking used by the STOP DATABASE command.

Table 1. Locking used by the STOP DATABASE command
Command	Table space type		Locks acquired
STOP AT COMMIT	Partitioned	PART	Drain-all on partitions specified.
	Partitioned		Drain-all on all partitions.
	Nonpartitioned		Drain-all on table space.
STOP	Partitioned	PART	X-lock partitions specified. Drain-all on partitions specified.
	Partitioned		X-lock all partitions. Drain-all on all partitions.
	Nonpartitioned		X-lock table space. Drain-all on table space.

Trace information for data sharing members

When this command with group scope is issued in a Db2 data sharing member, it also runs on all other active members. IFICID 090 trace records for other group members can show that the same command was issued by the SYSOPR authorization ID from the 016.TLPKN5F correlation ID, in addition to the trace records from the member where the original command was issued. See Command scope in Db2 data sharing.

Examples

Example 1: Stop table space DSN8S81E in database DSN8D81A and close the data sets that belong to that table space.

-STOP DATABASE(DSN8D81A) SPACENAM(DSN8S81E)

Example 2: Stop all databases (except DSNDB01, DSNDB06, and work file databases)

-STOP DATABASE(*)

Example 3: Stop all databases (except DSNDB01, DSNDB06, and work file databases) when all jobs release their claims and all utilities release their drain locks.

-STOP DATABASE(*) AT(COMMIT)

Example 4: Stop the first partition of XEMP2, a nonpartitioning index of a partitioned table space in database DSN8D81A. Partition 1 is logically stopped and cannot be accessed by applications; however, no data sets are closed because parts of a nonpartitioning index are not associated with separate physical data sets.

-STOP DATABASE(DSN8D81A) SPACENAM(XEMP2) PART(1)

Example 5: Stop all table spaces with names that begin with "T" and end with the "IQUA03" string in database DSN8D81A.

-STOP DATABASE(DSN8D81A) SPACENAM(T*IQUA03)

Output similar to the following output indicates that the command completed successfully:

DSN9022I - DSNTDDIS 'STOP DATABASE' NORMAL COMPLETION
DSNT736I - ASYNCHRONOUS STOP DATABASE COMMAND HAS
COMPLETED FOR COMMAND: STOP DB(DSN8D81A) SPACE(T*IQUA03)

Example 6: Stop clone objects.

-STOP DATABASE(MYDB*) SPACENAM(MYDB*SP) CLONE