|
|
Possibilities
88
Reload a New Table
131
Reload Rows into an Existing Table
132
Reload a Table with Full Environment
Chapter 8. Backing Up an Entire
Recreation
133
Database
91
Reload Tables With Forward Recovery From the
Deciding How to Backup Your Database
91
Log
135
Choosing DB2 Server for VSE & VM Database
Step 3. List the Changes Extracted from the Log
Archives
92
Files
140
Backup Procedures
92
When You Need to Execute the LISTLOG
Using Data Restore for Database Backups . . . 92
Function
140
Step 1. Stop the DB2 Server for VSE & VM
Using the LISTLOG Command
141
Server Before Backing Up Your Database . . . 93
Example of a Report from LISTLOG Execution
142
Step 2. Processing a Data Restore Backup . . . 93
Step 4. Apply LUWs Referenced in the Log to the
Step 3. Restarting the Database After Backing
Reloaded Tables
143
Up your Database
101
Using the APPLYLOG Command
143
Execute the APPLYLOG Function
144
Chapter 9. Restoring an Entire
Example of a Report from APPLYLOG
145
Database or an Entire Storage Pool . .
103
Procedures to Recover from Failures
103
Chapter 12. Accessing Data Whether
Recover from Directory Failure
103
the Server is Up or Down
147
Recover from Database Corruption
104
Introduction
147
Data Recovery
104
Description
147
Recover from System Failure
105
Using the SELECT Function in a VSE Environment
148
Step 1. Stop the Application Server
107
Using the SELECT Function in a VM Environment
149
Step 2. Decide Whether to Restore from the Last
Displaying the Results of the SELECT Function . . 149
Archive
108
Step 3. Determine If You Need to Reformat a
Chapter 13. Displaying the Contents
DBEXTENT
108
Step 3A. Using the FORMAT Function (VSE
of an Archive File
151
Only)
108
Introduction
151
Using the FORMAT Command
108
Description
151
Step 4. Starting the RESTORE Function
109
Using the DESCRIBE Command
151
Step 4A. Restoring an Entire Database Using the
Last Archive
109
Chapter 14. Displaying Dbspace
Step 4B. Restoring Specific Storage Pools Using
Information
155
the Last Archive
111
Introduction
155
Step 4C. Restoring an Entire Database Using
Description
155
Any Archive
112
Using the SHOWDBS Command
155
Step 4D. Restoring Specific Storage Pools Using
Report for SHOWDBS Function
157
Any Archive
114
Report for SHOWDBS Function (Summary Report)
157
Step 4E. Restoring Using INCREMENTAL
Interpreting the SHOWDBS Reports
158
Archive
116
Step 5. Deciding Whether to Use Log Recovery .
119
Chapter 15. Displaying Pool
Organization
161
Chapter 10. Backing Up Parts of a
Introduction
161
Database
121
Description
161
Using the UNLOAD Command
121
Using the SHOWPOOL Command
161
Unloading All Dbspaces
121
Report for SHOWPOOL Function
162
Interpreting the Output From the SHOWPOOL
Chapter 11. Restoring Logical
Function
162
Elements
125
Using the SHOWPOOL report
163
Recovery From a Logical Error
125
Step 1. Process a Standard DB2 Archive with Data
Part 3. Reference
165
Restore
126
Procedures to Translate an Archive into a
Chapter 16. Command Syntax
167
BACKUP File
127
Example of a Report from TRANSLATE . .
128
OPTIONS and CONTROL Statements
167
Step 2. Reload Procedures
129
OPTIONS Statement Parameters
168
Using the RELOAD Command
130
CONTROL Statement Parameters
170
Reload a Table after Deleting Existing Rows .
130
APPLYLOG
172
iv Data Restore Guide
BACKUP
173
In VM
236
DESCRIBE
174
Using the SELECT Function
237
FORMAT
175
Using the SHOWDBS Function
241
LISTLOG
176
Using the SHOWPOOL Function
241
RELOAD
177
Using the TRANSLATE Function
242
RESTORE
179
Using Control Center
242
SELECT
180
DB2 Archives
249
SHOWDBS
181
Using the UNLOAD Function
249
SHOWPOOL
182
SHOWPTFH
183
Appendix C. Problem Determination
255
TRANSLATE
184
Introduction
255
UNLOAD
186
Part 1. System Errors
255
VM Environment - Possible Problems:
255
Part 4. Appendixes
189
VSE Environment
261
Part 2. DB2 Errors
263
Both VM and VSE Environments
263
Appendix A. Messages and Codes
191
Part 3. Data Restore Feature Errors
264
Recover from a DB2 Server for VM Error during
Appendix B. Command Examples
203
Full Environment Recreation
266
Using the BACKUP Function
203
Reload Tables With Forward Recovery From the
In VSE
203
Log
268
In VM
203
Problem Determination when Reloading Tables
268
Using the DESCRIBE Function
203
Using the FORMAT Function
206
Notices
271
Using the RELOAD Function
207
Trademarks
273
Example of a Report from RELOAD . .
208
RELOAD Examples using VM
211
Bibliography
275
RELOAD Examples using VSE
216
Using Control Center
218
Examples of Reloading Tables in a VSE
Index
279
Environment
224
Examples of Reloading Tables in a VM
Contacting IBM
283
Environment
229
Product information
283
Using the RESTORE Function
234
In VSE
234
Contents v
About This Book
|
This book shows how to use the Data Restore feature of the IBM DATABASE 2 for
|
VSE and VM (DB2) Version 7 Release 3. This book contains a description of the
|
tasks associated with the use of Data Restore in a Virtual Machine/Enterprise
|
System Architecture (VM/ESA) environment or a Virtual Storage
|
Extended/Extended System Architecture (VSE/ESA) environment.
Who Should Use This Book
|
This book is a guide and reference for users of the Data Restore feature of DB2
|
Server for VSE & VM Version 7 Release 3, particularly database operators and
|
administrators who maintain DB2 Server for VSE & VM databases.
How to Use This Book
This book describes Data Restore and shows how and when to use it.
Organization
This book is organized as follows:
v
“Summary of Changes” on page xiii lists changes to the product since Version
6
Release 1.
v
Chapter 1, “How to Recover from Failures” on page 3 describes some of the
errors you can encounter and how to recover.
v
Chapter 2, “Recovery Strategies” on page 7 gives you recommendations and
scenarios to choose from to define your own archive and recovery strategy.
v
Chapter 3, “Archive and Recovery” on page 23 describes the options provided to
back up a complete DB2 database and restore its data.
v
Chapter 4, “Installing Data Restore” on page 33 provides the necessary steps for
installing Data Restore in any of the following environments: VM/ESA,
VSE/ESA, or VSE Guest Sharing.
v
Chapter 5, “Migration” on page 67 provides the necessary steps for migrating
Data Restore in any of the following environments: VM/ESA, VSE/ESA, or VSE
Guest Sharing.
v
Chapter 6, “How to Apply Service” on page 69 describes the steps required to
apply service to Data Restore.
v
Chapter 7, “Data Unload and Reload” on page 73 describes the unload and load
procedures.
v
Chapter 8, “Backing Up an Entire Database” on page 91 describes how to use
Data Restore to back up DB2 Server for VSE & VM databases.
v
Chapter 9, “Restoring an Entire Database or an Entire Storage Pool” on page 103
describes how to use Data Restore to restore an entire DB2 Server for VSE & VM
database or an entire storage pool from either a user or a DB2 Server for VSE &
VM archive.
v
Chapter 10, “Backing Up Parts of a Database” on page 121 describes how to use
Data Restore to unload dbspaces because you want to backup only a single
dbspace and not the entire database.
vii
v
Chapter 11, “Restoring Logical Elements” on page 125 describes how to use Data
Restore to reload a single table or a group of tables with its full environment:
indexes, referential integrity, views, grants, comments, and labels.
v
Chapter 12, “Accessing Data Whether the Server is Up or Down” on page 147
describes how to select data from a table while the application server is up or
down.
v
Chapter 13, “Displaying the Contents of an Archive File” on page 151 describes
how to use Data Restore to display the list of all tables in an archive or an
unload file.
v
Chapter 14, “Displaying Dbspace Information” on page 155 describes how to
produce a report from the application server (which can either be online or
offline) to show the number of used pages (header, data, or index) in the
database.
v
Chapter 15, “Displaying Pool Organization” on page 161 describes how the user
can produce a report from the application server to show the dbextent and
storage pool organization.
v
Chapter 16, “Command Syntax” on page 167 contains all the command syntax
diagrams and examples.
v
Appendix A, “Messages and Codes” on page 191 provides messages and codes.
v
Appendix B, “Command Examples” on page 203 provides procedures to
illustrate the usage of Data Restore functions.
v
Appendix C, “Problem Determination” on page 255 provides problem
determination information.
v
Index
Prerequisites
This book assumes the following:
v If you are installing in a VM environment, that you have a working knowledge
of VM Conversational Monitor System and are acquainted with CMS commands.
v If you are installing in a VSE environment, that you have a working knowledge
of VSE and are acquainted with the appropriate Job Control Language used in
VSE.
v If you are installing in VSE Guest Sharing, that you have a working knowledge
of VM Conversational Monitor System and VSE. It is further assumed that you
are acquainted with CMS commands as well as the appropriate Job Control
Language used in VSE.
v That you have a working knowledge of database manager backup and recovery
concepts.
Terminology
TERM
Meaning
APPLYLOG A Data Restore function to apply the DB2 statements that were
extracted from the log during the RELOAD operation.
Archive
A Data Restore archive.
Associate Full Backup
The associate full backup for an incremental backup is the most
recent full backup taken before the incremental backup. For
example, if a full backup is taken on a Sunday night, and
incremental backups are performed each night during the week,
viii Data Restore Guide
the associate full backup for each of the incremental backups, is the
full backup taken on the Sunday night preceding the incremental
backup.
backup
A backup is a user archive taken with the Data Restore Feature
BACKUP command. It is a copy of the entire database. This is the
default function for the BACKUP command and this type of
backup cannot be used as an associated backup for Incremental
BACKUP. A Data Restore function to produce an archive.
BACKUP FULL
A Data Restore function to produce an archive. The produced
archive could be later used as a reference for INCREMENTAL
BACKUP.
BACKUP INCREMENTAL
A Data Restore function to produce an incremental archive. This
function can only be used if a BACKUP FULL function has been
executed. Only modified pages are saved.
Data Restore archive
A Data Restore archive. Synonym of archive.
DB2 Server for VSE & VM database archive
An DB2 Server for VSE & VM Archive produced by the ARCHIVE
operator command.
DESCRIBE A Data Restore function to read an archive or unload a file and
produce a report with information about the contents of the file.
FORMAT
A Data Restore function to format a dbextent (VSE only).
Forward log recovery
Changes referenced in the log files are processed again after
restoring tables.
FULL ARCHIVE
A database archive file produced by the BACKUP FULL function.
FULL backup A FULL backup is a user archive taken with the Data Restore
feature BACKUP FULL command. It is a copy of the entire
database. Additionally, BACKUP FULL initializes the database
directory so that subsequent incremental backups can be taken. A
full backup must be taken before an incremental backup may be
taken.
INCREMENTAL ARCHIVE
A database archive file produced by the BACKUP INCREMENTAL
function.
INCREMENTAL backup
An INCREMENTAL backup is a user archive taken with the Data
Restore BACKUP INCREMENTAL command. It contains a copy of
the pages that have been modified since the last FULL backup was
taken. A full backup must be taken before an incremental archive is
allowed.
LISTLOG
A Data Restore function to list the contents of LMBRLG1,
LMBRLG2, and LMBRLG3.
regular backup
Same as backup.
About This Book ix
RELOAD
A Data Restore function to restore parts of a database.
RESTORE
A Data Restore function to restore an entire database.
SELECT
A Data Restore function to access data in an application server
when the database is either up or down.
SHOWDBS A Data Restore function to execute a fast show dbspace for an
entire database.
SHOWPOOL A Data Restore function to list a cross reference between dbextents
and storage pools organization.
SPLR
Storage Pool Level Recovery is the process of restoring one or
more storage pools using the POOL parameter of the RESTORE
command.
TRANSLATE A Data Restore function to increase the performance when later
restoring the complete database or parts of it from a DB2 archive.
UNLOAD A Data Restore function to back up parts of a database.
Unload
A Data Restore unload.
VM
Refers to all supported releases and versions of VM/ESA.
VSE
Refers to all supported releases and versions of VSE/ESA.
Note: When these terms are mentioned in this book, they refer to the above
definitions, not to DB2 Server for VSE & VM Database Services Utility
functions unless explicitly mentioned.
National Language Support
The default language for Data Restore is American English.
To set a different national language, you can either:
v Specify it in your SYSIN file with the LANG parameter on the OPTIONS
statement (for more information refer to “OPTIONS and CONTROL Statements”
on page 167).
v Set a default value in program LMBRPARM. Refer to Chapter 4, “Installing Data
Restore” on page 33.
Note: Setting the application server National Language Support does not affect the
Data Restore language.
Co-existence
Data Restore Version 3 Release 5 can be used with a DB2 Server for VSE & VM
Version 7 Release 3 database, however, the incremental archive and restore
functions and the pool recovery function are not available. Also, DATACAPTURE
setting for a given table will not be processed.
Data Restore Version 5 Release 1 can be used with a DB2 Server for VSE & VM
Version 7 Release 3 database, however, the incremental archive and restore
functions are not available.
Data Restore Version 7 Release 3 can be used with an SQL/DS Version 3 Release 5
database, however, the incremental archive and restore functions and the pool
recovery function is not available.
x Data Restore Guide
Data Restore Version 7 Release 3 can be used with a DB2 Server for VSE & VM
Version 5 Release 1 database, however, the incremental archive and restore
functions are not available.
About This Book xi
Summary of Changes
This is a summary of the technical changes to the DB2 Server for VSE & VM
database management system for this edition of the book. Several manuals are
affected by some or all of the changes discussed here. For your convenience, the
changes made in this edition are identified in the text by a vertical bar (|) in the
left margin. This edition may also include minor corrections and editorial changes
that are not identified.
This summary does not list incompatibilities between releases of the DB2 Server
for VSE & VM product; see either the DB2 Server for VSE & VM SQL Reference, DB2
Server for VM System Administration, or the DB2 Server for VSE System
Administration manuals for a discussion of incompatibilities.
Summary of Changes for DB2 Version 7 Release 3
|
Version 7 Release 3 of the DB2 Server for VSE & VM database management
|
system is intended to run on the VM/ESA Version 2 Release 4 or later environment
|
and on the Virtual Storage Extended/Enterprise Systems Architecture (VSE/ESA®)
|
Version 2 Release 5 Modification 1 or later environment.
|
Enhancements, New Functions, and New Capabilities
|
The following have been added to DB2 Version 7 Release 3:
|
Cancel TCP/IP Agent in VM
|
With this new functionality, if the client is connected to a DB2 for VM application
|
server via TCP/IP, and the client disconnects from the application server, the DB2
|
for VM application server will detect that the client has disconnected and will
|
immediately release the resources that it had held.
|
Force Inactive Users
|
This function introduces a new DB2 Server for VM operator command, FORCE
|
INACTIVE. The FORCE INACTIVE command will disconnect inactive users on
|
DB2 Server for VM. Also, when SQLEND is issued on DB2 Server for VM, inactive
|
users will be automatically disconnected so that they do not delay database
|
shutdown. This will make the behavior of SQLEND consistent on DB2 Server for
|
VM and on DB2 Server for VSE.
|
See the DB2 Server for VSE & VM Operation manual for further details.
|
CLI/JDBC V8 Redesign
|
If you would like to use DB2 Server for VSE & VM as a server with access by
|
CLI/ODBC/JDBC/OLE clients, you need the support of schema functions on the
|
DB2/VSE&VM database servers. These schema functions allow applications to get
|
catalog information in a way that is database vendor independent. The schema
|
functions return a standard result set to the user. In DB2 Universal Database V8.1,
|
these functions are implemented with views and stored procedures if the views
|
rely on SQL that is not supported on the VSE & VM platforms.
|
There is now a set of stored procedures that will generate the schema functions
|
results sets without views. The result sets returned by these stored procedures
|
correspond to the results obtained from other servers when SELECTing from the
|
schema views.
1995, 2003
xiii
|
The schema stored procedures created on VM and VSE servers are invoked
|
internally by CLI/JDBC drivers.
|
See the DB2 Server for VSE & VM Database Administration manual for additional
|
information.
|
Control Center for VSE Enhancements
|
The Control Center for VSE restriction of a hard coded user name and password
|
for connecting to a database has been removed. When a Control Center for VSE
|
transaction is initiated in CICS, Control Center will request the user ID and the
|
password from the terminal user. A parameter card will be used for batch
|
programs to input the user ID and password.
|
Control Center for VM Enhancements
|
Control Center enhancements include support for new operator commands,
|
enhancements to existing operator functions, and enhancements to current
|
initialization parameters.
Reliability, Availability, and Serviceability Improvements
Alternate Logging
A new inactive log can now take over when the active log reaches the ARCHPCT
value and alternate logging is enabled, instead of immediately forcing a log archive
to be taken. A log archive can then be taken at a later time, as determined by the
operator. If an attempt to switch to the inactive log occurs and that inactive log has
not already been archived, the operator is forced to take an archive of the inactive
and active logs.
For more information, see the following DB2 Server for VSE & VM documentation:
v DB2 Server for VSE System Administration
v DB2 Server for VM System Administration
v DB2 Server for VSE & VM Diagnosis Guide and Reference
v DB2 Server for VSE & VM Operation
v DB2 Server for VSE Program Directory
v DB2 Server for VM Program Directory
Data Restore Changes
Data Restore now allows users to use the RELOAD function with
RECOVERY=YES, even if an event such as a COLDLOG has broken the logical
continuity of the log files. In this case, the log can now be applied until this event
is reached. Alternate logging is fully supported with Data Restore for VSE & VM
V7.3.
Release Empty Pages
You can now release empty pages in any dbspace on VM without having to issue
DROP DBSPACE, by using a new single user mode utility called SQLRELEP. A
new single user mode startup option, STARTUP=P, is introduced to enable you to
release empty pages on VSE. For more information, see the DB2 Server for VSE &
VM Database Administration manual.
More Information on TCP/IP Messages
In the event of a TCP/IP server-side error, new messages will now be displayed
with more information pertaining to the TCP/IP error. This information will
include the input parameters of the service causing the error.
xiv Data Restore Guide
Migration Considerations
Migration is supported from SQL/DS® Version 3 and DB2 Server for VSE & VM
Versions 5 and 6. Migration from SQL/DS Version 2 Release 2 or earlier releases is
not supported. Refer to the DB2 Server for VM System Administration manual or the
DB2 Server for VSE System Administration manual for migration considerations.
Summary of Changes xv
xvi Data Restore Guide
Part 1. Getting Started
This part gives a brief overview of the Data Restore feature (also known as Data
Restore) and explains how to install and use it.
All enterprises want to reduce the time that the database is unavailable for
productive business use and many are striving to reach an operational goal of 24
hours a day, 7 days a week. These goals can be achieved by reducing the
maintenance window, maximizing the amount of available data, and hastening the
restoration of the database because of unpredictable circumstances. Together, DB2
Server for VSE & VM and its Data Restore feature provide customers with
increased flexibility in their archive and restore options while minimizing the
maintenance window. DB2 Server for VSE & VM now provides the tools that give
you more alternatives when balancing the time and resources required to perform
database archives against the risk of failure and the acceptable time for recovery.
1
2
Data Restore Guide
Chapter 1. How to Recover from Failures
This chapter describes some errors that can be encountered and how to recover
from these situations.
Failures to be Considered
Different types of failures can occur in a relational database management system:
v Power or Processor
Because of a power or processor error, the DB2 database manager can end
abnormally.
v Disk
A DASD can give hardware errors, and there might be cases where this DASD
should be replaced. The DB2 database manager can end abnormally.
v Software
Not only the operating system can end abnormally, but there may be some
conditions where the database manager decides to end abnormally.
v Application or User Logic
An application can fail and lead to a situation where some tables have been
updated and committed and others that should have been are not. Interactive
users can make changes and by error request an update or delete that was not
intended.
The various failure sources can lead to any of the following problems, which can
happen alone or in combinations:
v An LUW completion or cancellation requested by the administrator
v The database manager does not come up again
v The database manager ends abnormally
v Data on disk is damaged or unreadable, partially or completely
v Data in tables is in error, and the problem is not detected for quite some time,
that is, for several archives.
Power or Hardware Failure
From a failure where the complete system comes down, the DB2 database manager
normally recovers after the UNDO/REDO process. Only the LUWs that were
currently active when the failure occurred would have to be restarted. In some
cases this is not so obvious and needs some investigation to be sure that the data
in all tables is consistent with what is expected.
DASD Failure
Here you need to determine what type of disk or extent is affected.
Log Disk Failure
If single logging is used, any I/O error causes the database manager to end. With
dual logging, the database updates are recorded in two log extents. The database
manager continues running as long as it can read and write from one of the log
disks. It is recommended to allocate these log disks on different DASD, as an
unrecoverable error is unlikely to occur on both DASD at the same time.
3
In VM, you should additionally make sure that the database’s 191 minidisk is on
another DASD, because this also contains a copy of the log history file, and this
A-disk file is automatically used during a restore, if the log history area is
unusable due to a log failure.
Note: There might be a situation where the database should be restored on a
different system, or that all dbextents should be replaced. Refer to “Recover
from System Failure” on page 105 for a detailed explanation of saving the
log history information.
To recover from a damaged log disk, follow the procedure as described in the DB2
Server for VSE System Administration and DB2 Server for VM System Administration
manuals.
Directory Disk Failure
To reduce the probability of failures of the directory disk (BDISK), always end the
database manager with the SQLEND parameter DVERIFY, to have a directory
verification. This function checks for inconsistencies in the directory, and thus
allows you to avoid archiving an inconsistent directory.
Note: It is especially recommended to perform an SQLEND DVERIFY just for that
purpose after having loaded a large quantity of data or after critical data has
been updated. This verification can shorten the period between an
inconsistency and its discovery.
Physical Error
The only way to recover from a physical error on the directory disk, is to restore
any archive and, dependent on the logmode, recover the log (and log archives, if
LOGMODE=L).
Logical Error
There are some undocumented built-in tools in DB2 that can help to recover from
logical errors on the directory disk (BDISK) such as pointing to a wrong page.
Because these tools are very powerful and if misused can destroy the entire
database, these commands or procedures should never be run unless explicitly
requested by the IBM Support Center. Before starting any of these tools, make sure
you have an archive of the complete database just in case anything goes wrong.
Data Disk Failure
“Data Recovery” on page 104 gives you an overview of procedures that can be
used to recover as much data as possible, should a data disk be damaged.
Physical Error
The sequence of operations to replace a data disk (dbextent) is described in the
DB2 Server for VSE System Administration manual. The sequence of operations to
replace a data disk (database minidisk) is described in the DB2 Server for VM
System Administration manual.
Logical Error: Data or Index Page Corruption
A data or index page corruption can only be detected when this page is actually
requested by the database manager. When a corruption is detected, it may be
4
Data Restore Guide
possible that the restore of your database or storage pool will not help, because
this corruption was already present when the backup was taken.
Note: Therefore it is recommended to keep different sets of backups and the logs
in between, so that it may be possible to restore your database to the point
where it was free of corruption, and then apply forward recovery with the
logs if they are available.
If only the index has been corrupted, a drop and create index might correct the
problem.
The procedure for data pages is more complicated. Technical help from the IBM
Support Center using special programs might correct such a problem, depending
on the kind of corruption, but such programs should only be executed in
cooperation with someone at the IBM Support Center.
Operating System Abend
From an operating system abnormal end, such as from a power or hardware
failure, the DB2 database manager normally recovers after the UNDO/REDO
process. Only the LUWs that were currently active when the failure occurred
would have to be restarted. In some cases this is not so obvious and needs some
investigation to be sure that the data in all tables is consistent with what is
expected.
DB2 Failure
Due to a problem with the DB2 software, in an extreme situation a corruption
could occur in the database. If the database becomes corrupted, the problem may
not be very obvious and may not be noticed until several archives have been
taken.
Application or User Logic Error
A problem may occur with an application and lead to a situation where some
tables have been updated and committed and others that should have been are
not. This situation requires investigation to restore the data to a consistent state, as
it was before the application was started or to a point that all data is synchronized.
A similar situation can happen when interactive users make changes and by error
request an update or delete of data that was not intended.
Note: Care should be taken with the use of non-recoverable storage pools, as the
database manager does not undo INSERT, UPDATE, PUT, DELETE
statements that were successfully executed. For a detailed description of
characteristics of non-recoverable storage pools see the section on "Special
Topics in Recovery Design" in the DB2 Server for VSE System Administration
and DB2 Server for VM System Administration manuals.
Chapter 1. How to Recover from Failures
5
6
Data Restore Guide
Chapter 2. Recovery Strategies
In this chapter you will find recommendations, considerations and scenarios to
choose from, so that you can define your own archive and recovery strategy.
Chapter 5, “Migration” on page 67 gives you hints on how you can realize a
change in your strategy.
Recommendations
Taking backups and never having to use them, is often considered as a waste of
time. But whenever you get a situation where a backup has to be restored partially
or completely, you will appreciate its value.
Have a Strategy
That is why it is always recommended to have a good recovery strategy. It should
not only be defined once, but also regularly reviewed and adapted to the current
needs and to the experiences made when recovery has been required.
This recovery strategy should cover both backups and the possible restore
scenarios. It should comprise recovery on the same system, but also on another
system. Even a disaster backup and recovery should be considered, where the
whole environment can be recreated from scratch on another system, for example
in an IBM Backup and Recovery Center.
Test Backup And Restore
The procedures and the backups should also be tested, for example a recovery
should be tried from the backups actually taken either on the same system, or if
possible on a different system. If these tests are performed, they might reveal a
need for changes in your archiving and recovery procedures, so that you will be
able to benefit from these in case you need them.
Keep Several Backups
Keeping different sets of backups, and if applicable, log archives, may give you the
possibility to return to an earlier point in time than the latest archive. This can be
helpful, for example:
v when an application error has destroyed a table and this has not been detected
during several archiving periods.
v if some table from a specific period, such as year end closing, would be needed
for comparison reasons, you might want to keep an extra archive set of that
period, to be able to restore individual tables. No additional backups would
have to be made.
Starting from that older archive, a table can be restored individually with forward
recovery and this can be stopped at exactly the point in time that you decide,
where the data reflects the correct information that you need.
Backup Occasions
7
In addition to regular backups (see page 12), consider to include backups in your
strategy:
v Before and after an add or delete dbextent
v Before and after an add dbspace
v After dbspaces have been moved from storage pools, or other major layout
changes have been applied to your database
v After having loaded a large quantity of data or having applied major updates in
single user mode with LOGMODE=N
v Before migrating DB2 to a different level or release
v After installing a new database
v Before installing new programs, such as QMF, in the database
v Before changing the size of the log disk
v Before switching from single to dual log or the other way around
v Before changing the size of the directory
v Before copying the directory to a new extent to implement VMDSS.
Evaluation
Without the Data Restore feature, you might need duplicate backups of certain
tables or dbspaces, in addition to regular archives taken. There has been no
possibility to restore individual tables from a DB2 archive or user archive. To be
able to restore individual tables, often a backup with DBSU UNLOAD has been
taken additionally. Since it is not predictable which table or dbspace of a backup
will be needed for restore, in the worse case a copy of every table would have to
be available. So very often a selection of important data was made and only these
were separately copied, others were missing. An additional problem lies in the fact
that no forward recovery can be applied on tables.
With the Data Restore feature, you get the most flexibility using either the DB2
ARCHIVE or Data Restore BACKUP for making backups. The Data Restore
RESTORE lets you restore a full database or just one storage pool from a complete
archive. For information about log recovery after RESTORE of a storage pool, refer
to “Log Recovery” on page 26. The Data Restore RELOAD gives you the possibility
to reload any table from a complete archive, and there is no need to make
additional copies of tables just for backup. Therefore, it is recommended to spend
the resources on an improved archive process of all data rather than on making
duplicate backups of specific tables.
There are still occasions where you will need to unload and reload tables:
v When moving tables between storage pools
v When copying tables to a different database
v When modifying characteristics of a table or dbspace
For these kinds of operations there are several alternatives from which to choose
depending on which alternative with its capabilities fits your needs best.
Archive Advantages
DB2 ARCHIVE and Data Restore BACKUP offer you the following recovery
choices:
v Forward Recovery
8
Data Restore Guide
As with DB2 restore, forward recovery is possible with Data Restore RESTORE,
if LOGMODE=L or LOGMODE=A is used.
v Recovery with filtered log
In case of a DBSS failure, the database might, for example, abnormally end
during startup indicating the failing LUW. During startup you can specify to
omit a specific LUW. This holds for:
- DB2 restore
- Restart after a Data Restore RESTORE of a complete database
- Restart after a Data Restore RESTORE of a storage pool
- Restart having restored any user archive of the complete database
v Data Restore RELOAD of a single table
As described above, this relieves you from making additional unloads (duplicate
backups) of single tables.
v Data Restore RELOAD with forward recovery of a table, even to point in time.
With Data Restore LISTLOG you can easily find the statement that corrupted
your table and omit that (and the following commands) during recovery.
Archive Weaknesses
There are very few disadvantages of DB2 ARCHIVE and Data Restore BACKUP,
depending on what you compare them to.
v Compared to backup of a table only, it is a complete backup, which takes more
time than just an unload of a table.
But normally, unloads have been made in addition to complete backups, so that
these times in fact were added.
v User archives, for example with DDR, might have included the necessary files to
recover a database on another system or after exchanging all dbextents.
Therefore, in addition to offline DB2 ARCHIVE or Data Restore BACKUP, you
should save the database definition files as described in “Recover from System
Failure” on page 105.
Comparison between Data Restore BACKUP and DB2
ARCHIVE
The differences between DB2 ARCHIVE and Data Restore BACKUP are the
following:
DB2 ARCHIVE
v Archive can be made online, without reducing the availability of the database.
For disaster recovery on a different system, it is good to have an archive
available which was taken offline, or online in LOGMODE=L, because these are
consistent. Otherwise you need to be able to apply forward log recovery to get
consistent. The same holds when you want to use an older archive than the
latest, as described in “Recover from System Failure” on page 105.
v Data Restore TRANSLATE immediately or later.
You can translate every DB2 archive immediately after having taken it and keep
the work files.
A Data Restore RELOAD can also be made without translation, needing more
time; the translation is made internally, reading the tape twice. But this takes less
time than TRANSLATE and RELOAD from translated archive.
Chapter 2. Recovery Strategies
9
For Data Restore RESTORE a TRANSLATE would be required; you can omit
translation when restoring the database using the standard DB2 restore
procedure.
Data Restore BACKUP
v You do not need to translate the archive for:
- Data Restore RESTORE of a complete database
- Data Restore RESTORE of a storage pool
- RELOAD of an individual table
All of these operations can be performed directly from a Data Restore archive.
v You can make archives to disk.
Considering the restrictions about the size of the output disk related to the size
of the database, this applies only for small and medium databases (see “Data
Restore BACKUP” on page 27).
Given the fact that we measured around double the time for an archive to disk
than to tape, this is not a big advantage.
v You can make dual archives: to tape, to disk, or mixed.
For example, you might want to keep one tape copy for table recovery, and send
another tape copy to a safe place for disaster recovery.
v You can improve the performance of your BACKUP using the INCREMENTAL
BACKUP This will only save modified pages from a referenced archive
produced by the BACKUP FULL command.
So the most prominent difference is that a DB2 archive can be taken online, but
might be translated, which can take twice as long as the archive itself. For disaster
recovery in LOGMODE=A, offline archives are safer, as described in “Recover from
System Failure” on page 105 and page 14.
Considerations
The following is a checklist of factors that can influence the planning of your
archive and recovery strategy:
v The environment: production, development or test, or is data recreatable from
another database or platform?
v The size of the database
v The vitality of data: frequency of updates, amount of updates and frequency of
dataload
v The time it takes to apply the logfiles
v The facilities available for unattended (automated) archive
Taking into account the factors from the list above, some decisions should be made
how you run your database and how you define your recovery strategy:
v Frequency of reorganization
v Type of archive: DB2, Data Restore or user archive
v Archive to disk or tape
v Archive online or offline
v Frequency of archive
v Number of backup cycles to be kept
v Single or dual log
v LOGMODE parameter
10
Data Restore Guide
v Size of the log disk
v Checkpoint interval
v Disaster recovery
The following paragraphs outline considerations related to selected items of the
lists above.
Type of Data
Some differences in backup could apply if the main purpose of the data residing in
the database is statistics or running queries to determine trends, if the data is
imported from somewhere else.
You can consider to reduce the frequency of backup when data is just history data
and has very few updates.
You can consider even to omit regular backups, when the data is unimportant or
reproducible:
v When data is copied from another data source, for example for statistics for the
purpose of running queries to determine trends, when the data originates
partially or completely from a production database.
v Development or test database
Database Size
The backup strategy for a very large database is completely different from that for
a small database. For a large one, online archives could lead to a checkpoint
during the archive process preventing the users from work, so offline archives
might be better suited, and if they take too long, it might even be not possible to
perform an archive every day. The incremental backup can be a very efficient way
of processing a BACKUP of such a database, by only saving modified pages, that
represent a very small percentage of the database. For a small database, daily
online archives can be made, even a Data Restore BACKUP to disk could be
realistic, or just creating an image copy on a different set of disks; DDR disk to
disk is very quick and can be an intermediate step before copying to tape.
Data Manipulation: Frequent Updates
If the data is very important and updated often, it is recommended to increase the
size of the log disk, so that the interval between archives or log archives could be
increased. For LOGMODE=L you should consider that for frequent updates it can
take a very long time to apply forward recovery.
Type of Archive
Before the archive enhancements in SQL/DS V3R5 a tendency was to take user
archives. Now that DB2 archive performance is improved, it gives you the
possibility to choose between DB2 ARCHIVE and Data Restore BACKUP. The Data
Restore BACKUP can be FULL (the whole database is saved) or INCREMENTAL
(only modified pages from the last FULL archive are saved).
Archive Medium
For Data Restore BACKUP, user archives and (in VM) log archives, you have to
decide whether to make them to disk or tape. To disk might be faster when using
Chapter 2. Recovery Strategies
11
DDR or similar tools; with BACKUP we measured a better performance to tape.
For the purpose of disaster recovery, backup to tape is required. For DB2
ARCHIVE only tape is supported.
Archive Frequency
The interval between archives and log archives depends on the frequency of data
modifications in your database and the allocated size for the log disk. This should
influence your decision which logmode you choose. With log archives
(LOGMODE=L) often the interval between archives is increased. If you choose this,
consider that for frequent updates it can take a very long time to apply forward
recovery. Therefore, it is a good strategy to try to reduce the interval between
archives. If time allows and if your archive procedure is fully or almost fully
automated, this can be easy.
Additionally, make archives at certain points in time as described on page 7.
Archive Online or Offline
Using DB2 ARCHIVE, you can choose to perform online because its speed has
improved in SQL/DS V3R5. For disaster recovery and for restoring a back level
archive in LOGMODE=A, you might want to make offline archives from time to
time, see page 14. For Data Restore BACKUP and user archives you have no
choice; they must be taken offline.
Directory Verification
Whenever you perform an SQLEND, do it with the parameter DVERIFY to verify
the directory. After larger updates of the database you might want to take the
database manager offline just for the purpose of the database verification, as
described in “Directory Disk Failure” on page 4.
Archive Sets
You should decide how many archive sets will be kept, before re-using the tapes.
The number of tapes belonging to each set could be the main factor that influences
this number of archive sets. As mentioned before, there might be some need to
restore an older archive set due to a problem already present on the latest archive.
If LOGMODE=L is used, all intermediate log files should also be available for this
restore.
For example, if you are running your database in LOGMODE=L and you take
archives every other day and log archives daily, you might want to keep archives
and log archives for one week, and additionally the first (offline) archive of every
month for one year.
For LOGMODE=A, an online archive can only be used if this is the newest one
because online archives need the current log to become consistent. When you
restore an older archive, you must choose one that has been taken offline.
For more information about log recovery after RESTORE of a storage pool, refer to
“Log Recovery” on page 26.
Logging
12
Data Restore Guide
The log archive takes less time than a full database archive. In VM it is possible to
create log archives on disk.
There are some advantages creating log archives on disk instead of tape:
v No tape mounting procedure is involved either in archive or in restore of the log
v All log archive files can be available on the same disk, so the identification is
easier
v Log archives to disk are faster than to tape
You must decide whether you need the log archives even in case of disaster
recovery; if so, you need a tape copy.
When you update large amounts of data with DATALOAD or RELOAD in
multiple user mode, log consumption can be high. This can be avoided by running
DBSU in single user mode with LOGMODE=N. Or, for DATALOAD, you can
specify the COMMITCOUNT option together with LOGMODE=Y in multiple user
mode.
Dual Log
The database manager requires at least one log disk and supports dual logging.
Dual logging is recommended when high availability of the database is required
because it helps to recover from log disk failures. Especially in VSE, where there is
no additional copy of the log history file, this option is recommended.
In VM, make sure that the two log disks and the 191 disk of the database are on
different DASDs.
In VSE, make sure that the two log extents (clusters) not only reside on different
volumes (DASDs), but also in different VSAM catalogs in case of a catalog error.
LOGMODE
It should be decided, whether LOGMODE=A or LOGMODE=L will be used,
because if a restore of the complete database is requested, it can take some time to
apply all log files starting from the last archive.
In LOGMODE=L an online archive can be used for disaster recovery or to restore
an older level of archive; in LOGMODE=A, for these two cases only offline
archives can be used, as described in “Recover from System Failure” on page 105.
Log Size
A useful approach to calculate the size of your log is to estimate the percentage of
data that will be generated, deleted, and changed over one archive period. You can
estimate the log space requirement with the following calculation:
logsize estimate = (percentage generated
+ percentage deleted
+ percentage changed x 2) x database size
Checkpoint Interval
The database manager takes periodic checkpoints depending on the startup
parameter CHKINTVL. This parameter defines how many log pages are to be
written before DB2 automatically takes a checkpoint. A checkpoint record is
Chapter 2. Recovery Strategies
13
written to the log to synchronize the log with the state of the database. The
checkpoint parameter CHKINTVL controls the duration between checkpoints.
Many installations find that the optimum CHKINTVL setting is between 50 and
300. Installations with small databases tend to be in the upper end of that range.
Try to adjust the startup parameter CHKINTVL to have one checkpoint taken
every 15 minutes.
If DB2 archives are taken online, you normally choose times of low activity.
However, because your users want to continue to work, the CHKINTVL must be
chosen large enough to avoid that during the archive another checkpoint (than the
one at the beginning of the archive) will be requested; when that happens, your
users will be forced to wait until the archive is finished.
Failure
Logmode L
Last
Off- or
Log
Online
Log
Latest
Last
checkpoint
month’s
online
archive
archive
archive
online
log
off- or
archive
archive
archive
online
(no open
archive
LUWs)
Failure
Logmode A
Last
Offline
Online Online Online Online Lastest
checkpoint
month’s
archive
archive archive archive archive online
off- or
archive
online
archive
Figure 1. Sample Event Timing
Disaster Recovery
Depending on your recovery strategy for your complete system, you will have to
determine additional archiving steps. If your recovery procedure includes the
ability to move your complete operating environment to a different system, for
example in an IBM Backup and Recovery Center, and you can rely on regular
backups taken, you need not worry about database definition files.
If this is not planned but your database should be able to be restarted on a
different system, for example, with remote applications accessing your database,
you need to keep an actual copy of your database definition files together with
your archives.
All archives, log archives and files must reside on tapes for use on a different
system.
When you are running in LOGMODE=A and performing regular online archives,
you should consider to make additional offline archives (of any type) with a lower
14
Data Restore Guide
frequency, which might be acceptable for disaster recovery (see page 15). If a lower
frequency even for the case of a disaster is not acceptable, you can run your
database with LOGMODE=L, make log archives to tape, and after each one:
v in VM copy the log history to tape
v in VSE take your database offline to copy the log file to tape.
For more details, see “Recover from System Failure” on page 105.
Fallback Time
When the database is restored after a failure, it represents the contents of a certain
point in time backward from the failure; ideally this would be the moment
immediately before the failure.
Depending on:
v the type of failure you consider
v the fallback time that your organization is willing to accept for that case
v the regular effort you can spend for backup
v the interruptions allowed to the availability of the database
you must choose the archive frequency, the logmode, and whether you perform
archives online or offline.
Consider the following sequence of events, as shown in Figure 1 on page 14.
v
In case the system suddenly came down, in both LOGMODEs A and L, the
database falls back to the point in time of the last checkpoint. Using the log,
incomplete LUWs are rolled back, and completed LUWs are re-created
completely and committed.
v
In case of a DASD failure or an immediately detected database corruption,
- if LOGMODE=L, you can install the last archive taken, and then perform
forward log recovery using log archives and the current log.
- if LOGMODE=A, you can install the last archive and perform forward log
recovery from the current log.
v
In case a database corruption had occurred some time back and is detected at
the indicated point in time of failure,
- if LOGMODE=L, restore from the latest archive from the point in time before
the cause, and if you still have all the log archive tapes in between then and
now, you can perform forward log recovery with these, which might take a
long time. You can filter the recovery to avoid the damage being repeated.
- if LOGMODE=A, you have to restore from the latest offline archive before the
cause, and no forward log recovery will be possible.
v
In case of a disaster, you fall back
- if you are running in LOGMODE=L, to the (online or offline) archive you
install, and additionally forward recovery of all consecutive log archives can
be made (although this might take much time), provided you have the log
archives without gap and the log history file has the appropriate information.
That is depending on your recovery strategy for your complete system.
- if you are running in LOGMODE=A, to the offline archive you install. No
forward log recovery can be made, and online archives can not be chosen
because the current log is not available to put the archive into a consistent
state.
Chapter 2. Recovery Strategies
15
Possible Strategies
In this chapter we describe some database management systems with different
requirements to help you define possible strategies for your database environment.
User Archives and Unloads
This strategy looks as follows:
User archive + unloads → failure - restore
restore is either of:
v database - user restore
v storage pool - you have to restore the entire database
v table - DBSU RELOAD without log recovery
This strategy has been widespread, for example using DDR because it used to be
quicker than DB2 ARCHIVE until SQL/DS V3R5. In addition to the user archive,
the important tables or even all tables were unloaded to be able to fix smaller
problems quickly.
You should make sure that the database definition files and the log file are
included in your user archive.
With the enhanced capabilities of SQL/DS V3R5 and the availability of the Data
Restore feature, it might be advantageous to change your recover strategy to one of
the following.
Online DB2 Archive and Translation
This strategy looks as follows:
Online DB2 ARCHIVE - Data Restore TRANSLATE → failure - restore
restore is either of:
v database - Data Restore RESTORE or a DB2 restore
v storage pool - Data Restore RESTORE of a storage pool from translated DB2
archive
v table - Data Restore RELOAD from translated DB2 archive with log recovery
Because of the availability of the database, the DB2 archive is always followed by a
translate process, keeping a copy of the work files. This has been chosen because
the reload of individual tables with forward recovery is considered to apply and
must perform quickly.
If you need to recover one or more storage pools, Data Restore RESTORE of a
storage pool is the best choice.
When individual tables need to be recovered, Data Restore RELOAD is the best
alternative.
For a complete restore you can choose between a restore from DB2 and Data
Restore RESTORE.
16
Data Restore Guide
Complete log recovery is possible in both cases, RELOAD of a table and RESTORE
of the whole database, and you will be prompted for the log archives (if any)
according to the information that is available in the log history.
With a lower frequency, any kind of offline archive is made for disaster recovery.
For the same purpose, a copy of the database definition files is kept with the
archives.
Online DB2 Archive
This strategy looks as follows:
Online DB2 ARCHIVE → failure - restore
restore is either of:
v database - DB2 restore
v storage pool - Translate and then Data Restore RESTORE of a storage pool, or
just DB2 restore of the entire database
v table - Data Restore RELOAD from DB2 archive with log recovery
Because reload with forward recovery is not requested with ultimate performance,
the translate process has been chosen to be bypassed.
Similarly, it was decided for the recovery of a storage pool:
v Either a translation is done and then a Data Restore RESTORE of a storage pool
v or a complete DB2 restore process is made.
When a table needs to be recovered, Data Restore RELOAD can be run from the
DB2 archive. The reload of tables is slower, because the tapes are read twice, but
this time is accepted because reload is not often requested. A lot of time and effort
is saved in comparison to the scenario where a Data Restore TRANSLATE is
executed on each archive.
For the case of a complete restore, DB2 restore can be applied, which is equivalent
to the slower alternative of first running a Data Restore TRANSLATE and then
Data Restore RESTORE.
As in the previous scenario, complete log recovery is possible in both cases,
RELOAD of a table and restore of the whole database, and you will be prompted
for the log archives (if any), according to the information that is available in the
log history.
With a lower frequency, any kind of offline archive is made for disaster recovery.
For the same purpose, a copy of the database definition files is kept with the
archives.
Data Restore BACKUP
This strategy looks as follows:
Data Restore BACKUP → failure - Data Restore RESTORE
restore is either of:
v database - Data Restore restore
v storage pool - Data Restore RESTORE of a storage pool
Chapter 2. Recovery Strategies
17
v table - Data Restore RELOAD from Data Restore archive with log recovery
There are three reasons why this strategy can be chosen:
v Putting the database offline for the archive, is not considered a problem, because
there is a window where the database is allowed to be offline.
v Taking online archives takes too long. Checkpoints might occur during the
archive, and performance degradation cannot be accepted for so long. Although
it would be preferable to have the database online, offline archive is chosen.
v For offline archives, Data Restore BACKUP is chosen because no translate
process is needed.
If you need to recover one or more storage pools, Data Restore RESTORE of a
storage pool is the best alternative.
When individual tables need to be recovered, Data Restore RELOAD is the choice.
For a complete restore you use Data Restore RESTORE.
Complete log recovery is possible in both cases, RELOAD of a table and RESTORE
of the whole database, and you will be prompted for the log archives (if any),
according to the information that is available in the log history.
For disaster recovery, a copy of the database definition files is kept with the
archive.
Data Restore BACKUP FULL/INCREMENTAL
This strategy looks as follows:
Data Restore BACKUP FULL/INCREMENTAL → failure - Data Restore RESTORE
restore is either of:
v database - Data Restore restore
v storage pool - Data Restore RESTORE of a storage pool
v table - Data Restore RELOAD from Data Restore archive with log recovery
There are four reasons why this strategy can be chosen:
v Putting the database offline for the archive, is not considered a problem, because
there is a window where the database is allowed to be offline.
v Taking online archives takes too long. Checkpoints might occur during the
archive, and performance degradation cannot be accepted for so long. Although
it would be preferable to have the database online, offline archive is chosen.
v For offline archives, Data Restore BACKUP is chosen because no translate
process is needed.
v The offline archive can use the INCREMENTAL function to shorten the time
necessary to produce the archive.
If Data Restore functions are processing an INCREMENTAL archive file, to reload
individual tables or restore a storage pool or complete database, the associated
FULL archive will be accessed while processing.
Changing Your Recovery Strategies
This section reviews some common recovery strategies and makes
recommendations for new strategies to exploit new Data Restore functions.
18
Data Restore Guide
Prior to SQL/DS Release 3.5, many users took user database archives because
external tools provided better performance. To also be able to recover data at the
table level, many users also performed dbspace and table unloads for critical data.
Now that the Data Restore feature is available and the database manager archive
has been enhanced, there are some alternatives to be considered.
If you currently take online database archives:
If you are using the DB2 ARCHIVE online, there is no need to alter your recovery
strategy. You get the additional advantage of the performance enhancement to the
archive process.
If you used to make additional backups using UNLOAD, you can omit this. Data
Restore RELOAD allows you to RELOAD tables from the archive online, and
additionally offers you the ability to apply forward log recovery to the reloaded
tables. You can choose to use either the implicit (in Data Restore RELOAD) or
explicit (in TRANSLATE) translate process. You can also use the Data Restore
RESTORE command to recover a storage pool.
You just have to install the Data Restore feature and become familiar with the Data
Restore RELOAD and RESTORE, and optionally TRANSLATE process, as well as
LISTLOG and APPLYLOG. See Figure 2.
online
online
ARCHIVE
ARCHIVE
TRANSLATE
ARCHIVE
UNLOAD
selected data
UNLOAD
all
data
Current Strategy
New Strategy
Figure 2. Migration from Online DB2 Archive
If you currently take database manager archives on shutdown:
Chapter 2. Recovery Strategies
19
If DB2 archive was used offline with SQLEND ARCHIVE, you can keep this
archive option and consider the TRANSLATE, or you can recover using Data
Restore BACKUP to omit the TRANSLATE. You can also switch to online archive,
because it now takes less time. This might take a little bit more time than offline, if
there are any users running concurrently with the archive, but there is the
advantage of keeping the database online. See Figure 3 Choices A, B and C.
If you used to make additional backups in form of UNLOAD, you can omit this.
Data Restore RELOAD allows you to RELOAD tables from the archive online, and
additionally offers you the ability to apply forward log recovery to the reloaded
tables. You can choose to use either the implicit (in Data Restore RELOAD) or
explicit (in TRANSLATE) translate process.
You just have to install the Data Restore feature and become familiar with the Data
Restore RELOAD and RESTORE, and BACKUP or TRANSLATE process, as well as
LISTLOG and APPLYLOG.
offline
offline
offline
online
Data Restore
ARCHIVE
ARCHIVE
ARCHIVE
BACKUP
TRANSLATE
TRANSLATE
ARCHIVE
ARCHIVE
UNLOAD
selected data
UNLOAD
all
data
Choice B
Choice C
Current Strategy
Choice A
Figure 3. Migration from Offline DB2 Archive
If you currently take user database archives:
Most of the user archive tools were better performers than the DB2 archive. In
addition to this, unload of selected dbspaces was used to have the possibility for
individual restore. (See Figure 4 on page 22 “Current Strategy”.) If all or a major
part of the dbspaces would be unloaded, this could take some time and resources,
although the unload could run when the database was online.
Now you have two choices:
1. Migrate to Data Restore feature BACKUP, which is also performed with the
database offline. This might take some more time than your usual archive, (see
Figure 4 on page 22 “Choice A”), but has major advantages:
20
Data Restore Guide
v No maintenance of the input definitions (to run the user archives) is
required. The Data Restore feature just needs access to the production disk
(default 195) in VM, or to the database identification job, containing the
DLBL statements for the extents in VSE.
v All storage pools are available for individual restore.
v All tables are available for individual restore. Thus, additional UNLOAD of
selected or all data can be omitted.
v Reload tables with forward recovery is possible.
2. If the availability of the database is a major requirement, changing your
recovery strategy to online DB2 ARCHIVE could be considered. The possible
disadvantage is the additional translate process, which can be performed at any
time. (See Figure 4 on page 22 “Choice B”.)
For recovery to online DB2 archive, the procedures would have to be changed, so
the operator command should be executed instead of ending the database, running
your user archive, and later possibly doing additional unloads. If you run with
LOGMODE=A, you might want to additionally take offline archives at longer
intervals for disaster recovery.
If Data Restore feature BACKUP is selected as an alternative for your archive, then
there would be no change in the process of ending your database. You just replace
your user archive procedure by starting the Data Restore BACKUP.
In both cases, additionally having the database definition files (to be copied only
after changes of the database layout) should be considered, as described in
“Recover from System Failure” on page 105, to have the chance to restore and run
the database on another system. You might want to check whether these files have
been included in your current user archives or not, and whether restore on another
system is reflected in your recovery strategy.
You can improve the recovery even further by taking a copy of the log history to
tape after each log archive, as described in 14.
Chapter 2. Recovery Strategies
21
offline
offline
online
DDR
Data Restore
ARCHIVE
BACKUP
UNLOAD
TRANSLATE
selected data
ARCHIVE
UNLOAD
all
data
Current Strategy
Choice A
Choice B
Figure 4. Migration from User Archive
22
Data Restore Guide
Chapter 3. Archive and Recovery
This chapter describes the options provided to back up a complete DB2 database
and to restore this data.
The specifications for the work files can be found in Figure 182 on page 216.
Overview
With the Data Restore feature, there are additional possibilities to manage the
backup and recovery process.
The different possibilities explained are:
v User archives with non-DB2 tools
v DB2 ARCHIVE and restore
v Data Restore BACKUP and RESTORE
v Data Restore BACKUP FULL/INCREMENTAL and RESTORE
Control Center
Control Center (formerly known as SQL Master) is a feature of DB2 Server for VSE
& VM version 7. As a DB2 feature, it supports the use of Data Restore feature in a
VM environment. With this support, you can explore the new Data Restore feature
functions which facilitate database maintenance for the administrator.
Control Center, can help you automate the process for taking archives. Control
Center can schedule an automated archive at predefined times or when certain
conditions are met.
Control Center also has some interfaces that let you take non-DB2 archives or
initiate backups using other user or vendor written programs, for example
VMBACKUP or VMTAPE.
For a complete example of Control Center using Data Restore TRANSLATE
function, refer to “Using the TRANSLATE Function” on page 242.
For a detailed description of archive and/or backup automation refer to DB2 for
VM Control Center Operations Guide.
User Archives
There are many ways to perform a backup depending on the operating system and
on the programs installed in this environment.
An archive taken using non-DB2 facilities is called a user archive. It can only be
performed offline, that is when the database manager has been shut down by
SQLEND UARCHIVE. Never use SQLEND or SQLEND QUICK when performing
a user archive. You must use SQLEND UARCHIVE, because this:
v Creates an entry in the log history area.
v Makes a checkpoint, and thus leaves the database in a clean status for the
archive.
23
v Avoids unnecessary UNDO/REDO operations after restoring the database from
a user archive.
User archives let you use a variety of tools and you can switch between different
types of archive as often as you like. The following is a list of non-DB2 archive
tools available:
v
DDR
Available on VM only. It can be executed in VM as a module or from a
standalone tape to back up either a VM minidisk or a DASD belonging to VSE.
The disadvantage of DDR is that it requires some input definitions to be
supplied and these must be maintained in case the physical layout of the
database changes.
In VM this means, that the procedure that initiates the copy needs to know
about all the minidisks belonging to the database. Use the current directory file,
for example USER DIRECT, or the SQLFDEF file that is maintained by the
database manager and is available on the DB2 production disk.
In VSE, only complete disks, not dbextents, can be copied, and VSAM
definitions on those disks must not be changed.
v
DFSMS™
Available on VM only.
It performs better than DDR in most cases but, as DDR, it requires that you
supply input definitions and maintain them in case the physical layout of the
database changes.
v
VSAM IDCAMS Backup/Restore
Available on VSE only. IDCAMS BACKUP can be applied. As with DDR and
DFSMS, VSAM Backup/Restore requires some input parameters related to the
extents and must be maintained if the design of the database changes. For a
detailed description of this command see the IBM VSE/VSAM Commands
manual.
The VSAM IDCAMS REPRO command cannot be used for the backup of DB2
extents.
v
Any other user or vendor written application that can perform any type of
backup of complete disks or minidisks. Most of these backup applications are
based on DDR in VM for the copy of a whole minidisk.
Restore is made using the same tool as the archive, and then restarting the
database with STARTUP=U for forward log recovery.
Note: Typically, no table level recovery is available using these user archive tools.
Archiving with DB2
There are two kinds of archives using the DB2 product; a database archive and a
log archive.
DB2 Archive
A database archive (DB2 archive) is a tape copy of the database directory and the
dbextents. The database manager takes a checkpoint (the begin-archive checkpoint)
and writes a copy of the database directory and the active data pages to tape, as
they were at the checkpoint. A database archive does not include a copy of the log.
24
Data Restore Guide
Archives can be taken either online (ARCHIVE) or in the process of being shut
down, called “offline” (SQLEND ARCHIVE).
v An offline archive is consistent in itself because there are no ongoing updates.
On restore, log recovery can be applied.
v An online archive
- with LOGMODE=A, makes a checkpoint before and after the archive. LUWs
can exist during the begin archive checkpoint. Therefore, the archive can be
inconsistent and needs forward recovery through the current log to become
consistent when restored.
- with LOGMODE=L, enforces a log archive before, and checkpoints before and
after the log archive and the archive. The begin log archive checkpoint waits
for the end of any active LUWs and new LUWs are prevented until the end
of the begin archive checkpoint. Therefore, the archive is consistent in itself.
In SQL/DS V3R5, enhancements were made to improve the performance of the
archive process:
v The database manager archive process only archives pages that have been
allocated. Non-allocated pages are not archived. This not only reduces the time
but also the number of tapes needed to back up the database.
v In VM, it uses asynchronous I/O to reduce the wait time for reading DASD by
having concurrent disk and tape operations.
v In VM, it uses Multiple-Block *BLOCKIO requests in a single IUCV SEND
request.
v In VSE, it uses VSAM controlled buffers. By doing this, VSAM is able to read
multiple records with a single I/O request. This improvement in VSE is
equivalent to both VM improvements above, asynchronous I/O and multiple
block *BLOCKIO.
A detailed description and a test result overview can be found in the SQL/DS
Version 3 Release 5 Usage Guide.
Log Archive
A log archive is a tape or disk copy of the log, recording just the changes applied
to the data since the last archive or log archive.
Log archives can be made between DB2 archives or user archives to improve the
availability of the database. In most situations, if you are using log archive, the
period between database archives is increased.
Warning: If you use LOGMODE=L on a database with lots of updates, when you
restore the database the log recovery will have to re-execute all changes.
This process can be very time consuming and use a lot of resources from
the database or the system, especially the processor (CPU).
Only DB2 facilities can be used to archive the log. The database manager must be
running with the startup parameter LOGMODE=L. Log archives are taken when
the database manager is either running or still running but in the process of being
shut down.
The output for the log archive is defined by the FILEDEF (in VM) or TLBL/DLBL
(in VSE) statement with ddname ARILARC. To be able to restore a database to its
current level the log archives must be continuous.
Chapter 3. Archive and Recovery
25
For filtered log recovery, which excludes some operations from the UNDO/REDO
process, refer to the DB2 Server for VSE & VM Diagnosis Guide and Reference
manual.
For a detailed description of log archives refer to the DB2 Server for VM System
Administration and DB2 Server for VSE System Administration manuals.
DB2
Restore
To perform the recovery procedure, the database manager has to be started with
the STARTUP=R parameter. The directory and all dbextents are reloaded from the
DB2 archive tape. Log archive files and log recovery can be applied after the
restore of the data.
In VSE, STARTUP=F is equivalent to STARTUP=R in VM; in VSE, STARTUP=R
first formats all dbextents, which can take longer than the restore process itself.
If LOGMODE=A and the archive being restored is not the last one taken, you must
do a COLDLOG to avoid inconsistency or an abnormal end. But even so, if the
archive restored was an online archive, the inconsistency due to active LUWs
during the online archive cannot be resolved.
If LOGMODE=A and the latest archive is restored, the current log can be used for
the UNDO/REDO process. This process makes a online archive, taken with
LOGMODE=A, become consistent.
If LOGMODE=L, do not perform COLDLOG because this would prevent you from
applying the log archives and the current log.
For detailed information how to restore a database from a DB2 archive refer to the
DB2 Server for VSE System Administration or DB2 Server for VM System
Administration manuals.
Log Recovery
v At database startup after restoring a user archive (STARTUP=U) or when
restoring a DB2 archive (STARTUP=R or STARTUP=F in VSE), forward log
recovery is applied. This includes log archives, if LOGMODE=L. The log
archives must be without a gap. The log recovery is stopped as soon as one file
is missing.
You can filter your log for the UNDO/REDO process. This means you can
exclude some specific DB2 command(s) from the recovery process, and after that
specific command the log application is resumed.
v With Data Restore RELOAD, LISTLOG and APPLYLOG you can apply log
recovery (to the tables restored) up to a certain point in time. But unlike filtered
log recovery, the log recovery cannot be resumed if it stops because it has:
- Reached a certain predefined point in time
- Encountered a DROP TABLE, ALTER TABLE or DROP DBSPACE command
During RELOAD you are prompted for the log archives. You are prompted if
you want to skip a log archive tape or file. The LISTLOG and APPLYLOG will
execute only on the DB2 commands loaded into the work files during RELOAD.
v If you are using LOGMODE=L, after a Data Restore RESTORE of a storage
pool from an older archive than the most recent, you have to apply all the log
archives in between, including the current log, in order to have the information
26
Data Restore Guide
up to date and consistent. If you are running in LOGMODE=A, you have to
either restore a full archive, or restore a storage pool from the most recent
archive.
Data Restore BACKUP
The Data Restore BACKUP command creates a Data Restore archive of the entire
database. The Data Restore BACKUP command is performed as a DB2 user archive,
thus the database has to be offline. As the DB2 ARCHIVE, it includes the directory
and dbextents and does not include the log.
The output can be used as input for either:
v the Data Restore command RESTORE to restore a storage pool
v the Data Restore command RESTORE to restore the whole database
v the Data Restore command RELOAD for a selective restore of tables
Data Restore BACKUP writes additional information to the beginning of the
archive file which is used for a later Data Restore RELOAD of tables. This is
equivalent to the work files SYS0001, HEADER and DIRWORK of TRANSLATE, as
described on page 184.
If recovery to a certain ’point in time’ is needed, the DB2 database must be
running with LOGMODE=L or LOGMODE=A. This means that successive logs can
be applied up to a certain point in time.
Data Restore BACKUP can be directed to tape, disk or both.
Files
The Data Restore BACKUP requires some input and output files:
For VM only:
v SYSIN file - the file contains the command to be processed.
v SYSPRINT file - Data Restore feature creates a report that lists the SYSIN
values, messages and results.
For VM and VSE:
v ARCHIV - first copy
v ARCHIV2 - second copy
If only one output is needed the FILEDEF (in VM) and DLBL/TLBL (in VSE) for
ddname ARCHIV and the OPTIONS DEVICE= TAPE/DASD defines this output to
tape or disk.
If dual backup is needed then the FILEDEF (in VM) or DLBL/TLBL (in VSE) for
ddname ARCHIV2 and the OPTIONS DEVICE2=DASD or TAPE define the second
output, which can be again either to tape or disk. Refer to Figure 161 on page 203
for a sample JCL, or Figure 162 on page 203 and Figure 163 on page 203 for a
sample exec and SYSIN for the BACKUP.
In VM the user, running the backup, must have access to the database production
disk, default 195, because the “dbname SQLFDEF” file is used to link and access the
database minidisks.
Chapter 3. Archive and Recovery
27
In VSE, the backup job must also execute the DLBL statements for directory and
dbextents.
For backup to disk, the following restrictions apply:
In VM, the size of the output file is limited to the size of the minidisk, and
because a minidisk can only be defined on a single volume, the maximum size
is the size of the volume.
In VSE, the space for this file can be allocated on different volumes, by defining
space for this user catalog on different volumes. The size of the file is limited to
4GB by VSAM, and to the physical space that is available for allocation by this
catalog.
Data Restore BACKUP FULL/INCREMENTAL
Both FULL and INCREMENTAL backups are considered by the database manager
as a user archive.
The BACKUP FULL archive includes the directory and dbextents, but not the log.
This backup is equivalent to the one produced by the BACKUP function as
explained in the previous paragraph, but can be a reference for a further
INCREMENTAL backup.
The BACKUP INCREMENTAL archive includes the directory and only dbextent
pages modified since the BACKUP FULL has been processed.
To process INCREMENTAL backup, a FULL BACKUP must be executed first to
generate a complete copy.
The time necessary to process a BACKUP INCREMENTAL function is very low
compared with any archive or user archive procedure.
While processing the INCREMENTAL BACKUP, there is no need to access any
FULL archive.
In both cases, the output can be used as input for either:
v The Data Restore command RESTORE to restore a storage pool
v The Data Restore command RESTORE to restore the whole database
v The Data Restore command RELOAD for a selective restore of tables
Data Restore BACKUP writes additional information to the beginning of the
archive file which is used for a later Data Restore RELOAD of tables. This is
equivalent to the work files SYS0001, HEADER and DIRWORK of TRANSLATE, as
described on page 184.
If recovery to a certain ’point in time’ is needed, the DB2 database must be
running with LOGMODE=L or LOGMODE=A. This means that successive logs can
be applied up to a certain point in time.
Data Restore BACKUP can be directed to tape, disk or both.
Files
The Data Restore BACKUP requires some input and output files:
For VM only:
28
Data Restore Guide
v SYSIN file - the file contains the command to be processed.
v SYSPRINT file - Data Restore feature creates a report that lists the SYSIN
values, messages and results.
For VM and VSE:
v ARCHIV - first copy
v ARCHIV2 - second copy
If only one output is needed the FILEDEF (in VM) and DLBL/TLBL (in VSE) for
ddname ARCHIV and the OPTIONS DEVICE= TAPE/DASD defines this output to
tape or disk.
If dual backup is needed then the FILEDEF (in VM) or DLBL/TLBL (in VSE) for
ddname ARCHIV2 and the OPTIONS DEVICE2=DASD or TAPE define the second
output, which can be again either to tape or disk. Refer to Figure 161 on page 203
for a sample JCL, or Figure 162 on page 203 and Figure 163 on page 203 for a
sample exec and SYSIN for the BACKUP.
In VM the user, running the backup, must have access to the database production
disk, default 195, because the “dbname SQLFDEF” file is used to link and access the
database minidisks.
In VSE, the backup job must also execute the DLBL statements for directory and
dbextents.
For backup to disk, the following restrictions apply:
In VM, the size of the output file is limited to the size of the minidisk, and
because a minidisk can only be defined on a single volume, the maximum size
is the size of the volume.
In VSE, the space for this file can be allocated on different volumes, by defining
space for this user catalog on different volumes. The size of the file is limited to
4GB by VSAM, and to the physical space that is available for allocation by this
catalog.
Data Restore RESTORE
The Data Restore RESTORE command can be used to recover a single storage pool,
a set of storage pools or an entire database after a system or disk failure, or to
create a copy of the entire database on the same or another system. To run Data
Restore RESTORE, the database manager must be offline.
For information about log recovery after RESTORE of a storage pool, refer to “Log
Recovery” on page 26.
The input can be any of the following:
v A Data Restore archive (the output of a Data Restore BACKUP).
v A Data Restore FULL or INCREMENTAL archive (the output of a Data Restore
BACKUP FULL or BACKUP INCREMENTAL function).
v A translated DB2 archive.
In VSE, the Data Restore feature formats the BDISK while restoring the database.
In VM, if the Data Restore feature attempts to read or write the database BDISK, a
dbextent or log disk, the minidisk will be linked as the same CUU as the original
database minidisk. The Data Restore feature must have authority to link to all
Chapter 3. Archive and Recovery
29
|
||
|
|
|