-STOP DATABASE (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.

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

                      .-,-------------.           
                      V               |           
>>-STOP DATABASE--(-+---database-name-+-----+-)----------------->
                    +-*---------------------+     
                    +-dbname1:dbname2-------+     
                    +-dbname*---------------+     
                    +-*dbname---------------+     
                    +-*dbname*--------------+     
                    '-*dbstring1*dbstring2*-'     

>--+---------------------------------------------------------------------------------------------+-->
   |             .-,----------.                                                                  |   
   |             V            |                                                                  |   
   '-SPACENAM(-+---space-name-+--------------+-)-+-----------------------------------+-+-------+-'   
               +-*---------------------------+   |       .-,---------------------.   | '-CLONE-'     
               +-spacename1:spacename2-------+   |       V                       |   |               
               +-spacename*------------------+   '-PART(---+-integer-----------+-+-)-'               
               +-*spacename------------------+             '-integer1:integer2-'                     
               +-*spacename*-----------------+                                                       
               '-*spacestring1*spacestring2*-'                                                       

>--+------------+----------------------------------------------><
   '-AT(COMMIT)-'

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. If you use more than one name, separate names in the list by commas.

(*): Stops all databases for which the privilege set of the process has at least DBMAINT authority or STOPDB privilege.
However, DSNDB01, DSNDB06, and work file databases, such as DSNDB07, can be stopped only by specifying them explicitly (for example, STOP DATABASE(DSNDB01)).

dbname and dbstring can have any of the forms in the following list (where dbname1 and dbname2 represent any strings of from 1 to 8 characters, and dbname represents any string of from 1 to 7 characters):

Form: Stops...
dbname1:dbname2: All databases whose names, in UNICODE, collate greater than or equal to dbname1 and less than or equal to dbname2
dbname*: All databases whose names begin with the string dbname
*dbname: All databases whose names end with the string dbname
*dbname*: All databases whose names contain the string dbname
*dbstring1*dbstring2*: All databases whose names contain the strings dbstring1 and dbstring2

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.

You can write space-name like database-name to designate:

The name of a single table space or index space
A range of names
A partial name, including a beginning or ending pattern-matching character (*), pattern-matching character between two strings, or any combination of these uses. Consecutive pattern-matching characters (*) are not allowed, and you cannot specify two pattern-matching characters in the middle of a keyword string.

See the following section for instructions on how to start a table space or index space again.

(*): Stops all table spaces and indexes of the specified database.

spacename and spacestring can have any of the forms in the following list (where spacename1 and spacename2 represent any strings of from 1 to 8 characters, and spacename represents any string of from 1 to 7 characters):

Form: Displays the status of...
spacename1:spacename2: All table spaces or index spaces whose names, in UNICODE, collate greater than or equal to spacename1 and less than or equal to spacename2
spacename*: All table spaces or index spaces whose names begin with the string spacename
*spacename: All table spaces or index spaces whose names end with the string spacename
*spacename*: All table spaces or index spaces whose names contain the string spacename
*spacestring1*spacestring2*: All table spaces or index spaces whose names contain the strings spacestring1 and spacestring2

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: If 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.

Start of change 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. End of change

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.

Start of change 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. End of change

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 STOP DATABASE command can be used to stop LOB table spaces and indexes on auxiliary tables. LOB table spaces are stopped independently of the base table space with which the LOB table space is associated.

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	IX mass delete lock. Drain-all on partitions specified.
	Partitioned		IX mass delete lock. Drain-all on all partitions.
	Nonpartitioned		IX mass delete lock. 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.

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