-START DATABASE (DB2)

The START DATABASE command makes the specified database available for use.

Depending on which options you specify, the following objects can be made available for read-only processing, read-write processing, or utility-only processing:
  • Databases
  • Table spaces
  • Index spaces
  • Physical partitions of partitioned table spaces or index spaces (including index spaces housing data-partitioned secondary indexes (DPSIs))
  • Logical partitions of nonpartitioned secondary indexes.
The command is typically used after one of the following events:
  • The STOP DATABASE command is issued
  • A table space, partition, or index is placed in group buffer pool RECOVER-pending status (GRECP)
  • Pages have been put on the logical page list (LPL) for a table space, partition, or index

In a data sharing environment, the command can be issued from any DB2® subsystem in the group that has access to the specified database.

Abbreviation: -STA DB

Environment

This command can be issued from a z/OS® console, a DSN session, 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 authorities:
  • STARTDB privilege
  • DBMAINT authority
  • DBCTRL authority
  • DBADM authority
  • Start of changeSystem DBADM authorityEnd of change
  • SYSCTRL authority
  • SYSADM authority
When you are using a privilege set that does not contain the STARTDB privilege for a specified database, DB2 issues an error message.

For implicitly created databases, the database privilege or authority can be held on the implicitly created database or on DSNDB04. If the START DATABASE command is issued on specific table spaces or index spaces in an implicitly created database, ownership of the table spaces is sufficient to start 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.

All specified databases with the STARTDB privilege included in the privilege set of the process are started.

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 start a database, a table space, or an index space containing a registration table or index.

Table space DBD01 in database DSNDB01 and table spaces and index spaces in database DSNDB06 are required to check the authorization for using the START DATABASE command. If a table space or index space required for this authorization check is stopped, or is unavailable because it is in LPL or GRECP status, installation SYSADM authority is required to start any database, table space, or index space, including the ones required for the authorization check.

Syntax

                       .-,-------------.           
                       V               |           
>>-START 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*-'                                                       

>--+-------------------------------------------------+---------><
   |         .-RW--------------------------------.   |   
   '-ACCESS(-+-RO--------------------------------+-)-'   
             +-UT--------------------------------+       
             +-Start of changeRREPLEnd of change-+       
             '-FORCE-----------------------------'       

Option descriptions

( database-name , …)
Specifies the name of a database, or a database for the table spaces or index spaces that are to be started. If you use more than one name, separate names in the list with commas.
(*)
Starts all databases for which the privilege set of the process has at least DBMAINT authority or STARTDB privilege (except databases that are already started). You cannot use (*) with ACCESS(FORCE).

You can start DSNDB01, DSNDB06, and work file databases, such as DSNDB07, only by explicitly specifying them (for example, START DATABASE(DSNDB01)).

dbname and dbstring can have any of the forms in the following list (where dbname1 and dbname2 represent any 1- to 8-character string, and dbname represents any 1- to 7-character string):
Form
Starts
dbname1:dbname2
All databases whose names, in Unicode, are 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
Specifies the particular table spaces or indexes within the database that are to be started. If you use ACCESS(FORCE), you must use SPACENAM with a list of table space and index names.
Abbreviation: SPACE, SP
( space-name , …)
Specifies the name of a table space or index space that is to be started. You can use a list of several names of table spaces and index spaces. Separate names in the list with commas.
You can specify 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 (*)
  • Two strings separated by a pattern-matching character (*)
  • Any combination of the previous items in this list, with the following exceptions. Consecutive pattern-matching characters (*) are not allowed, and you cannot specify two pattern-matching characters (*) in the middle of a keyword string.

You cannot use a partial name or a range of names with the ACCESS(FORCE) option.

(*)
Starts all table spaces and index spaces in the specified database. You cannot use (*) with ACCESS(FORCE).
spacename and spacestring can have any of the forms in the following list (where spacename1 and spacename2 represent any 1- to 8-character string, and spacename represents any 1- to 7-character string):
Form
Displays the status of
spacename1:spacename2
All table spaces or index spaces whose names, in Unicode, are 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 , ...)
Specifies the partition number of one or more partitions, within the specified table space or index, that are to be started. The start or stop state of other partitions does not change.

The specified integer 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 other valid partitions that you specified are started.

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 are greater than or equal to integer1 and less than or equal to integer2
  • A combination of lists and ranges

The PART option 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 started.

CLONE
Starts clone objects. In the absence of the CLONE keyword, base table objects are started and the clone table objects are not processed. If you specify the CLONE keyword then only clone objects are processed.
ACCESS
Specifies whether the objects that are started are in read/write, read only, or utility only status. Also forces access to objects that are in unavailable status.

Abbreviation: ACC

(RW)
Allows programs to read from and write to the specified databases, table spaces, indexes, or partitions.
(RO)
Allows programs to only read from the specified databases, table spaces, indexes, or partitions. Any programs attempting to write to the specified objects will not succeed. Do not use this option for a database for declared temporary tables (databases created with the AS TEMP option).
(UT)
Allows only DB2 online utilities and the SQL DROP statement to access the specified databases, table spaces, indexes, or partitions.
Start of change(RREPL) End of change
Start of changeStart of changeAllows programs only read access to the specified databases, table spaces, indexes, or partitions, unless those programs were identified as replication programs. Attempts by non-replication programs to write to the specified objects fail. Most DB2 utilities are allowed on the specified objects.

If ACCESS(RREPL) is specified in DB2 10 conversion mode or DB2 10 enabling-new-function mode, DB2 converts ACCESS(RREPL) to ACCESS(RO), and does not issue a message.

Restriction: Do not use ACCESS(RREPL) for a database for declared temporary tables (a database that was created with the AS TEMP option).
End of changeEnd of change
(FORCE)
Resets any indications that a table space, index, or partition is unavailable because of pages in the logical page list, pending-deferred restarts, write-error ranges, read-only accesses, or utility controls. FORCE also resets the CHECK-pending, COPY-pending, and RECOVER-pending states. Full access to the data is forced. FORCE cannot be used to reset the restart-pending (RESTP) state.

When using ACCESS(FORCE), you must use a single database name, the SPACENAM option, and an explicit list of table space and index names. You cannot use any range or combination of pattern-matching characters (*), including DATABASE (*) or SPACENAM (*).

A utility-restrictive state is reset (and the utility is terminated) only if all of the target objects are reset with this command. To identify which objects are target objects of the utility, use the DISPLAY DATABASE command, or run the DIAGNOSE utility with the DISPLAY SYSUTIL option. The DIAGNOSE utility should be used only under the direction of IBM® Software Support.

Note: ACCESS(FORCE) will not successfully complete if the object you are trying to force was placed in a utility-read-only (UTRO), utility-read-write (UTRW), or utility-utility (UTUT) state by a utility running in a previous release of DB2. If this situation is encountered, DB2 issues message DSNI041I. To reset the restrictive state, you must terminate the utility using the release of DB2 in which it was started.

A table space or index space that is started with ACCESS(FORCE) might be in an inconsistent state.

Usage notes

Data sets offline: Disk packs that contain partitions, table spaces, or indexes, do not necessarily need to be online when a database is started. Packs must, however, be online when partitions, table spaces, or indexes are first referred to. If they are not online, an error in opening occurs.

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

Effect on objects marked with GRECP or with LPL entries: If a table space, partition, or index is in the group buffer pool RECOVER pending (GRECP) status, or if it has pages in the logical page list (LPL), the START DATABASE command begins recovery of the object. You must specify the SPACENAM option and ACCESS (RW) or (RO).

This recovery operation is performed even if SPACENAM specifies an object that is already started.

If the object is stopped when the command is issued, then the START DATABASE command both starts the object and clears the GRECP or LPL status. If the GRECP or LPL recovery action cannot complete, the object is still started.

If any table space or index space that is required to check command authority is unavailable, Installation SYSADM or Installation SYSOPR authority will be required to issue the START DATABASE command.

When recovering objects that are in GRECP or LPL status, avoid using pattern-matching characters (*) for both the database name and the space name. Multiple START DATABASE(dbname) SPACENAM(*) commands running in parallel should complete faster than one START DATABASE(*) SPACENAM(*) command.

If you use pattern-matching characters (*) for both the database name and space name, you must have DBMAINT authority and ensure that the catalog and directory databases have already been explicitly started in the following order:
  • -START DATABASE(DSNDB01) SPACENAM(*)
  • -START DATABASE(DSNDB06) SPACENAM(*)

Although not recommended, you can start an object using START DATABASE ACCESS(FORCE). That deletes all LPL and write error page range entries without recovering the pages. It also clears the GRECP status.

When a table space or partition is placed in the LPL because undo processing is needed for a NOT LOGGED table space, the -START DATABASE command does not remove the table space or partition from the LPL.

When starting a LOB table space defined as LOG NO and either in GRECP or having pages in the LPL, the LOB table space will be placed in the AUXW state and the LOB will be invalidated if DB2 detects that log records required for LPL recovery are missing due to the LOG NO attribute.

Use of ACCESS(FORCE): The ACCESS(FORCE) option is intended to be used when data has been restored to a previous level after an error, by DSN1COPY, or by a program that is not DB2 for z/OS, and the exception states resulting from the error still exist and cannot be reset. When using ACCESS(FORCE), it is up to the user to ensure the consistency of data with respect to DB2.

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. DB2 does not detect the status until the application tries to access the table space or index, when the application receives an error message indicating that the resource is not available (SQLCODE -904). After receiving this message, the application 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 START DATABASE ACCESS(FORCE) for either the table space or the index space while the lock is in effect, the command fails.

If an object has retained locks (that is, a member of a DB2 data sharing group has failed and the locks it held on the object are retained in the lock structure), START DATABASE ACCESS (FORCE) is not allowed.

START DATABASE ACCESS(FORCE) does not execute if postponed abort or indoubt units of recovery exist. If you attempt to issue the START DATABASE ACCESS(FORCE) command in this situation, the command fails. FORCE cannot be used to reset the restart pending (RESTP) state.

Restricted mode (RO or UT): When a START DATABASE command for a restricted mode (RO and UT) takes effect depends on whether applications are started after the START DATABASE command has completed, or whether applications are executing at the time the command is issued. For applications that are started after START DATABASE has completed, access restrictions are effective immediately. For applications that are executing at the time START DATABASE is issued, the access restrictions take effect when a subsequent claim is requested or the application is allowed to run to completion. Whether the application is interrupted by the START DATABASE command depends on various factors. These factors include the ACCESS mode that is specified on the START DATABASE command, the type of drain activity, if any, on the table space or partition, and whether any cursors are being held on the table space or partition.

Do not start table spaces or index spaces for defined temporary tables with RO or UT access. You can start a temporary file database with UT access to accommodate the REPAIR DBD utility.

If the table space, index, or partition must be accessed in a mode that is incompatible with the ACCESS type currently in effect, DB2 issues a resource-unavailable message.

For shared-owner databases, a STOP DATABASE command must be issued to quiesce a database or table space prior to issuing the START DATABASE command.

Start of change Starting a table space partition in PRO restricted status: When a table space that is in Persistent Read Only (PRO) restricted status is started, the partition remains in PRO restricted status.End of change

Communications database or resource limit facility: If the communications database (CDB) or resource limit facility (RLF) is currently being used by any member of the data sharing group, any attempt to start either active database or table space with ACCESS(UT) fails.

Synchronous processing completion: Message DSN9022I indicates that synchronous processing has completed successfully.

Asynchronous processing completion: Recovery of objects in GRECP status or with pages on the LPL is performed asynchronously. Message DSNI022I is issued periodically to give you the progress of the recovery. The starting of databases, table spaces, or indexes (a synchronous task) often completes before the recovery operation starts. Therefore, when DB2 issues message DSN9022I, which indicates that synchronous processing has completed, the recovery of objects might not be complete.

Message DSNI006I is issued in response to the START DATABASE command when the object (table space or index space) that is identified by TYPE and NAME has group buffer pool recovery pending (GRECP) or logical page list (LPL) status, and recovery was triggered. The START DATABASE command does not complete until the asynchronous task of recovery completes.

Message DSNI021I indicates that asynchronous processing for an object has completed. You can issue the command DISPLAY DATABASE to determine whether the recovery operation for all objects is complete. If recovery is complete, the output from the command shows either a RW or a RO status without LPL or GRECP.

Starting a LOB table space: The START DATABASE command can be used to start LOB table spaces and indexes on auxiliary tables. LOB table spaces are started independently of the base table space with which the LOB table space is associated.

Examples

Example 1: Start table space DSN8S81E in database DSN8D81A. Recover the table space if it is in GRECP status or recover the pages on the LPL if one exists.
-START DATABASE (DSN8D81A) SPACENAM (DSN8S81E)
Example 2: Start all databases (except DSNDB01, DSNDB06, and work file databases) for which you have authority. Recovery for any objects with GRECP or LPL status is not performed.
-START DATABASE (*)
Example 3: Start the third and fourth partitions of table space DSN8S81E in database DSN8D81A for read-only access. Recover the partitions if they are in GRECP status or recover the pages on the LPL if one exists.
-START DATABASE (DSN8D81A) SPACENAM (DSN8S81E) PART (3,4) ACCESS (RO)
Example 4: Start all table spaces that begin with "T" and end with the string "IQUA03" in database DBIQUA01 for read and write access.
-START DATABASE (DBIQUA01) SPACENAM (T*IQUA03) ACCESS (RW)
This command produces output that is similar to the following output:
DSN9022I - DSNTDDIS 'START DATABASE' NORMAL COMPLETION

Example 5: Start clone objects.

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