DB2 Server for VSE & VM: Data Restore Guide

Data Restore UNLOAD and RELOAD

As opposed to DBSU UNLOAD and RELOAD, Data Restore UNLOAD and RELOAD do not only perform differently, but also serve different purposes.

While DBSU UNLOAD and RELOAD can both work on the same units, either dbspaces or tables, Data Restore UNLOAD can only be made from dbspaces, and Data Restore RELOAD can only load one table in one command from that unload file.

While DBSU UNLOAD and RELOAD can be used for reorganization, and UNLOAD sorts the rows according to the clustering index, Data Restore UNLOAD does no sorting at all, nor does Data Restore RELOAD.

The reason is the following: While DBSU UNLOAD and RELOAD are designed for restructure, Data Restore UNLOAD and RELOAD serve the purpose of table recovery. For restructure, performance of UNLOAD and RELOAD are equally important, but for recovery, UNLOAD will be performed much more often than RELOAD.

Therefore, the Data Restore feature improves the performance of the UNLOAD function: Data Restore UNLOAD actually unloads all the active pages of the dbspace, whereas DBSU UNLOAD unloads row after row from the table. This makes Data Restore UNLOAD faster for large tables and filled dbspaces. However, Data Restore RELOAD processing is slower because it must read the output file of Data Restore UNLOAD, which contains all data pages, and must find the rows in this file. DBSU RELOAD does not have to do this, since DBSU UNLOAD already has unloaded the rows in sequence from the table.

The difference between the Data Restore feature and DBSU is a question of which process, either unload or reload, has to analyze the pages to locate the data rows: For DBSU, the rows are found in the data pages and sorted during UNLOAD; for the Data Restore feature, the rows are found in the pages during RELOAD, and keep their sequence.

Data Restore UNLOAD

The UNLOAD function of the Data Restore feature enables you to perform selective backups of data. Data Restore UNLOAD allows you to unload data from DB2 dbspaces to a file with a system-defined format. With the Data Restore feature you can unload single or multiple dbspaces while the database manager is online or offline. The Data Restore UNLOAD and RELOAD file format is not compatible with DBSU UNLOAD and RELOAD.

With the UNLOAD function you can not only specify one dbspace to be unloaded, but you can also choose:

• either a list of dbspaces to be unloaded
• or to unload all dbspaces, except for a list of dbspaces

The parameters COND=INCLUDE or EXCLUDE on the UNLOAD command identify whether the list of dbspaces is to be included or excluded (INCLUDE is the default). The restriction is that only one UNLOAD statement can be specified within one SYSIN file. For the RELOAD, it is possible to specify more than one statement. Figure 54, Figure 55, and Figure 185 show samples for the RELOAD function with RECOVERY=YES and two RELOAD statements specified.

Purpose

With the UNLOAD function you can back up dbspaces containing critical tables more frequently than the rest of your database. By defining one table per dbspace, you can even make table-level backups.

Files

The Data Restore UNLOAD requires some input and output files:

For VM only:

• SYSIN file - contains the command to be processed and the parameters that specify what and how it will be executed. Figure 46 shows a sample.
• SYSPRINT file - a report that lists the SYSIN values, messages and results. Figure 48 shows a sample of the SYSPRINT output.

  For VM and VSE:

• ARCHIV - this ddname, together with OPTIONS DEVICE=DASD/TAPE in the SYSIN file, identifies the file that contains the output data. Figure 47 shows the FILEDEF definitions of the UNLOAD function.

Example

Figure 46, Figure 47, and Figure 48 show how to UNLOAD a dbspace while the database manager is running (MODE=ONLINE). When you do this, make sure that there are no concurrent updates on the dbspace, because Data Restore UNLOAD will issue a LOCK DBSPACE command. To be sure that all updates to the dbspace are on disk, an online UNLOAD forces a checkpoint by issuing an ACQUIRE DBSPACE and a DROP DBSPACE command.

At least one unacquired dbspace should be available to complete this process.

Figure 46. SYSIN File for Data Restore UNLOAD (DRFUNLOA SYSIN)

OPTIONS DEVICE=DASD CONTROL BASE=S35VMDB1 DBAPW=SQLDBAPW UNLOAD DBSPACE(SAMPLE) MODE=ONLINE

Figure 47. EXEC File for Data Restore UNLOAD (DRFUNLOA EXEC)

/*---*/ 'FILEDEF ARCHIV DISK DRFSAMPL DATA A (RECFM VB BLOCK 32760' 'FILEDEF SYSIN DISK DRFUNLOA SYSIN A' 'FILEDEF SYSPRINT DISK DRFUNLOA SYSPRINT A'

Figure 48. SYSPRINT File of Data Restore UNLOAD (DRFUNLOA SYSPRINT)

+--------------------------------------------------------------------------------+
| XTS9-143 CONTROL BASE=S35VMDB1,DBAPW=********                                  |
| XTS9-143 UNLOAD  MODE=ONLINE,DBSPACE=(SAMPLE),COND=INCLUDE                     |
| XTS9-143 /*                                                                    |
| XTS9-196 Do you want to continue the UNLOAD    process ?                       |
| XTS9-406 Enter 0(CANCEL) or 1(CONTINUE)                                        |
| XTS9-403 Reply is 1                                                            |
| XTS9-100 Data Restore feature VERSION 7.1.0                                    |
| XTS9-309 Processing DB2 for VSE and VM version 7.1.0                           |
| XTS9-160 External labeling of this unload is                                   |
| XTS9-142 Base S35VMDB1 Date 04/06/96 Time 11:13:16                             |
| XTS9-013 Table SQLDBA  .ACTIVITY           may be reloaded                     |
| XTS9-013 Table SQLDBA  .DEPARTMENT         may be reloaded                     |
| ........                                                                       |
| ........                                                                       |
| XTS9-013 Table SQLDBA  .PROJECT            may be reloaded                     |
| XTS9-006 Processing DDSK1                                                      |
| XTS9-005        94 blocks saved                                                |
| XTS9-007 Processing successfully completed                                     |
+--------------------------------------------------------------------------------+

Data Restore RELOAD

The Data Restore RELOAD function loads single tables from one of the following:

• a DB2 archive
• a translated DB2 archive
• a Data Restore archive produced by either BACKUP, BACKUP FULL or BACKUP INCREMENTAL function
• a Data Restore UNLOAD file

The DB2 database manager must be online during Data Restore RELOAD, and applications using the database manager cannot access any table being reloaded during the reload process. For the RELOAD from an archive, the option RECOVERY=YES can be specified. This allows you to later perform a forward log recovery. Forward log recovery is not available for an UNLOAD file.

If RECOVERY=YES is specified, the log records containing the relevant DB2 statements are extracted from the database manager log (and, if LOGMODE=L, from the analyzed log archive) and written to disk.

You can RELOAD tables from a DB2 archive in two ways:

• Use the TRANSLATE function to convert the DB2 archive into the Data Restore archive format.

  You can run the TRANSLATE function at any time after you have taken the DB2 archive with the database either online or offline. Running TRANSLATE in advance will minimize the processing time when a RELOAD is required. However, you may have to TRANSLATE many DB2 archives that will never be reloaded, and you must keep the work files SYS0001, HEADER and DIRWORK, produced during the Data Restore TRANSLATE, for a later Data Restore RELOAD. In VSE, a retention period of 0 days is not suitable.

• Run the RELOAD directly from the DB2 archive tape.

  Specify the options statement ARCHTYPE=SQLDS to identify the type of input tapes. The Data Restore RELOAD processing of a DB2 archive reads the archive tape twice and produces the

  SYS0001, HEADER, and DIRWORK work files. If you are using the Data Restore RELOAD command with a DB2 archive in a VSE environment, make sure you define the work files with a retention period of 0 days. This avoids the 4228I and 4233I messages indicating that the file already exists when running Data Restore RELOAD again.

You cannot reload the System Catalog tables.

Files

The Data Restore RELOAD requires some input and output files:

For VM only:

• SYSIN file - contains the command to be processed. Figure 49 shows a sample.
• SYSPRINT file - Data Restore feature creates a report that lists the SYSIN values, messages and results. Figure 51 shows a sample of the SYSPRINT output.

For VM and VSE:

• ARCHIV - one of the following:
  • input file from a Data Restore archive created by BACKUP

    Note:

    If the input file is an incremental backup file, the incremental backup file will be processed first.

  • input file from a Data Restore archive produced by a Data Restore TRANSLATE function against a DB2 archive
  • workfile during RELOAD if the input is an DB2 archive
  • input file from an output of a Data Restore UNLOAD

  If the archive file processed has been generated by the BACKUP INCREMENTAL function, a FULL archive will be accessed to complete processing. You will be prompted to mount the right FULL archive).

• FULLARC - Data Restore FULL archive file necessary to complete the RELOAD, if the ARCHIV file is an INCREMENTAL BACKUP.

  Note: If the FULLARC file is used, the DEVICE2 parameter on the OPTIONS statement, should specify TAPE or DASD.

• ARIARCH - input file from a DB2 archive

• LMBRWRK - workfile, which contains one of the following:
  • pages for tables containing LONG columns,
  • the unloaded dbspace from where the table is to be reloaded with RECOVERY=YES

• LMBRLG1 - output, used to extract all of the changes referenced in the log files. This is only needed when the option RECOVERY=YES is specified.

• LMBRLG2 - output, stores information about rollback LUW. This is only needed when the option RECOVERY=YES is specified.

• LMBRLG3 - output, contains columns in LONGVARCHAR format for the updated rows. This is only needed when the option RECOVERY=YES is specified.

  LMBRWRK, LMBRLG1, LMBRLG2 and LMBRLG3 will be used by the LISTLOG and APPLYLOG functions. Figure 55 shows an example.

• LARCHIV - input, the log archive files or tapes

• SYS0001 - workfile, stores the active pages of this dbspace

• HEADER - workfile, stores the header pages for this dbspace

• DIRWORK - workfile, stores the directory pages

The specifications for the work files needed with RECOVERY=YES can be found in Figure 182.

Example

Figure 49, Figure 50, and Figure 51 show an example using the RELOAD function from an existing table into a new table (FUNCTION=NEW) from a Data Restore UNLOAD on disk.

Figure 49. SYSIN File for Data Restore RELOAD (DRFRE1A SYSIN)

CONTROL BASE=S35VMDB1 DBAPW=SQLDBAPW RELOAD CREATOR=SQLDBA TNAME=ACTIVITY NEWTNAME=NEW_ACTIVITY DBSPACE=SAMPLE FUNCTION=NEW

Figure 50. EXEC File for Data Restore RELOAD (DRFRE1B EXEC)

/*---*/ 'FILEDEF LMBRWRK DISK LMBRWRK DATA A (RECFM FB BLOCK 28672 LRECL 4096' 'FILEDEF ARCHIV DISK DRFSAMPL DATA A (RECFM VB BLOCK 32760' 'FILEDEF SYSIN DISK DRFRE1A SYSIN A' 'FILEDEF SYSPRINT DISK DRFRE1C SYSPRINT A' 'XEDIT DRFRE1A SYSIN A' 'XTS91001'

Figure 51. SYSPRINT File of Data Restore RELOAD (DRFRE1C SYSPRINT)

+--------------------------------------------------------------------------------+
| XTS9-143 OPTIONS RECOVERY=NO,DEVICE=DASD                                       |
| XTS9-143 CONTROL BASE=S35VMDB1                                                 |
| XTS9-143 RELOAD  CREATOR=SQLDBA,TNAME=ACTIVITY,FUNCT=NEW                       |
| XTS9-143  DBSPACE=SAMPLE                                                       |
| XTS9-143  NEWTNAME=NEW_ACTIVITY                                                |
| 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-174 Processing S35VMDB1 unloaded on (04/06/96-13:48:47)                   |
| XTS9-102       106 rows loaded, procedure completed                            |
| XTS9-128       106 rows loaded into (SQLDBA.NEW_ACTIVITY)                      |
| XTS9-101         1 tables successfully processed                               |
| XTS9-314 COMMIT WORK successful for reload of data, creating                   |
|          required objects                                                      |
| CONNECT SQLDBA  ;                                                              |
| COMMIT WORK ;                                                                  |
| XTS9-007 Processing successfully completed                                     |
+--------------------------------------------------------------------------------+

Guest Sharing

RELOAD is one of the very few Data Restore functions that can run in a VM/VSE Guest Sharing environment. It can only run if the OPTIONS statement RECOVERY=NO is specified. Figure 186 shows a sample JCL that was used to reload "SQLDBA.ACTIVITY" into the VM database "S35VMDB1" from a Data Restore feature archive of the VSE database "SQLVSE02".

The CONTROL statement DBNAME=SQLVSE02 defines the name of the database from which this backup or unload originated. The PARM='DBNAME(S35VMDB1)' parameter, defines the target database for the reload of this table.