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:
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 Archive Request Editor. The Access Definition Editor is displayed with Tables as the active tab.
in theYou 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.
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.
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.
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.
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.
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.
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.
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.
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.
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:
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.
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:
In the following examples, the operators and syntax may not be valid for all database management systems. However, the functions demonstrated are universal.
To archive data for all customers with names beginning with the letter A, from Pennsylvania, you can specify:
To archive data for an item that is no longer sold to customers (for example, The Man Who Would be King), you can specify:
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:
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 . The Table Specifications dialog is displayed 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 or click the Close button in the title bar. Note the presence of the Selection Criteria icon in the Table List.
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 . The Table Specifications dialog is displayed with Archive Index as the active tab.
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.
When finished, click 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.
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.
The Selection Criteria icon indicates
that selection criteria is specified for the ORDERS table.
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.
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.
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:
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.
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.
Each listed relationship has a status, which indicates:
(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.
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.
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.
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:
The Option (1) and Option (2) specifications are relevant when:
In this scenario, the default settings for each relationship direct Archive to:
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 to display a narrative that describes the traversal path.
To return to the Relationships tab, click . Then click to update the Access Definition and return to the Archive Request Editor.