Administrative Commands

Administrative commands are provided to assist the user with the active administration, resource specification, and environment modification of DB2 subsystems. Each command modifies an environmental aspect of the DB2 subsystem. The administrative commands are as follows :

-ALTER BUFFERPOOL	Used to alter buffer pool size , thresholds, and CASTOUT attributes for active and inactive buffer pools.
-ALTER GROUPBUFFERPOOL	Used to alter the attributes of group buffer pools.
-ALTER UTILITY	Can change the value of some parameters for the REORG utility.
-ARCHIVE LOG	Forces a DB2 log archival.
-CANCEL THREAD	Cancels a local or distributed DB2 thread
-MODIFY TRACE	Changes the specifications for active DB2 traces.
-RECOVER BSDS	Re-establishes a valid Boot Strap Data Set after an I/O error on the BSDS data set.
-RECOVER INDOUBT	Recovers in-doubt threads that cannot be recovered automatically by DB2 or the appropriate transaction manager.
-RECOVER POSTPONED	Completes backout processing for units of recovery that are left incomplete during an earlier restart ( POSTPONED ABORT units of recovery). To be used when automatic resolution was not selected.
-RESET GENERICLU	Purges information stored by VTAM in the coupling facility.
-RESET INDOUBT	Purges information from the "in doubt" thread report (generated by the -DISPLAY THREAD command).
-SET ARCHIVE	Used to set the parameters for log archiving.
-SET LOG	Modifies the checkpoint frequency. The changes that SET LOG makes are temporary; at restart, DB2 again uses the values that were specified when DB2 was installed. The new LOGLOAD value takes effect following the next system checkpoint.
-SET SYSPARM	Modifies DSNZPARM parameters without recycling DB2.
-START DATABASE	Starts a stopped database, table space, table space partition, index, or index partition or changes the status of these objects to RW , RO , or UT .
-START FUNCTION SPECIFIC	Starts an external UDF that is stopped. Not to be used for built-in functions or UDFs that are sourced on another function.
-START PROCEDURE	Starts a stored procedure enabling subsequent execution using the CALL statement.
-START RLIMIT	Starts the Resource Limit Facility with a specific Resource Limit Specification Table (RLST).
-START TRACE	Activates DB2 traces, classes, and IFCID s; specifies limiting constraints for plans and authids; and specifies the output destination for the activated trace records.
-STOP DATABASE	Stops a database, a table space, or an index and closes the underlying VSAM data sets associated with the stopped object. As of DB2 V3, partitions can be stopped individually.
-STOP FUNCTION SPECIFIC	Stops an external UDF disabling subsequent execution. Not to be used for built-in functions or UDFs that are sourced on another function.
-STOP PROCEDURE	Stops a stored procedure disabling subsequent execution.
-STOP RLIMIT	Stops the Resource Limit Facility.
-STOP TRACE	Stops the specified DB2 traces and classes.
- TERM UTILITY	Terminates the execution of an active or a stopped DB2 utility, releases all the resources that are being utilized by the utility, and cleans up the DB2 Directory.

Administrative Command Guidelines

When you issue administrative commands, you are actually changing the DB2 environment. Administrative commands should be used with caution. Review the following guidelines before utilizing administrative commands.

Educate the Users of Administrative Commands

Only an experienced analyst who knows the DB2 commands and their effect on the DB2 subsystem and its components should issue administrative commands. Such control should be accomplished by implementing strict DB2 authorization procedures.

Use ALTER BUFFERPOOL to Dynamically Manage Bufferpools

The ALTER BUFFERPOOL command can be used to dynamically change the size and characteristics of a buffer pool. The following parameters can be used to change the buffer pool using ALTER BUFFERPOOL :

VPSIZE	Size of the virtual buffer pool
HPSIZE	Size of the associated hiperpool
VPSEQT	Virtual pool sequential steal threshold
HPSEQT	Hiperpool sequential steal threshold
VPPSEQT	Virtual pool parallel sequential steal threshold
VPXPSEQT	Virtual pool assisting parallel sequential steal threshold
DWQT	Virtual pool deferred write threshold
VDWQT	Virtual pool vertical deferred write threshold (by data set)
CASTOUT	Hiperpool dirty page discard
VPTYPE	Whether the buffer pool is allocated in the DB2 database services address space ( PRIMARY ) or in a data space associated with DB2 ( DATASPACE )

Use ALTER UTILITY to Impact REORG Processing

The ALTER UTILITY command can be used to change the value of the DEADLINE , MAXRO , LONGLOG , and DELAY parameters for REORG utilities running SHRLEVEL REFERENCE or SHRLEVEL CHANGE . Refer to Chapter 33, "Data Organization Utilities," for more information on the functionality of these parameters.

Use ARCHIVE LOG to Synchronize Disaster Recovery Plans with DB2

Issue the ARCHIVE LOG command to synchronize DB2 log archival and copying with application and DB2 Catalog image copies sent to a remote site for disaster recovery. See Chapter 38, "DB2 Contingency Planning," for further guidance.

Use ARCHIVE LOG to Synchronize New Logs with Shift Changes

Sometimes a new active DB2 log should begin at the commencement of each new operational shift. This can be accomplished with the ARCHIVE LOG command.

Use RECOVER INDOUBT with Caution

The RECOVER INDOUBT command can abort or commit changes made by in-doubt threads. Be cautious before committing in-doubt threads. Most DB2 programs are coded to process updates in commit scopes defined as a unit of work.

The unit of work, as described in Chapter 11, "Using DB2 in an Application Program," is coded as much as possible to maintain data integrity between related tables. If the RECOVER INDOUBT command commits changes for a partial unit of work, the affected tables may not be in a consistent state. If database-enforced referential integrity is always used, this is not a concern because the database forces the tables to be in a consistent state. However, very few applications require that every referential constraint be explicitly defined and enforced by DB2.

Avoid Using ACCESS(FORCE)

Issuing the START DATABASE command with the ACCESS(FORCE) option is not recommended because it may cause table spaces or indexes to be in an inconsistent state. ACCESS(FORCE) forces all pending flags (check, copy, and recover) to be reset for the specified object. Never use ACCESS(FORCE) unless you are absolutely sure that the data is in a consistent state for the specified object (for example, after restoring objects using the DSN1COPY service aid utility).

To be safe, never use ACCESS(FORCE) . Instead, use the appropriate utility to reset the exception flags.

Ensure That DASD Is Online Before Stopping Databases

The DASD volume for the underlying VSAM data sets for the object that will be started by the START DATABASE command does not need to be online when the START command is issued. Because the STOP DATABASE command closes the underlying VSAM data sets, however, the corresponding volume for that object must be online when the STOP command is issued.

Start and Stop at the Partition Level

The START and STOP commands can be executed for partitioned table spaces and indexes at the partition level. This functionality enhances availability by enabling users to stop only portions of an application (table space or index).

Be Aware of the Time Constraints of the STOP Command

The STOP command can be used to close VSAM data sets and cause buffer pages associated with the closed data set to be flushed and forced to DASD. The VSAM close operation may take a while before it is complete, though. The buffers may not be flushed completely to DASD immediately after the STOP DATABASE command completes. Subsequent processing must consider this fact.

Explicitly Start Objects Stopped with the SPACENAM Parameter

When a table space or index is explicitly stopped using the SPACENAM parameter of the STOP DATABASE command, it must be explicitly started again before it can be accessed. Starting at the database level will not affect the status of explicitly stopped table spaces or indexes.

Use START PROCEDURE Before Calling

The START PROCEDURE command must be issued for each DB2 stored procedure prior to any application calling the stored procedure. Failure to start a stored procedure before trying to execute it with the CALL statement results in the CALL statement failing.

Use the ACTION Clause When Stopping Stored Procedures

The stop command disables subsequent executions of the named stored procedure. The ACTION clause can be specified to indicate whether future attempts to run the stored procedure will be entirely rejected [ACTION(REJECT)] or queued [ACTION(QUEUE)] to be run when the stored procedure is started again.

Use START RLIMIT to Vary Resource Limits

START RLIMIT can use different resource limit specification tables (RLST) with different limits. By specifying the ID parameter, a specific RLST is chosen ; for example

 -START RLIMIT ID=02 

starts the RLF using the SYSIBM.DSNRLS02 table. This enables different limits to be specified for

• Different times of the day

• Batch and online processing

• Heavy and light ad hoc processing

Use START TRACE to Specify Trace Destinations

When issuing the START TRACE command, each type of trace can specify different destinations for the trace output. The following lists destinations for each type of trace:

Trace Destination	Trace Types
GTF	ACCTG , AUDIT , GLOBAL , MONITOR , PERFM , STAT
OP n	ACCTG , AUDIT , GLOBAL , MONITOR , PERFM , STAT
OPX	ACCTG , AUDIT , GLOBAL , MONITOR , PERFM , STAT
RES	GLOBAL
SMF	ACCTG , AUDIT , GLOBAL , MONITOR , PERFM , STAT
SRV	ACCTG , AUDIT , GLOBAL , MONITOR , PERFM , STAT

Use START TRACE to Specify Constraints

When you issue the START TRACE command, each type of trace can place optional constraints on the data to be collected. The following lists constraints for each type of trace:

Constraint Type	Trace Types
AUTHID	ACCTG , AUDIT , GLOBAL , MONITOR , PERFM
CLASS	ACCTG , AUDIT , GLOBAL , MONITOR , PERFM , STAT
PLAN	ACCTG , AUDIT , GLOBAL , MONITOR , PERFM
RMID	GLOBAL , MONITOR , PERFM

Use No More Than Six Active Traces

Although as many as 32 traces can be active at one time, you should limit the number of active traces to 6 to avoid performance degradation. Add this recommendation to the trace guidelines presented in Chapter 24 to establish the proper controls for issuing DB2 traces.

Be Aware of the Authority Required to Terminate Utilities

To terminate utilities, the issuer of the TERM UTILITY command must meet one of the following requirements. The issuer must

• Be the user who initially submitted the utility

• Have SYSADM , SYSCTRL , or SYSOPR authority

If your operational support staff must have the ability to terminate utilities that they did not originally submit, they should be granted SYSOPR authority. However, SYSOPR authority permits the user to start and stop DB2, which is not generally acceptable because the uncontrolled issuing of these commands can wreak havoc on a production system. There is no viable alternative to SYSOPR authority, though, because explicit TERM UTILITY authority is unavailable.

Avoid Using Wildcards When Terminating Utilities

When terminating utilities, explicitly specify the UID to be terminated , rather than use the -TERMINATE UTILITY command to terminate all utilities invoked by your ID. When you explicitly specify what should be terminated, you avoid inadvertently terminating an active utility. After a utility is terminated, it can never be restarted. The utility must be rerun from the beginning and may require data recovery before rerunning.

Use Caution When Changing System Parameters "On The Fly"

Changing system parameters can have a dramatic impact on the manner in which your DB2 subsystem operates. Before changing any DSNZPARM system parameters using the SET SYSPARM command, be sure to examine the parameter in “depth and understand the effect the change will have on your DB2 subsystem. Also, be aware that not every system parameter can be changed dynamically. Consult the IBM DB2 Installation Guide (Appendix C) for a complete list of system parameters, including information regarding whether or not each parameter can be changed dynamically.