Outline

• Recovery Restart
• Quiz
• DB2 Commands, Tools, and Utilities

Introduction

• Last week, we talked about archiving in DB2, the COPY utility, and the RECOVER utility.
• Then we started talking about installing DB2 and the start-up sequence for DB2.
• DB2 shutdown was then presented, followed by the restart process after a normal shutdown.
• Today, we will begin by looking at the restart process after an abnormal shutdown.
• Then we will talk about DB2 commands, tools, and utilities.

Recovery Restart

• We have already seen the case where DB2 goes down normally following a STOP DB2 command.
• Now, let's consider the more interesting case where the failure occurs unexpectedly...
• In reality, we're most likely to work in a more complex environment, where DB2 interacts with applications running on other platforms.
• In such a distributed system, the actions of a logical unit of work might occur in more than one system.
• When these actions update recoverable resources, the commit process ensures that either all the effects of the logical unit of work persist or that none of the effects persist, despite component, system, or communications failures.
• Let's look at the four phases of start-up for this situation.

Phase 1 -- Log Initialization

• DB2 begins by checking that both BSDSs have the same timestamp and correct ICFCAT (Integrated Catalog Facility). If this is true, the startup proceeds.
• DB2 finds the end of log by finding the high-water RBA (relative byte address ) in the BSDS and then reading forward until it hits EOL ... the end of the log.
• DB2 now schedules any needed archiving (left over from when DB2 was up). This could involve multiple mount requests.

Phase 2 -- Status of Transactions at Failure

• CSR (Current Status Rebuild) will rebuild the central storage control blocks representing the units of recovery and page sets.
• The basic procedure is to start with the most recent checkpoint (found in the BSDS Checkpoint History) to establish a base set of control blocks.
• DB2 then reads the log in the forward direction to update the control blocks with the status change records on the log.

More on Phase 2

• In phase 2, DB2 also determines the status of all Units of Recovery, and the status of page sets/partitions at the point of crash
• It identifies the starting log RBA for Phase 3 (Checkpoint preceding oldest pending write or oldest INDOUBT UR -- we'll say what INDOUBT means in a moment (related to distributed setting))
• It finally identifies the ending log RBA for Phase 4 (Oldest INFLIGHT or INABORT UR -- again, we'll describe these in a moment).

Phase 3 -- Forward Log Recovery

• In phase 2, DB2 knew where to begin forward log processing by using the page set control blocks.
• These contain the RBA of the checkpoint preceding the oldest pending write associated with that page set.
• Phase 3 will commence with the oldest of those oldest checkpoint addresses.
• Since this might involve reading quite far back in the log, you want to have your checkpoints be reasonably frequent, at least twice hopefully per archive.
• During the forward log read of Phase 3, the log is read forward from checkpoint preceding oldest pending write.
• Qualified REDO log records for all UR (Unit of Recovery) whose status is completed, in-commit, in-doubt, in-flight or postpone-abort are applied.

Two Phase Commit & Abend States

In a distributed setting that a several new conditions a transaction might be in when a failure occurs...

• INFLIGHT -- The participant or coordinator failed before finishing Phase 1 commit; during restart, both systems back out the updates.
• INDOUBT -- The participant failed after finishing Phase 1 and before starting Phase 2; only the coordinator knows whether the failure happened before or after the commit. If it happened before, the participant must back out its changes; if it happened afterward, it must make its changes and commit them. After restart, the participant waits for information from the coordinator before processing this unit of recovery.
• INCOMMIT -- The participant failed after it began its own phase 2 processing; it makes committed changes.
• INABORT -- The participant or coordinator failed after a unit of recovery began to be rolled back but before the process was complete (not shown in the figure). The operational system rolls back the changes; the failed system continues to back out the changes after restart.
• COMPLETED commit/abort -- DB2 fails after completion of "end phase 2 commit". During DB2 restart all data updates will be reapplied and externalized.
• POSTPONED ABORT -- If the LIMIT BACKOUT installation option is set to YES or AUTO, any back out not completed during restart is postponed. The status of the incomplete URs is changed from INFLIGHT or INABORT to POSTPONED ABORT

Phase 4 -- Backward Log Recovery

• Phase 4 is like Phase 3 except that we read the log backwards and that now we process threads that were INABORT or INFLIGHT.
• Phase 4 is fundamentally different in that it not only reads the log but also writes the log in the forward direction. The log write activity is for the Compensation Log Records (CLRs).
• Note that some long URs may not be fully backed out if consistent restart options have been chosen (LBACKOUT= YES | AUTO, BACKODUR). These URs will show up as POSTPONED ABORT and will have to be finished later.
• This phase might require archives to be mounted
• Phase 4 externalizes buffer updates.
• It writes startup checkpoint
• After this, DB2 is available for new work.

Quiz

Which of the following statements is true?

1. To bring DB2 back when a full system backup was done you need to use RECOVER SYSTEM.
2. Information about image copies of table spaces is not stored in the catalog.
3. Very little work is done in Phase 3 and 4 of a normal DB2 Restart.

Final Topic -- DB2 Commands, Utilities and Tools

• We are now going to start our final topic for this semester.
• We are going to look at DB2 commands, Utilities, and Tools.

DB2 Environment Command Examples

• DB2 "environment" commands consists of three parts: Command prefix, Command, and Command operand.
• The prefix is the character associated with the DB2 subsystem when it is installed onto z/OS. We will - in the examples below.
• There are three main categories of commands:
  • Information-gathering commands: For example, -DISPLAY DATABASE, -DISPLAY BUFFERPOOL, -DISPLAY UTILITY
  • Administrative commands: For example, -START TRACE, -ALTER BUFFERPOOL (BP0), -TERM UTILITY, -RECOVER INDOUBT
  • Environment control commands: For example, -START/STOP DB2, -START/STOP DDF

Where Are Environment Commands Issued?

• z/OS console -- Only the z/OS operator has the access to the console
• An application program, using the DB2 Instrumentation Facility Interface (IFI)
• TSO (Time Sharing Option) -- could be done using: online TSO, DB2I or batch TSO, under the control of DSN.

TSO -- Time Sharing Option

• TSO enables z/OS users to work with a panel-driven online interface that is panel-driven by ISPF (Interactive System Productivity Facility).
• DB2 commands can be issued from TSO in two ways:
  • Online, in the TSO foreground, driven by application programs, CLISTs, or REXX EXECs coded to communicate with DB2 and TSO, using ISPF panels. Otherwise, you can use DB2I an online, TSO/ISPF based interface to DB2 commands. It consists of a series of ISPF panels, programs, and CLISTs enabling quick access to DB2.
  • In batch mode using the TSO terminal Monitor Program (TMP), IKJEFT01 (or IKJEFT1B), to invoke the DSN command.
• The following JCL shows a batch TSO under the control of the DSN command processor:
  

  //STATRACE  EXEC PROGRAM=IKJEFT01
  //SYSTSIN DD *
    DSN S(DB2A) R(1) T(1) 	
   -STA TRACE(P) CLASS(6,7)     DEST(SMF)
   -DIS TRACE(*) 
  END /*
  
  

DSN

• DSN is a command processor that enables the user to issue DB2 commands from the following context: DB2I panels, A TSO session, A batch job
• Commands include: Environment Commands, BIND, DCLGEN, FREE, REBIND, RUN, SPUFI, END.

Issue a DB2 Command

Display Buffer Pool

Buffer Pool Status

• As you can see, there are two buffer pools: BP0 and BP8K0.
• In DB2, 4-KB page buffer pools are named BP0, BP1, ..., BP49. 8-KB page buffer pools are named BP8K0, BP8K1, ..., BP8K9.
• BP0 has 2000 pages. 5 pages are in-use/updated. 236 pages are active.
• VP SEQUENTIAL specifies the sequential steal threshold for the buffer pool. This value is expressed as a percentage of the total buffer pool size, and valid values range from 0 to 100. This threshold affects the allocation of buffers in the buffer pool to page read requests that are part of a sequential access pattern. This includes pages being prefetched. If the number of buffers that contain sequentially accessed pages exceeds the threshold, a sequential request attempts to reuse one of those buffers rather than a buffer that contains a non-sequentially accessed page.
• VERTICAL DEFERRED WRT specified the threshold that determines when deferred writes begin, based on the number of updated pages for a given data set. Deferred writes begin for that data set when the count of updated buffers for a data set exceeds the threshold.

CICS Subsystem Commands on DB2

• All CICS commands that pertain to DB2 are prefixed with DSNC. DSNC is a CICS transaction that enables the execution of DB2 commands from a CICS terminal.
• CICS commands must be issued from a valid terminal connected to CICS by an authorized CICS user.
• Some examples commands are: DISPLAY, DISCONNECT, STOP, STRT
• The DISPLAY command displays information on CICS transactions accessing DB2 data.
• The DISCONNECT command provides manual control to release resources being shared by normal transactions so that special purpose processes, such as utilities, can have exclusive access to the resources.
• The DSNC STOP command stops the attachment facility.
• The DSNC STRT command starts the CICS attachment facility, which allows CICS application programs to access DB2 databases.

Display Utilities

• This command will display current active utilities.

DB2 Tools

• DBAs are simply expected to keep DB2 up and running.
• He or she has to perform a wide range of tasks which often require complicated operations.
• Those tasks include issuing DB2 commands, running DB2 utilities, managing authorization, managing daily operations, and being prepared to recover from any potential errors or problems.
• Several management tools are available to help DBAs to perform these tasks.

Tools Make DBA's Job Easier

DB2 has many useful tools for a variety of tasks:

• Database administration -- IBM Data Studio and the older DB2 Control center
• SQL tuning -- DB2 for z/OS Optimization Service Center, DB2 for z/OS Optimization Expert
• Backup and recovery
• Monitor and reporting
• Most of these have a GUI interface and one can manage multiple systems from a single console.