Data Restore RELOAD is the only way to apply log recovery on a table level. When restoring a complete database you can apply log recovery, but in some cases restoring a complete database might be an unreasonably large effort. With Data Restore RELOAD, you can even specify to apply log recovery up to a certain point in time.
You can also use Control Center to execute a RELOAD with forward recovery. For a complete example of Control Center using Data Restore RELOAD function, refer to page ***.
Here we are going to show you a sample scenario how a table can be reloaded from a complete Data Restore BACKUP and also apply the log archives and the current log up to a certain point in time.
The sequence of operations was:
For the original data before any modification see Figure 174.
The modifications are:
Figure 52. Modifications against SQLDBA.PROJECT
+--------------------------------------------------------------------------------+ |UPDATE SQLDBA.PROJECT SET PROJNAME='SYSTEMS SUPPORT UPD1' | | WHERE PROJNO='OP2010' | |UPDATE SQLDBA.PROJECT SET PROJNAME='OPERATION UPD2' | | WHERE PROJNO='OP1010' | |UPDATE SQLDBA.PROJECT SET PROJNAME='USER EDUCATION UPD3' | | WHERE PROJNO='IF2000' | |DELETE FROM SQLDBA.PROJECT WHERE PROJNO = 'MA2100' | +--------------------------------------------------------------------------------+
The FILEDEF statement for Data Restore RELOAD from archive (and log archive, if used) should be adjusted to the FILEDEF used when the archive (and log archive) was created. Make sure you adjust the options for record length, and blocking factor. The device name TAP1 should be used in VM for the log archives and TAP2 for the archive. Special care should be taken on the FILEDEF for the log archive tapes, as these tapes are only requested after the archive tapes and if any error occurs, the complete tape set has to be read again, losing precious time. For tapes from a DB2 archive, these tapes have to be read twice. For the exec used in VM to reload a table refer to Figure 175, for the SYSPRINT see Figure 176. For the JCL used in VSE to reload a table refer to Figure 183 and Figure 182. If a DB2 archive is used as input, specify SYS007 as the input assignment. If the ARCHIV workfile is on disk, you must also specify OPTIONS DEVICE=DASD. For this JCL refer to Figure 184.
RELOAD reads the archive tapes and restores the table as it was at the point in time the archive was taken. Additionally it writes the log entries that are relevant for this table into workfiles. Therefore you are prompted to mount the log archive tapes (if any) and have the choice to skip them as shown in Figure 53. For the complete output of this function refer to Figure 176. Those workfiles are later used to apply forward recovery on this table using the Data Restore feature functions LISTLOG and APPLYLOG described below. For a comparison between filtered log recovery and APPLYLOG with forward log recovery to a certain point in time, see Log Recovery.
Figure 53. Forward Recovery Prompt during Data Restore RELOAD
XTS9-202 Processing database manager archive (timestamp=05/28/96 11:29:22) XTS9-182 Following files are needed for recovery XTS9-195 ARCHIVE currently mounted XTS9-180 LARCHIVE at 05/28/96 11:52:37 XTS9-179 Current log ....... XTS9-183 Please mount larchive at 05/28/96 11:52:37 XTS9-407 Enter 0(CANCEL),1(CONTINUE) or 111(SKIPFILE) ....... XTS9-184 Processing current log XTS9-407 Enter 0(CANCEL),1(CONTINUE) or 111(SKIPFILE) |
Figure 54 and Figure 55 show how to reload two tables with RECOVERY=YES specified in VM; for VSE, see Figure 185.
Figure 54. SYSIN File for Data Restore RELOAD Tables and RECOVERY=YES
OPTIONS RECOVERY=YES CONFIRM=NO CONTROL BASE=S35VMDB1 RELOAD CREATOR=SQLDBA,TNAME=EMP_ACT,FUNCT=REPLACE RELOAD CREATOR=SQLDBA,TNAME=PROJ_ACT,FUNCT=REPLACE |
Figure 55. EXEC File for Data Restore RELOAD Tables and RECOVERY=YES
/*------------------------------------------------------------------*/ /*- Reload procedure with forward Recovery -*/ /*- input drf backup tape -*/ /*------------------------------------------------------------------*/ 'FILEDEF ARCHIV DISK ARCHIV DATA T (RECFM VB BLOCK 32760' 'FILEDEF LARCHIV TAP1 SL (RECFM FB BLOCK 28672 LRECL 4096' 'FILEDEF LMBRLG1 DISK LMBRLG1 DATA T (RECFM VB BLOCK 32760' 'FILEDEF LMBRLG2 DISK LMBRLG2 DATA T (RECFM VB BLOCK 32760' 'FILEDEF LMBRLG3 DISK LMBRLG3 DATA T (RECFM VB BLOCK 32760' 'FILEDEF LMBRWRK DISK LMBRWRK DATA T (RECFM FB BLOCK 28672 LRECL 4096' 'FILEDEF SYSIN DISK DRFRELD SYSIN A' 'FILEDEF SYSPRINT DISK DRFRELD SYSPRINT A' 'XTS91001' Exit rc |
Figure 56. SYSPRINT File for Data Restore RELOAD Tables and RECOVERY=YES
+--------------------------------------------------------------------------------+
| XTS9-143 OPTIONS RECOVERY=YES CONFIRM=NO |
| ........ |
| ........ |
| XTS9-102 1242 rows loaded, procedure completed |
| XTS9-128 74 rows loaded into (SQLDBA.EMP_ACT) |
| XTS9-128 77 rows loaded into (SQLDBA.PROJ_ACT) |
| XTS9-128 572 rows loaded into (DATARFTR.SYSCOLUMNS) |
| XTS9-128 69 rows loaded into (DATARFTR.SYSCATALOG) |
| XTS9-128 190 rows loaded into (DATARFTR.SYSTABAUTH) |
| XTS9-128 90 rows loaded into (DATARFTR.SYSINDEXES) |
| XTS9-128 7 rows loaded into (DATARFTR.SYSVIEWS) |
| XTS9-128 15 rows loaded into (DATARFTR.SYSKEYCOLS) |
| XTS9-128 11 rows loaded into (DATARFTR.SYSKEYS) |
| XTS9-128 115 rows loaded into (DATARFTR.SYSUSAGE) |
| XTS9-128 22 rows loaded into (DATARFTR.SYSCOLAUTH) |
| XTS9-101 11 tables successfully processed |
| XTS9-314 COMMIT WORK successful for reload of data, creating |
| required objects |
| CREATE INDEX "SQLDBA"."PROJNOIN" ON "SQLDBA"."EMP_ACT" ("PRO |
| JNO" ASC ) PCTFREE=10; |
| COMMIT WORK ; |
| CREATE INDEX "SQLDBA"."EMPNOIN" ON "SQLDBA"."EMP_ACT" ("EMPN |
| O" ASC ) PCTFREE=10; |
| COMMIT WORK ; |
| ALTER TABLE "SQLDBA"."PROJ_ACT" ADD PRIMARY KEY ("PROJNO" AS |
| C ,"ACTNO" ASC ,"ACSTDATE" ASC ) PCTFREE=10; |
| COMMIT WORK ; |
| ALTER TABLE "SQLDBA"."EMP_ACT" ADD FOREIGN KEY "R_EMPLY3" (" |
| EMPNO") REFERENCES "SQLDBA"."EMPLOYEE" ON DELETE CASCADE ; |
| COMMIT WORK ; |
| ALTER TABLE "SQLDBA"."EMP_ACT" ADD FOREIGN KEY "R_PROACT" (" |
| PROJNO","ACTNO","EMSTDATE") REFERENCES "SQLDBA"."PROJ_ACT" O |
| N DELETE RESTRICT; |
| COMMIT WORK ; |
| ALTER TABLE "SQLDBA"."PROJ_ACT" ADD FOREIGN KEY "R_PROJ2" (" |
| PROJNO") REFERENCES "SQLDBA"."PROJECT" ON DELETE RESTRICT; |
| COMMIT WORK ; |
| CONNECT SQLDBA ; |
| GRANT SELECT ON "SQLDBA"."EMP_ACT" TO "PUBLIC"; |
| COMMIT WORK ; |
| GRANT SELECT ON "SQLDBA"."PROJ_ACT" TO "PUBLIC"; |
| COMMIT WORK ; |
| CONNECT SQLDBA ; |
| COMMIT WORK ; |
| XTS9-183 Please mount larchive at 06/27/96 14:56:15 |
| ........ |
| XTS9-007 Processing successfully completed |
+--------------------------------------------------------------------------------+
If a RELOAD with RECOVERY=YES function requires multiple log archive tapes to be mounted, and the tapes are managed by a tape manager facility. During RELOAD processing, the LARCHIV filedef defines the tape file to be used. When Data Restore is finished reading a log archive tape and is preparing to read the next log archive tape, it may be necessary to modify the VOLSER for the LABELDEF and/or FILEDEF for the next log archive tapes. Data Restore will execute the "XTS9X001 EXEC" before attempting to OPEN a log archive tape file. For example, the XTS9X001 EXEC may be coded as:
Figure 57. EXEC File for Data Restore During RELOAD with RECOVERY=YES
/* */
/* This exec is called by Data Restore during RELOAD with */
/* RECOVERY=YES before opening each log file to allow you to */
/* modify the LABELDEF and/or FILEDEF statement */
/* */
/* Each time Data Restore needs to process a new tape, customers */
/* who use a tape management facility, may need to modify the */
/* the FILEDEF and/or LABELDEF statement to process several tapes */
/* with different VOLSERs. */
/* */
/* You may need to modify this EXEC if you are using a tape */
/* management system. */
/* */
/* If you are not using a tape management system, you may leave */
/* this EXEC as it is. */
*/
exit 0
/* */
answer = '11'
do while answer = '11'
say 'Please enter the VOLSER for the next tape'
pull volid
do until answer ='0' ! answer = '1' ! answer = '11'
say 'The Next VOLSER of for ARILARCH is ' volid
say ' Enter 0(Cancel), 1(Continue) or 11(Retry)'
pull answer
end
end
if answer = '0' then exit 16
'labeldef arilarch volid ' volid
exit rc
|
You can specify the new VOLSER for the next log archive file and a new LABELDEF is generated. The tape management facility will then know the correct tape to mount.
Data Restore LISTLOG lists the DB2 statements extracted from the log (and the log archive) during the RELOAD operation with forward recovery. With this list you can determine exactly what operations should not be performed and where the recovery (Data Restore APPLYLOG) should stop.
| Note: | The LISTLOG function stops when it recognizes a DROP TABLE, ALTER TABLE or DROP DBSPACE command. There is no way to list the changes made after that. Figure 60 shows a sample of how this message is presented to the user. |
Figure 58 shows the JCL that was used to perform this function in VSE. The JCL of XTS9DLBL is shown in Figure 182. Figure 179 shows the EXEC that was used to perform this function in VM.
The sample output can be found in Figure 59. It lists the modifications shown in Figure 52. For the file definitions, refer to Data Restore RELOAD.
Figure 58. JCL File for Data Restore LISTLOG
* $$ JOB JNM=DRFLSTLG,CLASS=0,DISP=D * $$ LST CLASS=A,DISP=D,PRI=3,DEST=(*,VSESQADM) * $$ PUN CLASS=A,DISP=D,PRI=3,DEST=(*,VSESQADM) // JOB DRFLSTLG LISTLOG procedure // LIBDEF *,SEARCH=(PRD2.SQL350,PRD2.RCVvrm) // EXEC PROC=SQLVSE02 dlbl for database dbextents // EXEC PROC=XTS9DLBL dlbl for data restore feature workfiles // EXEC XTS91001,SIZE=AUTO CONTROL DBAPW=SQLDBAPW LISTLOG /* /& * $$ EOJ |
Figure 59. SYSPRINT File of Data Restore LISTLOG
+--------------------------------------------------------------------------------+ | XTS9-143 CONTROL DBAPW=******** | | XTS9-143 LISTLOG | | XTS9-143 /* | | XTS9-196 Do you want to continue the LISTLOG process ? | | XTS9-406 Enter 0(CANCEL) or 1(CONTINUE) | | XTS9-403 Reply is 1 | | XTS9-100 Data Restore feature VERSION 7.1.0 | | | | C 00000512 1996-151-15-32-37-908640 | | UPDATE "SQLDBA"."PROJECT" SET "PROJNO" ='OP2010', | | "PROJNAME" ='SYSTEMS SUPPORT UPD1',"DEPTNO" ='E21', | | "RESPEMP" ='000100', | | "PRSTAFF" = 4,00 ,"PRSTDATE" =1982-01-01, | | "PRENDATE" =1983-02-01,"MAJPROJ" ='OP2000' | | WHERE "PROJNO" ='OP2010' | | | | C 00000513 1996-151-15-32-55-265424 | | UPDATE "SQLDBA"."PROJECT" SET "PROJNO" ='OP1010', | | "PROJNAME" ='OPERATION UPD2',"DEPTNO" ='E11', | | "RESPEMP" ='000090',"PRSTAFF" = 5,00 , | | "PRSTDATE" =1982-01-01, | | "PRENDATE" =1983-02-01,"MAJPROJ" ='OP1000' | | WHERE "PROJNO" ='OP1010' | | | | C 00000514 1996-151-15-33-16-747088 | | UPDATE "SQLDBA"."PROJECT" SET "PROJNO" ='IF2000', | | "PROJNAME" ='USER EDUCATION UPD3',"DEPTNO" ='C01', | | "RESPEMP" ='000030'," | | "PRSTAFF" = 1,00 ,"PRSTDATE" =1982-01-01, | | "PRENDATE" =1983-02-01,"MAJPROJ" =NULL | | WHERE "PROJNO" ='IF2000' | | | | C 00000516 1996-151-15-34-07-382272 | | DELETE FROM "SQLDBA"."PROJECT" WHERE "PROJNO" ='MA2100' | | XTS9-007 Processing successfully completed | +--------------------------------------------------------------------------------+
Figure 60. Data Restore LISTLOG Termination
+--------------------------------------------------------------------------------+ |XTS9-196 Do you want to continue the LISTLOG process ? | |XTS9-406 Enter 0(CANCEL) or 1(CONTINUE) | |1 | |XTS9-100 Data Restore feature VERSION 7.1.0 | |XTS9-185 Forward recovery stopped due to DROP TBL | |XTS9-186 Timestamp of statement is 1996-157-09-06-57-704112 | |XTS9-007 Processing successfully completed | +--------------------------------------------------------------------------------+
Data Restore APPLYLOG applies the DB2 statements that were extracted from the log (and the log archive) during the RELOAD operation. The forward recovery can be applied completely or stopped at a certain point in time. When log recovery stops it cannot be resumed. It stops automatically when it has:
For the difference between filtered log recovery and APPLYLOG with forward log recovery to a certain point in time, see Log Recovery.
The command syntax is identical to the one that was used for the LISTLOG. Only the options in VSE or SYSIN for VM have to be changed. For the file definitions, refer to Data Restore RELOAD. Figure 61 shows a sample of the options in VSE or the SYSIN in VM, used to specify until what point in time these records should be applied.
Figure 61. SYSIN File for Data Restore APPLYLOG
CONTROL DBAPW=SQLDBAPW APPLYLOG END=1996-151-15-33-16-747088 |
The "END=" specifies the end of the apply procedure, and the record with this corresponding timestamp is not applied. Refer to Figure 181 for the output of the rows that were affected, in SQLDBA.PROJECT by the previous APPLYLOG. As you can see, the last two DB2 commands (UPDATE and DELETE) from Figure 52 and Figure 59 have not been applied.
| Note: | It is not possible to resume the log application once it has ended
through
|
whichever comes first.