DB2 Server for VSE & VM: Data Restore Guide

Using the RELOAD Function

Archive
The file produced by the BACKUP function, or a DB2 Server for VSE & VM database archive.

SYSIN
The file which specifies the tables to restore.

Log n
The log archive files and the current log.

Reload
The function to process.

LMBRWRK
The work file which contains pages for tables containing LONG columns.

LMBRLG1, LMBRLG2, and LMBRLG3
The files used to extract all of the changes referenced in the log files.

DB2 Server for VSE & VM database
The database directory and all dbextents.

Figure 170. Example Console for a RELOAD with the RECOVERY=YES Parameter

XTS9-143 OPTIONS RECOVERY=YES
XTS9-143 CONTROL DBNAME=dbname
XTS9-143 RELOAD CREATOR=SQLDBA,TNAME=SUPPLIERS,FUNCT=NEW,NEWTNAME=SUPPLIERS2
XTS9-143   DBSPACE=SAMPLE
XTS9-143 /*
XTS9-100 Data Restore feature VERSION 7.1.0
XTS9-136 Processing BASE2         archived on (31/10/95-10:59:15)
XTS9-182 Following files are needed for recovery
XTS9-195 UARCHIVE          CURRENTLY MOUNTED
XTS9-180 LARCHIV        at 1995-243-11-01-05-015505
XTS9-179 CURRENT LOG
XTS9-406 Enter O(CANCEL) or 1(CONTINUE)
XTS9-403 Reply is 1
XTS9-102            1000 rows loaded procedure completed
XTS9-128            1000 rows loaded into (SQLDBA.SUPPLIERS2)
XTX9-101               1 tables successfully processed
XTS9-314 Commit work successful for reload of data, creating required objects
CONNECT SQLDBA  ;
COMMIT WORK  ;
XTS9-183 Please mount LARCHIV at 1995-243-11-01-05-015504
XTS9-401 Enter 0(stop) OR 1(continue) OR 111(skipfile)
XTS9-403 Reply is 1
XTS9-184 Processing current log
XTS9-401 Enter 0(stop) OR 1(continue) OR 111(skipfile)
XTS9-403 Reply is 1
XTS9-007 Processing successfully completed

Example of a Report from RELOAD

The number of reloaded rows is displayed at the end of each RELOAD function.

Figure 171. Sample Report from the RELOAD Function

XTS9-100 Data Restore feature VERSION 7.1.0
XTS9-136 Processing BASE1    archived on (11/10/94-15:19:11)
XTS9-128              124 Rows loaded into (SQLDBA.SUPPLIERS)
XTS9-128               25 Rows loaded into (SQLDBA.EMPLOYEE)
XTS9-101                2 Tables successfully processed
XTS9-314 Commit work successful for reload of data, creating required objects
CONNECT SQLDBA;
COMMIT WORK;

If the COMMITCOUNT parameter is specified on the OPTIONS statement, a message is displayed each time a COMMIT WORK is performed. (For more information, see OPTIONS and CONTROL Statements.)

Figure 172. Example Report from the RELOAD Function with the COMMITCOUNT Parameter

XTS9-100 Data Restore feature VERSION 7.1.0
XTS9-136 Processing BASE1    archived on (11/10/94-15:19:11)
XTS9-127               50 Rows loaded procedure  continuing
XTS9-127              100 Rows loaded procedure  continuing
XTS9-128              124 Rows loaded into (SQLDBA.SUPPLIERS)
XTX9-101                1 tables successfully processed
XTS9-314 Commit work successful for reload of data, creating required objects
CONNECT SQLDBA;
COMMIT WORK;

In VM, if a log archive taken on DASD is subsequently moved to tape, Data Restore will still try to process the log archive from DASD. By replying 11 to message XTS9-408, you can instruct Data Restore to process the log archive from tape.

Figure 173. Example Reload with the RECOVERY=YES Parameter Changing Log Archive Support

         XTS9-143 OPTIONS RECOVERY=YES
         XTS9-143 CONTROL  VERIFY=NO DBNAME=dbname READPW=RR
         XTS9-143 RELOAD  CREATOR=SQLDBA,TNAME=CUSTOMERS,FUNCT=REPLACE
         XTS9-143   DBSPACE=SAMPLE
         XTS9-143 /*
         XTS9-196 Do you want to continue the RELOAD process ?
         XTS9-406 Enter 0(CANCEL) or 1(CONTINUE)
         XTS9-403 Reply is 1
         XTS9-100 Data Restore feature VERSION 7.1.0
         XTS9-136 Processing BASE620 archived on (29/09/95-13:11:37)
         XTS9-182 Following files are needed for recovery
         XTS9-195 UARCHIVE     currently mounted
         XTS9-180 LARCHIV     at 1995-272-13-13-24-714512
         XTS9-180 LARCHIV     at 1995-272-13-39-57-753488
         XTS9-179 Current log
         XTS9-406 Enter 0(CANCEL) or 1(CONTINUE)
         XTS9-403 Reply is 1
         XTS9-128       100 rows loaded into (SQLDBA.CUSTOMERS)
         XTS9-128      1481 rows loaded into (DATARFTR.SYSCOLUMNS)
         XTS9-128       317 rows loaded into (DATARFTR.SYSCATALOG)
         XTS9-128       661 rows loaded into (DATARFTR.SYSTABAUTH)
         XTS9-128       113 rows loaded into (DATARFTR.SYSINDEXES)
         XTS9-128        16 rows loaded into (DATARFTR.SYSVIEWS)
         XTS9-128        22 rows loaded into (DATARFTR.SYSKEYCOLS)
         XTS9-128        18 rows loaded into (DATARFTR.SYSKEYS)
         XTS9-128       544 rows loaded into (DATARFTR.SYSUSAGE)
         XTS9-128        28 rows loaded into (DATARFTR.SYSCOLAUTH)
         XTS9-101        10 tables successfully processed
         XTS9-314 COMMIT WORK successful for reload of data, creating required
                  objects.
         CONNECT SQLDBA  ;
         COMMIT WORK ;
(1) ---> XTS9-200 Processing LARCHIV at 1995-272-13-13-24-714512
(2) ---> XTS9-201 Medium is disk BASE35   09299502 *
(3) ---> XTS9-408 Enter 0(CANCEL),1(CONTINUE),11(CHANGE) or 111(SKIPFILE)
(4) ---> XTS9-403 Reply is 11
(5) ---> XTS9-183 Please mount LARCHIV at 1995-272-13-13-24-714512
         XTS9-407 Enter 0(CANCEL),1(CONTINUE) or 111(SKIPFILE)
(6) ---> XTS9-403 Reply is 1
(7) ---> XTS9-183 Please mount LARCHIV at 1995-272-13-39-57-753488
         XTS9-407 Enter 0(CANCEL),1(CONTINUE) or 111(SKIPFILE)
         XTS9-403 Reply is 1
(8) ---> XTS9-184 Processing current log
         XTS9-407 Enter 0(CANCEL),1(CONTINUE) or 111(SKIPFILE)
(9) ---> XTS9-403 Reply is 111
         XTS9-007 Processing successfully completed

Statement 1
Displays the timestamp of the first log archive.

Statement 2
Displays the name and medium (in this case, disk) of the log archive.

Statement 3
Prompts the operator to change the medium or continue with the process.

Statement 4
Displays the operator's response to the above prompt (in this case, since the operator has chosen to change the medium, the new medium is assumed to be tape).

Statement 5
Requests that the log archive (on tape) be mounted.

Statement 6
Displays the operator's response (continue).

Statement 7
Requests the next log archive.

Statement 8
Indicates that the current log is being processed.

Statement 9
Displays the operator's response (not to process the current log).

RELOAD Examples using VM

Figure 174. SQLDBA.PROJECT before Updates

SELECT * FROM SQLDBA.PROJECT
PROJNO PROJNAME              DEPTNO RESPEMP PRSTAFF  ....
------ --------------------- ------ ------- -------
AD3100 ADMIN SERVICES        D01    000010     6.50
AD3110 GENERAL ADMIN SYSTEMS D21    000070     6.00
AD3111 PAYROLL PROGRAMMING   D21    000230     2.00
AD3112 PERSONNEL PROGRAMMING D21    000250     1.00
AD3113 ACCOUNT PROGRAMMING   D21    000270     2.00
IF1000 QUERY SERVICES        C01    000030     2.00
IF2000 USER EDUCATION        C01    000030     1.00
MA2100 WELD LINE AUTOMATION  D01    000010    12.00
MA2110 W L PROGRAMMING       D11    000060     9.00
MA2111 W L PROGRAM DESIGN    D11    000220     2.00
MA2112 W L ROBOT DESIGN      D11    000150     3.00
MA2113 W L PROD CONT PROGS   D11    000160     3.00
OP1000 OPERATION SUPPORT     E01    000050     6.00
OP1010 OPERATION             E11    000090     5.00
OP2000 GEN SYSTEMS SERVICES  E01    000050     5.00
OP2010 SYSTEMS SUPPORT       E21    000100     4.00
OP2011 SCP SYSTEMS SUPPORT   E21    000320     1.00
OP2012 APPLICATIONS SUPPORT  E21    000330     1.00
OP2013 DB/DC SUPPORT         E21    000340     1.00
PL2100 WELD LINE PLANNING    B01    000020     1.00

In Figure 175 and Figure 176 the Data Restore archive is read from tape, as well as the log archives.

Figure 175. EXEC File for Data Restore RELOAD from Data Restore Archive with Forward Recovery

/*------------------------------------------------------------------*/
/*- Reload procedure with forward Recovery -------------------------*/
/*- INPUT Data Restore Feature BACKUP - Log Archive - Current LOG---*/
/*------------------------------------------------------------------*/
Address Command
'FILEDEF ARCHIV   TAP2 SL 1 (RECFM VB BLOCK 32760'
'FILEDEF LARCHIV  TAP1 SL 1 (RECFM FB BLOCK 28672 LRECL 4096'
 
'FILEDEF LMBRWRK DISK LMBRWRK DATA A (RECFM FB BLOCK 28672 LRECL 4096'
'FILEDEF LMBRLG1 DISK LMBRLG1 DATA A (RECFM VB BLOCK 32760'
'FILEDEF LMBRLG2 DISK LMBRLG2 DATA A (RECFM VB BLOCK 32760'
'FILEDEF LMBRLG3 DISK LMBRLG3 DATA A (RECFM VB BLOCK 32760'
 
'FILEDEF SYSIN   DISK RELOAD   SYSIN  A'
'FILEDEF SYSPRINT DISK RELOAD   SYSPRINT A'
'XTS91001'
Exit rc

Figure 176. SYSPRINT File of Data Restore RELOAD from Data Restore Archive with Forward Recovery

+--------------------------------------------------------------------------------+
| XTS9-143 OPTIONS RECOVERY=YES                                                  |
| XTS9-143 CONTROL BASE=S35VMDB1                                                 |
| XTS9-143 RELOAD  CREATOR=SQLDBA,TNAME=PROJECT                                  |
| XTS9-143         FUNCT=REPLACE                                                 |
| XTS9-143 /*                                                                    |
| XTS9-196 Do you want to continue the RELOAD    process ?                       |
| XTS9-406 Enter 0(CANCEL) or 1(CONTINUE)                                        |
| XTS9-403 Reply is 1                                                            |
| XTS9-100 Data Restore feature VERSION 7.1.0                                    |
| XTS9-136 Processing S35VMDB1 archived on (05/30/96-15:18:48)                   |
| 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-102      1351 rows loaded, procedure completed                            |
| XTS9-128        20 rows loaded into (SQLDBA.PROJECT)                           |
|     .... more lines ....                                                       |
| XTS9-128        24 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"."PROJECT" ADD PRIMARY KEY ("PROJNO" ASC                   |
|  ) PCTFREE=10;                                                                 |
| COMMIT WORK ;                                                                  |
| CREATE INDEX "SQLDBA"."DEPTNOI" ON "SQLDBA"."PROJECT"                          |
| ("DEPTNO" ASC ) PCTFREE=10;                                                    |
| COMMIT WORK ;                                                                  |
| CREATE INDEX "SQLDBA"."RESPEMPI" ON "SQLDBA"."PROJECT"                         |
| ("RESPEMP" ASC ) PCTFREE=10;                                                   |
| COMMIT WORK ;                                                                  |
| ALTER TABLE "SQLDBA"."PROJ_ACT" ADD FOREIGN KEY "R_PROJ2"                      |
| ("PROJNO") REFERENCES "SQLDBA"."PROJECT" ON DELETE RESTRICT;                   |
| COMMIT WORK ;                                                                  |
| ALTER TABLE "SQLDBA"."PROJECT" ADD FOREIGN KEY "R_DEPT2"                       |
| ("DEPTNO") REFERENCES "SQLDBA"."DEPARTMENT" ON DELETE RESTRICT;                |
| COMMIT WORK ;                                                                  |
| ALTER TABLE "SQLDBA"."PROJECT" ADD FOREIGN KEY "R_EMPLY2"                      |
| ("RESPEMP") REFERENCES "SQLDBA"."EMPLOYEE" ON DELETE SET NULL;                 |
| COMMIT WORK ;                                                                  |
| CONNECT SQLDBA  ;                                                              |
| GRANT SELECT ON "SQLDBA"."PROJECT" TO "PUBLIC";                                |
| COMMIT WORK ;                                                                  |
| CONNECT SQLDBA  ;                                                              |
| COMMIT WORK ;                                                                  |
| XTS9-184 Processing current log                                                |
| XTS9-407 Enter 0(CANCEL),1(CONTINUE) or 111(SKIPFILE)                          |
| XTS9-403 Reply is 1                                                            |
| XTS9-007 Processing successfully completed                                     |
+--------------------------------------------------------------------------------+

In Figure 177, ARCHIV is used as a work file while reading the DB2 archive (ARIARCH) from tape. The parameter ARCHTYPE=SQLDS is given in Figure 180.

Figure 177. EXEC File for Data Restore RELOAD from DB2 Archive without Forward Recovery

/*------------------------------------------------------------------*/
/*- Reload procedure without forward recovery ----------------------*/
/*- Input=database manager archive----------------------------------*/
/*------------------------------------------------------------------*/
'FILEDEF ARCHIV   DISK ARCHIV DATA A ( RECFM VB BLOCK 32760'
'FILEDEF ARIARCH  TAP1 SL            (RECFM FB BLOCK 28672 LRECL 4096'
 
'FILEDEF LMBRLG1 DISK LMBRLG1 DATA A (RECFM VB BLOCK 32760'
'FILEDEF LMBRLG2 DISK LMBRLG2 DATA A (RECFM VB BLOCK 32760'
'FILEDEF LMBRLG3 DISK LMBRLG3 DATA A (RECFM VB BLOCK 32760'
 
'FILEDEF LMBRWRK DISK LMBRWRK DATA A (RECFM FB BLOCK 28672 LRECL 4096'
'FILEDEF SYS0001 DISK SYS0001 DATA A (RECFM F  BLOCK 4096'
'FILEDEF HEADER  DISK LMBRWRK DATA A (RECFM F  BLOCK 4096'
'FILEDEF DIRWORK DISK LMBRWRK DATA A (RECFM F  BLOCK 4096'
 
'FILEDEF SYSIN    DISK DRFRELA   SYSIN  A'
'FILEDEF SYSPRINT DISK DRFRELA   SYSPRINT A'
'XTS91001'
Exit rc

Figure 178. EXEC File for Data Restore DESCRIBE

/**/
'FILEDEF ARCHIV  TAP1 SL 1 (RECFM VB BLOCK 32760'
 
'FILEDEF SYSIN DISK DESCRIBE SYSIN  A'
'FILEDEF SYSPRINT DISK DESCRIBE SYSPRINT A'
'XTS91001'
Exit rc

Figure 179. EXEC File for Data Restore LISTLOG

/output
/**/
'FILEDEF LMBRLG1 DISK LMBRLG1 DATA A (RECFM VB BLOCK 32760'
'FILEDEF LMBRLG2 DISK LMBRLG2 DATA A (RECFM VB BLOCK 32760'
'FILEDEF LMBRLG3 DISK LMBRLG3 DATA A (RECFM VB BLOCK 32760'
 
'FILEDEF LMBRWRK DISK LMBRWRK DATA T (RECFM FB BLOCK 28672 LRECL 4096'
 
'FILEDEF SYSIN DISK LISTLOG   SYSIN  A'
'FILEDEF SYSPRINT DISK LISTLOG   SYSPRINT A'
'XTS91001'
Exit rc

Figure 180. SYSIN for Data Restore RELOAD from DB2 Archive without Forward Recovery

OPTIONS RECOVERY=NO  CONFIRM=NO ARCHTYPE=SQLDS
CONTROL BASE=S35VMDB1
RELOAD  CREATOR=SQLDBA,TNAME=ACTIVITY,FUNCT=REPLACE

Figure 181. Changed Data in Table SQLDBA.PROJECT after Data Restore APPLYLOG

SELECT * FROM SQLDBA.PROJECT
PROJNO PROJNAME              DEPTNO RESPEMP PRSTAFF
------ --------------------- ------ ------- -------
 .....
IF2000 USER EDUCATION        C01    000030     1.00
 .....
MA2100 WELD LINE AUTOMATION  D01    000010    12.00
 .....
OP1010 OPERATION UPD2        E11    000090     5.00
 .......
OP2010 SYSTEMS SUPPORT UPD1  E21    000100     4.00
 .....

To reload from an INCREMENTAL backup tape, see the following example. The JCL or EXEC used for the RELOAD command must be a full backup file. The ARCHIV label or filedef should be used for the incremental backup file.

The SYSIN file used for the RELOAD command must be modified to add the WRKSIZE parameter on the OPTIONS statement. The value specified must be the same as the one specified at incremental backup time. 



      /**/
      'TAPE REW'
      'FILEDEF ARCHIV TAP1 SL 1 (RECFM VB BLOCK 32760'
      'FILEDEF LMBRWRK DISK WRK DATA A (RECFM FB BLOCK 28672
                                        LRECL 4096'
      'FILEDEF SYSPRINT DISK RESTORE SYSPRINT A'
 ---> 'FILEDEF FULLARC TAP1 SL 1 (RECFM VB BLOCK 32760'
      'FILEDEF SYSIN DISK RELOAD  SYSIN A'
      'XTS91001'

The SYSIN file should contain: 



 
      OPTIONS DEVICE=TAPE DEVICE2=TAPE WRKSIZE=4096
      CONTROL DBNAME=SQLDS
      RELOAD CREATOR=SQLDBA TNAME=CUSTOMERS FUNCT=PURGE
      /*

The output from RELOAD will be: 



      XTS9-143 OPTIONS DEVICE=TAPE DEVICE2=TAPE WRKSIZE=4096
      XTS9-143 CONTROL DBNAME=SQLDS
      XTS9-143 RELOAD CREATOR=SQLDBA TNAME=CUSTOMERS FUNCT=PURGE
      XTS9-143 /*
      XTS9-100 Data Restore feature VERSION 7.1.0
      XTS9-136 Processing SQLDS archived on (11/25/97-15:16:53)
 ---> XTS9-219 Mount BASE2 archived on (09/25/97-14:52:59)
 ---> XTS9-406 Enter 0(CANCEL) or 1(CONTINUE)
 ---> XTS9-403 Reply is 1
      XTS9-102       225 rows loaded, procedure completed
      XTS9-128       225 rows loaded into (SQLDBA.CUSTOMER)
      XTS9-101         1 tables successfully processed

RELOAD Examples using VSE

Figure 182 shows the JCL used in some of the following examples. It defines the RELOAD work files for forward recovery.

Figure 182. JCL File DLBL for Data Restore Work Files (XTS9DLBL.PROC)

* $$ JOB JNM=XTS9DLBL,CLASS=0,DISP=D
* $$ LST CLASS=A,DISP=D,PRI=3,DEST=(*,VSESQADM)
* $$ PUN CLASS=A,DISP=D,PRI=3,DEST=(*,VSESQADM)
// JOB XTS9DLBL  CATALOG DLBL for data restore feature
// EXEC    LIBR,PARM='MSHP'
   ACCESS  SUBLIB=PRD2.RCVvrm
   CATALOG XTS9DLBL.PROC     EOD=&& DATA=YES REPLACE=YES
// PROC CAT='VSESPUC'
// DLBL IJSYSUC,'VSESP.USER.CATALOG',,VSAM
* **********************************************************
* XTS9DLBL: DATA RESTORE FEATURE WORKFILES
* **********************************************************
// DLBL LMBRWRK,,,VSAM,CAT=VSESPUC
// DLBL LMBRLG1,,,VSAM,CAT=VSESPUC
// DLBL LMBRLG2,,,VSAM,CAT=VSESPUC
// DLBL LMBRLG3,,,VSAM,CAT=VSESPUC
// DLBL SYS0001,,0,VSAM,RECSIZE=4096,RECORDS=(100,100),CAT=VSESPUC
// DLBL HEADER,,0,VSAM,RECSIZE=4096,RECORDS=(100,100),CAT=VSESPUC
// DLBL DIRWORK,,0,VSAM,CAT=VSESPUC
&&
/*
/&
* $$ EOJ

In Figure 183, the DLBL JCL from Figure 182 is addressed. The Data Restore archive is read from tape. Therefore, SYS006 is assigned and OPTIONS DEVICE=TAPE.

Figure 183. JCL File for Data Restore RELOAD from Data Restore Archive with Forward Recovery

* $$ JOB JNM=DRFRLOAD,CLASS=0,DISP=D
* $$ LST CLASS=A,DISP=D,PRI=3,DEST=(*,VSESQADM)
* $$ PUN CLASS=A,DISP=D,PRI=3,DEST=(*,VSESQADM)
// JOB DRFRLOAD               RELOAD TABLE WITH FORWARD RECOVERY
// LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
// EXEC PROC=SQLVSE02         DLBL for database extents
// EXEC PROC=XTS9DLBL         DLBL for drf files
// TLBL     ARCHIV
// ASSGN    SYS006,180        input tape from BACKUP
// MTC      REW,SYS006
// EXEC XTS91001,SIZE=AUTO,PARM='DBNAME(SQLVSE02)'
OPTIONS RECOVERY=YES DEVICE=TAPE
CONTROL DBNAME=SQLVSE02
RELOAD CREATOR=SQLDBA TNAME=PROJECT FUNCT=REPLACE
/*
/&
* $$ EOJ

Figure 184 shows an example without using the DLBL from above. SYS007 is assigned because a DB2 archive is read. ARCHIV is used as a work file on disk as shown in the DLBL ARCHIV and the OPTIONS DEVICE=DASD statements.

Figure 184. JCL File for Data Restore RELOAD from DB2 Archive with Forward Recovery

* $$ JOB JNM=DRFRLOAD,CLASS=0,DISP=D
* $$ LST CLASS=A,DISP=D,PRI=3,DEST=(*,VSESQADM)
* $$ PUN CLASS=A,DISP=D,PRI=3,DEST=(*,VSESQADM)
// JOB DRFRLOAD               RELOAD TABLE WITH FORWARD RECOVERY
// LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
// EXEC PROC=SQLVSE02
// TLBL     ARIARCH
// ASSGN    SYS007,181             INPUT SQL ARCHIVE
// MTC      REW,SYS007
// DLBL LMBRWRK,,,VSAM,CAT=VSESPUC
// DLBL LMBRLG1,,,VSAM,CAT=VSESPUC
// DLBL LMBRLG2,,,VSAM,CAT=VSESPUC
// DLBL LMBRLG3,,,VSAM,CAT=VSESPUC
// DLBL SYS0001,,,VSAM,RECSIZE=4096,RECORDS=(100,100),CAT=VSESPUC
// DLBL HEADER,,,VSAM,RECSIZE=4096,RECORDS=(100,100),CAT=VSESPUC
// DLBL DIRWORK,,,VSAM,CAT=VSESPUC
// DLBL ARCHIV,,,VSAM,RECORDS=(100,100),RECSIZE=4096,CAT=VSESPUC
// EXEC XTS91001,SIZE=AUTO,PARM='DBNAME(SQLVSE02)'
OPTIONS RECOVERY=YES,ARCHTYPE=SQLDS,DEVICE=DASD
CONTROL DBNAME=SQLVSE02
RELOAD CREATOR=SQLDBA TNAME=PROJECT FUNCT=REPLACE
/*
/&
* $$ EOJ

In Figure 185 a DB2 archive is read using SYS007 as shown in Figure 184, but the work file ARCHIV is defined on tape. Refer to the TLBL ARCHIV and ASSGN SYS006 statements.

Figure 185. JCL File for Data Restore RELOAD of 2 Tables from DB2 Archive with Forward Recovery

* $$ JOB JNM=SQLRLOAD,CLASS=0,DISP=D
* $$ LST CLASS=A,DISP=D,PRI=3,DEST=(*,VSESQADM)
* $$ PUN CLASS=A,DISP=D,PRI=3,DEST=(*,VSESQADM)
// JOB SQLRLOAD    RELOAD 2 TABLEs WITH FORWARD REC from sql archi
// LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
// EXEC PROC=SQLVSE02
// EXEC PROC=XTS9DLBL
// TLBL     ARCHIV
// TLBL     ARIARCH
// TLBL     LARCHIV
// ASSGN    SYS006,180
// ASSGN    SYS007,181
// MTC      REW,SYS007
// EXEC XTS91001,SIZE=AUTO,PARM='DBNAME(SQLVSE02)'
OPTIONS ARCHTYPE=SQLDS RECOVERY=YES
CONTROL DBNAME=SQLVSE02
RELOAD CREATOR=SQLDBA TNAME=PROJECT FUNCT=REPLACE
RELOAD CREATOR=SQLDBA TNAME=PROJ_ACT FUNCT=REPLACE
/*
/&
* $$ EOJ

The guest sharing example shows the general use of the PARM='DBNAME()" statement as opposed to CONTROL DBNAME=. PARM indicates the target database, whereas the CONTROL statement defines the source of the archive, that is, the database from which the archive was taken.

Figure 186. JCL File for Data Restore RELOAD with Guest Sharing

* $$ JOB JNM=DRFRLOAD,CLASS=0,DISP=D
* $$ LST CLASS=A,DISP=D,PRI=3,DEST=(*,VSESQADM)
* $$ PUN CLASS=A,DISP=D,PRI=3,DEST=(*,VSESQADM)
// JOB DRFRLOAD    RELOAD TABLE with guest sharing
// LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
// TLBL     ARCHIV                * input tape *
// ASSGN    SYS006,181
// MTC      REW,SYS006
// SETPFIX  LIMIT=1M
// EXEC XTS91001,SIZE=AUTO,PARM='DBNAME(S35VMDB1)'
OPTIONS RECOVERY=NO,DEVICE=TAPE
CONTROL DBNAME=SQLVSE02
RELOAD CREATOR=SQLDBA TNAME=ACTIVITY FUNCT=REPLACE
/*
/&
* $$ EOJ

To reload from an INCREMENTAL backup tape in, see the following example. The JCL or EXEC used for the RELOAD command must be a full backup file. The ARCHIV label or filedef should be used for the incremental backup file.

The SYSIN file used for the RELOAD command must be modified to add the WRKSIZE parameter on the OPTIONS statement. The value specified must be the same as the one specified at incremental backup time. 



      // JOB RELOAD
      // LIBDEF *,SEARCH=(PRD2.DB2vrm,PRD2.RCVvrm)
      // EXEC PROC=ARIS71DB
      // DLBL LMBRWRK,,,VSAM
      // TLBL ARCHIV,'ARCHIVE.SQL',,,1
 ---> // TLBL FULLARC,'ARCHIVE.SQL',,,,1
      // ASSGN SYS006,180
      // MTC REW,SYS006
      // EXEC XTS91001,SIZE=AUTO
      OPTIONS DEVICE=TAPE DEVICE2=TAPE WRKSIZE=4096
      CONTROL DBNAME=SQLDS
      RELOAD CREATOR=SQLDBA TNAME=CUSTOMERS FUNCT=PURGE
      /*

Using Control Center

RELOAD allows the user to reload and recreate tables from a Data Restore BACKUP, translated database archive, or a Data Restore UNLOAD. Control Center requires that a database archive first be translated before it is used as an input source for RELOAD.

In our example the RECOVERY option is set to 'YES'. Then Data Restore will read the active log to record all changes to this table executed since the BACKUP into work files. This can later be examined with the LISTLOG function and re-executed using the APPLYLOG function.

To run the Data Restore RELOAD the database must be active.

In this example we used a translated archive tape as input to the RELOAD process.

To start the RELOAD function, select R from the Date Restore Menu as shown in Figure 187.

Figure 187. Invoking Data Restore RELOAD Function

+--------------------------------------------------------------------------------+
|  05.03.1997                  Control Center Facility                13.55.16   |
| *------------------------------- Data Restore Menu -------------------------*  |
| | Option ===> R                                            CTRLID: ELDBMSRV |  |
| |    Database ===> ELDB2B                                  NODE:   BOEVMCT1 |  |
| |                                                          DRMACH: ELDB2DRF |  |
| |                                                                           |  |
| |     T  TRANSLATE ARCHIVE          TRANSLATE archive into BACKUP format    |  |
| |     U  UNLOAD DBSPACES            UNLOAD one or more dbspaces             |  |
| |     R  RELOAD TABLES              RELOAD one or more tables               |  |
| |     LL LISTLOG                    LISTLOG selection panel                 |  |
| |     AL APPLYLOG                   APPLYLOG selection panel                |  |
| |     VJ VIEW JOB SCHEDULE          VIEW database job schedule              |  |
| |     S  VIEW DRMACH STATUS         VIEW Data Restore Machine status        |  |
| |     SR RESET DRMACH STATUS        RESET Data Restore Machine status       |  |
| |     D  SHOWDBS                    Generate report about all dbspaces      |  |
| |          View Tapes   Edit Tapes    View History    View Log              |  |
| |  BACKUP      BT          BM             BH             BL                 |  |
| |  UNLOAD      UT          UM             UH             UL                 |  |
| |  TRANSLATE   TT          TM             TH             TL                 |  |
| |                                                                           |  |
| |           Enter OPTION, select DATABASE, press ENTER to process           |  |
| |                                                                           |  |
| *---------------------------------------------------------------SDRMAIN-----*  |
|  PF:   1 Help    3 End                                                         |
+--------------------------------------------------------------------------------+

On the screen bellow, we specified Log Recovery=1 (Yes) and the input type is a translated archive tape. 


+--------------------------------------------------------------------------------+
|  05.03.1997              Control Center Facility                     14.02.19  |
| *------------------  Data Restore Reload Table Utility  ---------------------* |
| |                                                          CTRLID:  ELDBMSRV | |
| | Database ==> ELDB2B                                      NODE:    BOEVMCT1 | |
| |                                                          DRMACH:  ELDB2DRF | |
| |                                                                            | |
| |                                                                            | |
| |    Log Recovery ==> 1         ( 1 = 'YES', 2 = 'NO'                        | |
| |    Input Type   ==> 2         ( 1 = BACKUP, 2 = TRANSLATE, 3 = UNLOAD      | |
| |    Restart      ==> 2         ( 1 = 'YES', 2 = 'NO', Restart RELOAD        | |
| |                                     and use the same RELDCTL file          | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| *---------------------------------------------------------------DRRELD2------* |
|   PF1 HELP    PF3 QUIT    PF4 EXIT    PF5 Main Menu    Enter Continue          |
+--------------------------------------------------------------------------------+

To see all available restore sets, type all on the RELOAD SET field. A restore set report will be presented. Write down the restore set number you wish to use. In our case, the restore set number 1 will be used. 


+--------------------------------------------------------------------------------+
|  05.03.1997                 Control Center Facility             14.06.06       |
| *----------------------- Reload Restore Set Selection -----------------------* |
| | Command ==>                                               SQMID: ELDBMSRV  | |
| |    Database ===> ELDB2B                                   NODE:  BOEVMCT1  | |
| |                                                                            | |
| |                                                                            | |
| |    RELOAD SET ===> all                Archive Restore Set to RELOAD        | |
| |                    ( Blank for LATEST or ALL for all available )           | |
| |         To execute RELOAD, you must select the archive restore set         | |
| |    to use.  The restore set is a group of tapes or disks that were used    | |
| |    for a previous BACKUP or TRANSLATE.  You may choose ALL to display ALL  | |
| |    available restore sets, or you may leave the restore set field BLANK    | |
| |    to retrieve the LATEST restore set.                                     | |
| |                                                                            | |
| |    The selected restore set will be displayed and you will be asked to     | |
| |    enter the restore set number to begin the RELOAD.                       | |
| |                                                                            | |
| |        Enter Restore Set number and press ENTER to process, or press       | |
| |         PF3 to QUIT                                                        | |
| |                                                                            | |
| *---------------------------------------------------------------SQMAR60------* |
|  PF:   1 Help    3 End (Quit)                                                  |
+--------------------------------------------------------------------------------+

Figure 188. Restore Set Report

RESTORE SET REPORT for Data Base ELDB2B    Date: 07.03.1997  Time: 18.49.05
DRF  1 2 3
 
RESTORE SET(s) generated using LOGMODE = L
 
  NOTE:  Examine the following restore set(s) and
       remember the restore set that you wish to use.
 
 
         Restore Set #1   From Data Base Archive Created 07.03.1997 15.03.11
 
           Archive         Archive       Date     Time
        Type (DB or Log)   Sequence   Archived  Archived Series Volid
        _________________  ________  __________ ________ ______ ______
        Data Base Archive  Tape #1   07.03.1997 15.03.11   100  VOL100
                           TDSK @1   07.03.1997 15.39.35   100  ELRES2
TRANSLAT * 192
                           BAC1 @2   07.03.1997 15.41.01   100  TRL200
                                ....................................
        ACTIVE Log         >>>>    Archived During The Recovery    <<<<
 
         Restore Set #1 END ................................................
 
         Restore Set #2   From Data Base Archive Created 07.03.1997 10.54.48
 
           Archive         Archive       Date     Time
        Type (DB or Log)   Sequence   Archived  Archived Series Volid
        _________________  ________  __________ ________ ______ ______
        DB USER ARCHIVE    Uarc #1   07.03.1997 10.54.48   200  USER BACKUP
DRFBACKUP 03/07/97 10:51:13
 
        Log Archive(s):    Log  #2   07.03.1997 15.00.19   200  LOG201
                                ....................................
        ACTIVE Log         >>>>    Archived During The Recovery    <<<<
 
         Restore Set #2 END ................................................
 
         Restore Set #3   From Data Base Archive Created 07.03.1997 10.19.58
 
           Archive         Archive       Date     Time
        Type (DB or Log)   Sequence   Archived  Archived Series Volid
        _________________  ________  __________ ________ ______ ______
        DB USER ARCHIVE    Uarc #1   07.03.1997 10.19.58   200  USER BACKUP
DRFBACKUP 03/07/97 10:16:23
                           BAC1 @1   07.03.1997 10.19.56   200  VOL002
                                ....................................
        ACTIVE Log         >>>>    Archived During The Recovery
 
         Restore Set #3 END ................................................

On the RELOAD Restore Set Selection screen, we type the restore set number 1 to be used. In our case, the database is running in logmode=L and we do not have any log tapes. Just the active log will be processed. If you have 1 or more log tapes, you can limit the number of log tapes to be processed by especifing Y on the Process Partial Logs field. 

+--------------------------------------------------------------------------------+
|  05.03.1997                 Control Center Facility             14.08.12       |
| *----------------------- RELOAD Restore Set Selection -----------------------* |
| | Command ==>                                               SQMID: ELDBMSRV  | |
| |    Database ===> ELDB2B                                   NODE:  BOEVMCT1  | |
| |                                                                            | |
| |  Logmode          ===> L             Logmode for the RELOAD                | |
| |  RELOAD Set       ===> 1             Archive Restore Set Number to Use     | |
| |                                      (Select from the list below)          | |
| |  Process Partial Logs   ===> N       Limit Logs Processed (Y or N)         | |
| |                                                                            | |
| |  BACKUP Type       ===> BACKUP       BACKUP or BACKUP2                     | |
| |         ------------------ Valid Restore Sets -------------------          | |
| |  1 (04.03.1997), 2 (04.03.1997), 3 (04.03.1997)                            | |
| |                                                                            | |
| |                                                                            | |
| |   NOTE:   If RECOVERY='YES', Data Restore will process the logs.  To       | |
| |           limit the number of logs read, ENTER Y for Partial Log           | |
| |           processing.                                                      | |
| |                                                                            | |
| |    Enter Restore Set and press ENTER to process, or press PF3 to QUIT      | |
| |                                                                            | |
| *---------------------------------------------------------------SQMAR70------* |
|  PF:   1 Help    3 End (Quit)                                                  |
+--------------------------------------------------------------------------------+

Press enter to continue the RELOAD process. 

+--------------------------------------------------------------------------------+
|  05.03.1997                 Control Center Facility                   14.13.08 |
| *--------------------------- Reload Verification ----------------------------* |
| | Command ==>                                               SQMID: ELDBMSRV  | |
| |    Database ===> ELDB2B                                   NODE:  BOEVMCT1  | |
| |                                                                            | |
| |    This is your chance to review the RELOAD set.  If you want to           | |
| |    continue with the RELOAD, press ENTER, if you want to CANCEL the        | |
| |    RELOAD now, press PF3.                                                  | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| |                                                                            | |
| |        Press ENTER to continue with the RELOAD request, or                 | |
| |        press PF3 to QUIT                                                   | |
| |                                                                            | |
| *---------------------------------------------------------------SQMAR80------* |
|  PF:   1 Help    3 End (Quit)                                                  |
+--------------------------------------------------------------------------------+

The next panel is the RELOAD Table List screen. Here, you specify the table(s) to be reloaded. Each table must include the function (Purge, New, Add, Replace) to be applied. In this example, the sample sqldba.activity table will be reloaded. You can specify up to 90 RELOAD table statements. After entering the RELOAD table commands, press PF5 to continue the execution of the RELOAD process. 

+--------------------------------------------------------------------------------+
|  05.03.1997                 Control Center Facility                  14.15.21  |
| *---------------------------- Reload Table List -----------------------------* |
| |  Database => ELDB2B                             Selected Tables => 0       | |
| |     P,N,A,R   Table                         Dbspace                        | |
| | Num  Funct    Creator   Table Name          Owner      Dbspace Name        | |
| | ---  -----    --------  ------------------  --------   ------------------  | |
| |       P       sqldba__  activity__________  public__   sample____________  | |
| |          NEW  ________  __________________                                 | |
| |                                                                            | |
| |       _       ________  __________________  ________   __________________  | |
| |          NEW  ________  __________________                                 | |
| |                                                                            | |
| |       _       ________  __________________  ________   __________________  | |
| |          NEW  ________  __________________                                 | |
| |                                                                            | |
| |       _       ________  __________________  ________   __________________  | |
| |          NEW  ________  __________________                                 | |
| |                                                                            | |
| |                                                                            | |
| |                              Page 1     of 1                               | |
| *---------------------------------------------------------------DRRELD3------* |
| PF:  1 Help         3 QUIT     5 Continue     6 Previous Menu                  |
+--------------------------------------------------------------------------------+

In the next step, Control Center will show the RELOAD control file that is created to perform the RELOAD function. Here we can check if the right files (tape and disk) are being used. Press PF3 to exit from the file and continue with the next panel.

Figure 189. Reload Control File

 $$RELOAD $RELDCTL A1  V 74             1 Blks  3/07/97 Line       1 of 5
===>
:DBMACH=ELDB2B RELOAD RECOVERY=YES from TRANSLATEREC @ 07.03.1997 15.03.11
#1 DRF TRANSDSK 07.03.1997 15.39.35 100 N  ELRES2 TRANSLAT * 192
#2 DRF BACKUP 07.03.1997 15.41.01 100 N  TRL200
#3 LOG Archive ACTIVE LOG
END RESTORE SET
* * * End of file * * *

Below we have the last panel of the RELOAD function. On this panel you specify the type of execution. In our case we will execute the RELOAD immediately. Also, In this example we are using RECOVERY=YES, therefore the RELOAD ID is name of the database machine (ELDB2B) and you cannot change it. For RECOVERY=NO, this field is empty and you can enter any unique file name. 

+--------------------------------------------------------------------------------+
|  07.03.1997                 Control Center Facility                 17.24.39   |
| *---------------------------- RELOAD EXECUTION ------------------------------* |
| |  DATABASE ==> ELDB2B                                      CTRLID: ELDBMSRV | |
| |                                                           NODE:   BOEVMCT1 | |
| |                                                                            | |
| | Execution       ==> 2            ( 1 = Schedule, 2 = Execute Immediately   | |
| |                                                                            | |
| | RELOAD ID       ==> ELDB2B       (Unique file name for RELOAD event )      | |
| | The Reload Id (name) will be used as the file name for the RELDCTL control | |
| | file which will hold media and table list data required for the RELOAD.    | |
| | If you execute a RECOVERY=YES, the RELOAD ID is the database machine.      | |
| |                                                                            | |
| | NOTIFY ==> ________  (Userid)   ________   (Node)                          | |
| |                                                                            | |
| |                                                                            | |
| | Commitcount    ==> ______   ( Rows to reload before a commitcount          | |
| |                                                                            | |
| | Nbviews        ==> ______   ( Estimate number of views to recreate.        | |
| |                                                                            | |
| |                                                                            | |
| *----------------------------------------------------------------DRRELD5-----* |
|   PF1 HELP   PF3 QUIT     PF4 View RELDCTL file                                |
|                           PF7 PREVIOUS SCREEN            ENTER  CONTINUE       |
+--------------------------------------------------------------------------------+

After Control Center finishes the RELOAD operation, the LISTLOG function can be used to produce a report of the changes to the tables that were specified at the RELOAD operation. In our case, the sample sqldba.activity table was used. The report produced with the LISTLOG function can be used to decide which LUWs will be re-applied to the database using the APPLYLOG function.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]