Define the Data to Archive

After specifying the Archive Process parameters, you must provide an Access Definition.

The Access Definition references the tables that contain the data, provides selection criteria for the data, prescribes the way that relationships between tables are traversed, and provides other specifications such as index parameters and Archive Actions (user‑defined SQL statements, including calls to stored procedures) to be executed during processing. In other words, an Access Definition defines the data you want to archive and provides table-specific parameters for processing.

For this scenario, you will create an Access Definition that is Local, or exclusive to the current Archive Request. You will:

Open the Access Definition Editor

The first step in the scenario is to select the tables that contain the data that you want to archive. You can select these tables by using the Access Definition Editor.

To open the Access Definition Editor, click Tools > Edit Access Definition in the Archive Request Editor. The Access Definition Editor is displayed with Tables as the active tab.

Access Definition Editor with Tables as the active tab

You use the Access Definition Editor to create and edit Access Definitions. In the Description box, you can enter the purpose or function of the Access Definition. For this scenario, type Archive orders older than two years.

You will use the Tables tab to list the names of tables that contain the desired data and to define specifications for the data in the listed tables.

Select the Tables

After you open the Access Definition Editor, the next step in the scenario is to select the tables that contain data that you want to archive.

In this scenario, you will archive information for orders more than two years old. The ORDERS, CUSTOMERS, DETAILS, and ITEMS tables contain the desired data. Use the options on the Tables tab to select the tables.

Default Qualifier

The Default Qualifier saves time when you enter table names in the grid, or Table List. A fully qualified table name has three parts: Dbalias.Creatorid.Tablename. Dbalias is a user-defined name that provides parameters needed to connect to the database. Creatorid qualifies a table name and often reflects the user who created it. Tablename is the base table name.

You can use the Default Qualifier to provide one or both parts for you — generally, use a Default Qualifier that applies to most or all tables in the Table List. To prefix the table names in the Access Definition in this scenario, enter the Dbalias and Creatorid that correspond to the sample database tables.

Start Table

Each Archive Process begins with a Start Table — the table from which data is first archived — and proceeds to obtain related data from the remaining tables in the Table List. Since you want to archive a specific set of data related to orders more than two years old, as determined by dates in a column in the ORDERS table, you must specify ORDERS as the Start Table. Archive automatically places ‘ORDERS' in the first line of the Table List.

If you do not specify a Start Table, the first name entered in the Table List is the assumed Start Table. You can change the Start Table by typing a new name or selecting from the Start Table drop-down list, or right-clicking a name on the Table List and selecting Set as Start from the shortcut menu.

Table List

The Table List references tables from which data is archived. When you add a table name to the Table List, Archive automatically displays the type of object referenced by the table name and the name of the DBMS for the table.

You can use two methods to add table names to the list. One method is to type names in the Table/View column. An easier method, however, may be to let Archive find the tables that contain related data. Using the Select Table(s) dialog, you can direct Archive to list tables related to the Start Table. You can review the list and select tables you want to include in the Archive Process.

Select Tables Dialog

To display the Select Table(s) dialog, right-click the ORDERS table name in the Table List and select Add Tables from the shortcut menu. The dialog provides a list of tables, based on the Default Qualifier.

To limit the list to tables related to the ORDERS table, use the options in the Find Tables Related to Table area. Select the Find Tables Related to Table check box, ensure default settings Both and All Levels are selected, and click Display.

Select Table(s) dialog
Note: The default settings ensure that Archive retrieves all related tables (children, parents, grandparents, grandchildren, and so on) for the entire data model.

Locate the following tables in the list: CUSTOMERS, DETAILS, and ITEMS. Select each table while pressing the Ctrl key and click Select to add the tables to the Table List in the Access Definition.

Choose the Data to Delete

After you select the tables that you want to archive, the next step in the scenario is to specify whether you want to delete rows that are archived by the Archive Process.

In the Archive Process, you copy selected data to an Archive File. You can delete archived data from all or selected database tables during the Archive Process or you can defer the delete.

Deferring the deletion of archived data gives you the opportunity to verify the data after the Archive Process is complete. For example, you can browse the Archive File to ensure the correct data is archived before using the Delete Process to delete the archived data from the database.

Regardless of whether you delete during the Archive Process or defer the delete, you must indicate the data intended for deletion. That is, the database tables from which archived rows are to be deleted. You can identify the tables in the Access Definition or in the Archive Delete List on the Delete After Archive Specifications dialog.

For this scenario, you will streamline your database by deleting archived rows from the ORDERS and DETAILS tables. You intend to retain the archived rows in the CUSTOMERS and ITEMS tables, because this data (names, addresses, and information about each product in inventory) is pertinent to recent and future orders.

To delete the selected rows from each table, select the Delete Rows After Archive check boxes for ORDERS and DETAILS.

Database Table grid on Access Definition Editor. The Delete Rows After Archive check boxes are selected for the ORDERS and DETAILS tables.

When defining the Archive Process parameters earlier in this scenario, you chose to delete rows as part of the Archive Process. After you run the Archive Process, Archive will remove archived rows from the ORDERS and DETAILS tables.

Understand Table Specifications

Table specifications provide display and criteria parameters for data. You can provide specifications for any table in the Table List by right‑clicking the table name and selecting a Table Specifications option from the shortcut menu.

A brief description of each option follows:

Columns
Select columns to be included and excluded from a Point and Shoot display. You can use the Point and Shoot facility to visually select specific rows from the Start Table.
Selection Criteria
Specify criteria to select rows according to values in one or more columns in a table.
SQL
Create an SQL Where clause for complex selection criteria, for example, criteria that requires a combination of OR and AND logical operators.
Sort
Arrange Start Table rows in a Point and Shoot display according to values in one or more columns.
Archive Actions
Define user-written SQL statements, including calls to stored procedures, to be executed at predefined points in an Archive or Restore Process.
Archive Index
Establish index parameters for a column (or set of columns).
File Attachments
Provide parameters needed to archive a file referenced within or associated with a row of extracted data and include it in an Archive File.

In this scenario, you will use table specifications to specify selection criteria for the data you want to archive and to establish an Archive Index. The next sections demonstrate how to accomplish these tasks.

Specify Selection Criteria for the Data

The next step in the scenario is to select the data that you want to archive using selection criteria.

Selection criteria allow you to pinpoint the data you want to archive. You can select data according to values in one or more columns. Selection criteria must conform to SQL syntax and include relational or logical operators.

For example, you can select data for archiving by:

  • Age — determined by values in one or more DATE columns.
  • Values in one or more columns — such as an ‘inactive' indicator.
  • Manually selecting rows in the Start Table using Point and Shoot.

In the following examples, the operators and syntax may not be valid for all database management systems. However, the functions demonstrated are universal.

Example 1

To archive data for all customers with names beginning with the letter A, from Pennsylvania, you can specify:

CUSTNAME
LIKE ‘A%'
STATE
= ‘PA'

Example 2

To archive data for an item that is no longer sold to customers (for example, The Man Who Would be King), you can specify:

ITEM_ID
= ‘AD013'

Select Rows on the Basis of Date

In this scenario, you will archive information for orders more than two years old. You can define selection criteria for the data based on values in a DATE column — particularly, the value in the ORDER_DATE column in the ORDERS table. To accomplish the task, you can use a unique operator provided by Archive.

The syntax for the operator is: BEFORE (nD, nW, nM, nY)

The D, W, M, and Y arguments, in any combination, indicate the number of days, weeks, months, or years subtracted from the current date at runtime. Rows with a date older than the calculated date are archived.

The BEFORE operator allows you to specify generic criteria for an Archive Request, which can be scheduled to run repeatedly. For example, if you wish to retain data for no more than 5 years, you can specify:

BEFORE(5Y)

as selection criteria for an Archive Request and schedule the Archive Process to run automatically every quarter, without having to change the selection criteria for each run.

In the Table List, right-click the ORDERS table name. On the shortcut menu, click Table Specifications > Selection Criteria. The Table Specifications dialog is displayed with Selection Criteria as the active tab.

Table Specifications dialog with Selection Criteria as the active tab

In the Selection Criteria column, type BEFORE(2Y) as selection criteria for ORDER_DATE. To return to the Table List, click File > Close or click the Close button in the title bar. Note the presence of the Selection Criteria icon in the Table List.

Define an Archive Index

After you select the data that you want to archive using selection criteria, the next step in the scenario is to establish Archive Indexes for your archived tables.

You can establish one or more Archive Indexes for each archived table. An Archive Index consists of values in one or more Index Columns. Archive Index information is stored in an Archive Index File, which Archive can search more quickly than it can search an entire Archive File. When you browse or restore data, Archive will determine if it can use an index to expedite the search.

Using the Archive Index tab in the Table Specifications dialog, you can create as many as 16 Archive Indexes for each table in the Archive File. In this scenario, you will create one Archive Index of values for CUST_ID in the CUSTOMERS table, since you anticipate searching Archive Files based on values for customer ID.

In the Table List, right-click the CUSTOMERS table name. On the shortcut menu, click Table Specifications > Archive Index. The Table Specifications dialog is displayed with Archive Index as the active tab.

Table Specifications dialog with Archive Index as the active tab
Note: Initially, the name of the table for which you opened the shortcut menu is displayed. To edit specifications for another table in the Table List, you can select the name from the Table drop-down list.

Index names are generated by default, in the form Indexn, where n is a sequential number that provides a unique name for each index. For this scenario, use the default index name.

Available Columns lists columns in the selected table that you can use as Index Columns. Double-click CUST_ID to add the column name to the Index Columns list. The column is defined as an Index Column. You can also drag a column name from Available Columns to Index Columns.

Note: If the index consists of more than one Index Column, you can change the order of the columns in the index by dragging a column name to the desired position. The order determines the sequence in which the column values are searched.

When finished, click File > Close to save the Archive Index specifications and return to the Access Definition Editor. Note the presence of the Archive Index icon in the Table List. The Archive Index created in the Archive Process will be used later in this scenario.

Review Table Specifications

After you establish Archive Indexes for your archived tables, the next step in the scenario is to review the table specifications to ensure that they are correct.

Icons on the Table List indicate the table specifications used in this scenario.

Access Definition Editor with Tables as the active tab. Icons display for the ORDERS and CUSTOMERS tables in the grid under Table Specifications.

Selection Criteria icon The Selection Criteria icon indicates that selection criteria is specified for the ORDERS table.

Archive Index icon The Archive Index icon indicates that an Archive Index is defined for the CUSTOMERS table.

You can click an icon to quickly display the table specifications on the corresponding tab in the Table Specifications dialog.

Define the Traversal Path

After you review the table specifications, the next step in the scenario is to define the traversal path. That is, you select the relationships to be used and the direction in which the relationships are traversed — from parent to child, from child to parent, or in both directions — during the Archive Process.

The traversal path determines the sequence in which Archive selects data from tables referenced in the Access Definition. Consider the database structure of the tables you selected for archiving. Diagram of traversal path between the CUSTOMERS, ORDERS, DETAILS, and ITEMS tables. The relationships are described as follows.

In the diagram and in the following discussion, the relationships between tables are indicated by a three-letter code: the letter "R", the first letter in the name of the parent table, and the first letter in the name of the child table.

ORDERS is the Start Table, because you want to archive a specific set of data related to ORDERS more than two years old. Based on the relationships between each pair of tables:

  • CUSTOMERS is a parent of ORDERS (relationship RCO).
  • ORDERS and ITEMS are parents of DETAILS (relationships ROD and RID).

To obtain the desired set of data for this scenario, you will include relationships RCO, ROD, and RID in the Archive Process and specify the direction in which Archive traverses them.

You use the options on the Relationships tab to define the traversal path for selecting data from the tables referenced in the Access Definition.

Access Definition Editor with Relationships as the active tab

All relationships between pairs of tables in the Table List are displayed. The parent table and the corresponding child table in each relationship is provided.

Review Status

Each listed relationship has a status, which indicates:

New
Relationship is listed for the first time. The status of a relationship is New the first time the list is displayed after:
  • A new relationship between tables on the list is defined.
  • A table is added to the list.

(Once you have viewed the relationship, the status is no longer new, which is indicated by a blank status the next time you display the Relationships tab.)

When you run the Archive Process, a warning message advises you of any new relationships. Although you may proceed despite the warning, it may be prudent to review the relationship usage list, which is the point of the warning.

blank
Relationship is defined and is not new to the list.
Ref
Relationship is ignored because the parent or child table is a reference or lookup table, as specified on the Tables tab.
Unknown
Relationship does not exist. This condition can occur when a change in Default Qualifier causes a new set of tables and relationships to be referenced in the Access Definition. Relationships with Unknown status are ignored during a process. To remove a relationship in Unknown status, right-click to open the shortcut menu and select Remove or Remove All Unknown.

The Use new relationships check box is selected by default to include all relationships with a New status in the Archive Process. If you clear this check box, you must manually select new relationships to include them in an Archive Process. Clearing the check box ensures that a completed Access Definition is not affected by the addition of a new relationship.

Select Relationships

The Select column determines whether a relationship is used in the Archive Process. For this scenario, verify that check boxes for relationships ROD, RCO, and RID are selected.

Specify Options 1 and 2

By default, the Archive Process begins at the Start Table and proceeds through the data model, traversing selected relationships from parent to child — ORDERS to DETAILS. Specifically, Archive obtains rows from ORDERS that match your selection criteria and then traverses the relationship ROD to obtain related DETAILS rows, the children of ORDERS.

However, to select related data from ITEMS and CUSTOMERS, Archive must traverse the remaining relationships from child to parent. For example, to obtain CUSTOMERS rows related to ORDERS and maintain referential integrity, Archive must traverse the relationship RCO from child to parent. Similarly, to obtain ITEMS rows related to DETAILS, Archive must traverse the relationship RID from child to parent.

You can use the Options check boxes to extend the traversal path:

  • Option (1) determines whether Archive traverses a relationship from child to parent and archives a parent row for each selected child row. By default, Option (1) is selected. As a result, this setting maintains the relational integrity of the data.
  • Option (2) determines whether additional child rows are archived when a parent row is archived because of Option (1). In other words, if Archive has traversed a relationship from child to parent and archived a parent row, all children of that parent are archived when Option (2) is selected. By default, Option (2) is cleared.

Examples

The Option (1) and Option (2) specifications are relevant when:

  • The Start Table is a child table. For example, if the ORDERS table is the Start Table, the Option (1) setting determines whether the related CUSTOMERS rows are extracted and, if so, the Option (2) setting determines if additional ORDERS rows, related to the CUSTOMERS rows, are selected.
  • A table has more than one parent table. For example, if the ITEMS table is the Start Table, related rows are extracted from the DETAILS table because it is a child of the ITEMS table. However, the ORDERS table is also a parent of the DETAILS table. The Option (1) setting determines whether Archive then traverses from child to parent to extract the ORDERS rows related to the DETAILS rows and, if so, the Option (2) setting determines if additional DETAILS rows, related to the ORDERS rows, are selected.

In this scenario, the default settings for each relationship direct Archive to:

  • Select data from rows in the ORDERS table, which meet the selection criteria BEFORE(2Y) for ORDER_DATE.
  • Select parent rows from the CUSTOMERS table because Option (1) is selected for the relationship RCO between CUSTOMERS and ORDERS.
  • Select child rows from the DETAILS table to follow the basic traversal path from parent to child for the relationship ROD between ORDERS and DETAILS.
  • Select parent rows from the ITEMS table because Option (1) is selected for the relationship RID between DETAILS and ITEMS.

Review the Traversal Path

After defining the traversal path but prior to running the Archive Process, verify that the appropriate data will be archived.

Use the Show Steps option to display the steps to be performed in the Archive Process.

Click Tools > Show Steps to display a narrative that describes the traversal path.

Show Steps for Extract dialog. This dialog lists the steps in the traversal path.

To return to the Relationships tab, click File > Close. Then click File > Update and Return to update the Access Definition and return to the Archive Request Editor.

Note: See the Common Elements Manual for additional information about using Access Definitions.