|
|
XTS9-143 CONTROL DBAPW=******** DBNAME=dbname
XTS9-143 SELECT TNAME=CUSTOMER CREATOR=SQLDBA
XTS9-100 Data Restore feature VERSION 7.1.0
XTS9-309 Processing DB2 Server for VSE & VM version 7
XTS9-152
3 ROWS SELECTED
XTS9-007
PROCESSING SUCCESSFULLY COMPLETED
Figure 132. Console Messages for SELECT with the OUTPUT=PRINTER Parameter
Data Restore displays the result on SYSPRINT/SYSLST. Question marks (?)
represent NULL columns as shown in Figure 133.
TNAME=CUSTOMERS
COLNO
001
CNAME "CUST_NO"
COLNO
002
CNAME "CUST_NAME"
COLNO
003
CNAME "CUST_ADDRESS"
COLNO
004
CNAME "CUST_POSTCODE"
COLNO
005
CNAME "CUST_TEL"
**********************************************************************************
1
2
* 3
4
* 5
**********************************************************************************
105725*DUPONT PIERRE
*33 AV DES CHAMPS ELYSEES
*75008*44-40-41-42
309001*MARTIN PAUL
*1 RUE DE LA PAIX
*93001*37-37-10-12
25018 *DURAND JACQUES
*???????????????????????????????????*75009*40-32-10-24
**********************************************************************************
Figure 133. Report for SELECT with OUTPUT=PRINTER Parameter
After a SELECT function with the OUTPUT=TAPE or the OUTPUT=DASD
parameter, the number of rows selected is displayed on the console as shown in
Figure 134.
XTS9-143 OPTIONS OUTPUT=DASD
XTS9-143 CONTROL DBAPW=******** DBNAME=dbname
XTS9-143 SELECT TNAME=CUSTOMER CREATOR=SQLDBA
XTS9-143 /*
XTS9-100 Data Restore feature VERSION 7.1.0
XTS9-309 Processing DB2 Server for VSE & VM version 7
XTS9-152
3 ROWS SELECTED
XTS9-007
PROCESSING SUCCESSFULLY COMPLETED
Figure 134. Console Messages for SELECT with OUTPUT=DASD Parameter
Data Restore writes the data to tape or DASD and displays the column’s position
on the PRINTER as shown in Figure 135.
"CUST_NO"
007-010
FIXED
"CUST_NAME"
013-032
CHARACTER
"CUST_ADDRESS"
035-066
NULL IF POS(33)=255 CHARACTER
"CUST_POSTCODE"
069-073
NULL IF POS(67)=255 CHARACTER
"CUST_TEL"
076-085
NULL IF POS(74)=255 CHARACTER
Figure 135. List Displayed for SELECT with OUTPUT=DASD Parameter
150
Data Restore Guide
Chapter 13. Displaying the Contents of an Archive File
Introduction
When you decide to restore a database or reload a table from an archive other than
the current one, you must specify the correct version of the information you want
to restore. Such information includes the date, time, and valid table names.
To obtain this information, use the DESCRIBE function which reads an archive or
an unload file and produces a report with information about the contents of the
file. From this report, you can determine the correct values for the required
parameters.
Description
Figure 136 shows an overview of the DESCRIBE process. When reloading tables
from an archive other than the latest, the date and time parameters on the
CONTROL statement are required. When reloading from the latest archive, these
parameters are optional. Use the DESCRIBE function to list all the information
about the archive file: date, time, and names of reloadable tables. Figure 137 on
page 152 shows an example of using the DESCRIBE function. Use the DESCRIBE
function to list all reloadable tables from an archive or an UNLOADed dbspace.
ARCHIVE
OR
DESCRIBE
Figure 136. Overview of the DESCRIBE Process
Using the DESCRIBE Command
Figure 138 on page 152 shows how to use the DESCRIBE function in a VM
environment.
151
// JOB DESCRIBE
(1) ---> // LIBDEF *,SEARCH=(PRD2.RCVvrm)
(2) ---> // TLBL ARCHIV,’ARCHIVE.DB2’
(3) ---> // ASSGN SYS006,180
(4) ---> // MTC REW,SYS006
(5) ---> // EXEC XTS91001,SIZE=AUTO
(6) ---> DESCRIBE
(7) ---> /*
Figure 137. JCL for the DESCRIBE function in VSE
Statement 1
Specifies the Data Restore library. You must specify
the Data Restore library for the DESCRIBE
function.
Statement 2
Identifies the label on the tape.
Statement 3
Assigns the tape drive.
Statement 4
Rewinds the tape to the first file.
Statement 5
Executes the program XTS91001.
Statement 6
Performs the DESCRIBE function as requested.
Statement 7
Ends the SYSIN file.
Sample Procedure (VM).
/**/
(1) ---> ’TAPE REW’
(2) ---> ’FILEDEF ARCHIV TAP1 SL 1 (RECFM VB BLOCK 32760’
(3) ---> ’FILEDEF SYSPRINT DISK XTS91001 SYSPRINT A’
(4) ---> ’FILEDEF SYSIN DISK RESTORE SYSIN A’
(5) ---> ’XTS91001’
Figure 138. EXEC for the DESCRIBE Function in VM
Statement 1
Rewinds the tape to its first file.
Statement 2
Identifies the label on the tape.
Statement 3
Specifies the destination of the SYSPRINT file.
Statement 4
Identifies the input source for the DESCRIBE
function.
Statement 5
Executes the program XTS91001.
The SYSIN file must contain the following statements:
(6) ---> DESCRIBE
Figure 139. Input Statements for the DESCRIBE Function
Statement 6
Specifies the function to perform. In this case, the
DESCRIBE function.
Figure 140 on page 153 shows an example of a report after completing the
DESCRIBE function on a DB2 FULL archive. Message XTS9-136 shows that the
archive in the example was taken on February 10th, 1997 at 09:03:11. Message
152
Data Restore Guide
XTS9-007 at the end of the report indicates that the DESCRIBE function executed
successfully. The rest of the report lists the tables that you can reload.
XTS9-143 CONTROL BASE=SQLDBA
XTS9-143 DESCRIBE
XTS9-143 /*
XTS9-196 Do you want to continue the DESCRIBE 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 SQLDBA archived on (02/10/97-09:03:11)
--> XTS9-229 The file is a DB2 FULL archive
XTS9-013 Table SQLDBA
.ACTIVITY
may be reloaded
XTS9-013 Table SQLDBA
.DEPARTMENT
may be reloaded
XTS9-007 Processing successfully completed
Figure 140. Output from the DESCRIBE Function Using a DB2 FULL Archive
Figure 141 shows an example of a report after completing the DESCRIBE function
on a DB2 regular archive.
XTS9-143 CONTROL BASE=SQLDBA
XTS9-143 DESCRIBE
XTS9-143 /*
XTS9-196 Do you want to continue the DESCRIBE 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 SQLDBA archived on (02/10/97-09:43:25)
--> XTS9-229 The file is a DB2 regular archive
XTS9-013 Table SQLDBA
.ACTIVITY
may be reloaded
XTS9-013 Table SQLDBA
.DEPARTMENT
may be reloaded
XTS9-007 Processing successfully completed
Figure 141. Output from the DESCRIBE Function Using a DB2 FULL Archive
You can use the DESCRIBE function to get information on the date and time of a
previous archive. Figure 142 and Figure 143 on page 154 show how to use the
DESCRIBE function in the VSE and VM environments.
// JOB DESCRIBE
(1) ---> // LIBDEF *,SEARCH=(PRD2.RCVvrm)
(2) ---> // TLBL ARCHIV,’ARCHIVE.DB2’
(3) ---> // ASSGN SYS006,180
(4) ---> // MTC REW,SYS006
(5) ---> // EXEC XTS91001,SIZE=AUTO
CONTROL
(6) ---> DESCRIBE
(7) ---> /*
Figure 142. JCL to View the Contents of a Tape (VSE)
Statement 1
Specifies the DB2 for VSE Library for the DESCRIBE function. (You
must specify the DB2 library.)
Statement 2
Identifies the label on the tape.
Statement 3
Assigns the tape drive.
Statement 4
Rewinds the tape to the first file.
Chapter 13. Displaying the Contents of an Archive File
153
Statement 5
Executes the program XTS91001.
Statement 6
Processes the DESCRIBE function as requested.
Statement 7
Ends the SYSIN.
/**/
(1) ---> ’TAPE REW’
(2) ---> ’FILEDEF ARCHIV TAP1 SL 1 (RECFM VB BLOCK 32760’
(3) ---> ’FILEDEF SYSPRINT DISK XTS91001 SYSPRINT A’
(4) ---> ’FILEDEF SYSIN DISK DESCRIBE SYSIN A’
(5) ---> ’XTS91001’
Figure 143. EXEC to List the Contents of a Tape (VM)
Statement 1
Rewinds the tape to its first file.
Statement 2
Identifies the label on the tape.
Statement 3
Specifies the destination of the SYSPRINT file.
Statement 4
Identifies the input source for the DESCRIBE function.
Statement 5
Executes the program XTS91001.
The SYSIN file must contain the following statements:
(6) ---> CONTROL DBNAME=dbname
(7) ---> DESCRIBE
Figure 144. SYSIN File to List the Contents of a Tape
Statement 6
Points to the appropriate database.
Statement 7
Specifies the function to be performed. In this case,
the DESCRIBE function.
Use the job (or a similar one) as depicted in the preceding figures.
154
Data Restore Guide
Chapter 14. Displaying Dbspace Information
Introduction
It can be most useful to you, the database administrator, if you can obtain current
information relating to the dbspace. The SHOWDBS function provides information
such as the number of header, data, or index pages available in a dbspace when
the server is offline or online.
The SHOWDBS command is similar to the DB2 SHOW DBSPACE operator
command. The SHOWDBS function usually provides better performance than the
SHOW DBSPACE command.
Note: Only information about acquired dbspaces is displayed.
Description
Figure 145 depicts the SHOWDBS process.
DB2
SHOWDBS
DATABASE
Figure 145. Overview of the SHOWDBS Function
Using the SHOWDBS Command
The following is an example of the JCL required in VSE:
// JOB SHOWDBS
(1) ---> // LIBDEF *,SEARCH=(PRD2.RCVvrm)
(2) ---> // EXEC PROC=ARIS73DB
(3) ---> // EXEC XTS91001,SIZE=AUTO
(4) ---> // MTC REW,SYS006
(5) ---> OPTIONS NOTATION=U
(6) ---> SHOWDBS SORT=NAME
(7) ---> /*
Figure 146. JCL to Execute the SHOWDBS command
Statement 1
Specifies the Data Restore library. You must specify
the Data Restore library for the SHOWDBS
function.
Statement 2
Contains all DLBL for the dbextents.
155
Statement 3
Executes the program XTS91001.
Statement 4
Rewinds the tape to the first file.
Statement 5
Allows the user to indicate the notation preference,
United States or European.
Statement 6
Performs the SHOWDBS function as requested
while sorting the output by dbspace name.
Statement 7
Ends the SYSIN file.
The following is an example of an EXEC to use in VM:
/**/
(1) ---> ’FILEDEF SYSPRINT DISK XTS91001 SYSPRINT A’
(2) ---> ’FILEDEF SYSIN DISK SHOWDBS SYSIN A’
(3) ---> ’XTS91001’
Figure 147. VM EXEC to Run the SHOWDBS function
Statement 1
Specifies the destination of the SYSPRINT file.
Statement 2
Identifies the input source for the SHOWDBS
function.
Statement 3
Executes the program XTS91001.
The SYSIN file must contain the following statements:
(4) ---> OPTIONS NOTATION=U
(5) ---> CONTROL DBNAME=dbname
(6) ---> SHOWDBS SORT=NAME
Figure 148. Input File to Run the SHOWDBS function
Statement 4
Allows you to indicate your notation preference,
United States or European.
Statement 5
Specifies the database name.
Statement 6
Performs the SHOWDBS function as requested
while sorting the output by dbspace name.
156
Data Restore Guide
Report for SHOWDBS Function
XTS9-143 OPTIONS NOTATION=U
XTS9-143 CONTROL DBNAME=dbname
XTS9-143 SHOWDBS SORT=NAME
XTS9-143 /*
XTS9-100 Data Restore feature VERSION 7.1.0
XTS9-313 DB2 for VSE & VM version 7 processed.
XTS9-601 DATE:11/10/94
TIME:16:18:05
-----------------------------------------------------------------------------
XTS9-014 Pool
1 Dbno
4 Name TEST
Pages
1,024 Headr 8 Index
33
XTS9-019
Header
Data
Index
XTS9-020
---------
--------
---------
XTS9-018 Maximum
8
679
337
XTS9-016 Used
1
4
3
XTS9-017 %Used
12.5
0.5
0.8
------------------------------------------------------------------------------
XTS9-014 Pool
1 Dbno
8 Name TEST2
Pages
1,024 Headr 8 Index
33
XTS9-019
Header
Data
Index
XTS9-020
----------
---------
---------
XTS9-018 Maximum
8
679
337
XTS9-016 Used
1
5
15
XTS9-017 % Used
12.5
0.7
4.4
------------------------------------------------------------------------------
XTS9-014 Pool
-2 Dbno
27 Name XTEST1
Pages
256 Headr 8 Index
33
XTS9-019
Header
Data
Index
XTS9-020
---------
--------
---------
XTS9-018 Maximum
8
164
84
XTS9-015 Reserved
1
1
1
XTS9-016 Used
1
0
0
XTS9-017 %Used
12.5
0.0
0.0
------------------------------------------------------------------------------
Figure 149. Report for the SHOWDBS function
Report for SHOWDBS Function (Summary Report)
XTS9-601 DATE:11/10/94
TIME:16:18:05
XTS9-022 Pool:001
XTS9-023 ========
XTS9-024
Dbno Dbspacename
Reserved
Used
Empty
XTS9-025
-----
------------
---------
------
-------
XTS9-021
1
XTEST2
2,622
171
2,451
XTS9-021
2
ZTEST
404
318
86
XTS9-026
--------
------
-------
XTS9-027 Total pool:
1
3,026
487
2,537
XTS9-022 Pool:002
XTS9-023 ========
XTS9-024
Dbno
Dbspacename
Reserved
Used
Empty
XTS9-025
-----
-------------
---------
-----
-------
XTS9-021
27
XTEST1
3
1
2
XTS9-026
---------
------
-------
XTS9-027 Total pool: -2
3
1
2
XTS9-601 DATE:11/10/94
TIME:16:18:05
XTS9-007 Processing successfully completed
Figure 150. Summary Report for the SHOWDBS Function
Chapter 14. Displaying Dbspace Information
157
Interpreting the SHOWDBS Reports
The SHOWDBS function produces two reports: a detailed report for all acquired
dbspaces, and a summary report for dbspaces with empty pages.
The detailed report displays:
1.
Information about the storage pool:
XTS9-014 Pool N1 Dbno N2 Name N3 Pages N4 Heardr N5 Index N6
N1 : Pool number (if negative, this is nonrecoverable)
N2 : Dbspace number
N3 : Dbspace name
N4 : Total number of pages in the dbspace (NPAGES)
N5 : Number of header pages (NHEADER)
N6 : Percentage of pages reserved for index pages in the dbspace (PCTINDEX)
2.
Information about maximums for the dbspace:
XTS9-018 Maximum N1
N2
N3
N1 : Maximum number of header pages
N2 : Maximum number of data pages
N3 : Maximum number of index pages
3.
Information about reserved pages in the pool for the dbspace:
Note: These reserved pages cannot be used by any other dbspace in the storage
pool.
XTS9-015 Reserved N1
N2
N3
N1 : Number of reserved header pages
N2 : Number of reserved data pages
N3 : Number of reserved index pages
This information is displayed only if there are reserved pages.
4.
Information about used pages in the dbspace:
XTS9-016 Used N1
N2
N3
N1 : Number of used header pages
N2 : Number of used data pages
N3 : Number of used index pages
5.
Information about percentage of used pages:
XTS9-017 % Used N1
N2
N3
N1 : Value of percentage of used header pages
N2 : Value of percentage of used data pages
N3 : Value of percentage of used index pages
Note: Percentage is obtained by:
% USED = (USED PAGES/MAXIMUM PAGES) * 100
The summary report displays all dbspaces with empty pages. The dbspaces are
grouped by storage pool.
1. Information about the storage pool:
XTS9-022 Pool: N1
N1 : Storage pool number
2. Information about the dbspaces with empty pages:
XTS9-024
Dbno Dbspacename Reserved Used Empty
XTS9-021
N1
N2
N3
N4
N5
158
Data Restore Guide
N1 : Dbspace number
N2 : Dbspace name
N3 : Number of reserved pages
N4 : Number of used pages
N5 : Number of empty pages (N3-N4)
3. Information about storage pools having at least one dbspace with empty pages:
XTS9-027
Total pool: N1
N2
N3
N4
N1 : Pool number (if negative, the storage pool is non-recoverable)
N2 : Total number of reserved pages in the pool for dbspaces with empty pages
N3 : Total number of used pages in the pool for dbspaces with empty pages
N4 : Total number of empty pages in the pool for dbspaces with empty pages (N2-N3)
Chapter 14. Displaying Dbspace Information
159
160
Data Restore Guide
Chapter 15. Displaying Pool Organization
Introduction
The physical organization of a database is composed with dbextents, VSAM
clusters for DB2 Server for VSE or minidisks for DB2 Server for VM those
dbextents are assigned to a storage pool(STORPOOL) during database generation
or during ADD DBEXTENT function.
If a disk on which the database is defined is damaged, it is necessary to restore the
lost information. You can restore the whole database or decide to restore only the
affected pool. When performing storage pool level recovery, the SHOWPOOL
function is used to identify which pools must be restored by listing the dbextents
associated with each storage pool defined on the database server.
Description
SHOWPOOL
DB2
DATABASE
Figure 151. General process for SHOWPOOL Function
Using the SHOWPOOL Command
The following is an example of using JCL to execute the SHOWPOOL command.
// JOB SHOWPOOL
(1) ---> // LIBDEF *,SEARCH=(PRD2.RCVvrm)
(2) ---> // EXEC PROC=ARIS73DB
(3) ---> // EXEC XTS91001,SIZE=AUTO
(4) ---> SHOWPOOL
/*
Figure 152. Sample of JCL (VSE) to execute SHOWPOOL
Statement 1
Specifies the Data Restore library. You must specify
the Data Restore library for the SHOWPOOL
function.
Statement 2
Contains all DLBL for DBEXTENTS.
Statement 3
Executes the program XTS91001.
Statement 4
Process the SHOWPOOL command.
161
Sample Procedure(VM)
/**/
(1) ---> ’FILEDEF SYSPRINT DISK XTS91001 SYSPRINT A’
(2) ---> ’FILEDEF SYSIN DISK SHOWPOOL SYSIN A’
(3) ---> ’XTS91001’
Figure 153. Sample VM procedure for running the SHOWPOOL function
Statement 1
Specifies the destination of the SYSPRINT file.
Statement 2
Identifies the input source for the SHOWPOOL
function.
Statement 3
Executes the program XTS91001.
The SYSIN file must contain the following statements:
(4) ---> CONTROL DBNAME=dbname
(5) ---> SHOWPOOL
Figure 154. Sample input file for running the SHOWPOOL function
Statement 4
Specifies the database name.
Statement 5
performs the SHOWPOOL function.
Report for SHOWPOOL Function
XTS9-143 CONTROL BASE=BASE2
XTS9-143 SHOWPOOL
XTS9-143 /*
XTS9-196 Do you want to continue the SHOWPOOL process ?
XTS9-406 Enter 0(CANCEL) or 1(CONTINUE)
XTS9-100 Data Restore feature VERSION 7.1.0
XTS9-313 DB2 for VSE & VM version 7 processed
XTS9-143 ***************************************************************
XTS9-203 Pool=
1
First dbextent=
1
pool is RECOVERABLE
XTS9-203 Pool=
2
First dbextent=
2
pool is RECOVERABLE
XTS9-203 Pool=
3
First dbextent=
4
pool is RECOVERABLE
XTS9-143 ***************************************************************
XTS9-204 Dbextent=
1
Pool=
1
Next dbextent=NONE
XTS9-204 Dbextent=
2
Pool=
2
Next dbextent=
3
XTS9-204 Dbextent=
3
Pool=
2
Next dbextent=NONE
XTS9-204 Dbextent=
4
Pool=
3
Next dbextent=NONE
XTS9-204 Dbextent=
5
Extended deleted
XTS9-143 ***************************************************************
XTS9-007 Processing successfully completed
Figure 155. Sample of Report for SHOWPOOL Function
Interpreting the Output From the SHOWPOOL Function
The SHOWPOOL command produces a report consisting of two parts.
The first part displays, for each pool, the first dbextent of the pool, and if the pool
is recoverable or not.
162
Data Restore Guide
The second part displays each dbextent, the pool in which it is located, and the
next dbextent in the same pool.
The information in the first part of the report has the following format:
XTS9-203 Pool=N1 first dbextent=N2 pool is N3
N1 is the pool number
N2 is the number of the first dbextent in pool N1
N3 indicates if the pool is RECOVERABLE or NON-RECOVERABLE
The information in the second part of the report has the following format:
XTS9-204 dbextent=N1 pool=N2 next dbextent=N3
N1 is the dbextent number
N2 is the pool number for dbextent N1
N3 is the next dbextent number in the pool or
NONE, indicating that this is the last dbextent in the pool
Using the SHOWPOOL report
The SHOWPOOL report is used to verify which storage pool should be recovered
in case of a DASD failure.
The second part of the report is used to determine which storage pool contains the
affected dbextent(s). The first part of the report is then used to determine if that
storage pool is eligible for storage pool level recovery (indicated by ’pool is
RECOVERABLE’ in message XTS9-203.) The pool number can then be included in
the pool list for the RESTORE function.
Chapter 15. Displaying Pool Organization
163
164
Data Restore Guide
Part 3. Reference
This part contains the following information:
v Command syntax
v Options and control statements
v Performance and tuning
v Messages and codes
v Examples of commands
v Problem determination
165
166
Data Restore Guide
Chapter
16. Command Syntax
All commands start in column 1. If the statement is longer than one line, continue
the statement on the next line starting after a column position other than 1.
You can use a mixture of both upper or lower case.
To include comments in the SYSIN file, enter an asterisk (*) as the first character on
the line.
You can separate parameters with either a space or comma.
The SYSIN report is sent to the printer device and shows all the input control
records used to execute the function.
An example SYSIN file that processes a Data Restore function is shown in
Figure 156. (This example reloads a table named CUSTOMERS created by
SQLDBA.)
(1) ---> OPTIONS DEVICE=TAPE LANG=S002
(2) ---> CONTROL DBNAME=SQLDBA
(3) ---> RELOAD CREATOR=SQLDBA TNAME=CUSTOMERS
(4) ---> FUNCT=NEW
(5) ---> * RELOAD CREATOR=SQLDBA TNAME=EMPLOYEE
Figure 156. Example SYSIN File to Process a Data Restore Function
Statement 1
Specifies the OPTIONS statement starts in column 1. You must
define the OPTIONS statement on the first line of the SYSIN file.
Statement 2
Specifies the CONTROL statement which starts in column 1 and
must follow the OPTIONS statement.
Statement 3
Identifies the function starting in column 1. The function definition
must follow the OPTIONS and CONTROL statements.
Statement 4
Continues the statement started on the preceding line. Note that
the statement does not start in column 1.
Statement 5
Uses an asterisk in column 1 to indicate a comment.
OPTIONS and CONTROL Statements
You can specify additional parameters for each of the Data Restore functions.
Specify these parameters in the OPTIONS statement or the CONTROL statement.
The list of those parameters follows:
167
OPTIONS Statement Parameters
VV OPTIONS
V
COMMITCOUNT=integer
WRKSIZE=integer
V
V
1
MSGDEV=integer
3
MSGCLASS=integer
2
1
2
V
V
TAPE
DEVICE2
TAPE
RESTARTCOUNT=integer
DEVICE
DASD
DASD
V
V
M
E
NBVIEWS=integer
CASE
U
NOTATION
U
V
V
RECOVERY=
NO
LANG=lang
CONFIRM=
NO
YES
YES
V
VW
ARCHTYPE=DB2
NBPASS=integer
2
REWIND=
YES
1
NO
Figure 157. OPTIONS statement
COMMITCOUNT
When the RELOAD or APPLYLOG function is invoked, this
parameter executes a COMMIT WORK after the specified number
of rows has been reloaded (no default value).
WRKSIZE
Available memory for the Data Restore feature to work, expressed
in KB (default value is 2048KB for BACKUP function and 256KB
for any other function). Take WKSIZE= NBP*5/1024 where NBP is
the number of active pages of the database to be unloaded or
backed up. If WKSIZE is not great enough Data Restore, at
execution time, will send message XTS9-122 followed by XTS9-158
containing the value needed.
When reloading or restoring from an incremental backup, the
WRKSIZE parameter must be equivalent to the value specified at
backup time. Message XTS9-230 will display the required value.
MSGCLASS
1: All messages are displayed. This is the default.
2: Only error messages are displayed
MSGDEV
1: Messages are displayed on VSE SYSLOG or VM console
2: Messages are displayed on VSE SYSLST or VM SYSPRINT
3: Messages are displayed on both devices
DEVICE
TAPE: Archive on tape. This is the default.
DASD: Archive on disk
168
Data Restore Guide
The following parameter, DEVICE2, is optional for the BACKUP or the UNLOAD
function should the user want to perform a primary and a secondary backup of
the archive in a single operation:
DEVICE2 For BACKUP Function
TAPE: A second archive copy is made on tape
DASD: A second archive copy is made on disk (ARCHIV2 must be
specified in JCL or EXEC).
In the VM environment, this parameter only specifies that dual archiving is
enabled. (It does not matter if the parameter is set to tape or DASD.) The FILEDEF
for ARCHIV2 will determine whether tape or DASD output is created.
In the VSE environment, if tape is specified a TLBL ARCHIV2 must be defined. If
DASD is specified, a DLBL must be specified for ARCHIV2.
DEVICE2 For RELOAD or RESTORE From an Incremental Backup File
TAPE: FULLARC TLBL must be specified for FULL backup file
DASD: FULLARC DLBL must be specified for FULL backup file
RESTARTCOUNT
When the RELOAD function is invoked, this parameter specifies
how many rows will be skipped before reloading. This parameter
is used for recovery after reload error, for that reason all reload
cards in SYSIN must not be modified.
CASE
M: Mixed to display messages with lower and uppercase. This is
the default.
U: Upper to display messages with upper case only, (for printers
not supporting lower case).
LANG
To obtain Data Restore feature messages in a specific language,
specify the National Language code on LANG parameter:
S001 - American English (AMENG). This is the default.
S002 - Upper case English
S003 - French
S004 - German
D001 - Japanese
D003 - Chinese
NOTATION
E: European: Numbers are displayed with point for thousand and
comma for decimal. For example: 1.234.567,89. This is the default.
U: United States: Numbers are displayed with point for decimal
and comma for thousand. For example: 1,234,567.89
RECOVERY
NO: No table recovery from log files is required. This is the
default.
YES: The user wants to generate the file containing all
modifications on reloaded tables from LOGS.
NBVIEWS
If the database contains many views (more than 200 for the
Chapter 16. Command Syntax
169
reloaded tables) the user will specify the number of views
estimated to recreate after RELOAD function (default value: 200).
CONFIRM Only available in VM environment
YES: Before processing the function in SYSIN, Data Restore will
prompt the operator to confirm the function to
NO: No confirmation for the process is required.
ARCHTYPE DB2: The RELOAD function is to be processed directly from a
standard DB2 Server for VSE & VM archive file.
NBPASS
For RELOAD processing from DB2 Server for VSE & VM archive
indicates the number of reading passes.
2: For normal processing. This is the default.
1: can be specified on a Restart (for instance after a power failure)
to avoid reading the DB2 Server for VSE & VM archive twice, only
valid if the message XTS9-193: ″Mount first tape of DB2 Server for
VSE & VM archive″ was previously received.
REWIND
For UNLOAD processing in VSE only, whether the output tape
should be rewound during OPEN/CLOSE processing.
YES: Rewind during OPEN/CLOSE processing.
NO: Do not rewind during OPEN/CLOSE processing.
CONTROL Statement Parameters
DBNAME=dbname
VV
CONTROL
V
BASE=dbname
DATE=date TIME=time
V
V
YES
DBAPW=dbapw
READPW=read password
VERIFY
NO
V
VW
WRITEPW=write password
Figure 158. CONTROL statement
DBNAME Dbname of database to process. This parameter is optional for the
VSE environment (BASE is equivalent to DBNAME parameter).
DATE
Date of BACKUP.
This parameter must be specified when restoring from an archive
that is not the last archive executed. The date format is provided
by your system.
TIME
Time of BACKUP.
This parameter must be specified when restoring from an archive
that is not the last archive executed. The time format is
HH:MM:SS.
170
Data Restore Guide
VERIFY
With VERIFY=YES parameter: if the parameter DBNAME on the
CONTROL statement is different from the archive TAPE/DASD the
RELOAD job terminates immediately. (default value).
With VERIFY=NO parameter: even if the parameter DBNAME on
the CONTROL statement is different from the archive
TAPE/DASD, the process continues.
DBAPW
RELOAD function needs DBA authority to execute all commands
necessary to restore a table. A connect to SQLDBA is issued during
processing. If SQLDBA user’s password has changed between
BACKUP and RELOAD function, the new password must be
specified on CONTROL statement.
This parameter must be specified for SELECT and UNLOAD
functions.
It will appear as asterisks on output for confidentiality reasons.
READPW
Data Restore links application server’s minidisks during
processing, if the minidisks are password protected, the READ
password must be specified (VM only) for SELECT, UNLOAD,
RELOAD and SHOWDBS functions (all minidisks must be defined
with the same password).
WRITEPW
Data Restore links application server’s minidisks during
processing, if the minidisks are password protected, the WRITE
password must be specified (VM only) for BACKUP and RESTORE
functions (all minidisks must be defined with the same password).
Chapter 16. Command Syntax
171
APPLYLOG
APPLYLOG
VV APPLYLOG END=Timestamp
VW
Purpose
Use the APPLYLOG command to apply the DB2 statements that were extracted
from the log (and the log archive) during the RELOAD operation.
172
Data Restore Guide
BACKUP
BACKUP
VV BACKUP
VW
FULL
INCREMENTAL
Purpose
Use the BACKUP command to back up your database.
Operands
FULL
A full backup is executed using this parameter.
INCREMENTAL
An incremental backup is executed using this parameter. NOTE: This
parameter can only be specified if a FULL backup has been executed.
Chapter 16. Command Syntax
173
DESCRIBE
DESCRIBE
VV DESCRIBE
VW
Purpose
Use the DESCRIBE command to read an archive or unload a file and produce a
report with information about the contents of the file.
To Data Restore RESTORE a Data Restore archive that was not the last archive
taken, you first have to run the Data Restore DESCRIBE function in order to have
the correct identification of that archive. It also gives you a list of tables that are
available in this archive file and can be reloaded individually if necessary.
The archive identification must be specified in the CONTROL statement with
“DATE=” and “TIME=” for the Data Restore RESTORE. Refer to Figure 168 on
page 205 for a sample CONTROL statement that defines an archive to be restored.
The Data Restore DESCRIBE function reads one of the following:
v A Data Restore archive
v A Data Restore translated DB2 archive
v A Data Restore UNLOAD file
and produces a report with information about the contents of that file. From the
report you can get information about the following values: date, time and names of
reloadable tables.
Files
The Data Restore DESCRIBE requires some input and output files:
For VM only:
v SYSIN file - contains the command to be processed; in this case the DESCRIBE
function. Figure 164 on page 204 shows a sample of how this command could be
used.
v SYSPRINT file - Data Restore feature creates a report of the contents from the
Data Restore archive or Data Restore UNLOADed file. Figure 166 on page 204
shows a sample of the SYSPRINT output.
For VM and VSE:
v ARCHIV - one of the following:
- Data Restore archive file to be listed
- Data Restore UNLOAD file to be listed
174
Data Restore Guide
FORMAT
FORMAT
VV FORMAT DDSK=integer
VW
Purpose
Use the FORMAT command to format a VSE/VSAM defined file in the DB2 Server
for VSE required format.
In VSE, when a dbextent is damaged or has to be moved to a different volume, the
extent has to be defined using the IDCAMS DEFINE CLUSTER command. Before
restoring the storage pool or the database using the Data Restore RESTORE
command, you must prepare the defined cluster for DB2. This process only applies
for data extents.
It does not apply for:
v the directory disk (BDISK). The Data Restore feature formats the BDISK while
restoring the database.
v the log disk (LOGDSK1). To format the log disk, the COLDLOG procedure with
the parameters SYSMODE=S and STARTUP=L should be used.
If in VSE DB2 facilities are used to restore a database, you have two choices:
v The Data Restore FORMAT is not needed, as the DB2 restore procedure with
STARTUP=R first formats all dbextents and then restores the database.
v You can use Data Restore FORMAT to format just one extent, for example after
replacing, and then you can apply the DB2 restore procedure with STARTUP=F,
so that it omits formatting. This can be much quicker, depending on the number
of extents used.
In VM, the CMS commands FORMAT and RESERVE can be used to perform the
equivalent function for both follow-on processes, Data Restore RESTORE and DB2
restore.
Chapter 16. Command Syntax
175
LISTLOG
LISTLOG
VV LISTLOG
VW
Purpose
To list the contents of LMBRLG1, LMBRLG2, and LMBRLG3, execute the LISTLOG
function using the XTS91001 program.
176
Data Restore Guide
RELOAD
RELOAD
VV RELOAD CREATOR=creator TNAME=tname
V
NEWCREATOR=creator
V
V
NEWTNAME=tname
OWNER=owner
DBSPACE=dbspacename
V FUNCT=
NEW
VW
ADD
REPLACE
Purpose
Use the RELOAD command to reload tables.
Operands
RELOAD
Executes a reload of a table.
FUNCT
Specifies the reload option to use:
PURGE
Deletes all rows from the table NEWCREATOR.NEWTNAME and restores
all the rows from the table CREATOR.TNAME into it.
NEW
Creates the table NEWCREATOR.NEWTNAME and restores all the rows
from CREATOR.TNAME into it.
ADD
Inserts all the rows from CREATOR.TNAME into the table
NEWCREATOR.NEWTNAME.
REPLACE
Drops and recreates the table NEWCREATOR.NEWTNAME, restores all
the rows from the table CREATOR.TNAME into it, and recreates all the
indexes, referential integrity, views, grants, comments and labels.
Note: This option creates the table even if the table did not exist
previously.
TNAME
Specifies the name of the table to be restored.
CREATOR
Specifies the creator of the table to be restored.
NEWTNAME
Specifies a new table name, in case you want to reload a table with a different
name.
NEWCREATOR
Specifies a new creator for the table, in case you want to reload a table with a
different creator.
Chapter 16. Command Syntax
177
RELOAD
The CREATOR.TNAME table is reloaded into NEWCREATOR.NEWTNAME.
If you do not specify these optional parameters, Data Restore uses TNAME for
NEWTNAME and CREATOR for NEWCREATOR, so the table will be reloaded
with the same name.
DBSPACE
Specifies the name of the dbspace which will contain the table to be restored.
This parameter must be specified for FUNCT=NEW and FUNCT=REPLACE. If
the table CREATOR.TNAME exists, the default value for the parameter is the
name of the dbspace where this table resides.
OWNER
Specifies the owner of the dbspace which contains table to be restored. If you
do not specify an owner, PUBLIC is assumed. This parameter must be
specified for private dbspaces.
Usage Notes
1. If the table to be reloaded contains a LONG column, a file called LMBRWRK is
created and used as a workfile. LMBRWRK contains all of the data pages of the
dbspace containing the table with LONG columns.
2. If the first character on the SYSIN card is *, the card not used. The SYSIN is
displayed on the PRINTER.
3. An OPTIONS statement can also be specified (for more information see
“OPTIONS and CONTROL Statements” on page 167).
4. To reload more than one table, specify more RELOAD functions in SYSIN (up
to 90 tables can be reloaded in one SYSIN).
5. To list the tables that can be reloaded from a BACKUP or UNLOAD file, use
the DESCRIBE function (refer to Chapter 13, “Displaying the Contents of an
Archive File” on page 151 for details).
178
Data Restore Guide
RESTORE
RESTORE
VV RESTORE
VW
POOL=
n
,
X
( n
)
Purpose
Use the RESTORE command to recover a single storage pool, a set of storage pools
or an entire database after a system or disk failure, or to create a copy of the entire
database on the same or another system.
Chapter 16. Command Syntax
179
SELECT
SELECT
VV SELECT CREATOR=creator TNAME=tname
V
,
COLUMNS=
X
cname
colno
V
VW
PRINTER
COUNT=nnn
OUTPUT=
TAPE
DASD
Purpose
The SELECT command lets you select data from DB2 tables directly out of the
dbextents, bypassing the database manager.
Operands
CREATOR
The name of the creator of the table.
TNAME
The name of the table which will be selected
COLUMNS
The specific columns to be selected. It can list either column name(s) or
column number(s). If you omit the COLUMNS= parameter, all columns are
selected.
OUTPUT
The output device:
PRINTER The output file is spooled to the printer. This is the default.
TAPE The output file is a tape file
DASD The output file is a disk file
COUNT
The specific number of rows to be selected (from 1 to 32000).
180
Data Restore Guide
SHOWDBS
SHOWDBS
VV SHOWDBS
VW
SORT
NAME
DBNO
PCT
Purpose
Use the SHOWDBS command to provide information such as the header, data or
index pages available in a dbspace when the server is offline or online.
The Data Restore SHOWDBS executes a SHOW DBSPACE function while the
database is online or offline.
Operands
SORT
Use this parameter to obtain sorted output. This parameter is optional.
PCT
Displays the results sorted by dbspaces having the highest percentage of
used data or index pages.
NAME
Displays results sorted by dbspace name.
DBNO
Displays the results sorted by dbspace number.
Chapter 16. Command Syntax
181
SHOWPOOL
SHOWPOOL
VV SHOWPOOL
VW
Purpose
Use the SHOWPOOL command to identify which pools must be restored by listing
the dbextents associated with each storage pool defined on the database server.
The Data Restore SHOWPOOL executes a SHOW POOL function while the
database is online or offline. This function is used to identify which storage pool
should be restored by listing the dbextents associated with each storage pool
defined on the database server.
182
Data Restore Guide
SHOWPTFH
SHOWPTFH
VV SHOWPTFH
VW
Purpose
Use the SHOWPTFH command to display all Program Temporary Fixes (PTF)
installed on the system for Data Restore feature.
Chapter 16. Command Syntax
183
TRANSLATE
TRANSLATE
VV TRANSLATE
VW
Purpose
Use the TRANSLATE command to increase the performance when later restoring
the complete database or parts of it from a DB2 archive.
The Data Restore feature command TRANSLATE can be used to increase the
performance when later restoring the complete database or parts of it from a DB2
archive. TRANSLATE reads the DB2 archive tapes twice and creates tapes or disk
files that can be used as input for the Data Restore RESTORE or RELOAD, as if it
were a Data Restore archive made via Data Restore BACKUP. TRANSLATE
improves the performance of the recovery operation.
TRANSLATE is required to Data Restore RESTORE from a DB2 database, or the
database can be restored using the standard DB2 restore facility. For Data Restore
RELOAD, TRANSLATE is optional.
The TRANSLATE process can be started at any time and does not affect the
operation of your database manager. When this process should run depends on
your environment:
v either immediately following the archive,
v or preceding the restore of either the database (RESTORE) or part of the
database (RESTORE, RELOAD).
Files
The Data Restore TRANSLATE 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 ARCHIV - workfile
v ARIARCH - input file from a DB2 archive
v LMBRWRK - workfile or cluster which contains pages for tables containing
LONG columns.
SYS0001, HEADER and DIRWORK will be used by a later RELOAD function.
v SYS0001 - workfile, stores active pages
v HEADER - workfile, stores header pages
v DIRWORK - workfile, stores the directory pages
Figure 159 on page 185 is an example of a function report for the TRANSLATE
function on a DB2 FULL archive.
184
Data Restore Guide
TRANSLATE
XTS9-143 TRANSLATE
XTS9-143 /*
XTS9-196 Do you want to continue the TRANSLATE process ?
XTS9-406 ENTER 0(CANCEL) OR 1(CONTINUE)
XTS9-403 REPLY IS 1
XTS9-100 DATA RESTORE FEATURE VERSION 7.1.0
--> XTS9-229 The file is a DB2 FULL archive
XTS9-193 Mount first tape of a database server archive
XTS9-406 Enter 0(CANCEL) or 1(CONTINUE)
XTS9-403 REPLY IS 1
XTS9-013 TABLE SQLDBA
.ACTIVITY
MAY BE RELOADED
XTS9-013 Table SQLDBA
.DEPARTMENT
MAY BE RELOADED
XTS9-007 Processing successfully completed
Figure 159. Sample Report after Translation of a DB2 Server for VM FULL Archive
Figure 160 is an example of a function report for the TRANSLATE function on a
DB2 regular archive.
XTS9-143 TRANSLATE
XTS9-143 /*
XTS9-196 Do you want to continue the TRANSLATE process ?
XTS9-406 ENTER 0(CANCEL) OR 1(CONTINUE)
XTS9-403 REPLY IS 1
XTS9-100 DATA RESTORE FEATURE VERSION 7.1.0
--> XTS9-229 The file is a DB2 regular archive
XTS9-193 Mount first tape of a database server archive
XTS9-406 Enter 0(CANCEL) or 1(CONTINUE)
XTS9-403 REPLY IS 1
XTS9-013 TABLE SQLDBA
.ACTIVITY
MAY BE RELOADED
XTS9-013 Table SQLDBA
.DEPARTMENT
MAY BE RELOADED
XTS9-007 Processing successfully completed
Figure 160. Sample Report after Translation of a DB2 Server for VM Regular Archive
Notes:
1. The translate process reads DB2 Server for VM database archive tapes twice.
2. When the message XTS9-193 is displayed you have to remount the archive
tapes beginning with the first archive tape.
3. You can restore the whole database from the translated DB2 Server for VM
database archive, as well as restoring parts of the database.
Chapter 16. Command Syntax
185
UNLOAD
UNLOAD
,
VV UNLOAD
DBSPACE=(
X
dbspacename
)
V
COND=
INCLUDE
EXCLUDE
V
VW
MODE=
ONLINE
OFFLINE
Purpose
The UNLOAD command unloads dbspaces to an output file.
Operands
DBSPACE=
Identify the list of dbspaces to unload.
dbspacename
Identify the names of dbspaces to process (default value for owner is PUBLIC).
* Identify that all dbspaces are included in the list. To unload a specific dbspace,
enter the name of the dbspace. To unload several dbspaces at the same time,
enter the list of dbspace names separated by a comma.
COND=
Identify whether the list of dbspaces is to be included or excluded for
unloading.
INCLUDE
Specify that the list identifies those dbspaces that are to be included in
unloading. This is the default.
EXCLUDE
Specify that the list identifies those dbspaces that are to be excluded in
unloading.
MODE=
Identify whether the application server is running or not during the UNLOAD
process.
ONLINE
Specify that the application server is up.
OFFLINE
Specify that the application server is down.
Usage Notes
The UNLOAD command can include up to 90 dbspace names on a maximum of 10
SYSIN lines. In VSE, the REWIND parameter on the OPTIONS line, can be used to
specify whether the output tape should be positioned (REWIND=YES) on not
(REWIND=NO). In VM, you can use the LEAVE parameter on the FILEDEF for the
186
Data Restore Guide
UNLOAD
output file to achieve the same results.
Chapter 16. Command Syntax
187
UNLOAD
188
Data Restore Guide
Part 4. Appendixes
189
|
||
|
|
|