|
|
// JOB RESTORE
(1) ---> // LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
(2) ---> // EXEC PROC=ARIS73DB
(3) ---> // TLBL ARCHIV,’ARCHIVE.DB2’,,,1
(4) ---> // ASSGN SYS006,180
(5) ---> // MTC REW,SYS006
(6) ---> // EXEC XTS91001,SIZE=AUTO
(7) ---> CONTROL DBNAME=dbname
(8) ---> RESTORE
(9) ---> /*
Figure 77. Sample of JCL (VSE) to Restore an Entire Database from the Last Archive
Statement 1
Specifies the DB2 for VSE for the RESTORE
function.
Statement 2
Contains all DLBL defining the directory, log, and
all dbextents.
Statement 3
Identifies the label on the tape.
Statement 4
Assigns a specific tape drive that will be used.
Statement 5
Rewinds the tape to its first file.
Statement 6
Executes the program XTS91001.
Statement 7
CONTROL statement specifying the dbname to
control compatibility with BACKUP tape provided
Statement 8
Specifies the function to be performed. In this case,
the RESTORE function.
Statement 9
Ends the SYSIN file.
If you are in a VM environment, restore your last archive using an EXEC similar to
that in Figure 78.
/**/
(1) ---> ’VMFPLC2
REW’
(2) ---> ’FILEDEF
ARCHIV TAP1 SL 1 (RECFM VB BLOCK 32760’
(3) ---> ’FILEDEF
SYSPRINT DISK RESTORE SYSPRINT A’
(4) ---> ’FILEDEF
SYSIN DISK RESTORE SYSIN A’
(5) ---> ’XTS91001’
Figure 78. Sample of JCL (VM) to Restore an Entire Database from the Last Archive
Statement 1
Rewinds the tape to its first file.
Statement 2
Identifies the file on the tape.
Statement 3
Specifies the destination of the SYSPRINT file.
Statement 4
Identifies the input source for the RESTORE
function on SYSIN.
Statement 5
Executes the program XTS91001.
The SYSIN file must contain the following statements:
110
Data Restore Guide
(6) ---> CONTROL DBNAME=dbname
(7) ---> RESTORE
Figure 79. Sample of SYSIN to Restore an Entire Database from the Last Archive
Statement 6
Points the user to the appropriate database.
Statement 7
Specifies the function to be performed; in this case,
the RESTORE function.
Make sure that you can access the dbname SQLFDEF file on the database
production disk when you use Data Restore.
Use the LINK command and the ACCESS command to access the Data Restore
minidisk to execute the RESTORE function.
Step 4B. Restoring Specific Storage Pools Using the Last
Archive
// JOB RESTORE
(1) ---> // LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
(2) ---> // EXEC PROC=ARIS73DB
(3) ---> // TLBL ARCHIV,’ARCHIVE.DB2’,,,1
(4) ---> // DLBL DIRWORK,,,VSAM
(5) ---> // ASSGN SYS006,180
(6) ---> // MTC REW,SYS006
(7) ---> // EXEC XTS91001,SIZE=AUTO
(8) ---> CONTROL DBNAME=dbname
(9) ---> RESTORE POOL=(n, m, ...)
(10)---> /*
Figure 80. Sample of JCL (VSE) to Restore a List of Storage Pools from the Last Archive
Statement 1
Specifies the DB2 for VSE library for the RESTORE
function.
Statement 2
Contains all DLBL defining the directory, log, and
all dbextents.
Statement 3
Identifies the label on the tape.
Statement 4
Identifies the DIRWORK work file.
Statement 5
Assigns a specific tape drive that will be used.
Statement 6
Rewinds the tape to its first file.
Statement 7
Executes the program XTS91001.
Statement 8
CONTROL statement specifying the dbname to
control compatibility with BACKUP tape provided
Statement 9
Specifies the function to be performed. In this case,
the RESTORE function for some storage pools
(maximum 10 pools).
Statement 10
Ends the SYSIN file.
If you are in a VM environment, restore your last archive using an EXEC similar to
that in Figure 81 on page 112.
Chapter 9. Restoring an Entire Database or an Entire Storage Pool
111
/**/
(1) ---> ’VMFPLC2 REW’
(2) ---> ’FILEDEF ARCHIV TAP1 SL 1 (RECFM VB BLOCK 32760’
(3) ---> ’FILEDEF DIRWORK DISK DIRWORK DATA H(RECFM F BLOCK 512’
(4) ---> ’FILEDEF SYSPRINT DISK RESTORE SYSPRINT A’
(5) ---> ’FILEDEF SYSIN DISK RESTORE SYSIN A’
(6) ---> ’XTS91001’
Figure 81. Sample of JCL (VM) to Restore a List of Storage Pools from the Last Archive
Statement 1
Rewinds the tape to its first file.
Statement 2
Identifies the file on the tape.
Statement 3
Identifies the DIRWORK work file.
Statement 4
Specifies the destination of the SYSPRINT file.
Statement 5
Identifies the input source for the RESTORE
function on SYSIN.
Statement 6
Executes the program XTS91001.
The SYSIN file must contain the following statements:
(7) ---> CONTROL DBNAME=dbname
(8) ---> RESTORE POOL=(n, m, ...)
Figure 82. Sample of SYSIN to Restore a List of Storage Pools from the Last Archive
Statement 7
Points the user to the appropriate database.
Statement 8
Specifies the function to be performed; in this case,
the RESTORE function for some storage pools
(maximum 10 pools).
Make sure that you can access the dbname SQLFDEF file on the database
production disk when you use Data Restore.
Use the LINK command and the ACCESS command to access the Data Restore
minidisk to execute the RESTORE function.
At this point, you have restored the database or the storage pools.
Step
4C. Restoring an Entire Database Using Any Archive
If you are in a VSE environment, restore your archive using a job similar to that
shows in Figure 83 on page 113.
112
Data Restore Guide
// JOB RESTORE
(1) ---> // LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
(2) ---> // EXEC PROC=ARIS73DB
(3) ---> // TLBL ARCHIV,’ARCHIVE.DB2’,,,1
(4) ---> // ASSGN SYS006,180
(5) ---> // MTC REW,SYS006
(6) ---> // EXEC XTS91001,SIZE=AUTO
(7) ---> CONTROL DATE=20/09/95
TIME=18:05:12, DBNAME=dbname
(8) ---> RESTORE
(9) ---> /*
Figure 83. Sample of JCL (VSE) to Restore an Entire Database from Any Archive
Statement 1
Specifies the DB2 Server for VSE & VM library for
the RESTORE function.
Statement 2
Contains all the DLBL defining the directory, log,
and all dbextents.
Statement 3
Identifies the label on the tape.
Statement 4
Assigns a specific tape drive that will be used.
Statement 5
Rewinds the tape to its first file.
Statement 6
Executes the program XTS91001.
Statement 7
Specifies instructions to the RESTORE function of
Data Restore, for example, date and time. For more
information about what date and time to specify,
see Chapter 13, “Displaying the Contents of an
Archive File” on page 151.
Statement 8
Specifies the function to be performed. In this case,
the RESTORE function.
Statement 9
Ends the SYSIN file.
If you are in a VM environment, restore your archive using an EXEC similar to
that in Figure 84.
/**/
(1) ---> ’VMFPLC2
REW’
(2) ---> ’FILEDEF
ARCHIV TAP1 SL 1 (RECFM VB BLOCK 32760’
(3) ---> ’FILEDEF
SYSPRINT DISK RESTORE SYSPRINT A’
(4) ---> ’FILEDEF
SYSIN DISK RESTORE SYSIN A’
(5) ---> ’XTS91001’
Figure 84. Sample of Procedure (VM) to Restore an Entire Database from Any Archive
Statement 1
Rewinds the tape to its first file.
Statement 2
Identifies the file on the tape.
Statement 3
Specifies the destination of the SYSPRINT file.
Statement 4
Identifies the input source for the DESCRIBE
function.
Statement 5
Executes the program XTS91001.
The SYSIN file must contain the following statements:
Chapter 9. Restoring an Entire Database or an Entire Storage Pool
113
(6) ---> CONTROL DBNAME=dbname DATE=20/09/95 TIME=18:05:12
(7) ---> RESTORE
Figure 85. Sample of SYSIN to Restore an Entire Database from Any Archive
Statement 6
Specifies instructions for the RESTORE function of
Data Restore; for example, date and time of the
archives. For more information about what date
and time to specify, see Chapter 13, “Displaying the
Contents of an Archive File” on page 151.
Statement 7
Specifies the function to be performed; in this case,
the RESTORE function.
Make sure that you can access the dbname SQLFDEF file on the database
production disk when you use Data Restore.
Use the LINK command and the ACCESS command to access the Data Restore
minidisk to execute the RESTORE function.
When you finish restoring the database, you see a list of all messages issued
similar to the report shown in Figure 86.
XTS9-143
CONTROL DBNAME=dbname
XTS9-143
RESTORE
XTS9-143
/*
XTS9-100
Data Restore feature VERSION 7.1.0
XTS9-304
Restore from user archive invoked
XTS9-305
Current database will be destroyed
XTS9-196
Do you want to continue the RESTORE process ?
XTS9-406
Enter 0(Cancel) OR 1(Continue)
1
XTS9-136
Processing dbname archived on (17/10/95-12:12:49)
XTS9-010
Restoring DIRECTORY
XTS9-024
20010 Directory blocks restored
XTS9-011
Restoring DDSK1
XTS9-025
2870 blocks restored
XTS9-011
Restoring DDSK2
XTS9-025
383 blocks restored
XTS9-011
Restoring DDSK3
XTS9-025
7 blocks restored
XTS9-011
Restoring DDSK4
XTS9-025
1 block restored
XTS9-011
Restoring DDSK5
XTS9-025
1 block restored
XTS9-011
Restoring DDSK6
XTS9-025
1 block restored
XTS9-011
Restoring DDSK7
XTS9-025
1 block restored
XTS9-011
Restoring DDSK8
XTS9-025
1 block restored
XTS9-307
Startup the database manager with parameter "STARTUP=U"
XTS9-007
Processing successfully completed
Figure 86. Example of a Function Report After a Database Is Restored
Step
4D. Restoring Specific Storage Pools Using Any Archive
If you are in a VSE environment, restore your archive using a job similar to that
shown in Figure 87 on page 115.
114
Data Restore Guide
// JOB RESTORE
(1) ---> // LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
(2) ---> // EXEC PROC=ARIS73DB
(3) ---> // TLBL ARCHIV,’ARCHIVE.DB2’,,,1
(4) ---> // DLBL DIRWORK,,,VSAM
(5) ---> // ASSGN SYS006,180
(6) ---> // MTC REW,SYS006
(7) ---> // EXEC XTS91001,SIZE=AUTO
(8) ---> CONTROL DATE=20/09/95 TIME=18:05:12, DBNAME=dbname
(9) ---> RESTORE POOL=(n, m, ...)
(10)---> /*
Figure 87. Sample of JCL (VSE) to Restore a List of Storage Pools from Any Archive
Statement 1
Specifies the DB2 for VSE library for the RESTORE
function.
Statement 2
Contains all the DLBL defining the directory, log,
and all dbextents.
Statement 3
Identifies the label on the tape.
Statement 4
Identifies the DIRWORK work file.
Statement 5
Assigns a specific tape drive that will be used.
Statement 6
Rewinds the tape to its first file.
Statement 7
Executes the program XTS91001.
Statement 8
Specifies instructions to the RESTORE function of
Data Restore, for example, date and time. For more
information about what date and time to specify,
see Chapter 13, “Displaying the Contents of an
Archive File” on page 151.
Statement 9
Specifies the function to be performed. In this case,
the RESTORE function for some storage pools
(maximum 10 pools).
Statement 10
Ends the SYSIN file.
If you are in a VM environment, restore your archive using an EXEC similar to
that in Figure 88.
/**/
(1) ---> ’VMFPLC2
REW’
(2) ---> ’FILEDEF
ARCHIV TAP1 SL 1 (RECFM VB BLOCK 32760’
(3) ---> ’FILEDEF
DIRWORK DISK DIRWORK DATA H(RECFM F BLOCK 512’
(4) ---> ’FILEDEF
SYSPRINT DISK RESTORE SYSPRINT A’
(5) ---> ’FILEDEF
SYSIN DISK RESTORE SYSIN A’
(6) ---> ’XTS91001’
Figure 88. Sample of Procedure (VM) to Restore a List of Storage Pools from Any Archive
Statement 1
Rewinds the tape to its first file.
Statement 2
Identifies the file on the tape.
Statement 3
Identifies the DIRWORK work file.
Statement 4
Specifies the destination of the SYSPRINT file.
Chapter 9. Restoring an Entire Database or an Entire Storage Pool
115
Statement 5
Identifies the input source for the DESCRIBE
function.
Statement 6
Executes the program XTS91001.
The SYSIN file must contain the following statements:
(7) ---> CONTROL DBNAME=dbname DATE=20/09/95 TIME=18:05:12
(8) ---> RESTORE POOL=(n, m, ...)
Figure 89. Sample of SYSIN to Restore a List of Storage Pools from Any Archive
Statement 7
Specifies instructions for the RESTORE function of
Data Restore; for example, date and time of the
archives. For more information about what date
and time to specify, see Chapter 13, “Displaying the
Contents of an Archive File” on page 151.
Statement 8
Specifies the function to be performed; in this case,
the RESTORE function.
Make sure that you can access the dbname SQLFDEF file on the database
production disk when you use Data Restore.
Use the LINK command and the ACCESS command to access the Data Restore
minidisk to execute the RESTORE function.
When you finish restoring some storage pools, you see a list of all messages issued
similar to the report shown in Figure 90.
XTS9-143 CONTROL DBNAME=dbname
XTS9-143 RESTORE POOL=2
XTS9-143 /*
XTS9-196 Do you want to continue the RESTORE process ?
XTS9-406 Enter 0(Cancel) OR 1(Continue)
XTS9-403 Reply is 1
XTS9-136 Processing dbname archived on (11/06/96-17:06:37)
XTS9-182 Following files are needed for recovery
XTS9-195 UARCHIVE
currently mounted
XTS9-179 Current log
XTS9-406 Enter 0(Cancel) OR 1(Continue)
XTS9-403 Reply is 1
XTS9-211 Beginning update of directory
XTS9-406 Enter 0(Cancel) OR 1(Continue)
XTS9-403 Reply is 1
XTS9-006 Processing DDSK2
XTS9-010
383 blocks restored
XTS9-307 Startup the database manager with parameter "STARTUP=U"
XTS9-007 Processing successfully completed
Figure 90. Example of a Function Report After Some Storage Pools are Restored
Step
4E. Restoring Using INCREMENTAL Archive
The following examples are processing the restore of an entire database but the
restore of specific storage pools can also be processed.
116
Data Restore Guide
// JOB RESTORE
(1) ---> // LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
(2) ---> // EXEC PROC=ARIS73DB
(3) ---> // TLBL ARCHIV,’ARCHIVE.DB2’,,,1
(4) ---> // TLBL FULLARC,’ARCHIVE.DB2’,,,1
(5) ---> // ASSGN SYS006,180
(6) ---> // MTC REW,SYS006
(7) ---> // EXEC XTS91001,SIZE=AUTO
(8) ---> // OPTIONS DEVICE2=TAPE
(9) ---> CONTROL DATE=20/09/95
TIME=18:05:12, DBNAME=dbname
(10) ---> RESTORE
(11) ---> /*
Figure 91. Sample of JCL (VSE) to Restore an Entire Database from Any Archive
Statement 1
Specifies the DB2 Server for VSE & VM library for
the RESTORE function.
Statement 2
Contains all the DLBL defining the directory, log,
and all dbextents.
Statement 3
Identifies the label on the tape.
Statement 4
Identifies the label of the FULL archive.
Statement 5
Assigns a specific tape drive that will be used.
Statement 6
Rewinds the tape to its first file.
Statement 7
Executes the program XTS91001.
Statement 8
The OPTIONS statement specifies, if the FULLARC
is on tape or DASD.
Statement 9
Specifies instructions to the RESTORE function of
Data Restore, for example, date and time. For more
information about what date and time to specify,
see Chapter 13, “Displaying the Contents of an
Archive File” on page 151.
Statement 10
Specifies the function to be performed. In this case,
the RESTORE function.
Statement 11
Ends the SYSIN file.
If you are in a VM environment, restore your archive using an EXEC similar to
that in Figure 92.
/**/
(1) ---> ’VMFPLC2
REW’
(2) ---> ’FILEDEF
FULLARC TAP1 SL 1 (RECFM VB BLOCK 3260’
(3) ---> ’FILEDEF
SYSPRINT DISK RESTORE SYSPRINT A’
(4) ---> ’FILEDEF
SYSIN DISK RESTORE SYSIN A’
(5) ---> ’XTS91001’
Figure 92. Sample of Procedure (VM) to Restore an Entire Database from Any Archive
Statement 1
Rewinds the tape to its first file.
Statement 2
Identifies the file on the tape.
Statement 3
Specifies the destination of the SYSPRINT file.
Chapter 9. Restoring an Entire Database or an Entire Storage Pool
117
Statement 4
Identifies the input source for the DESCRIBE
function.
Statement 5
Executes the program XTS91001.
The SYSIN file must contain the following statements:
(6) ---> CONTROL DBNAME=dbname DATE=20/09/95 TIME=18:05:12
(7) ---> RESTORE
Figure 93. Sample of SYSIN to Restore an Entire Database from Any Archive
Statement 6
Specifies instructions for the RESTORE function of
Data Restore; for example, date and time of the
archives. For more information about what date
and time to specify, see Chapter 13, “Displaying the
Contents of an Archive File” on page 151.
Statement 7
Specifies the function to be performed; in this case,
the RESTORE function.
Make sure that you can access the dbname SQLFDEF file on the database
production disk when you use Data Restore.
Use the LINK command and the ACCESS command to access the Data Restore
minidisk to execute the RESTORE function.
When you finish restoring the database, you see a list of all messages issued
similar to the report shown in Figure 94 on page 119.
118
Data Restore Guide
XTS9-143 CONTROL DBNAME=dbname
XTS9-143 RESTORE
XTS9-143 /*
XTS9-100 Data Restore feature VERSION 7.1.0
XTS9-304 Restore from user archive invoked
XTS9-305 Current database will be destroyed
XTS9-196 Do you want to continue the RESTORE process ?
XTS9-406 Enter 0(Cancel) OR 1(Continue)
1
XTS9-136 Processing dbname archived on (17/10/95-12:12:49)
XTS9-010 Restoring DIRECTORY
XTS9-024
20010 Directory blocks restored
XTS9-011 Restoring DDSK1
XTS9-025
2870 blocks restored
XTS9-011 Restoring DDSK2
XTS9-025
383 blocks restored
XTS9-011 Restoring DDSK3
XTS9-025
7 blocks restored
XTS9-011 Restoring DDSK4
XTS9-025
1 block restored
XTS9-011 Restoring DDSK5
XTS9-025
1 block restored
XTS9-011 Restoring DDSK6
XTS9-025
1 block restored
XTS9-011 Restoring DDSK7
XTS9-025
1 block restored
XTS9-011 Restoring DDSK8
XTS9-025
1 block restored
XTS9-307 Startup the database manager with parameter "STARTUP=U"
XTS9-007 Processing successfully completed
Figure 94. Example of a Function Report After a Database Is Restored
Step 5. Deciding Whether to Use Log Recovery
At this point, you have restored the database or the storage pools.
Now, you need to make sure you maintain your data integrity. Restart the
application server with the STARTUP=U parameter to indicate that you have
restored the database from a user archive and want the log history area updated
and the log recovery applied.
Figure 95 on page 120 shows an example of restarting after restoring the entire
database.
Chapter 9. Restoring an Entire Database or an Entire Storage Pool
119
ARI0025I THE PROGRAM ARISQLDS IS LOADED AT 7F2078.
ARI0025I THE PROGRAM ARICMOD IS LOADED AT 905D80.
ARI0025I THE PROGRAM ARIXRDS IS LOADED AT 9CA000.
ARI0025I THE PROGRAM ARIXSXR IS LOADED AT B1C000.
ARI0208D DID THE USER-RESTORE OF THE DIRECTORY AND 8
DBEXTENT(S) EXECUTE SUCCESSFULLY
ENTER 0(N0) OR 1(YES)
1
ARI0283I LOG ANALYSIS COMPLETE.
ARI0282I LUW UNDO COMPLETED.
ARI0281I LUW REDO IS COMPLETED.
ARI0060I DATABASE MANAGER INITIALIZATION COMPLETE.
ARI0045I READY FOR OPERATOR COMMUNICATIONS
Figure 95. Restart Your Database Manager After Restoring the Database
Note: If you restored some storage pools only but not the entire database, you
must restart the database server with STARTUP=U parameter.
If a user archive (SQLEND UARCHIVE) was not used or you do not want to
execute log recovery and you restored the entire database, do a COLDLOG to clear
the contents of the current log.
120
Data Restore Guide
Chapter
10. Backing Up Parts of a Database
In today’s data processing environment, running global business operations in a 24
hours-a-day, 7 days-a-week mode is a key goal of many data centers. Data Restore
helps data centers approach this goal by minimizing the time the database needs to
be down.
It is for users who need the maximum database availability that the UNLOAD
function is most useful. With the UNLOAD function, you can do a partial backup
by unloading while the server is online or offline. If a physical error occurs, you
can reload tables from the UNLOAD file after redefining the database. With the
UNLOAD function, you can back up dbspaces containing critical tables more
frequently than the rest of your database. If you have defined one table per
dbspace, you effectively have table-level backup.
If a logical error occurs, you can reload all dbspaces in error from the unloaded
tape or DASD.
Note: Forward log recovery is not available when restoring from unloaded
dbspaces.
The primary purposes of the UNLOAD function is to allow you to:
v Unload dbspaces with the server online
v Unload dbspaces with the server offline
v Unload a single dbspace
v Unload multiple dbspaces
Note: Data Restore executes a physical unload of all pages containing data for the
specified dbspaces. For that reason, the UNLOAD command and the
RELOAD command of this feature and the UNLOAD command and the
RELOAD command of the IBM DATABASE 2 Server for VSE & VM Services
Utility (DBSU) are not compatible, so one cannot use the output of the other.
Using the UNLOAD Command
The UNLOAD command can include up to 90 dbspace names on a maximum of 10
SYSIN lines. In VSE, the REWIND parameter on the OPTIONS line, can be used to
specify whether the output tape should be positioned (REWIND=YES) on not
(REWIND=NO). In VM, you can use the LEAVE parameter on the FILEDEF for the
output file to achieve the same results. Depending on the requirements, you can
direct the UNLOAD function in a variety of ways as shown on page “UNLOAD”
on page 186.
The UNLOAD command unloads dbspaces to an output file.
Unloading All Dbspaces
You can use an asterisk instead of a dbspace name to unload all the tables as
shown in Figure 96 on page 122.
121
(1) ---> UNLOAD DBSPACE=(*)
Figure 96. Unload All Dbspaces in the Database
Statement 1
Indicates that all dbspaces are to be unloaded.
Figure 97 shows how to use the JCL to unload dbspaces in a VSE environment.
// JOB UNLOAD
(1)
---> // LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
(2)
---> // EXEC PROC=ARIS73DB
(3)
---> // TLBL ARCHIV,’UNLOAD.DB2’
(4)
---> // ASSGN SYS006,180
(5)
---> // MTC REW,SYS006
(6)
---> // EXEC XTS91001,SIZE=AUTO, PARM=’DBNAME(dbname)’
(7)
---> OPTIONS DEVICE=TAPE,REWIND=YES/NO
(8)
---> CONTROL DBAPW=XXXXXXXX,DBNAME=dbname
(9)
---> UNLOAD DBSPACE=(*)
/*
Figure 97. JCL to Unload All Dbspaces
Statement 1
Specifies the DB2 library for the UNLOAD function.
Statement 2
Contains all DLBL defining directory, log, and all dbextents.
Statement 3
Identifies the label on the tape.
Statement 4
Assigns a specific tape drive that will be used.
Statement 5
Rewinds the tape to its first file.
Statement 6
Executes the program XTS91001.
Statement 7
Sets the device to a tape volume. In VSE, you can specify
REWIND=YES or NO,(the default value is YES). If REWIND=NO
is specified, at OPEN/CLOSE time the tape will not be positioned.
Statement 8
Uses the Control statement to specify the password for SQLDBA
and identifies the dbname to be specified on the output file.
Statement 9
Specifies which database to UNLOAD. In this case, all dbspaces
will be unloaded.
Figure 98 shows how to use an EXEC to unload dbspaces in a VM environment.
/**/
(1) ---> ’FILEDEF ARCHIV TAP1 SL 1 (RECFM VB BLOCK 32760’
(2) ---> ’FILEDEF SYSPRINT DISK UNLOAD SYSPRINT A’
(3) ---> ’FILEDEF SYSIN DISK UNLOAD SYSIN A’
(4) ---> ’XTS91001’
Figure 98. EXEC to Unload All Dbspaces
The SYSIN file must contain the following statements:
122
Data Restore Guide
(5) ---> CONTROL DBNAME=dbname DBAPW=XXXXXXXX
(6) ---> UNLOAD DBSPACE=(*)
Figure 99. SYSIN File to Specify Unloading All Dbspaces
Statement 1
Identifies the output file.
Statement 2
Identifies the SYSPRINT file
Statement 3
Identifies the SYSIN file.
Statement 4
Executes the program XTS91001.
Statement 5
Uses the Control statement to specify the password for the
SQLDBA and identifies the dbname to be processed.
Statement 6
Specifies the type of UNLOAD to perform. In this case, an
UNLOAD of all dbspaces will be performed.
Make sure that when you are executing Data Restore you can access the dbname
SQLFDEF file on the database production disk.
Chapter 10. Backing Up Parts of a Database
123
124
Data Restore Guide
Chapter
11. Restoring Logical Elements
You can reload tables from DB2 archive in one of two ways:
v Use the TRANSLATE function to convert the DB2 archive into a format that will
minimize the time required for RELOAD. The TRANSLATE function can be run
offline, after you have taken the DB2 archive, while the database is up.
v Run the RELOAD directly from the DB2 archive tape. The archive tape will be
read twice during RELOAD processing.
There is a trade-off between running the TRANSLATE function to improve
RELOAD processing and reading the DB2 archive tape twice if the TRANSLATE is
not done.
This chapter contains all of the steps necessary to reload a table. You can reload
directly from a Data Restore archive file, produced either by the BACKUP,
BACKUP FULL/INCREMENTAL, or UNLOAD function.
Step 1 describes how to use the TRANSLATE function. You can use TRANSLATE
to convert a DB2 archive into a format that improves RELOAD processing time.
Step 2 shows how to restore portions of the database from a BACKUP or
UNLOAD file using the RELOAD function. Use RELOAD to recover the following:
v Existing tables after deleting existing rows
v New tables after creating the table
v Rows in an existing table
v Tables with all of their associated indexes, referential integrity, views, grants,
comments, and labels
v Tables to prepare for forward recovery from log files
Step 3 shows how to list the changes recorded in the log archives and the current
log for the tables being reloaded.
Step 4 shows how to apply the changes from the log archives and the current log.
Note: The reload process is done using DB2 commands, so this will create log
records unless the RELOAD is run in single user mode with LOGMODE=N.
Recovery From a Logical Error
If the Dbspace can be Dropped
You can:
v Either repair the database in the following way:
1. Drop the dbspace for which there is a logical error
2. Create the dbspace again
3. Acquire the dbspace
4. Reload all tables of the dbspace that you dropped previously using the Data
Restore RELOAD with forward recovery from an archive, as described in
“From a Physical Error” on page 104.
125
v Or restore the storage pool or the database as described in “From a Physical
Error” on page 104. Depending on the number of tables in the dbspace in error,
this may be the quicker way.
If LOGMODE=Y you cannot use forward recovery. However, the Data Restore
feature does let you recover some data from the database with the SELECT
command. The UNLOAD command cannot be used because it will end abnormally
when it tries to read the corrupted data. The output from such a partially
unloaded dbspace is not usable for the RELOAD process. Try to unload data using
the SELECT command from the tables that reside in the dbspace, that report the
error. The SELECT command ends at the point where it tries to read the data in
error. Neither the Data Restore feature nor DB2 can bypass the damaged area and
continue unloading the required data. Some of this unloaded data might be useful
to help determine the differences between the table reloaded from the archive and
the output of the previously executed SELECT.
If the Dbspace cannot be Dropped
There are cases where DB2 does not allow you to drop a dbspace but abnormally
ends instead. In this situation, you can:
v Either restore the database from archive tapes and consecutive log files,
including the current log. Follow the description in “Recover from Directory
Failure” on page 103.
v Or disable the dbspace and contact the IBM Support Center for help. Acquire a
new dbspace and Data Restore RELOAD and APPLYLOG the individual tables
of this dbspace from an archive as described before.
Warning: The tables must be reloaded with a new name, because you cannot
have duplicate table names in the catalog. This means that applications, pointing
to the tables in the disabled dbspace, will need to be changed, preprocessed and
compiled.
Step
1. Process a Standard DB2 Archive with Data Restore
Some databases must run 24 hours a day. Since user archives cannot be taken
online, only DB2 database archives can be taken if the database must remain
online.
There are two ways to process a DB2 archive tape to restore individual tables:
v If you rarely restore a table, you can specify the parameter ARCHTYPE=DB2 on
an OPTIONS statement and proceed to step 2. Refer to “OPTIONS and
CONTROL Statements” on page 167 for more information on the OPTIONS
statement in the SYSIN file. Remember that the RELOAD function reads DB2
database archives twice but reads Data Restore archives only once.
v If you often restore tables from archives, or if you want to minimize the time
necessary to restore the tables, execute the TRANSLATE function to convert the
archive tapes produced by the ARCHIVE operator command into a Data Restore
BACKUP file. This TRANSLATE function reads DB2 Database archive files
twice, but produces an optimized file that can be used by the RELOAD function.
Notes:
1. You can only translate archive tapes produced by DB2 Version 7 Release 3
2. Take the online archive with the database in LOGMODE L.
126
Data Restore Guide
STANDARD DB2
ARCHIVE
TRANSLATE
BACKUP
OR
Figure 100. Translating a DB2 Server for VSE & VM Archive into a BACKUP
Before you can recover from DB2 Server for VSE & VM archives, you need to
create some work files.
Recommended sizes for the work files are as follows:
1. For SYS0001, calculate the space required by using 4 KB for each active page in
the DBSPACE.
2. For HEADER, use 4 KB for each HEADER page in the DBSPACE.
3. For DIRWORK, use the same size as the directory.
4. For archives, use the same size of the archive.
Procedures to Translate an Archive into a BACKUP File
Example of JCL (VSE) to translate an archive tape
// JOB TRANSLATE
(1) ---> // LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
(2) ---> // TLBL ARIARCH,’ARCHIVE.DB2’
(3) ---> // TLBL ARCHIV,’BACKUP.DB2’
(4) ---> // DLBL SYS0001,,,VSAM,RECSIZE=4096,RECORDS=(100,100)
(5) ---> // DLBL HEADER,,,VSAM,RECSIZE=4096,RECORDS=(100,100)
(6) ---> // DLBL DIRWORK,,,VSAM
(7) ---> // EXEC XTS91001,SIZE=AUTO
(8) ---> TRANSLATE
/*
Figure 101. Sample JCL to Translate an DB2 Server for VSE Archive into a BACKUP File
Statement 1
Specifies the DB2 Server for VSE and Data Restore libraries.
Statement 2
Specifies the DB2 Server for VSE archive file with ddname
ARIARCH.
Statement 3
Specifies the generated BACKUP file with ddname ARCHIV.
Statement 4
Specifies the work file SYS0001.
Statement 5
Specifies the work file HEADER.
Statement 6
Specifies the work file DIRWORK.
Chapter 11. Restoring Logical Elements
127
Statement 7
Runs the program XTS91001.
Statement 8
Specifies the TRANSLATE function.
Example of an EXEC to translate an archive tape
/**/
(1) ---> ’FILEDEF ARIARCH TAP1 SL 1 (RECFM FB BLOCK 28672 LRECL 4096’
(2) ---> ’FILEDEF ARCHIV DISK BACKUP DATA G (RECFM VB BLOCK 32760’
(3) ---> ’FILEDEF SYS0001
DISK SYS0001
DATA H(RECFM F BLOCK 4096’
(4) ---> ’FILEDEF HEADER
DISK HEADER
DATA H(RECFM F BLOCK 4096’
(5) ---> ’FILEDEF DIRWORK DISK DIRWORK DATA H(RECFM F BLOCK 0512’
(6) ---> ’FILEDEF SYSIN DISK TRANS SYSIN
A’
(7) ---> ’FILEDEF SYSPRINT DISK TRANS SYSPRINT A’
(8) ---> ’XEDIT TRANS SYSIN A’
(9) ---> ’XTS91001’
Figure 102. Sample EXEC to Translate a DB2 Server for VM Archive into a BACKUP File
Statement 1
Specifies the DB2 Server for VM archive file ddname ARIARCH.
Statement 2
Specifies the generated BACKUP file with ddname ARCHIV
function.
Statement 3
Specifies the work file SYS0001.
Statement 4
Specifies the work file HEADER.
Statement 5
Specifies the work file DIRWORK.
Statement 6
Specifies the SYSIN file which contains the function specification.
Statement 7
Specifies the SYSPRINT file which contains the report.
Statement 8
Allows you to edit the SYSIN file to make any required changes
before processing.
Statement 9
Runs the program XTS91001.
The SYSIN file must contain the following statements:
(1) ---> TRANSLATE
Figure 103. Sample SYSIN to Translate a DB2 Server for VM Archive into a BACKUP File
Statement 1
Specifies the TRANSLATE function.
Example of a Report from TRANSLATE
The list of all reloadable tables is displayed on SYSPRINT.
128
Data Restore Guide
XTS9-143 OPTIONS
XTS9-143 TRANSLATE
XTS9-143 /*
XTS9-100 Data Restore feature VERSION 7.1.0
XTS9-193 MOUNT FIRST TAPE OF DATABASE SERVER ARCHIVE
XTS9-406 ENTER 0(CANCEL) OR 1(CONTINUE)
XTS9-403 REPLY IS
1
XTS9-013 TABLE SQLDBA
.ACTIVITY
MAY BE RELOADED
XTS9-013 Table SQLDBA
.ALLGOOD
MAY BE RELOADED
XTS9-013 Table SQLDBA
.A0001
MAY BE RELOADED
XTS9-013 Table SQLDBA
.A0002
MAY BE RELOADED
XTS9-013 Table SQLDBA
.A0011
MAY BE RELOADED
XTS9-007 Processing successfully completed
Figure 104. Sample Report after Translation of a DB2 Server for VM Archive
Notes:
1. The translate process reads DB2 Server for VM database archive tapes twice.
2. When the message XTS9-193 is displayed you have to remount the archive
tapes beginning with the first archive tape.
3. You can restore the whole database from the translated DB2 Server for VM
database archive, as well as restoring parts of the database.
Step
2. Reload Procedures
When a table has been corrupted by a program or accidentally dropped, you may
have to restore that table. The following sections show the possibilities for
reloading a table.
The database server must be active to reload tables, and applications using the
server cannot access any table being reloaded during the reload process.
Note: The System Catalog tables cannot be reloaded.
Chapter 11. Restoring Logical Elements
129
DB2 ARCHIVE/
BACKUP/UNLOAD
OR
SYSIN
RELOAD
DB2
DATABASE
Figure 105. The RELOAD Function
Notes:
1. RELOAD can be used with BACKUP, UNLOAD, or DB2 Server for VSE & VM
archive files.
2. The DB2 Server for VSE & VM database consists of the directory, all dbextents,
and the log.
3. The SYSIN file will contain the list of tables to be restored with all the required
parameters.
Using the RELOAD Command
To reload tables you use the RELOAD command.
The RELOAD function may also reload a table from one database to another.
Specify the name of the source database on the CONTROL statement (parameter
DBNAME) to verify that the file was produced from that database. If you are
reloading directly from a DB2 Server for VSE & VM archive, specify
ARCHTYPE=DB2 on the OPTIONS statement. If you are reloading from a
translated DB2 Server for VSE & VM archive file, include the FILEDEFs or DLBLs
for the DIRWORK, HEADER and SYS0001 files produced by the TRANSLATE
function.
Reload a Table after Deleting Existing Rows
Sometimes you want to reload a table without keeping its current contents. To
delete all of the rows in the table before starting the RELOAD process, specify
PURGE for the FUNCT parameter.
You can execute this process from an UNLOAD or BACKUP file or from a DB2
Server for VSE & VM database archive file.
130
Data Restore Guide
DBSPACE SAMPLE
SQLDBA.INVENTORY
ARCHIVE
NEW
ROWS
INCORRECT
DELETE FROM
SQLDBA.INVENTORY
RELOAD
FUNCT=PURGE
INSERT INTO
SQLDBA.INVENTORY
DBSPACE SAMPLE
SQLDBA.INVENTORY
ROWS
FROM
BACKUP
Figure 106. RELOADing a Table with the PURGE Parameter
Reload a New Table
Sometimes you want to reload a table that does not exist in the database (for
example, a DROP table has been issued, or you are reloading into a different
database) or you just want to reload into a table with a different name. Data
Restore creates the table before reloading rows if you specify NEW for the FUNCT
parameter and specify the DBSPACE where the table is to be created (using the
DBSPACE parameter).
Note: For private dbspaces you must specify on owner (on the OWNER
parameter).
You can execute this process from an UNLOAD or BACKUP file or from an DB2
Server for VSE & VM database archive file.
Chapter 11. Restoring Logical Elements
131
TABLE SQLDBA.INVENTORY
UNKNOWN
ARCHIVE
CREATE TABLE
SQLDBA.INVENTORY
IN TEST
RELOAD
FUNCT=NEW
INSERT INTO
SQLDBA.INVENTORY
DBSPACE SAMPLE
ROWS
FROM
ARCHIVE
Figure 107. RELOADing a Table with the NEW Parameter
Reload Rows into an Existing Table
Sometimes you want to add new rows to an existing table from a BACKUP or
UNLOAD file. To do this, specify ADD for the FUNCT parameter.
You can execute this process from an UNLOAD or BACKUP file or from an DB2
Server for VSE & VM database archive file.
132
Data Restore Guide
DBSPACE SAMPLE
SQLDBA.INVENTORY
ARCHIVE
NEW
ROWS
RELOAD
INSERT INTO
FUNCT=ADD
SQLDBA.INVENTORY
DBSPACE SAMPLE
SQLDBA.INVENTORY
NEW
ROWS
+
ROWS
FROM
ARCHIVE
Figure 108. RELOADing a Table with the ADD Parameter
Reload a Table with Full Environment Recreation
Specify REPLACE for the FUNCT parameter to recreate the full environment on
the table you want to reload.
If you specify REPLACE and the target table is the same as the source table
(NEWTNAME and NEWCREATOR are not specified), the entire environment
including indexes, referential integrity, views, grants, comments, and labels is
recreated after the data are reloaded.
You can execute this process from an UNLOAD or BACKUP file or from an DB2
Server for VSE & VM database archive file.
Chapter 11. Restoring Logical Elements
133
DBSPACE SAMPLE
SQLDBA.INVENTORY
ARCHIVE
NEW
ROWS
DROP TABLE
SQLDBA.INVENTORY
CREATE TABLE
RELOAD
SQLDBA.INVENTORY
FUNCT=RELACE
IN SAMPLE
INSERT INTO
DBSPACE SAMPLE
SQLDBA.INVENTORY
SQLDBA.INVENTORY
CREATE
ENVIRONMENT
(INDEXES, VIEWS,...)
ROWS
FROM
SQLDBA.
INVENTORY
FROM
ARCHIVE
Figure 109. RELOADing a Table with the REPLACE Parameter
All of the commands for environment recreation will be displayed on SYSPRINT or
SYSLST.
134
Data Restore Guide
XTS9-143 CONTROL DBNAME=dbname CONFIRM=NO
XTS9-143 OPTIONS COMMITCOUNT=600 CASE=M
XTS9-143 RELOAD CREATOR=SQLDBA TNAME=T1
FUNCT=REPLACE
XTS9-143 /*
XTS9-100 Data Restore feature VERSION 7.1.0
XTS9-136 Processing BASE1
archived on (11/10/94-15:19:11)
XTS9-127
600 rows loaded procedure continuing
XTS9-127
1200 rows loaded procedure continuing
XTS9-127
1800 rows loaded procedure continuing
XTS9-127
2400 rows loaded procedure continuing
XTS9-102
2650 rows loaded procedure completed
XTS9-128
5 rows loaded into (SQLDBA.T1)
XTS9-128
933 rows loaded into (DATARFTR.SYSCOLUMNS)
XTS9-128
108 rows loaded into (DATARFTR.SYSCATALOG)
XTS9-128
693 rows loaded into (DATARFTR.SYSTABAUTH)
XTS9-128
106 rows loaded into (DATARFTR.SYSINDEXES)
XTS9-128
19 rows loaded into (DATARFTR.SYSVIEWS)
XTS9-128
14 rows loaded into (DATARFTR.SYSKEYCOLS)
XTS9-128
14 rows loaded into (DATARFTR.SYSKEYS)
XTS9-128
719 rows loaded into (DATARFTR.SYSUSAGE)
XTS9-128
39 rows loaded into (DATARFTR.SYSCOLAUTH)
XTS9-101
10 tables successfully processed
XTS9-314 Commit work successful for reload of data, creating required objects
ALTER TABLE "SQLDBA"."T1" ADD PRIMARY KEY ("C1" DESC) PCTFREE=10;
CREATE UNIQUE INDEX "SQLDBA"."IND1" ON "SQLDBA"."T1" ("C1" ASC ) PCTFREE=10;
ALTER TABLE "SQLDBA"."T1" ADD FOREIGN KEY "FOREIGNT2" ("C2")
REFERENCES "SQLDBA"."T2" ON DELETE RESTRICT;
COMMIT WORK ;
CONNECT SQLDBA
;
GRANT SELECT ON "SQLDBA"."T1" TO "PAUL" WITH GRANT OPTION;
COMMIT WORK ;
CONNECT PAUL
;
CREATE VIEW V1 AS SELECT * FROM SQLDBA.T1;
GRANT SELECT ON "PAUL"."V1" TO "GEORGE" WITH GRANT OPTION;
CREATE VIEW VV1 AS SELECT A.C1 FROM PAUL.V1 A,PAUL.V2 B WHERE A.C1 = B.C2;
GRANT SELECT ON "PAUL"."VV1" TO "JOHN";
COMMIT WORK ;
CONNECT SQLDBA
;
COMMENT ON TABLE "SQLDBA"."T1" IS ’TABLE T1’;
COMMENT ON COLUMN "SQLDBA"."T1"."C1" IS ’COLUMN C1’;
LABEL ON COLUMN "SQLDBA"."T1"."C1" IS ’""’;
COMMIT WORK ;
XTS9-007 Processing successfully completed
Figure 110. Example List Displayed for a RELOAD with the REPLACE Parameter
To recreate the environment, Data Restore creates copies of some of the System
Catalog tables, using DATARFTR as the creator name; for example, information
from SYSTEM.SYSCATALOG is loaded into DATARFTR.SYSCATALOG.
Reload Tables With Forward Recovery From the Log
When you reload a table from a BACKUP or a DB2 Server for VSE & VM archive
file, the reloaded table does not contain any of the changes made on that table
after the backup was taken. Those changes are recorded in any log archives taken
since the backup and in the current log.
If you want to reload a table and apply the changes, specify RECOVERY=YES on
the OPTIONS statement (refer to “OPTIONS and CONTROL Statements” on
page 167) during the RELOAD process. Refer to the next sections for information
on listing and applying the changes in the log.
Chapter 11. Restoring Logical Elements
135
Notes:
1. This function is available for BACKUP or DB2 Server for VSE & VM archive
files processed when the server was running in LOGMODE A or L.
2. Forward Recovery can only be applied to tables that exist in the archive that is
being used for the RELOAD.
3. Forward log recovery stops when it encounters a DROP TABLE or DROP
DBSPACE command in the log.
4. If dual logging is being used, Data Restore will switch to the secondary log if
there is an error processing the primary log.
|
5. Alternate Logging is fully supported by Data Restore and no modification is
|
needed.
LMBRWRK
ARCHIVE
SYSIN
LMBRLG1
RELOAD
LMBRLG2
Log 1
Log 2
LMBRLG3
DB2
DATABASE
Figure 111. Possible Input Sources for RELOADing Tables with Forward Recovery
136
Data Restore Guide
// JOB RELOAD
(1) ---> // LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
(2) ---> // DLBL LMBRWRK,,,VSAM
(3) ---> // TLBL ARCHIV,’ARCHIVE.SQL’,,,1
(4) ---> // ASSGN SYS006,180
(5) ---> // MTC REW,SYS006
(5) ---> // EXEC XTS91001,SIZE=AUTO,PARM=’DBNAME(DBNAME)’
(7) ---> CONTROL DBNAME=DBNAME
(7) ---> OPTIONS DEVICE=TAPE
(7) ---> RELOAD CREATOR=SQLDBA TNAME=CUSTOMERS FUNCT=PURGE
(7) ---> RELOAD CREATOR=SQLDBA TNAME=SUPPLIERS
(7) --->
FUNCT=NEW
(7) --->
NEWTNAME=NEWSUPPLIERS
(7) --->
DBSPACE=SAMPLE
/*
Figure 112. Example of JCL to Reload in a VSE Environment From a Full Backup
Statement 1
DB2 library must be specified for the RELOAD function.
Statement 2
LMBRWRK is used if the reloaded tables contain LONG columns.
Statement 3
ARCHIV is the ddname for the BACKUP file. It can be a BACKUP
file or a FULL BACKUP.
Statement 4
The tape containing the backup file is assigned.
Statement 5
The tape is rewound.
Statement 6
Program to execute functions is called. The DBNAME of the
database to process must be specified on the PARM option.
Statement 7
SYSIN for the program is specified.
// JOB RELOAD
(1) ---> // LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
(2) ---> // DLBL LMBRWRK,,,VSAM
(3) ---> // TLBL ARCHIV,’ARCHIVE.SQL’,,,1
(4) ---> // TLBL FULLARC,’ARCHIVE.SQL’,,,1
(5) ---> // ASSGN SYS006,180
(6) ---> // MTC REW,SYS006
(7) ---> // EXEC XTS91001,SIZE=AUTO,PARM=’DBNAME(dbname)’
(8) ---> CONTROL DBNAME=dbname WRKSIZE=4096
(8) ---> OPTIONS DEVICE=TAPE DEVICE2=TAPE
(8) ---> RELOAD CREATOR=SQLDBA TNAME=CUSTOMERS FUNCT=PURGE
(8) ---> RELOAD CREATOR=SQLDBA TNAME=SUPPLIERS
(8) --->
FUNCT=NEW
(8) --->
NEWTNAME=NEWSUPPLIERS
(8) --->
DBSPACE=SAMPLE
/*
Figure 113. Example of JCL to Reload in a VSE Environment From an Incremental Backup
Statement 1
DB2 library must be specified for the RELOAD function.
Statement 2
LMBRWRK is used if the reloaded tables contain LONG columns.
Statement 3
ARCHIV is the ddname for the last Incremental Backup.
Statement 4
FULLARC is the ddname for the last Full Backup.
Statement 5
The tape containing the backup file is assigned.
Statement 6
The tape is rewound.
Chapter 11. Restoring Logical Elements
137
Statement 7
Program to execute functions is called. The DBNAME of the
database to process must be specified on the PARM option.
Statement 8
SYSIN for the program is specified. WRKSIZE must be equivalent
to the parameter specified during the BACKUP function. DEVICE2
specifies if a TAPE or DASD (TLBL/DLBL) is used for Full Backup
(FULLARC).
// JOB RELOAD
(1) ---> // LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
(2) ---> // DLBL LMBRWRK,,,VSAM
(3) ---> // DLBL LMBRLG1,,,VSAM
(3) ---> // DLBL LMBRLG2,,,VSAM
(3) ---> // DLBL LMBRLG3,,,VSAM
(4) ---> // DLBL ARCHIV,,,VSAM
(5) ---> // TLBL LARCHIV,,,,1
(6) ---> // TLBL ARIARCH,’ARCHIVE.SQL’,,,1
(7) ---> // ASSGN SYS006, 180
(8) ---> // MTC REW,SYS006
(9) ---> // DLBL SYS0001,,,VSAM
(9) ---> // DLBL HEADER,,,VSAM
(9) ---> // DLBL DIRWORK,,,VSAM
(10) --->
// EXEC XTS91001,SIZE=AUTO,PARM=’DBNAME(dbname)’
(11) --->
OPTIONS ARCHTYPE=SQLDS
(11) --->
CONTROL DBNAME=dbname
(11) --->
RELOAD CREATOR=SQLDBA,TNAME=CUSTOMERS FUNCT=NEW
(11) --->
DBSPACE=SAMPLE
Figure 114. Example of JCL to Reload in a VSE Environment From a DB2 Archive
Statement 1
DB2 library must be specified for the RELOAD function.
Statement 2
LMBRWRK is used if the reloaded tables contain LONG columns.
Statement 3
LMBRLG1, LMBRLG2, and LBMRLG3 are used to extract all
referenced in log files for the reloaded tables when
RECOVERY=YES parameter is specified. They will be used to
execute forward log recovery.
Statement 4
ARCHIV is the work file for this process.
Statement 5
LARCHIV is the ddname for the Log archive files.
Statement 6
ARIARCH is the ddname for the DB2 database archive file.
Statement 7
The tape containing the archive file is assigned.
Statement 8
The tape is rewound.
Statement 9
SYS0001, HEADER, DIRWORD are work files for the function.
Statement 10
Program to execute functions is called. The DBNAME of the
DBNAME of the database to process must be specified on PARM
option.
Statement 11
SYSIN for the program is specified. OPTIONS statement specifies
that process is executed from DB2 database archive.
138
Data Restore Guide
/**/
’TAPE REW’
’FILEDEF ARCHIV TAP1 SL 1 (RECFM VB BLOCK 32760’
’FILEDEF LMBRWRK DISK LMBRWRK DATA A (RECFM FB BLOCK 28672 LRECL 4096’
’FILEDEF SYSPRINT DISK RELOAD SYSPRINT A’
’FILEDEF SYSIN DISK RELOAD SYSIN A’
’XEDIT RELOAD SYSIN A’
’XTS91001’
SYSIN file must contain the following statements :
CONTROL DBNAME=dbname
RELOAD CREATOR=SQLDBA TNAME=CUSTOMERS FUNCT=PURGE
RELOAD CREATOR=SQLDBA TNAME=SUPPLIERS
FUNCT=NEW
NEWTNAME=NEWSUPPLIERS
Figure 115. Example of VM Procedure to Reload From a Backup File
/**/
’TAPE REW’
’FILEDEF ARCHIV TAP1 SL 1 (RECFM VB BLOCK 32760’
’FILEDEF FULLARC TAP1 SL1 (RECFM VB BLOCK 32760’
’FILEDEF LMBRWRK DISK LMBRWRK DATA A (RECFM FB BLOCK 28672 LRECL 4096’
’FILEDEF SYSPRINT DISK RELOAD SYSPRINT A’
’FILEDEF SYSIN DISK RELOAD SYSIN A’
’XEDIT RELOAD SYSIN A’
’XTS91001’
SYSIN file must contain the following statements :
OPTIONS WRKSIZE=4096 DEVICE2=TAPE
CONTROL DBNAME=dbname
RELOAD CREATOR=SQLDBA TNAME=CUSTOMERS FUNCT=PURGE
RELOAD CREATOR=SQLDBA TNAME=SUPPLIERS
FUNCT=NEW
NEWTNAME=NEWSUPPLIERS
DBSPACE=SAMPLE
Figure 116. Example of VM Procedure to Reload From an Incremental Backup
/**/
(1) ---> ’FI LMBRLG1 DISK LMBRLG1 DATA A (RECFM VB BLOCK 32760’
’FI LMBRLG2 DISK LMBRLG2 DATA A (RECFM VB BLOCK 32760’
’FI LMBRLG3 DISK LMBRLG3 DATA A (RECFM VB BLOCK 32760’
(2) ---> ’FI ARIARCH TAP1 SL (RECFM FB BLOCK 28672 LRECL 4096’
(3) ---> ’FI ARCHIV DISK ARCHIV DATA G (RECFM VB BLOCK 32760’
(4) ---> ’FI LARCHIV DISK BASE2 LOGDSK1
A1(RECFM F BLOCK 4096’
(5) ---> ’FI LMBRWRK DISK LMBRWRK DATA H (RECFM FB BLOCK 28672
LRECL 4096’
(6) ---> ’FI SYS0001 DISK SYS0001 DATA H(RECFM F BLOCK 4096’
(7) ---> ’FI HEADER DISK HEADER DATA H(RECFM F BLOCK 4096’
(8) ---> ’FI DIRWORK DISK DIRWORK DATA H(RECFM F BLOCK 0512’
(9) ---> ’FI SYSIN DISK RELOAD SYSIN A’
’FI SYSPRINT DISK RELOAD SYSPRINT A’
’XEDIT RELOAD SYSIN A’
’XTS91001’
/*Call BACKUP/RESTORE*/
Figure 117. Example of VM Procedure to Reload From a DB2 Archive
Chapter 11. Restoring Logical Elements
139
Statement 1
LMBRLG1, LMBRLG2, LMBRLG3 files are used for recovery
processing.
Statement 2
Defines the virtual tape drive for SQL/DS archive file.
Statement 3
Defines a work file. This file will contain on SQLDA per reloadable
table.
Statement 4
Defines the database log file.
Statement 5
Defines the work file to process function.
Statement 6
Defines the file to contain all pages for dbspace SYS0001
Statement 7
Defines the file to contain all header pages.
Statement 8
Defines the files to contain all directory pages.
Statement 9
Defines the SYSIN file.
Step
3. List the Changes Extracted from the Log Files
The RELOAD function can reload tables from a BACKUP file. All changes executed
on the tables after the BACKUP function are recorded in the log.
After a RELOAD function, if the parameter RECOVERY=YES was specified on the
OPTIONS statement (for more information see “OPTIONS Statement Parameters”
on page 168; the LMBRLG1, LMBRLG2, LMBRLG3 files contain all the SQL
statements from the log that affect the table.
You can use the LISTLOG function to list the contents of these files. The
APPLYLOG function must be used to apply the changes in these files. Tables
reloaded from an UNLOAD file cannot have log changes applied.
LMBRWRK
LMBRLG1
LMBRLG2
LMBRLG3
LOGLIST
Figure 118. List the Changes Extracted from the Log Input work files:
When You Need to Execute the LISTLOG Function
When you reload a table and request the RECOVERY process, all changes
referenced in the log will be extracted. If, for example, you are recovering a table
that was accidentally corrupted, and you reapply all the changes, the LUW that
140
Data Restore Guide
corrupted the table will also be reexecuted. You need to know the timestamp of
that LUW so that you can reapply all changes up to but not including the LUW in
error.
The timestamp is used during the APPLYLOG function (refer to “Step 4. Apply
LUWs Referenced in the Log to the Reloaded Tables” on page 143 for more
information) on the END parameter to apply the log changes up to the LUW in
error.
Note: The timestamp of each LUW and SQL command has the following format:
YYYY-DDD-HH-MM-SS-mmmmmm
Example: 1995-260-23-59-59-000000
Using the LISTLOG Command
To list the contents of LMBRLG1, LMBRLG2, and LMBRLG3, execute the LISTLOG
function using the XTS91001 program.
The following is an example of JCL (VSE) to list the contents of the recovery files.
// JOB LISTLOG
(1) ---> // LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
(2) ---> // DLBL LMBRWRK,,,VSAM
(3) ---> // DLBL LMBRLG1,,,VSAM
(3) ---> // DLBL LMBRLG2,,,VSAM
(3) ---> // DLBL LMBRLG3,,,VSAM
(4) ---> // EXEC XTS91001,SIZE=AUTO
(5) ---> CONTROL DBAPW=XXXXXXXX
(6) ---> LISTLOG
/*
Figure 119. Example JCL to List the Contents of the Recovery Files
Statement 1
Specifies the DB2 Server for VSE and Data Restore libraries.
Statement 2
Specifies the work file LMBRWRK which may be used if the table
contains LONG columns.
Statement 3
Specifies the work files LMBRLG1, LMBRLG2, and LMBRLG3
which contain data extracted from the log during the RELOAD
process.
Statement 4
Runs the program XTS91001.
Statement 5
Specifies the password for user SQLDBA.
Statement 6
Specifies the LISTLOG function.
The following is an example of an EXEC to list the contents of the recovery files.
Chapter 11. Restoring Logical Elements
141
/**/
(1) ---> ’FILEDEF SYSIN DISK LISTLOG SYSIN A’
(2) ---> ’FILEDEF SYSPRINT DISK LISTLOG SYSPRINT A’
(3) ---> ’FILEDEF LMBRWRK DISK LMBRWRK DATA A (RECFM FB BLOCK 28672 LRECL 4096’
(4) ---> ’FILEDEF LMBRLG1 DISK LMBRLG1 DATA A (RECFM VB BLOCK 32760’
(4) ---> ’FILEDEF LMBRLG2 DISK LMBRLG2 DATA A (RECFM VB BLOCK 32760’
(4) ---> ’FILEDEF LMBRLG3 DISK LMBRLG3 DATA A (RECFM VB BLOCK 32760’
(5) ---> ’XTS91001’
Figure 120. Sample Procedure to List the Content of the Recovery Files
Statement 1
Specifies the SYSIN file which contains the function specification.
Statement 2
Specifies the SYSPRINT file which contains the function report.
Statement 3
Specifies the work file LMBRWRK which is used if the table
contains LONG columns.
Statement 4
Specifies the work files LMBRLG1, LMBRLG2, and LMBRLG3
which contain data extracted from the log during the RELOAD
process.
Statement 5
Runs the program XTS91001.
The SYSIN file must contain the following statements:
(1) ---> CONTROL DBAPW=XXXXXXXX
(2) ---> LISTLOG
Figure 121. Control Statement for LISTLOG in a VM Environment
Statement 1
Specifies the password for user SQLDBA.
Statement 2
Specifies the LISTLOG function.
Example of a Report from LISTLOG Execution
For each row in the LMBRLG1 file, two lines are displayed:
1. An identification line that contains three pieces of information: a committed or
rolled back indicator (C or R), the LUWID, and the timestamp.
2. The SQL statement that was executed.
(1) ---> C 00004084 1995-251-10-56-29-861568
(2) ---> INSERT INTO "SQLDBA"."CUSTOMERS" VALUES (’TEST’,0,200)
(3) ---> R 00004092 1995-256-10-58-00-126994
(4) ---> DELETE FROM "SQLDBA"."CUSTOMERS" WHERE "CUST_NO"=1245
Figure 122. Example Report After LISTLOG Function Execution
Statement 1
Specifies that the LUW number 00004084 was committed at
10-56-29861568 on day 251 in 1995.
Statement 2
Shows that the SQL statement executed was an INSERT into the
table SQLDBA.CUSTOMERS.
Statement 3
Specifies that LUW number 00004092 was rolled back at
10-58-00-126994 on day 251 in 1995.
142
Data Restore Guide
Statement 4
Shows that the SQL statement executed was a DELETE from table
SQLDBA.CUSTOMERS.
Step 4. Apply LUWs Referenced in the Log to the Reloaded Tables
After a RELOAD function has been processed, the table contains rows as they
appeared in the table when the BACKUP was taken. All changes executed on the
table after the BACKUP function was taken can be reprocessed by Data Restore on
the reloaded table.
If among the log changes on a table, a DELETE FROM TABLE command was
issued in error, you may want to stop the recovery before the DELETE command is
reexecuted. By specifying END=timestamp, the recovery process stops before the
timestamp. If a DROP command that involves one of the reloaded tables is
encountered in the log, the recovery process stops automatically for the table.
Forward log recovery can only be processed from a BACKUP archive (not an
UNLOAD) and on the original database. You cannot do log recovery after
reloading a table into a different database.
BACKUP
OR
SYSIN
RELOAD
LMBRLG1
LMBRLG2
LMBRLG3
LMBRLG4
DB2
APPLYLOG
DATABASE
Figure 123. Apply Changes From the Log To the Reloaded Tables
Using the APPLYLOG Command
To determine the value to specify for the END parameter, execute the LISTLOG
function (for more information refer to “Step 3. List the Changes Extracted from
the Log Files” on page 140). An example of the use of TIMESTAMP is:
TIMESTAMP=1995-320-12-30-01-000000.
Chapter 11. Restoring Logical Elements
143
Notes:
1. All LUWs whose timestamp is equal to or greater than the specified END
timestamp will not be executed.
2. The database server must be active during the APPLYLOG function and all
changes on the database are logged.
3. If you want to apply all changes, specify a timestamp greater than the
timestamp of the last LUW in the log.
Execute the APPLYLOG Function
To process all the SQL statements in LMBRLG1, LMBRLG2, and LMBRLG3,
execute the APPLYLOG function using program XTS91001.
You must have previously run the RELOAD function, from a BACKUP, with the
RECOVERY=YES parameter to reload the tables.
The following is an example of JCL (VSE) to execute the statements in the recovery
files.
// JOB APPLYLOG
(1) ---> // LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
(2) ---> // DLBL LMBRWRK,,,VSAM
(3) ---> // DLBL LMBRLG1,,,VSAM
(3) ---> // DLBL LMBRLG2,,,VSAM
(3) ---> // DLBL LMBRLG3,,,VSAM
(4) ---> // EXEC XTS91001,SIZE=AUTO
(5) ---> CONTROL DBAPW=XXXXXXXX
(6) ---> APPLYLOG END=1995-260-23-59-59-000000
/*
Figure 124. Sample JCL to Execute SQL Statements in the Recovery Files
Statement 1
Specifies the DB2 Server for VSE Data Restore libraries.
Statement 2
Specifies the work file LMBRWRK which is used if the table
contains LONG columns.
Statement 3
Specifies the work files LMBRLG1, LMBRLG2, and LMBRLG3
which contain data extracted from the log during the RELOAD
process.
Statement 4
Runs the program XTS91001.
Statement 5
Specifies the password for user SQLDBA.
Statement 6
Specifies the APPLYLOG function with the END timestamp.
The following is an example on an EXEC to execute the SQL statements in the
recovery files.
144
Data Restore Guide
/**/
(1) ---> ’FILEDEF SYSIN DISK LISTLOG SYSIN A’
(2) ---> ’FILEDEF SYSPRINT DISK LISTLOG SYSPRINT A’
(3) ---> ’FILEDEF LMBRWRK DISK LMBRWRK DATA A (RECFM FB BLOCK 28672 LRECL 4096’
(4) ---> ’FILEDEF LMBRLG1 DISK LMBRLG1 DATA A (RECFM VB BLOCK 32760’
(4) ---> ’FILEDEF LMBRLG2 DISK LMBRLG2 DATA A (RECFM VB BLOCK 32760’
(4) ---> ’FILEDEF LMBRLG3 DISK LMBRLG3 DATA A (RECFM VB BLOCK 32760’
(5) ---> ’XTS91001’
Figure 125. Sample EXEC to Execute SQL Statements in the Recovery Files
Statement 1
Specifies the SYSIN file which contains the function specification.
Statement 2
Specifies the SYSPRINT file which contains the output report.
Statement 3
Specifies the work file LMBRWRK which is used if the table
contains LONG columns.
Statement 4
Specifies the work files LMBRLG1, LMBRLG2, and LMBRLG3
which contain data extracted from the log during the RELOAD
process.
Statement 5
Runs the program XTS91001.
The SYSIN file must contain the following statements:
(1) ---> CONTROL DBAPW=XXXXXXXX
(2) ---> APPLYLOG END=1995-260-23-59-59-000000
Figure 126. Sample SYSIN for the APPLYLOG Function
Statement 1
Specifies the password for user SQLDBA.
Statement 2
Specifies the APPLYLOG function with the END timestamp.
Example of a Report from APPLYLOG
XTS9-143 OPTIONS
LANG=S001
XTS9-143 CONTROL DBAPW=********
XTS9-143 APPLYLOG END=1996-255-16-46-47-646175
XTS9-143 /*
XTS9-100 Data Restore feature VERSION 7.1.0
XTS9-007 Processing successfully completed
Figure 127. Example of a Report from APPLYLOG Execution
Chapter 11. Restoring Logical Elements
145
146
Data Restore Guide
Chapter 12. Accessing Data Whether the Server is Up or
Down
Introduction
With the SELECT function, you can extract data from the database directly from
the dbextents. This function is available regardless of whether the database server
is online or offline. If SELECT is used when the database server is online, Data
Restore requests an exclusive lock on the dbspace. This lock ensures that no one
can update data while Data Restore is processing the SELECT request. The
exclusive dbspace lock may cause lock contention for other users. The SELECT
function requires that you specify the SQLDBA password on the DBAPW
parameter of the CONTROL statement as a security precaution.
When you specify OUTPUT=PRINTER, the SELECT function displays the selected
data in a report format to SYSPRINT.
When the OUTPUT=TAPE or the OUTPUT=DASD parameter is specified, you can
use the SELECT function to replace the DBSU DATAUNLOAD command. Data
Restore writes the selected data to the DATAUNL file in a format that the DBSU
DATALOAD facility can use. To help with the use of the DBSU DATALOAD
facility, DBSU control statements are written to SYSPRINT. Examples are provided
later in this chapter.
Note: The SELECT function divides at least by 2 the necessary time to unload data
using the DBSU DATAUNLOD function.
Description
Figure 128 on page 148 shows an overview of the SELECT function. Make sure that
the CREATOR and the TNAME parameters define a table, not a view. You can use
the SELECT function to extract data from any table without LONG columns. Make
sure you can access the dbname SQLDEF file on the database production disk.
Note: When you select the SELECT function, Data Restore does not execute local
date and time user exits nor does it execute field procedures on the selected
columns.
147
BDISK
SYSIN
DDISK1
OUTPUT = PRINTER
SELECT
Data from
table
specified
DDISK2
OR
DDISKn
DATAUNL files
Figure 128. Overview of the SELECT function
SYSIN
Contains the name of the table to select, the names of the columns to select
and the output device. You can use one SELECT statement in the SYSIN
file.
SELECT
Is the function to process to access data.
OUTPUT
Contains the result of the SELECT function. The DBAPW parameter of the
CONTROL statement appears as asterisks in the output.
DATAUNL files
Contain data when OUTPUT=TAPE/DASD is specified.
Using the SELECT Function in a VSE Environment
Figure 129 shows an example of JCL to execute the SELECT function.
// JOB SELECT
(1) ---> // LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
(2) ---> // EXEC PROC=ARIS73DB
(3) ---> // EXEC XTS91001,SIZE=AUTO
(4) ---> CONTROL DBAPW=XXXXXXXX
(5) ---> SELECT CREATOR=SQLDBA TNAME=CUSTOMERS
(6) ---> COLUMNS=(CUST_NO, CUST_NAME, CUST_ADDRESS)
(7) ---> /*
Figure 129. JCL to Execute a SELECT Function (VSE)
Statement 1
Specifies the DB2 library. You must specify the DB2 library for the
SELECT function.
Statement 2
Uses an EXEC to access the database files.
Statement 3
Processes the function.
148
Data Restore Guide
Statement 4
Specifies the SQLDBA password.
Statement 5
Indicates the creator name as well as the table name.
Statement 6
Indicates the column name criteria for selection.
Statement 7
Ends the SYSIN file.
Using the SELECT Function in a VM Environment
Figure 130 shows an EXEC to execute the SELECT function.
/**/
(1) ---> ’FILEDEF SYSPRINT DISK SELECT SYSPRINT A’
(2) ---> ’FILEDEF SYSIN DISK SELECT SYSIN A’
(3) ---> ’XEDIT SELECT SYSIN A’
(4) ---> ’XTS91001’
Figure 130. Procedure to Execute a SELECT Function (VM)
Statement 1
Designates the SYSPRINT file to contain
information about the process.
Statement 2
Specifies the SYSIN file.
Statement 3
Initiates an edit of the SYSIN file to allow for any
changes before processing.
Statement 4
Starts the program to process the function.
The SYSIN file must contain the following statements:
(5) ---> CONTROL DBAPW=XXXXXXXX, DBNAME=dbname
(6) ---> SELECT CREATOR=SQLDBA TNAME=CUSTOMERS
(7) ---> COLUMNS=(CUST_NO, CUST_NAME, CUST_ADDRESS)
Figure 131. SYSIN File to Specify Table Selection
Statement 5
Specifies the database to process and the SQLDBA
password.
Statement 6
Indicates the creator name and the table name.
Statement 7
Indicates the column name criteria for selection.
Make sure you can access the dbname SQLFDEF file on the database production
disk when you execute Data Restore.
Displaying the Results of the SELECT Function
If you specify the SELECT function with the OUTPUT=PRINTER parameter, the
number of rows selected is displayed on the console as shown in Figure 132 on
page 150.
Chapter 12. Accessing Data Whether the Server is Up or Down
149
|
||
|
|
|