Area-Level DBUTLTY Control

Products

Datacom/DB Datacom/AD Datacom

Issue/Introduction

Database or Area Level of DBUTLTY Control

Most databases contain a set of tables that are related and interdependent. That is, all must be available to an application or none are useful. For these databases, an area level of controls is of no value and should not be used. Some databases do contain tables in different areas that are independent and need high availability. With this feature, it is possible for some areas in a database to be open to applications in the Multi-User Facility (MUF) while other areas are being processed by DBUTLTY with functions that need total control of the area (for example, an area load). This feature provides significant functionality when combined with table partitioning, also in r11.

Previously, all controls have existed at the full database level. The definition in the Directory (CXX), the index, and all data areas were protected as a set. This new feature is accomplished by bringing a closer integration between the Multi-User Facility and DBUTLTY functions that are normally independent. With this feature, it is possible to be processing tables in several areas of a database in Multi-User, and at the same time, loading other areas and rebuilding the index (RETIX) of yet other areas.

Environment

Release:
Component: DB

Resolution

How it Functions

The definition in the Directory (CXX) is opened for update in the Multi-User and totally controlled there. The index is opened for update in the Multi-User and totally controlled there. Each data area in the database can be controlled for update in either the Multi-User or DBUTLTY. Operationally, there are requirements needed for the Multi-User and some for the DBUTLTY. These will be discussed, by function, in a moment.

The area level control for a data area is requested by specifying MULTUSE=YES in the following DBUTLTY functions: INIT, EXTEND, LOAD, BACKUP, EXTRACT, RETIX, REORG, and RECOVERY. The MULTUSE=YES parameter does not have the same area level control meaning for other DBUTLTY functions, such as MASSADD.

ACCESS at the Area Level

Each area of a database has an ACCESS status. The ACCESS status is: WRITE, READ, OFF and new in r11 UTLTY. The ACCESS status controls the ability to open a table in the Multi-User Facility. The new area level controls cannot allow any access that would violate existing database level controls. The control at the database level functions as it has for years and is not affected by the new area level controls in any way.

Each DBUTLTY function - whether run with MULTUSE=YES or not - protects the resources it needs for its operation and will not run inappropriately. However, DBUTLTY cannot ensure the success of a sequence of DBUTLTY functions on its own. If you want to ensure that no other task gets control of a data area between a set of functions in your DBUTLTY job, it should request that ACCESS be set to UTLTY ahead of the set and reset to WRITE after the set. The user requested access is not required for the success of the different data area functions but is useful in setting the environment to allow successful DBUTLTY execution.

An example of the benefit of a user-requested area access illustrates this point:

The goal: to BACKUP a data area and then LOAD it while other areas are open in MUF for processing
The process: the BACKUP function will verify it can run and protect its success
The BACKUP does this by ensuring that the area in MUF is neither currently restricted by a user requested area access of OFF nor by another DBUTLTY requested access of UTLTY
If the requirements are met, the BACKUP requests an area level ACCESS status of READ for the area
At the completion of the BACKUP, the DBUTLTY requested access is released (defaults back to WRITE)
The next function, LOAD, ensures that the area in MUF is neither currently restricted by a user access of OFF/READ nor by another DBUTLTY requested access of UTLTY
If the requirements are met, the LOAD requests an area level ACCESS status of UTLTY
After execution of the function, the DBUTLTY requested access is released (defaults back to WRITE)
If the requirements were not met for the LOAD, such as a user job was running with an UPDATE=NO (read) URT, then the LOAD will fail with a return code. The DBUTLTY functions cannot protect this window between the BACKUP and the LOAD, but a user-requested area ACCESS of UTLTY ensures the backup and load sequence is protected.
The way to always ensure that the LOAD will be able to run after the BACKUP is to issue the DBUTLTY ACCESS STATUS=UTLTY,DBID=nnnnn,AREA=aaa before the BACKUP and issue ACCESS STATUS=WRITE,DBID=nnnnn,AREA=aaa after the LOAD

The following sections explain what happens for various DBUTLTY functions using the area level controls.

BACKUP with MULTUSE=YES, EXTRACT with MULTUSE=YES, and REORG (backup phase)

The functions BACKUP, EXTRACT, and the backup phase of REORG use MUF to access the index and also the data area. They will set the DBUTLTY requested area access status to READ after ensuring no other DBUTLTY requested status of UTLTY and no user requested status of OFF exist. Because the index and data are read in the MUF address space, the utility benefits if either are defined as COVERED.

INIT or EXTEND with MULTUSE=YES

The INIT and EXTEND functions use MUF only for Directory (CXX) controls. They will set the DBUTLTY requested area access status to UTLTY after ensuring no other DBUTLTY requested status of READ or UTLTY, and no user requested status of OFF or READ exist. If the data area is open in MUF (because a previous job caused it to be opened), then it will be automatically closed in MUF. However, sometimes the INIT or EXTEND will have the need to un-catalog the dataset. If MUF has it open, this will not be accepted. So, if the area needs to be scratched or un-cataloged, then a COMM OPTION=CLOSE,DBID=n,AREA=aaa must be done before the INIT or EXTEND to cause MUF to close the single area so it can be processed by the DBUTLTY step.

NOTE: The Operating System performs the catalog function at step end, so no function that requires MUF to open the data area before step end will be successful.

LOAD, RETIX, and load phase of REORG with MULTUSE=YES

These functions use MUF for both Directory (CXX) controls and the index. They will set the DBUTLTY requested area ACCESS status to UTLTY after ensuring no other DBUTLTY requested status of READ or UTLTY and no user requested status of OFF or READ exist. If the data area is open in MUF, it will be automatically closed in MUF. The DBUTLTY function will open the data area in its address space and perform all data area I/O there. The index will be built or updated in MUF after the data is loaded by the utility passing 16 to 32k blocks of index information to MUF, and having it update the index.

Forward RECOVERY with MULTUSE=YES

This function uses MUF for Directory (CXX) controls, the index, and the data. It will set the DBUTLTY requested area ACCESS status to UTLTY after ensuring no other DBUTLTY requested status of READ or UTLTY, and no user requested status of OFF or READ exist. This set of options to RECOVERY cause it to work at the area level similar to LOAD, RETIX, and most other DBUTLTY functions. Like those other functions, forward recovery with MULTUSE=YES will allow a single area per execution. No logging occurs during this MULTUSE=YES forward recovery. Without the MULTUSE=YES keyword, forward recovery works at either the table level or the database level.

The following chart summarizes the information presented above:

DBUTLTY function	Areas accessed by MUF for the function	DBUTLTY area-level ACCESS requirement for data area in order to proceed	User-requested area-level ACCESS requirement for data area in order to proceed	DBUTLTY area-level ACCESS status set during function	Comments	Comments
BACKUP MULTUSE=YES	Directory (CXX), Index, Data	No other DBUTLTY has ACCESS=UTLTY	No ACCESS=OFF on the data area	READ	Data and index are read in MUF, so covering will help.
EXTRACT MULTUSE=YES	Directory (CXX), Index, Data	No other DBUTLTY has ACCESS=UTLTY	No ACCESS=OFF on the data area	READ	Data and index are read in MUF, so covering will help.
REORG (backup phase) MULTUSE=YES	Directory (CXX), Index, Data	No other DBUTLTY has ACCESS=UTLTY	No ACCESS=OFF on the data area	READ	Data and index are read in MUF, so covering will help.
INIT MULTUSE=YES	Directory (CXX)	No other DBUTLTY has ACCESS=READ or UTLTY	No ACCESS=OFF or READ	UTLTY	If data area is open in MUF, it will be closed in MUF before function. DBUTLTY region will open data area.	If data area needs to be uncatalogged during the process, issue COMM OPTION=CLOSE, DBID=n,AREA=aaa beforehand.
EXTEND MULTUSE=YES	Directory (CXX)	No other DBUTLTY has ACCESS=READ or UTLTY	No ACCESS=OFF or READ	UTLTY	If data area is open in MUF, it will be closed in MUF before function. DBUTLTY region will open data area.	If data area needs to be uncatalogged during the process, issue COMM OPTION=CLOSE, DBID=n,AREA=aaa beforehand.
LOAD MULTUSE=YES	Directory (CXX), Index	No other DBUTLTY has ACCESS=READ or UTLTY	No ACCESS=OFF or READ	UTLTY	If data area is open in MUF, it will be closed in MUF before function. DBUTLTY region will open data area. MUF opens index area.
RETIX MULTUSE=YES	Directory (CXX), Index	No other DBUTLTY has ACCESS=READ or UTLTY	No ACCESS=OFF or READ	UTLTY	If data area is open in MUF, it will be closed in MUF before function. DBUTLTY region will open data area. MUF opens index area.
REORG (load phase) MULTUSE=YES	Directory (CXX), Index	No other DBUTLTY has ACCESS=READ or UTLTY	No ACCESS=OFF or READ	UTLTY	If data area is open in MUF, it will be closed in MUF before function. DBUTLTY region will open data area. MUF opens index area.
RECOVERY OPTION=FORWARD, MULTUSE=YES	Directory (CXX), Index, Data	No other DBUTLTY has ACCESS=READ or UTLTY	No ACCESS=OFF or READ	UTLTY	RECOVERY OPTION=FORWARD, MULTUSE=YES is limited to a single data area and logging is not done.

Additional Information

Area Level DBUTLTY documentation