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. |
Figure 105. The RELOAD Function
|
Notes:
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.
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.
Figure 106. RELOADing a Table with the PURGE Parameter
 |
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.
Figure 107. RELOADing a Table with the NEW Parameter
 |
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.
Figure 108. RELOADing a Table with the ADD Parameter
 |
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.
Figure 109. RELOADing a Table with the REPLACE Parameter
 |
All of the commands for environment recreation will be displayed on SYSPRINT or SYSLST.
Figure 110. Example List Displayed for a RELOAD with the REPLACE Parameter
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
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.
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) during the RELOAD process. Refer to the next sections for information on listing and applying the changes in the log.
Notes:
Figure 111. Possible Input Sources for RELOADing Tables with Forward Recovery
 |
Figure 112. Example of JCL to Reload in a VSE Environment From a Full Backup
// 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 113. Example of JCL to Reload in a VSE Environment From an Incremental Backup
// 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 114. Example of JCL to Reload in a VSE Environment From a DB2 Archive
// 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 115. Example of VM Procedure to Reload From a Backup File
/**/
'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 116. Example of VM Procedure to Reload From an Incremental Backup
/**/ '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 117. Example of VM Procedure to Reload From a DB2 Archive
/**/
(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*/
|