ARCHIVE

Use an ARCHIVE statement to create an Archive File. An Archive File is a sequential file that contains the selected set of related rows from one or more tables and the object definitions for those tables. The Archive File is used as input to the Archive Restore and Browse Processes. Many users can access the Archive File repeatedly and simultaneously. The ARCHIVE statement requires an Access Definition defined in the Optim™ Directory. You can use parameters to override parameters in the Access Definition and to provide parameters similar to those for the online Archive Process.

The ARCHIVE statement provides several capabilities not available in the online Archive Process. You can generate Archive File and Index File names dynamically, based on the date and time. This is useful for automated scheduling of Archive Processes. Also, you can override the index parameters specified in the Access Definition.

Despite the large number of parameters allowed on the ARCHIVE statement, you can archive data using only two parameters, ACCESS_DEFINITION and ARCHIVE_FILE. For example, specify the following to create an Archive File named PSTUSER.ARCHIVE.ORDERS, using the Access Definition PSTUSER.AD.ORDERS:

ARCHIVE
   ACCESS_DEFINITION PSTUSER.AD.ORDERS
   ARCHIVE_FILE (DSNAME PSTUSER.ARCHIVE.ORDERS)

Use the ARCHIVE statement syntax as follows:

ARCHIVE
  { ACCESS_DEFINITION_DEFINE (parameters) ; | 
ACCESS_DEFINITION group.user.name
      [ DEFAULT_CID cid ]
      [ UNKNOWN { FAIL | ALLOW } ]
      [ SELECT ( [ cid. ] table1, coloperator, column1
         { [, critoperator, criteria ] | [ , DELETE ] }
         [, column2 { [, critoperator, criteria ] | [ , DELETE ] } ... ] ) ]
      [ SELECT ( [ cid. ] table2, coloperator, column1
         { [, critoperator, criteria ] | [ , DELETE ] }
         [, column2 { [, critoperator, criteria ] | [ , DELETE ] } ... ] ) ] ...
      [ SQL ( [ cid. ] table1 [, [/correlation/ ] whereclause1 ] ) ]
      [ SQL ( [ cid. ] table2 [, [/correlation/ ] whereclause2 ] ) ] ...
      [ VAR ( varname, value ) ]
      [ DATE ( [ cid. ] table1,
         column1, { [Yn] | [Mn] | [Wn] | [D n] | Xyyyy-mm-dd }
         [, column2, { [Yn] | [Mn] | [Wn] | [Dn] | Xyyyy-mm-dd } ... ] ) ]
      [ DATE ( [ cid. ] table2,
         column1, { [Yn] | [Mn] | [Wn] | [Dn] | Xyyyy-mm-dd }
         [, column2, { [Yn] | [Mn] | [Wn] | [D n] | Xyyyy-mm-dd } ... ] ) ] ...
      [ POINT_SHOOT_DSN explicitfilename ]
      [ POINT_SHOOT_ERROR { STOP | CONTINUE | SUBSET } ]
      [ OBJECT_DEFS { YES | NO } ]
      [ INCLUDE_OBJECT (obj1,obj2,...) ]
      [ EXCLUDE_OBJECT (obj1,obj2,...) ]      
      [ ROW_SELECT_BY { ROWLIST | BOTH } ] }
  ARCHIVE_FILE ( File Allocation Parameters )
  [ DROP_INDEX_COLUMN ( [ cid. ] table, column1 [,
  column2, ... ] ) ]
  [ DROP_INDEX_TABLE ( [ cid. ] table1 [, [ cid. ] table2,... ] ) ]
  [ DROP_INDEX_ALL { YES | NO } ]
  [ SPARSE_INDEX ( [ cid. ] table, column1 [, column2, ...] ) ]
  [ DENSE_INDEX ( [ cid. ] table, column1 [, column2, ... ] ) ]
      [ INDEX_FILE ( File Allocation Parameters )
      [ DUPE_INDEX_FILE (  File Allocation Parameters ) ] ]
  [ UNLOAD_UTILITY { BMC | IBM | CDB | CDBO } ]
      [ IMAGE_COPY 
         ( MODE { L | A | B | D }
         [ DATE yyyy-mm-dd ] 
         [ TIME hh.mm.ss ]  
         [ DSNAME dsname ] ) ]
      [ PARTITIONS ( part1 [, part2, ... ] ) 
         [ OTHER_PARTITIONS_SAME { YES | NO }] ]
  [ DELETE_DEFER { YES | NO } ]
      [ CONTROL_FILE ( File Allocation Parameters ) ]
      [ DELETE_LOCK { YES | NO } ]
      [ DELETE_COMMIT_ROWS n ]
      [ DELETE_COMMIT_MINUTES n ]
      [ DELETE_DISCARD n ]
      [ ACTION_IF_NO_ROWS { WARNING | ERROR }
      [ RESTART { YES | NO } ]
      [ COMPARE_ROW { YES | NO } ]
      [ DEFAULT_KEY_LIMIT n ]
      [ ACCESS_METHOD ( cid.tablename, { K | S | E }, key limit ) ]
      [ DAA_TABLE ( tablename1, { YES | NO } ) ]
      [ DAA_TABLE ( tablename2, { YES | NO } ) ]
  [ SECURITY_STATUS { PUBLIC | READONLY | PRIVATE } ]
  [ GROUP group ]
  [ DESC desc ]
  [ ROW_LIMIT n ]
  [ RETENTION_PERIOD { NOLIMIT | nD | nY | yyyy-mm-dd | yyyy.ddd | PERM | NEVER } ]
  [ DUPE_ARCHIVE_FILE ( File Allocation Parameters ) ]
  [ WITH_UR { YES | NO } ]
  [ ACTION_COMMIT n ]
  [ COLLECTION (cid.collectionname [, cid.collectionname2, ... ] ) ]
  [ REPLACE_ARC_DIR { YES | NO } ]
  [ SKIP_ARC_CATALOG { YES | NO | DUP } ]
  [ REPORT_LEVEL { DETAIL | SUMMARY } ]
  [ CENTERA_FILE { YES | NO }
      [ CENTERA_VALIDATE { START | DEFER } ] ]
  [ TIVOLI_FILE { YES | NO }
      [ TIVOLI_VALIDATE { START | DEFER } ] ]
  [ NOT_FOUND_RC4 { YES | NO } ]
  [ EMPTY_TABLE_FOUND_RC4 { YES | NO } ]
  [ DELETE_FAILED_ROWS_RC4 { YES | NO } ]
  [ DELETE_0_ROWS_RC4 { YES | NO } ]

Access Definition parameters

Use the following parameters to identify the Access Definition and override or augment various parameters in the Access Definition.

ACCESS_DEFINITION

The name of the Access Definition. ACCESS_DEFINITION must be included in the ARCHIVE statement and must precede any parameters that override parameters in the Access Definition (e.g., SELECT, SQL, DEFAULT_CID).

group.user.name: The three-part Access Definition name.

ACCESS_DEFINITION_DEFINE

The Optim online process generates this parameter when it creates an Archive job for batch execution.

When you create an Archive job outside of the Optim online process, the best practice is to use the ACCESS_DEFINITION parameter to refer to a named Access Definition in the Optim Directory.

The ACCESS_DEFINITION_DEFINE parameters are within parentheses and a semicolon must follow the close parenthesis. (See ACCESS_DEFINITION_DEFINE Parameters for the allowable parameters.)

Note: The ADNAME parameter is generated when you specify a named Access Definition in the online process. This parameter is for documentation purposes only.

DEFAULT_CID

Override for the default Creator ID specified in the Access Definition. When used, this parameter must precede any parameters that require a default Creator ID (e.g., SELECT, DROP_INDEX_TABLE).

cid: The default Creator ID.

UNKNOWN

The action to be taken if the Access Definition references unknown tables or relationships.

FAIL: Terminate processing if any tables or relationships named in the Access Definition are unknown (default).

ALLOW: Bypass unknown tables and relationships and continue processing.

– Selection Criteria

SELECT

Selection criteria for rows in a table. Specify critoperator and criteria to augment Access Definition selection criteria (according to coloperator) or to override any existing Access Definition selection criteria for the specified column. Use DELETE, instead of critoperator and criteria, to bypass existing Access Definition selection criteria for the specified column.

cid.table: The table name. If you omit the Creator ID, the default Creator ID is used.

coloperator

The operator used to combine selection criteria for multiple columns in a table. You must specify one of the following:

AND: Select data that matches criteria for all columns.

OR: Select data that matches criteria for at least one column.

column: The name of the column. You must specify at least one column operand.

critoperator: Operator for criteria. (See the 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.

Separate critoperator and criteria 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

DELETE: Bypass existing selection criteria in the Access Definition for the column. If you use DELETE, you must omit critoperator and criteria for the column.

SQL

An SQL WHERE clause for the specified table. Override any SQL WHERE clause specified in the Access Definition. (Omit whereclause to bypass an Access Definition SQL WHERE clause for the specified table.) You can specify multiple SQL statements, but duplicate table names cause an error.

cid.table: The table name. If you omit the Creator ID, the default Creator ID is used.

/correlation/: The optional correlation name must be enclosed in slashes and must immediately precede whereclause. Overrides any correlation name specified in the Access Definition.

whereclause: Lines of criteria as an SQL WHERE clause. Break each line at a blank, start anywhere on the next line, do not use quotes or parentheses, and end the clause with a 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.

VAR

Override for the default value of a substitution variable assigned in the Access Definition. If a default value was not defined in the Access Definition, you must use the VAR parameter to specify a value, or an error occurs.

varname: The name of the substitution variable assigned in the Access Definition. A colon (:) before VarName is optional.

value: The value for the substitution variable. You must enclose the value in single quotes if the variable is for a CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, BINARY, VARBINARY, DATE, TIME, TIMESTAMP, or TIMESTAMP WITH TIME ZONE column.
Note: If you specify a column name for the default value, do not enclose the value in quotes.

DATE

Override for existing archive date criteria for the specified column in a table. (If the Access Definition does not include date criteria for the specified column, an error occurs.)

cid.table: The table name. If you omit the Creator ID, the default Creator ID is used.

column: The name of the column. You must specify at least one column operand, with date criteria.

Yn, Mn, Wn, Dn, or Xyyyy-mm-dd: The cut-off date for archived data. Rows with an earlier date are selected for archiving. Specify an explicit date (in yyyy-mm-dd format), or the number of years, months, weeks, or days to subtract from the current date.

POINT_SHOOT_DSN

The name of a Point-and-Shoot file used to select data to be archived. Overrides any Point-and-Shoot file specified in the Access Definition

explicitfilename: The fully qualified name of the Point-and-Shoot File.
Note: You can also specify a Point-and-Shoot file by placing a PSDFPNS DD * data set in the job stream, containing key values to be processed. In this case, omit the POINT_SHOOT_DSN parameter.

POINT_SHOOT_ERROR

Indicate action to be taken when processing the Point-and-Shoot file results in errors.

STOP: Stop the run if the Point-and-Shoot file contains invalid keys or is empty. This is the default.

CONTINUE: Continue the run if invalid keys are found, but do not use the Point-and-Shoot file.

SUBSET: If invalid keys are found, continue the run with the valid keys only. If errors other than invalid keys are found, stop the run.

– Object Definitions

OBJECT_DEFS

Indicate whether to include object definitions (e.g., primary keys, relationships, indexes, views, synonyms, aliases, procedures, triggers, and user-defined types and functions).

YES: Include object definitions of all types (default).

NO: Do not include object definitions.

INCLUDE_OBJECT

Include specific object types. The OBJECT_DEFS parameter must be NO. Specify any of the following values in any order within parentheses:

PKREL: Primary keys and relationships

INDEX: Indexes

VIEW: Views

ALIAS: Aliases

SYN: Synonyms

FPROC: Column Field Procedure Names

TRIG: Triggers

SPROC: Stored Procedures

UDEF: User Defined Types and Functions

EXCLUDE_OBJECT

Exclude specific object types. The OBJECT_DEFS parameter must be YES. Specify any of the following values in any order within parentheses:

PKREL: Primary keys and relationships

INDEX: Indexes

VIEW: Views

ALIAS: Aliases

SYN: Synonyms

FPROC: Column Field Procedure Names

TRIG: Triggers

SPROC: Stored Procedures

UDEF: User Defined Types and Functions

ROW_SELECT_BY

Indicate whether selection criteria or Point-and-Shoot list should be used when Access Definition specifies both for the Start Table.

ROWLIST: Apply Point-and-Shoot list only (default).

BOTH: Apply Point-and-Shoot list and selection criteria.

Archive File parameter

ARCHIVE_FILE: Use the parameters in File Allocation Parameters to name and allocate the Archive File.

Index parameters

Use the following parameters to override index specifications in the Access Definition.

DROP_INDEX_COLUMN

Drop the index for one or more columns in a table in the Access Definition. (If the Access Definition does not have an index for the specified column, an error occurs.) You can specify multiple DROP_INDEX_COLUMN parameters, but duplicate column names cause an error.

cid.table: The name of the table. If you omit the Creator ID, the default Creator ID is used.

column: The name of one or more columns with an index to be dropped.

DROP_INDEX_TABLE

Drop all indexes for the specified tables in the Access Definition. You can specify multiple DROP_INDEX_TABLE parameters, but duplicate table names cause an error.

cid.table: The name of one or more tables with indexes to be dropped. If you omit the Creator ID, the default Creator ID is used.

DROP_INDEX_ALL

Drop all indexes for all tables in the Access Definition.

YES: Drop all indexes.

NO: Do not drop all indexes (default).

SPARSE_INDEX

Create a sparse index for one or more columns in the specified table. If the Access Definition includes a sparse index for the specified column, an error occurs unless the index has been dropped using a prior DROP_INDEX parameter.

You can use multiple SPARSE_INDEX parameters, but duplicate column names cause an error. You can specify a sparse index for a column that has a dense index.

cid.table: The name of the table. If you omit the Creator ID, the default Creator ID is used.

column: The name of one or more columns to be indexed.

DENSE_INDEX

Create a dense index for one or more columns in the specified table. If the Access Definition defines a dense index for the specified column, an error occurs unless the index has been dropped using a prior DROP_INDEX parameter.

You can use multiple DENSE_INDEX parameters, but duplicate column names cause an error. You can create a dense index for a column that also has a sparse index.

cid.table: The name of the table. If you omit the Creator ID, the default Creator ID is used.

column: The name of one or more columns to be indexed.

INDEX_FILE

Create an Index File for dense indexes. If omitted, and a dense index is to be created, an error occurs. Use the parameters shown in File Allocation Parameters.

DUPE_INDEX_FILE

Name and allocate a duplicate Index File. This parameter is allowed only if the INDEX_FILE parameter has been specified. Use the parameters shown in File Allocation Parameters.

Unload Utility parameters

Use the following parameters to unload data:

UNLOAD_UTILITY

The name of the unload utility used to retrieve the data. If omitted, DB2 is used to retrieve the data. This parameter is required to extract data from an image copy.

BMC: Use the BMC UNLOAD PLUS utility.

IBM: Use the High Performance Unload utility.

CDB: Use the CDB Auto-Unload utility.

CDBO: Use the CDB Auto-Online Unload utility.

Note: You must add the appropriate utility DD statements. Refer to the utility documentation for the required DD statements.

IMAGE_COPY

The image copy file to use as input. If omitted, DB2 files are used as input. This parameter is allowed only if the UNLOAD_UTILITY parameter is specified.

IMAGE_COPY parameters must be enclosed in parentheses. MODE is required; other parameters are optional, depending on the value of MODE.

Note: To archive data from DB2 image copy data sets in multiple partitions of the same tablespace stored on the same tape volume, you must manually edit the JCL to allocate the data sets. Multiple image copy data sets cataloged on the same tape volume can not be allocated using dynamic allocation. This is a z/OS limitation. If you attempt to use dynamic allocation, the archive process fails with a dynamic allocation error. Refer to File Allocation Parameters for details.

MODE

The image copy file to use:

L: Use the latest file.

A: Use the first file created on or after the specified DATE and TIME.

B: Use the first file created on or before the specified DATE and TIME.

D: Use the file named in DSNAME.

DATE

The date criteria for the image copy file in this format: yyyy-mm-dd.

TIME

The time criteria for the image copy file in this format: hh.mm.ss.If omitted, a value of 00.00.01 is assumed

DSNAME

The fully qualified name of the image copy file.

PARTITIONS

The numbers of the partitions to be processed. If omitted, all partitions are processed. This parameter applies only if the UNLOAD_UTILITY parameter is specified and the Start Table is partitioned.

part

The partition numbers, enclosed in parentheses and separated by commas.

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.

You may specify the partitions in any order. (Partitions that are not specified are not archived.)

OTHER_PARTITIONS_SAME

Use this operand to indicate whether to process tables in all partitions or only the tables in the partitions specified in the PARTITIONS operand for the Start Table.

YES: Limit processing to the tables in the partitions specified in the PARTITIONS operand for the Start Table. This assumes that the rows selected from related tables are physically located in the same partitions as the rows selected from the Start Table.

NO: Do not limit processing to the tables in partitions specified in the PARTITIONS operand for the Start Table. If needed, process tables in additional partitions to select rows from related tables. This is the default.

Delete After Archive parameters

Use the following parameters to provide processing options for the delete after archive operation. Note that these parameters apply only if the Access Definition contains delete after archive specifications.

DELETE_DEFER

Indicate whether the Delete Process should be deferred.

YES: Defer the Delete Process. All other delete after archive parameters are ignored and you must use the DEFERRED_DELETE statement to perform the Delete Process. See DEFERRED_DELETE for more information.

NO: Do not defer the Delete Process (default).

CONTROL_FILE

Name of the Control File and its allocation parameters. This parameter is required to execute the Delete Process. Use the parameters shown in File Allocation Parameters.

DELETE_LOCK

Indicate whether to lock tables during the Delete Process. Locking tables ensures other database activity does not interfere with the Delete Process. However, it will prevent other users from accessing the table.

YES: Lock tables. (This setting causes an error if site options prevent a user from locking tables.)

NO: Do not lock tables (default).

DELETE_COMMIT_ROWS

The frequency of commits in rows for the Delete Process. The commit points affect the starting point in case of a Restart. Frequent commits keep page locks to a minimum. If you omit both DELETE_COMMIT_ROWS and DELETE_COMMIT_MINUTES, the site limit is used.

n: Commit each n number of rows where n is a value from 1 through the Site Options Commit Frequency Rate, inclusive.
Note: If DELETE_LOCK is set to YES, DELETE_COMMIT_ROWS and DELETE_COMMIT_MINUTES are ignored. A commit is performed as processing for each table is completed.

DELETE_COMMIT_MINUTES

The frequency of commits in minutes for the Delete Process. If you omit DELETE_COMMIT_MINUTES, DELETE_COMMIT_ROWS determines the commit frequency.

n: Commit every n minutes, where n is 1 through 1440.

DELETE_DISCARD

The maximum number of rows that can be discarded when deleting rows, before terminating the process. If the limit is met, the process is terminated. You can use Restart to begin the Delete Process again at the termination point

n: Specify a value in the range 1 to 4,294,967,295.

blank: No limit on discarded rows.

ACTION_IF_NO_ROWS

The action to be taken if no rows are deleted from the database. That is, should the utility generate a warning or an error condition?

WARNING: Treat no rows being deleted as a warning condition. The utility issues a warning message and continues processing (default).

ERROR: Treat no rows being deleted as an error condition.

RESTART

Indicate whether to restart or retry the delete portion of an Archive Process if a processing failure occurs. Optim automatically determines whether to perform a restart or a retry.

YES: Restart or retry the delete, and, optionally, respecify any DELETE parameters.

NO: Do not attempt to restart or retry the delete (default).

Note: You can also restart or retry the process by adding the RESTART operand to the PARM field on the batch EXEC statement. Use a blank to separate the RESTART operand from the previous operand in the PARM field.

COMPARE_ROW

Indicate whether the Delete Process compares rows of data in the Archive File with rows in the database prior to deletion.

YES: Rows are deleted from the database only if they exactly match rows in the Archive File. Rows that do not exactly match are discarded and noted in the Control File. (This setting causes an error if the Compare Row Contents site option is not set to USER.)

NO: Row comparison is not performed (default). This may improve performance significantly; however, you risk losing any updates to the data in the database since the Archive Process was performed.

DEFAULT_KEY_LIMIT

Specify the default maximum number of keys to be processed at one time when deleting rows for any table in the Archive File. Applies when processing a table for which a key limit is not explicitly defined in the Access Definition or using the ACCESS_METHOD parameter.

n

1 - 100 (default is 1)

The following conditions must be true to process multiple keys at one time when deleting rows:

An index on the primary key is defined for the table.
COMPARE_ROW parameter is NO.
Row-level Archive Actions are not defined for the Delete Process (e.g., Before Delete of Row).
The table is not a parent in a DBMS relationship.

ACCESS_METHOD

Indicate how to access rows to be deleted from tables in the Archive File. You may specify this parameter once for each table to be processed. If you omit it for a table, Optim determines how to access the rows in that table. You must specify the parameters within parentheses, separated by commas, and in the following order.

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.

cid.tablename: The table name

K: Use key lookup

S: Use table scan

E: Optim determines the access method.

key limit: Maximum number of keys to be specified in an SQL statement when the access method is K. Specify a number between 1 and 100. You may omit this parameter if the access method is S.
Note: If you used the online process to generate the Archive batch job, you must use the ACCESS_METHOD parameter to override the default access method (that is, the Optim-determined access method).

DAA_TABLE

Indicates whether data from a table in the Access Definition is to be deleted. DAA_TABLE overrides any value from the Access Definition. If a DAA_TABLE value is not specified, the DAA value for each table is taken from the Access Definition. Multiple parameters are permitted as long as the table name is not repeated.

If you specify DAA_TABLE with the DELETE_DEFER parameter, DAA_TABLE will apply to the subsequent DEFERRED_DELETE statement unless you override it with another DAA_TABLE parameter in the DEFERRED_DELETE statement.

tablename: Name of the table.

YES: Delete the table data.

NO: Do not delete the table data.

Processing parameters

Use the following parameters to specify processing options for the Archive File.

SECURITY_STATUS

Security status to be assigned to the Archive File. If archive security is not currently activated, the assigned status becomes effective when security is activated for the site. Omit this parameter to use the site default for archive security (or PUBLIC, if the site default is not specified).

PUBLIC: All can use and modify.

READONLY: All can use, but only the Archive Administrator or the owner can modify.

PRIVATE: Only the Archive Administrator or the owner can use and modify.

GROUP

Group designation for the Archive File.

group: Up to 8-character group designation, enclosed in single or double quotes if using blanks or special characters.

DESC

Description of the Archive File.

desc: Up to 40-character description, enclosed in single or double quotes.

ROW_LIMIT

The maximum number of rows that can be archived. Specify a value in the range 1 to 4,294,967,295. If omitted, the site limit is used.

n: The maximum number of archived rows.

RETENTION_PERIOD

The retention period for the Archive File. You cannot delete or overwrite the Archive File, its associated Archive Directory entry, or any associated Archive Index File until after the specified retention period.

This parameter is valid only when the Site Option, Specify Arc File Retention, is set to User. If omitted, when the Site Option allows user specification, no retention period is assigned.

NOLIMIT: No retention period is assigned.

nD: The number of days to retain the Archive File.

nY: The number of years to retain the Archive File.

yyyy-mm-dd, yyyy/mm/dd, or yyyy.ddd: An explicit date, after which you can delete or overwrite the Archive File. Enter a year from 1900-2155. For ddd values, enter a day from 000-366.

PERM, NEVER, 1999.365, 1999.366, 1999/12/31, or 99/12/31: The retention period does not expire.
Note: Any Archive File assigned a retention period over 9999 days or 27 years is considered permanent, and can only be deleted or overwritten if you reduce the retention period using the ALTER statement of the IBM^® Utility program, IDCAMS.

Duplicate Archive File parameter

DUPE_ARCHIVE_FILE: Name and allocate a duplicate Archive File. Use the parameters shown in File Allocation Parameters. DUPE_ARCHIVE_FILE has the same parameters and operands as ARCHIVE_FILE. However, you can omit the TAPE parameter, since the duplicate Archive File TAPE value is automatically set to the value for the Archive File.

– Miscellaneous Options

WITH_UR

Indicate whether to archive uncommitted data from the database. This parameter is valid only when the Site Option, Use Uncommitted Reads, is set to User. Specify:

YES: Archive uncommitted data from the database.
Note: If you choose to archive uncommitted data, the relational integrity of the data in the Archive File may be compromised. Use caution if restoring data in any Archive File with uncommitted data.

NO: Do not archive uncommitted data from the database. This is the default.

ACTION_COMMIT

The commit interval for a table for which archive actions are specified. If omitted, commits are not performed for archive actions.

n: The number of archive actions executed between commit operations. Specify a value in the range 1 - 4,294,967,295.

COLLECTION

If appropriate, specify the name of the Archive Collection(s) to which the Archive File should be added. Each collection is identified by a Collection ID of up to eight characters, and a Collection Name of up to 12 characters, such as CID1.COLLECTION. You can specify multiple collections, but duplicate names cause an error.

cid: The Collection ID.

collectionname: The Collection Name.

REPLACE_ARC_DIR

The action taken if an Archive Directory entry already exists for the specified Archive File and the duplicate Archive File, if one is requested.

YES: Replace the existing entry.

NO: Stop the Archive process (default).

SKIP_ARC_CATALOG

Indicate whether to create an Archive Directory entry for the Archive File and the duplicate Archive File, if one is requested.

YES: Skip creating an entry for the Archive File or the duplicate Archive File

NO: Create an entry for the Archive File and the duplicate Archive File, if one is specified (default).

DUP: Skip creating an entry for the duplicate Archive File.

REPORT_LEVEL

The level of detail provided in the Process Report.

DETAIL: Produce a detailed report (default).

SUMMARY: Produce a summary report.

– Centera Options

CENTERA_FILE

Indicate whether to create a Centera file.

YES: Create a Centera file.
Note: If you enter YES and a CENTERA_OPTIONS statement has been specified, those options will override Centera Site Options specifications.

NO: Do not create a Centera file (default).

CENTERA_VALIDATE

Indicate when the Centera Pool is validated.

START: The Centera Pool is validated before data is archived (default). If the pool is invalid, the Archive Process is terminated.

DEFER: The Centera Pool is validated after the data is archived.
If the pool is invalid, the Archive File can be copied to Centera using the Archive Files panel. For more information about copying files to Centera using the Archive Files panel, see the Archive User Manual.

– Tivoli^® Options

TIVOLI_FILE

Indicate whether to create a Tivoli file.

YES: Create a Tivoli file.
Note: If you enter YES and a TIVOLI_OPTIONS statement has been specified, those options will override Tivoli Site Options specifications.

NO: Do not create a Tivoli file (default).

TIVOLI_VALIDATE

Indicate when the Tivoli Alias is validated.

START: The Tivoli Alias is validated before data is archived (default). If the alias is invalid, the Archive Process is terminated.

DEFER: The Tivoli Alias is validated after the data is archived.
If the alias is invalid, the Archive File can be copied to Tivoli using the Archive Files panel. For more information about copying files to Tivoli using the Archive Files panel, see the Archive User Manual.

NOT_FOUND_RC4

Indicate the action taken and return code (RC) setting when rows have not been found during Delete batch processing.

Note: If the source file has no rows, Optim ignores this parameter and issues a warning and RC=8 or RC=4, depending on the specification for ACTION_IF_NO_ROWS.

YES: A warning message will be issued and the program will end with RC=4 (default).

NO

The program will end with RC=0.

EMPTY_TABLE_FOUND_RC4

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

Note: If the source file has no rows, Optim ignores this parameter and issues a warning and RC=8 or RC=4, depending on the specification for ACTION_IF_NO_ROWS.

YES: Empty tables will be processed, a warning message will be issued, and processing will end with RC=4.

NO: Empty tables will be ignored and processing will end with RC=0 (default).

DELETE_FAILED_ROWS_RC4

Indicate the action taken and return code (RC) setting when some rows of a table have failed to be deleted because a condition existed that prevented it (for example, a violation of Referential Integrity would occur).

YES: A warning message will be issued and processing will end with RC=4.

NO: Processing will end with RC=0 (default).

DELETE_0_ROWS_RC4

Indicate the action taken and return code (RC) setting during Delete batch processing when a table has rows in an Archive File but no rows have been deleted. Possible reasons include

The rows cannot be deleted because there are no matching rows in DB2 tables
A condition existed that prevented it (for example, a violation of Referential Integrity would occur).

YES: A warning message will be issued and processing will end with RC=4.

NO: Processing will end with RC=0 (default).

Example

Use the following statement to create an Archive File named OPTUSER.ARCHIVE.CUST, using the OPTUSER.AD.CUSTOMERS Access Definition. This example also uses selection criteria, executes the UNLOAD PLUS utility, and provides a description for the Archive File.

ARCHIVE
   ACCESS_DEFINITION  OPTUSER.AD.CUSTOMERS
   ARCHIVE_FILE (DSNAME OPTUSER.ARCHIVE.CUST,
      MODE  REP, TAPE YES)
   SELECT (OPTUSER.CUSTOMERS, AND, AGE, GR, 21, AREA,
      EQ, ‘WEST')
   UNLOAD_UTILITY BMC
   DESC "WESTERN CUSTOMERS OVER 21 YEARS"