RESTORE

You can specify Archive Files and Table Maps using a list of names or DB2^® LIKE syntax. The RESTORE statement can be a convenient way to automate the restoration of archived data. For example, an application might generate a RESTORE statement to restore archived data when conditions require.

You can also use a RESTORE statement without restoring the data. Specify wildcards and the PREVIEW YES parameter to generate a list of matching Archive Files and to indicate which Archive File would have been restored.

RESTORE 
  ARCHIVE_FILE ( { USEDD | filename1 [, filename2 ] [ ,... ] } )
     [ GROUP  group ]
     [ DESC desc ] 
     [ FROMDATE yyyy-mm-dd ]
     [ TODATE yyyy-mm-dd ]
     [ OLDER (n parameter [, n parameter,… ] ) ]
     [ SORT ( [ NAME, { A | D } ] [ GROUP, { A | D } ] [ DESC, { A | D } ]
        [ DATE, { A | D } ] [ ARCHIVEDBY, { A | D } ] ) ]
     [ RESTORE_MULTIPLE { FIRST | ALL } ]
  { TABLE_MAP_DEFINE (parameters) ; | TABLE_MAP ( mapid.name1 [, mapid.name2, ... ] )
     [ TMDEFCID defcreatorid ]
     [ UNKNOWN { ALLOW | FAIL } ]
     [ TM_SORT [ DATE, ] { A | D } ] }
  { CONTROL_PREFIX  value | CONTROL_NAME explicitfilename } 
     [ USE_EXISTING_CONTROL { YES | NO } ] 
     [ CONTROL_DELETE { YES | NO } ]
  { SUBSET_PREFIX  { value | * } | SUBSET_NAME explicitfilename }
     [ USE_EXISTING_SUBSET { YES | NO } ] 
     [ SUBSET_DELETE  { YES | NO } ]
  [ ROWLIST {dsname | (USEDD) } ]
  [ SELECT ( [ cid. ] tblname1, coloperator, colname1, critoperator, criteria
        [, colname2, critoperator, criteria, ... ] ) ]
  [ SELECT ( [ cid. ] tblname2, coloperator, colname1,
critoperator, criteria 
        [, colname2, critoperator, criteria, ... ] ) ] ...
  [ SQL ( [ cid. ] tblname1, { AND | OR }, sqlclause1 ) ]
  [ SQL ( [ cid. ] tblname2, { AND | OR }, sqlclause2 ) ]...
  [ CASE { YES | NO } ]
  [ START_TABLE [ cid. ] tblname ]
  [ REF_TABLE ( [ cid. ] tblname1, { YES | NO } ) ]
  [ REF_TABLE ( [ cid. ] tblname2, { YES | NO } ) ]...
  [ REL_OVRD ( TABLE [ cid. ] tblname REL relname 
     [ SELECT { YES | NO } | Q1 { YES | NO } | Q2 { YES | NO } ] ) ]
  [ RESTORE_MODE  { INS | UPD | BOTH } ]
  [ MAX_RESTORE_ROWS n ]
  [ LOCK_TABLES  { YES | NO } ]
  [ COMMIT_COUNT  n ]
  [ COMMIT_MINUTES  n ]
  [ DISCARD_COUNT n ]
  [ RESTORE_ERROR { STOP | CONTINUE } ]
  [ TM_LIST { YES | NO } ]
  [ PREVIEW { YES | NO } ]
  [ REPORT_LEVEL { DETAIL | SUMMARY } ]
  [ NOT_FOUND_RC4 { YES | NO } ]
  [ EMPTY_TABLE_FOUND_RC4 { YES | NO } ]
  [ RESTART { YES | NO } ]

Archive File parameters

Use the following parameters to specify one or more Archive Files.

ARCHIVE_FILE

The name of one or more Archive Files to be restored. Multiple values must be enclosed in parentheses and separated by commas. (Note that if you specify a single value, parentheses are optional.) NAME is required.

USEDD

Use the file name specified in the PSDFEXTR control card statement. Only one USEDD operand is supported.

filename

The fully qualified name of a cataloged Archive File data set as an explicit value or a pattern, using DB2 LIKE syntax.

You can list Archive File names and patterns in any order. Use the SORT parameter to specify the order in which the Archive Files are processed; by default, the files are processed in ascending order.

If you specify more than one name, or if any name uses a pattern, you must also specify the CONTROL_PREFIX parameter.

GROUP

Group designation for the Archive Files to be restored.

group

Group as an explicit value or a pattern, using DB2 LIKE syntax.

DESC

Case-sensitive descriptive information for the Archive Files to be restored.

desc

Description as an explicit value or a pattern, using DB2 LIKE syntax. Delimit values that include blanks in single (‘ ') or double quotes (“ ”).

FROMDATE

The oldest date in a range of Archive File creation dates. Data from the selected Archive Files is restored. You can use FROMDATE or OLDER, but not both.

yyyy-mm-dd

Date in ISO, European, or USA format. Archive converts the date to your DB2 default format.

• Use FROMDATE alone to select all Archive Files created on or after that date.
• Use FROMDATE with TODATE to designate a range.

TODATE

The most recent date in a range of Archive File creation dates. Data from the selected Archive Files is restored. You can use TODATE or OLDER, but not both.

yyyy-mm-dd

Date in ISO, European, or USA format. Archive converts the date to your DB2 default format.

• Use TODATE alone to select all Archive Files created on or before that date.
• Use TODATE with FROMDATE to designate a range

OLDER

An interval used to exclude Archive Files; that is, only data from Archive Files that are older than the specified interval is restored. You can use OLDER, or FROMDATE and TODATE, but not both.

n

The number of days, weeks, months, or years in the interval.

parameter

DAY(S)WEEK(S)MONTH(S)YEAR(S)You can use one or more parameters (DAYS, WEEK, etc.), each with an n operand, in any order.

Examples:

• Specify OLDER (1 YEAR) on March 23, 1999, to restore data from Archive Files created on or before March 23, 1998.
• Specify OLDER (2 YEARS, 6 MONTHS) on June 30, 1999, to restore data from Archive Files created on or before December 31, 1996.

SORT

The sequence in which Archive Files are matched with file criteria. If you do not specify a SORT parameter, the files are matched in ascending order, by name.

NAME

In order by name.

GROUP

In order by group.

DESC

In order by description.

DATE

In order by date created.

ARCHIVEDBY

In order by TSO ID of the user that created the Archive File.

For all of the SORT parameters, valid values are:

A

In ascending order (default).

D

In descending order.

RESTORE_MULTIPLE

Selection of Archive Files for restoration, if more than one matches file criteria. If you do not specify a RESTORE_MULTIPLE parameter, data from all matching files is restored.

FIRST

Restore data from the first file that matches file criteria.

ALL

Restore data from all files that match file criteria.

Table Map parameters

Use the following parameters to specify one or more Table Maps.

TABLE_MAP

The name of one or more Table Maps used to restore data in one or more Archive Files. Multiple values must be enclosed in parentheses and separated by commas. (Note that if you specify a single value, parentheses are optional.) TABLE_MAP is required.

mapid.name

The fully qualified name of a Table Map as an explicit value or a pattern, using DB2 LIKE syntax. If you specify more than one Table Map name, or if any name uses a pattern, data in an Archive File is restored using the first Table Map with the same number of tables and the same table names as the archived data. Columns for each table in the Table Map must be compatible with those in the Archive File.

Use the TM_SORT parameter to specify the order in which Table Maps are matched with Archive Files; by default, they are matched in ascending order.

Use the TM_LIST parameter to list the Table Maps used in the process, and those not used.

TABLE_MAP_DEFINE

The Optim™ online process generates this parameter when it creates a RESTORE job for batch execution.

When you create a RESTORE job outside of the Optim online process, the best practice is to use the TABLE_MAP parameter to refer to a named Table Map in the Optim Directory.

Place the TABLE_MAP_DEFINE parameters within parentheses. A semicolon must follow the close parenthesis. (See TABLE_MAP_DEFINE Parameters for the allowable parameters.)

TMDEFCID

Override for the destination Creator ID for the selected Table Map.

defcreatorid

The default Creator ID

UNKNOWN

Processing for tables referenced in the Table Map that are unknown.

ALLOW

Bypass unknown table and continue RESTORE.

FAIL

Terminate the RESTORE (default).

TM_SORT

The order in which Table Maps are matched with Archive Files. Omit this parameter to match Table Maps in ascending order, by name.

DATE

Match Table Maps by last modified date sequence.

A

Match Table Maps in ascending order (default).

D

Match Table Maps in descending order.

Control File parameters

Use the following parameters to specify the Control File. All parameters required to create the control file are determined automatically by the batch utility or supplied as installation defaults.

CONTROL_PREFIX

The prefix used to generate a data set name for the Control File. A data set qualifier in the form “.Cnnnnn” is appended to create a unique Control File name for each restore operation. This parameter is required if you specify multiple Archive File names or a pattern for the NAME parameter, or if the CONTROL_NAME parameter is not specified.

value

A maximum of 32 characters.

CONTROL_NAME

The name of the Control File for each restore operation. This parameter is required unless CONTROL_PREFIX is specified, but is allowed only when the RESTORE_ERROR_STOP has been specified or assumed by default.

explicitfilename

The fully qualified name of the Control File used for each restore operation. An existing data set is overwritten.

USE_EXISTING_CONTROL

The action taken when the Control File already exists. Specify:

YES

Use the existing Control File.

NO

Delete the existing Control File and reallocate it. This is the default.

CONTROL_DELETE

Disposition of a Control File if no errors occur. This parameter applies only if CONTROL_PREFIX is also present. Omit the CONTROL_DELETE parameter to retain the Control File.

YES

Delete the Control File after the Restore Process is completed.

NO

Retain the Control File after the Restore Process is completed

Subset File parameters

Use the following parameters to specify the Subset File. All parameters required to create the subset file are determined automatically by the batch utility or supplied as installation defaults.

SUBSET_PREFIX

A prefix used to generate a data set name for a file containing the subset of data described by selection criteria. A data set qualifier in the form “.Snnnnn” is appended to create a unique subset file name. This parameter is required if a pattern is used for the Archive File name or SUBSET_NAME is not specified.

value

A maximum of 32 characters.

*

The value specified for CONTROL_PREFIX is used. Different data set qualifiers prevent a DSN conflict.

SUBSET_NAME

The name of the file containing the subset of data described by selection criteria. This parameter is required unless SUBSET_PREFIX is specified. RESTORE_ERROR_STOP must be specified or assumed by default.

explicitfilename

Fully qualified name of subset file. An existing data set is overwritten; a new data set is allocated and used.

USE_EXISTING_SUBSET

The action taken when the subset file already exists.

YES

Use the existing subset file.

NO

Delete the existing subset file and reallocate it. This is the default.

SUBSET_DELETE

Disposition of the subset file if no errors occur during the Restore Process. This parameter applies only if SUBSET_PREFIX is also present. Omit the SUBSET_DELETE parameter to retain the subset file.

YES

Delete the subset file after the process is completed.

NO

Do not delete the subset file (default).

Selection Criteria parameters

Use the following parameters to select the data to restore.

ROWLIST

The name of a Point-and-Shoot file used to select data for restoration.

Note: If you specify ROWLIST and SELECT or SQL criteria, only rows that meet the SELECT or SQL criteria are matched against the ROWLIST.

dsname

The fully qualified name of a sequential file or partitioned data set with member name.

Note: Specify $$TEMP to indicate a PSDFPNS DD * data set.

(USEDD)

Use the DDNAME PSDFPNS to specify the Point-and-Shoot file. Only one USEDD operand is supported.

SELECT

Criteria used to select data. If you omit the SELECT parameter, all rows in the Archive File are restored.

Note: If you specify ROWLIST and SELECT or SQL criteria, only rows that meet the SELECT or SQL criteria are matched against the ROWLIST.

cid.tblname

The table name. Prefix with Creator ID, if needed to identify the table.

coloperator

Operator used to combine criteria for the table. One of the following must be indicated, regardless of the number of colname operands.

AND

Select data that matches criteria for all columns.

OR

Select data that matches criteria for at least one column.

colname

The name of the column. At least one colname operand, with coloperator, critoperator, and criteria, must be used.

critoperator

Operator for criteria. (See following explanation for criteria.)

criteria

Up to 250 characters of criteria per column. If the column has a character, graphic, or binary data type (e.g., CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, BINARY, VARBINARY, DATE, TIME, TIMESTAMP, or TIMESTAMP WITH TIME ZONE), the criteria must be delimited with single quotes. If the column has a numeric data type (e.g., INTEGER, SMALLINT, BIGINT or DECIMAL), the criteria must not be in quotes. If the Archive File has dense indexes that you want to use, the criteria must be case-sensitive. See the CASE parameter for more information. Separate criteria and critoperator with a comma.

Note: You must leave a space after a comma that precedes a numeric value if the DB2 setup specifies a comma as the decimal point value.

• EQ, criteria
• NE, criteria
• GT, criteria
• LT, criteria
• GE, criteria
• LE, criteria
• IN, (a,b,c,d...)
• NOT IN, (a,b,c,d...)
• IS NULL
• IS NOT NULL
• LIKE, pattern
• NOT LIKE, pattern
• BETWEEN, x AND y
• NOT BETWEEN, x AND y

SQL

SQL WHERE criteria used to select data from a table in the Archive File.

Note: If you specify ROWLIST and SELECT or SQL criteria, only rows that meet the SELECT or SQL criteria are matched against the ROWLIST.

cid.tblname

Table name. Prefix with Creator ID, if needed to identify the table.

AND

Select data that matches selection criteria and SQL WHERE clause for table (default).

OR

Select data that matches selection criteria or SQL WHERE clause for table.

sqlclause

Specify criteria as an SQL WHERE clause. Do not use quotes or parentheses; clause ends with statement (at close parenthesis).

Note: You must leave a space after a comma that precedes a numeric value if the DB2 setup specifies a comma as the decimal point value.

CASE

Case requirement for matching selection criteria. Omit the CASE parameter to select data without regard to case. The value for this parameter must be YES if the Archive File has dense indexes that you want to use. Specifying the value NO or omitting this parameter causes any dense indexes to be ignored.

YES

Select data that match case of criteria.

NO

Select data that match criteria without regard to case. This is the default.

START_TABLE

Override for the Start Table in Access Definitions used to create the Archive Files.

cid.tblname

Table name. Prefix with Creator ID, if needed to identify the table.

REF_TABLE

If a selective restore is used, you can identify tables to use as reference tables in the Restore Process. All rows in a reference table are restored, unless selection criteria are specified for the table. By default, the reference table specifications in the Access Definition used to create the Archive File are used in a selective restore.

You can add reference table specifications or remove an existing reference table specification for tables. Use the REF_TABLE parameter for each table.

cid.tblname

Table name. Prefix with Creator ID, if needed to identify the table.

YES

Table is a reference table.

NO

Table is not a reference table.

REL_OVRD

Overrides the “select” flag and Q1/Q2 values for a relationship, as defined in the Access Definition. The REL_OVRD parameters must be enclosed in parentheses and separated by spaces.

See Select Relationships in the Common Elements Manual for a detailed description of how Optim uses the following relationship parameters.

TABLE

Name of the child table in the relationship (required).

Note: Prefix with the Creator ID, if needed to identify the table. If you omit the Creator ID, the default Creator ID from the Access Definition is used.

REL

The relationship name (required).

SELECT

Indicates use of the relationship in processing (optional).

YES

Use the relationship.

NO

Ignore the relationship.

Q1

The Q1 value for the relationship (optional).

Q2

The Q2 value for the relationship (optional).

Note: The SELECT, Q1, and Q2 parameters are optional. If omitted, the corresponding Access Definition values apply.

Processing parameters

Use the following parameters to specify processing options for the Restore Process.

RESTORE_MODE

The method used in the Restore Process. This parameter must be used if a Site Option allows users to select the mode.

INS

Data rows are inserted.

UPD

Data rows are updated.

BOTH

Data rows are both inserted and updated. If a restored row matches a destination row, the destination row is updated; if the restored row does not match a destination row, it is inserted.

MAX_RESTORE_ROWS

The maximum number of rows to be restored. The Restore Process is terminated if the number of restored rows exceeds this limit.

n

A number from 1 to 4,294,967,295.

LOCK_TABLES

Indicate whether to lock tables during processing. This parameter is not allowed if the site option prevents users from locking tables.

YES

Lock tables.

NO

Do not lock tables.

If you do not use the LOCK_TABLES parameter, tables are not locked during processing.

COMMIT_COUNT

The number of rows processed between commit operations. Omit the COMMIT_COUNT parameter to use the site limit.

n

A number from 1 to the site limit.

COMMIT_MINUTES

The number of minutes of elapsed time between commit operations. A COMMIT_MINUTES specification overrides COMMIT_COUNT.

n

A number from 1 to 1440.

DISCARD_COUNT

The maximum number of discarded rows per restore. Omit the DISCARD_COUNT parameter to allow an unlimited number of rows to be discarded.

n

A number from 1 to 4,294,967,295.

RESTORE_ERROR

Processing when error condition occurs during restoration. This parameter applies to operations and Archive Files subsequently processed while executing the RESTORE statement. It does not affect the execution of subsequent Batch Utility control statements.

STOP

Further restore operations for the RESTORE statement are suppressed.

CONTINUE

Further restore operations for the RESTORE statement are executed.

If you do not use the RESTORE_ERROR parameter, processing of the RESTORE statement stops when an error condition occurs.

Reporting

TM_LIST

Reporting for Archive Files and Table Maps referenced in the process. If you omit the TM_LIST parameter, the report is not generated.

YES

For each Archive File, generate a list of Table Maps referenced in the process, indicating whether a Table Map is selected or acceptable for use or the reason it is not. The report is written to PSDFASUM.

Note: Use PREVIEW YES with TM_LIST YES to generate the list without restoring the data.

NO

Do not generate a list of Archive Files and Table Maps referenced in the process.

PREVIEW

Processing indicator.

YES

Execute the RESTORE statement without processing the data. PREVIEW YES generates a list of Archive Files that match wildcard entries in the RESTORE statement and indicates which Archive File(s) would have been restored had the data been processed.

NO

Execute the RESTORE and process all data. If you omit the PREVIEW parameter, all processing takes place.

REPORT_LEVEL

The level of detail provided in the Process Report.

DETAIL

Produce a detailed report (default).

SUMMARY

Produce a summary report.

NOT_FOUND_RC4

Indicate the action taken and return code (RC) setting when no rows are restored in a selective restore.

Note: The Process Report Summary will also include messages when no rows are restored. This parameter affects only the return code.

YES

A warning message will be issued with RC=4 (default).

NO

No messages will be issued with RC=0.

EMPTY_TABLE_FOUND_RC4

Indicate the action taken and return code (RC) setting when empty tables have been detected during Restore batch processing.

YES

Empty tables will be processed and a warning message will be issued with RC=4.

NO

Empty tables will cause termination with an error and RC=12 (default).

RESTART

Indicate whether this is a restart or retry attempt for a RESTORE process that failed earlier. Optim automatically determines whether to perform a restart or a retry.

YES

Restart or retry RESTORE process.

NO

Do not attempt to restart or retry a RESTORE process (default).

Example

Specify the following to restore rows from the Archive File PSTARC.AF1, using Table Map PSTARC.TM1, and meeting the selection criteria for salesmen in the western territory who are over 21 years old. The following also specifies to use the named Control File and subset file, and to insert data rows.

RESTORE
   ARCHIVE_FILE PSTARC.AF1
   TABLE_MAP PSTARC.TM1
   CONTROL_NAME PSTARC.BTCHCNTL.FILE1
   SUBSET_NAME PSTARC.BTCHSUBS.FILE1
      SELECT (PSTARC.SALES, AND, AGE, GT, 21, TERRITORY, EQ, ‘West')
      REL_OVRD (TABLE PSTMJG.CUSTOMERS REL CUST Q2 YES)
   RESTORE_MODE INS