DB2 Server for VSE. Operations Guide / Handbooks (2004-2007)

Chapter 7. Data Unload and Reload

This chapter describes the options provided to back up selected data in different

formats in order to load it back to a relational database management system or

other database or application on the same or on a different operating system.

Likewise, it describes how to load data from various formats, including a partial or

complete database backup. With user defined formats, the sources of the data can

be any database or application on any platform.

Overview

Unload and load procedures are used when you want to reorganize the data,

import or export data, or back up or restore part of the database.

If you used unload procedures previously, you could not apply a log. That is, you

could not automatically track and apply any changes that were made to the data

after the unload was taken. However, there is now the possibility to RELOAD a

specific table from a Data Restore archive or DB2 archive and apply the log.

The main reasons to unload data are to:

v Reorganize a table

- when the indexes become unclustered

- when the table specifications need changes

- when the dbspace specifications need changes

v Copy a complete table or a part of it from one database to another to create a

test environment.

v Use the unloaded data as input to another relational database or even to a

non-relational database, for example a spreadsheet or reporting application.

v Back up critical tables between archives or log archives:

- If something goes wrong with your database, it would be faster to restart the

environment without having to restore all data.

- If the database manager cannot be restarted at all, you would at least have a

recent copy of important tables.

But note that no log can be applied and thus changes to those tables are not

reflected. These table backups are now no longer necessary when using the Data

Restore feature.

DBSU DATAUNLOAD and DATALOAD

DATAUNLOAD and DATALOAD are part of the DBSU.

DATAUNLOAD enables you to unload data from DB2 tables to a file with a

user-defined format. The data to be unloaded is selected from the database within

an SQL SELECT statement. The DATAUNLOAD command can have some

subcommands that describe the data fields and the source in the output records. In

most cases, each output record contains data from one row of a table. The record

format can vary and can have different formats not only depending on the

operating system, but also based on the user-defined record layout.

DATALOAD allows you to reload rows into existing DB2 tables from data

contained in a sequential input file that was created by either DATAUNLOAD or a

process outside the DB2 system.

For a complete and detailed description of these commands, refer to the DB2 Server

for VSE & VM Database Services Utility manual.

Purpose

Some of the different purposes of this facility are:

Reorganization

Indexes can become unclustered when rows in a table are being added, deleted or

updated. So there may be a need to unload and reload the data to cluster the

indexes again. Reorganization is also useful if many rows have been deleted and

the number of empty pages is too high. If reorganization is the main purpose, it is

easier and faster to use the method described in “DBSU UNLOAD and RELOAD”

on page 75. To unload data in a specific order and reload it independent of the

clustering index you can use ORDER BY on the DATAUNLOAD command.

Restructure

A change in the structure of a table may be required. If the layout of the table, the

datatype of the columns or the NULL definition needs changing, consider using

DATAUNLOAD and DATALOAD because you can provide the table definition

with the DATALOAD command sequence. DATAUNLOAD and DATALOAD

would be the best way to do this.

DATAUNLOAD should also be used if columns need the NOT NULL specification,

or columns have to be created that would be parts of an existing column or

combination of columns. In general, DATAUNLOAD and DATALOAD are best for

any data manipulation against the existing database because you have the

possibility to make changes.

Export/Import

Data may be needed for a test system or for another application. For example, if

you are building a new application, you may want to use some of your data or

tables as a sample of the actual data with which the application will need to work.

Or you may want to unload data so it can be moved to another database, used by

a completely different type of application, or moved to another platform. There,

this data could be used for creating a report, making an analysis, or for other

purposes.

Other database system

If data needs to be unloaded from or reloaded to a different platform or another

type of database, the appropriate command should be used there to do the

equivalent operation.

Ease of Operation with Control Center

If data remains on the same platform, Control Center can help you to ease work.

The Control Center function SQLTABLE uses the DATAUNLOAD function and

offers a menu driven interface to provide options to:

Data Restore Guide

v add or delete columns

v save the DDL needed to reload the data to the same table, a different table, or a

different DB2 database

v select the DATA ONLY parameter to provide a copy of the data for any other

purpose.

For a complete description of SQLTABLE refer to DB2 for VM Control Center

Operations Guide or DB2 for VSE Control Center Operations Guide.

DBSU UNLOAD and RELOAD

The UNLOAD and RELOAD commands are similar to the previous

DATAUNLOAD and DATALOAD commands. One difference is that they can be

applied not only to tables, but also to views or dbspaces. The major difference is

that the data must be (un)loaded in a system defined format. Also, UNLOAD

unloads the rows sorted according to the first index created for that table. This first

index is also known as the clustering index. This index is identified by a value of F

or W in the column CLUSTER in the SYSTEM.SYSINDEXES table. The data is

ordered by the clustering index during UNLOAD. If no indexes exist on this table,

the rows are unloaded in no particular order.

In addition to containing a record for each row in the table, the UNLOAD output

file also has records containing information about the table and indexes. This

information is needed by the RELOAD function. UNLOAD does not unload any

indexes, only the statements to recreate these indexes. No SELECT statement is

used to unload the table.

Purpose

The main purpose of UNLOAD and RELOAD is reorganization for a better index.

You can also choose this method if you want to change dbspace characteristics,

such as PCTINDEX. However, you cannot change the characteristics of tables or

columns while running this type of reorganization.

Another reason can be for balancing the I/O load between different DASD. The

table or dbspace can be moved to another storage pool that owns extents on

another DASD volume.

Ease of Operation with Control Center

Enhanced Control Center reorganization and maintenance tools support you in the

different activities of a database administrator. The Control Center command

SQLREORG offers a menu-driven interface and makes use of the DB2 UNLOAD

and RELOAD functions. With the Control Center SQLREORG option you can

choose to change the data definition values. For example, you can choose to have a

different dbspace name, PCTINDEX value, or change the size of the dbspace.

For a complete description of SQLREORG, refer to the DB2 for VM Control Center

Operations Guide or DB2 for VSE Control Center Operations Guide.

Data Restore SELECT

SELECT is a function of the Data Restore feature and allows you to select data

from DB2 tables directly out of the dbextents, bypassing the database manager.

With the Data Restore feature you can select tables while the database manager is

online or offline.

Chapter 7. Data Unload and Reload

Note: The Data Restore SELECT function does not execute local date and time

user exits, nor does it execute field procedures on the selected columns. Any

table without LONG columns can be specified. This restriction is true even

when the database manager is online.

Data Restore SELECT 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.

If SELECT is used when the database manager is online, the Data Restore feature

requests an exclusive lock on the dbspace. This lock ensures that no one can

update data while the Data Restore feature is processing the SELECT request.

Purpose

Data Restore SELECT allows you to retrieve data while the database manager is

offline, for example due to a failure. This can be of help during error recovery if no

backup of the data is available.

Note: Do not rely upon this, because, for example, the ability to retrieve data from

a defect disk is limited, as described in “Recovery From a Logical Error” on

page 125.

Files

The Data Restore SELECT requires some input and output files:

For VM only:

v SYSIN file - contains the command to be processed.

v SYSPRINT file - Data Restore feature creates a report that lists the SYSIN

values, messages and results.

For VM and VSE:

v DATAUNL - this file will contain the selected data when

OUTPUT=TAPE/DASD is specified.

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.

Data Restore Guide

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:

v either a list of dbspaces to be unloaded

v 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 on page 83, Figure 55 on page 83, and Figure 185 on page 217

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:

v SYSIN file - contains the command to be processed and the parameters that

specify what and how it will be executed. Figure 46 on page 78 shows a sample.

v SYSPRINT file - a report that lists the SYSIN values, messages and results.

Figure 48 on page 78 shows a sample of the SYSPRINT output.

For VM and VSE:

Chapter 7. Data Unload and Reload

v 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.

OPTIONS DEVICE=DASD

CONTROL BASE=S35VMDB1 DBAPW=SQLDBAPW

UNLOAD DBSPACE(SAMPLE) MODE=ONLINE

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

/*---*/

’FILEDEF ARCHIV DISK DRFSAMPL DATA A (RECFM VB BLOCK 32760’

’FILEDEF SYSIN

DISK DRFUNLOA SYSIN A’

’FILEDEF SYSPRINT DISK DRFUNLOA SYSPRINT A’

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

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

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

Data Restore RELOAD

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

v a DB2 archive

v a translated DB2 archive

v a Data Restore archive produced by either BACKUP, BACKUP FULL or

BACKUP INCREMENTAL function

v a Data Restore UNLOAD file

Data Restore Guide

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:

v 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.

v 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:

v SYSIN file - contains the command to be processed. Figure 49 on page 80 shows

a sample.

v SYSPRINT file - Data Restore feature creates a report that lists the SYSIN

values, messages and results. Figure 51 on page 81 shows a sample of the

SYSPRINT output.

For VM and VSE:

v 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

Chapter 7. Data Unload and Reload

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 on page 83 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 on page 216.

Example

Figure 49, Figure 50, and Figure 51 on page 81 show an example using the

RELOAD function from an existing table into a new table (FUNCTION=NEW)

from a Data Restore UNLOAD on disk.

CONTROL BASE=S35VMDB1 DBAPW=SQLDBAPW

RELOAD CREATOR=SQLDBA TNAME=ACTIVITY

NEWTNAME=NEW_ACTIVITY

DBSPACE=SAMPLE FUNCTION=NEW

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

/*---*/

’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 50. EXEC File for Data Restore RELOAD (DRFRE1B EXEC)

Data Restore Guide

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

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

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 on page 218 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.

Data Restore RELOAD with Forward Recovery

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 218.

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:

1. COLDLOG to clear old log information

2. Data Restore BACKUP or DB2 archive of complete database

3. Modifications against the table “SQLDBA.PROJECT”

4. RELOAD the affected table from Data Restore archive or DB2 archive

5. LISTLOG to display all modifications

6. APPLYLOG to apply changes from log (to point in time if “END=” parameter

is specified).

Chapter 7. Data Unload and Reload

For the original data before any modification see Figure 174 on page 211.

The modifications are:

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’

Figure 52. Modifications against SQLDBA.PROJECT

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 on page 212, for the SYSPRINT see

Figure 176 on page 213. For the JCL used in VSE to reload a table refer to

Figure 183 on page 216 and Figure 182 on page 216. 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

on page 217.

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 on page 213. 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” on page 26.

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 53. Forward Recovery Prompt during Data Restore RELOAD

Figure 54 on page 83 and Figure 55 on page 83 show how to reload two tables with

RECOVERY=YES specified in VM; for VSE, see Figure 185 on page 217.

Data Restore Guide

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 54. SYSIN 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 55. EXEC File for Data Restore RELOAD Tables and RECOVERY=YES

Chapter 7. Data Unload and Reload

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

Figure 56. SYSPRINT File for Data Restore RELOAD Tables and RECOVERY=YES

Data Restore RELOAD for Incremental Backup

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:

Data Restore Guide

/* 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

if answer = ’0’ then exit 16

’labeldef arilarch volid ’ volid

exit rc

Figure 57. EXEC File for Data Restore During RELOAD with RECOVERY=YES

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

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 on page 86 shows a sample of how this message is

presented to the user.

Figure 58 on page 86 shows the JCL that was used to perform this function in VSE.

The JCL of XTS9DLBL is shown in Figure 182 on page 216. Figure 179 on page 214

shows the EXEC that was used to perform this function in VM.

The sample output can be found in Figure 59 on page 86. It lists the modifications

shown in Figure 52 on page 82. For the file definitions, refer to “Data Restore

RELOAD” on page 78.

Chapter 7. Data Unload and Reload

* $$ 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 58. JCL File for 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 59. SYSPRINT File of Data Restore LISTLOG

XTS9-196 Do you want to continue the LISTLOG process ?

XTS9-406 Enter 0(CANCEL) or 1(CONTINUE)

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

Figure 60. Data Restore LISTLOG Termination

Data Restore Guide

Data Restore APPLYLOG

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:

v Reached a certain predefined point in time

v Encountered a DROP TABLE, ALTER TABLE or DROP DBSPACE command

For the difference between filtered log recovery and APPLYLOG with forward log

recovery to a certain point in time, see “Log Recovery” on page 26.

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” on page 78. 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.

CONTROL DBAPW=SQLDBAPW

APPLYLOG END=1996-151-15-33-16-747088

Figure 61. SYSIN File for Data Restore APPLYLOG

The “END=” specifies the end of the apply procedure, and the record with this

corresponding timestamp is not applied. Refer to Figure 181 on page 215 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 on page 82 and Figure 59 on page 86 have not been applied.

Note: It is not possible to resume the log application once it has ended through

v either the “END=” statement

v or the detection of a DROP TABLE, ALTER TABLE or DROP DBSPACE

command

whichever comes first.

Summary

Compatibilities

DBSU DATAUNLOAD/DATALOAD, DBSU UNLOAD/RELOAD, and Data

Restore UNLOAD/RELOAD are pairs of functions that are in themselves

compatible. You cannot use the output of one pair as input for another.

The Data Restore SELECT function is compatible with the DBSU DATALOAD

function. You can SELECT data via Data Restore and load the data with the DBSU

DATALOAD. The Control Center SQLTABLE function is compatible with, and

makes use of, the DBSU DATAUNLOAD function.

The Control Center SQLREORG function is compatible with, and makes use of, the

DBSU UNLOAD and RELOAD functions.

The Data Restore UNLOAD and RELOAD functions are incompatible with the

DBSU UNLOAD and RELOAD functions, as described in “Data Restore UNLOAD

and RELOAD” on page 76.

Chapter 7. Data Unload and Reload

Table 6 gives you an overview of the compatibilities among the methods to unload

or reload data on a DB2 database.

Table 6. Compatibilities Data Unload and Load

... can be Input to:

Data

DBSU

Data Restore

Restore Control Center

Output from ...

DATALOAD RELOAD DESCRIBE RELOAD SQLREORG

DBSU DATAUNLOAD

DBSU UNLOAD

Data Restore UNLOAD

Data Restore SELECT

Control Center SQLTABLE

Control Center SQLREORG

DB2 archive

Translated DB2 archive

Data Restore BACKUP

Possibilities

Table 7 gives you an overview of the methods and their possibilities offered to

unload or reload data on a DB2 database.

Reorganization can be achieved also with other means; we indicate here just the

main purpose.

For “Data Restore RELOAD from TRANSLATE” (translated DB2 archive), see

column “RELOAD from BACKUP”.

Table 7. Possibilities Data Unload and Load

Control

DBSU

Data Restore

Center

Function

Unit

row

table

dbspace

DML Data Manipulation Language

user defined

DDL Data Definition Language

user defined columns

user defined table

user defined dbspace

Data Restore Guide

Table 7. Possibilities Data Unload and Load (continued)

Control

DBSU

Data Restore

Center

Reorganization

free empty pages

to other dbspace

user defined (order by)

system defined (clustering

index)

Export / Import data

DB2 X

non- DB2 X

Database status

online X

offline

Recover log

apply all

to point in time

Chapter 7. Data Unload and Reload

Chapter

8. Backing Up an Entire Database

This chapter shows how to create a copy of your database to protect it from

system or DASD failures.

Step 1 shows how to terminate your application server before executing the

BACKUP function.

Step 2 shows how to process the backup of your database in both VM and VSE

environments.

Step 3 shows how to restart your application server after processing the backup

function.

Deciding How to Backup Your Database

You can make a copy of your database by taking a user archive using Data

Restore. This copy can be on tape or DASD.

You can take the backup on either of 2 different device types (tape and tape, tape

and disk, or disk and disk) at the same time, so that, for example, one copy can be

taken offsite for archiving while the other copy is kept onsite for recovery

purposes.

The file that is produced contains a copy of the directory and all dbextents that

contain data if processing a BACKUP or BACKUP FULL function.

A new option can be used to produce a file that contains the directory and all

dbextents pages modified since the last BACKUP FULL function. This function is

the BACKUP INCREMENTAL and can save a lot of time to shorten the duration of

application server shutdown.

Note: To ensure database integrity, you can only take a user archive when you

have stopped the application server.

To execute the recovery process to apply all changes referenced in the log files, the

database has to run in LOGMODE=A or LOGMODE=L. This will allow

point-in-time recovery to be performed.

You should take a backup of each database regularly, so that if a system failure

occurs and restoration is required, the time to restart the server is minimized.

For example, suppose you have taken a database backup every Friday evening,

and a log archive on Tuesday and Thursday evenings, and on the next Friday, a

media failure occurs. You would have to restore the last backup and reapply all

changes processed on the database during the whole week using the log archives.

If there was heavy activity that week, this process can take a long time.

If the backup is processed every day, and a media failure occurs, after restoring the

database, only the changes made to the database on that day will have to be

reapplied. Consequently, the server will be down for a shorter period of time.

Note: You must balance the time and resources required to perform the database

archives against the risk of failure and the acceptable time for recovery.

You can take a database archive using either the DB2 Server for VSE & VM

database archive or the Data Restore backup facility.

You can back up only the parts of the database which have been modified since

the last full backup using the INCREMENTAL BACKUP function.

For example, support a full backup is executed on Sunday night, and an

incremental backup is taken each day. The incremental backup produced on

Monday will only contain pages that were modified on Monday. The incremental

backup taken on Tuesday, will contain all pages that were modified on Monday

and Tuesday (all pages modified since the last FULL BACKUP function). When a

RELOAD is done using the incremental archive taken on Tuesday, the RELOAD

process requires the Tuesday incremental archive tape and the associated full

archive taken on Sunday. The incremental tape from Monday is not required.

Choosing DB2

Server for VSE & VM Database Archives

When you need to restore a table from an archive, you want the process to be as

quick as possible.

With Data Restore, you can reload the table from the last archive taken and apply

all the changes from the log files. This provides you with a quick way to recover a

portion of the database (see Chapter 11, “Restoring Logical Elements” on page 125)

without affecting all users of the rest of the database. The use of other unaffected

tables can continue with very little or no disruption to users requiring access to the

tables.

Data Restore can process DB2 Server for VSE & VM database archives directly as

well as its own Data Restore archives. You will have better performance when Data

Restore processes its own archives because DB2 Server for VSE & VM database

archives must be processed twice instead of once. However, DB2 Server for VSE &

VM database archives can be taken online while Data Restore user archives must

be taken offline. If desired, a DB2 Server for VSE & VM archive may be converted

to a Data Restore archive (refer to page “TRANSLATE” on page 184 for details).

For more information on the DB2 Server for VSE & VM archive and recovery

procedures, refer to the DB2 Server for VM System Administration manual.

Backup Procedures

This section describes how to create an archive to protect your database against

system failure using the BACKUP function.

Using Data Restore for Database Backups

Database backup files must be referenced in the DB2 Server for VSE & VM history

area, so that if you use Data Restore to restore the database, all changes in

successive log files are automatically reapplied when the STARTUP=U parameter is

specified.

Data Restore Guide

Step 1. Stop the DB2 Server for VSE & VM Server Before

Backing Up Your Database

Before you start to backup your database, you must stop your application server.

v If you are running the server in LOGMODE=A or L

You must stop your server by issuing an SQLEND UARCHIVE operator command

to have the backup referenced in the history area as shown in Figure 62. After all

logical units of work are completed, the database manager indicates that you must

save the directory and all dbextents. If the database is running in LOGMODE=L

and the log is not empty, a log archive is taken before terminating the database.

When the server is stopped, take the user archive using Data Restore.

ARI0062A ENTER A DB2 FOR VSE OPERATOR COMMAND.

SQLEND UARCHIVE

ARI0028I THE DATABASE MANAGER IS TERMINATING.

ARI0065I OPERATOR COMMAND PROCESSING IS COMPLETE.

ARI0239I EXTERNAL LABELING OF THIS ARCHIVE IS

TYPE

DATABASE ARCHIVE

TIMESTAMP

09-09-95

14:30:15

ARI0205I YOU MUST USER-ARCHIVE THE DIRECTORY AND 8

DBEXTENTS(S) BUT NOT THE LOG

ARI0032I THE DATABASE MANAGER HAS TERMINATED.

Figure 62. Stopping the Server with SQLEND UARCHIVE

v If you are running in LOGMODE=Y

You can only recover from the archive. You cannot reapply the changes referenced

in the log.

In this case, it is not necessary for the server to know when the database backup

was done. You can use the SQLEND operator command to stop the server, as

shown in Figure 63. If you want to ensure that the log history is updated, use the

SQLEND UARCHIVE command.

ARI0062A ENTER A DB2 FOR VSE OPERATOR COMMAND.

SQLEND

ARI0028I THE DATABASE MANAGER IS TERMINATING.

ARI0065I OPERATOR COMMAND PROCESSING IS COMPLETED.

ARI0032I THE DATABASE MANAGER HAS TERMINATED.

Figure 63. Stopping the Server with SQLEND

Step

2. Processing a Data Restore Backup

To process a backup of a database, you must create a SYSIN file to identify the

database you want to process and the name of the function to process. (Figure 64

on page 94 contains an overview of the BACKUP process.)

Chapter 8. Backing Up an Entire Database

DB2

DATABASE

BACKUP

DB2 Server for VSE. Operations Guide / Handbooks (2004-2007) - page 42