Search and Selection Criteria

You can use criteria to search for and identify Archive Files that contain specific data. You can also use the same or similar criteria to select and restore a subset of data from an Archive File (that is, selection criteria).

Two types of selection criteria can be used in a Restore Process:

Global
Global selection criteria is defined as part of the Restore Request, and can be selectively applied to any Archive File listed in the Restore Request. In other words, you can define global selection criteria once and use the shortcut menu to apply it to selected Archive Files or to all Archive Files listed in the Restore Request Editor.
Note: Any search criteria specified in an automated Search and Restore process overrides Global selection criteria in the Restore Request and is applied to all processed files.
Local
Local selection criteria is specific to a single Archive File. You can define and apply local selection criteria to any Archive File listed in the Restore Request. You can also use substitution variables referenced in the current Access Definition and Point and Shoot to select specific Start Table rows from an Archive File.
Note: Any search criteria used to select an Archive File for the Restore Process is copied to the Restore Request as local selection criteria. Local selection criteria has no effect in an automated Search and Restore process.

When running the Restore Process from the graphical user interface, you can use shortcut menu commands to apply local or global selection criteria to any Archive File listed in the Restore Request. You can also copy global selection criteria and apply it as local, modifying or adding to the copied criteria, as necessary. See Archive Files List for the Archive File(s) List.

Note: Any Restore Request settings to apply criteria to listed files are disabled in an automated Restore Process for which Archive File overrides are provided, or in an automated Search and Restore process.

Define Search or Selection Criteria

Search or global selection criteria can be applied to several Archive Files, even if the data models are not exactly the same.

Use the Index Search Selection Criteria dialog to define search criteria for any table in any Archive File listed in the Select Archive File(s) dialog. Use the Global Selection Criteria dialog to define selection criteria for any table in any Archive File processed using the Restore Request.

The tabs on the Index Search Selection Criteria dialog and the Global Selection Criteria dialog are alike and the methods used to define search criteria are the same as those used to define global selection criteria. Although the following discussion concerns the Global Selection Criteria dialog, the information also applies to the Index Search Selection Criteria dialog as well as to the Column Criteria and SQL tabs on the Local Selection Criteria dialog.

Column Criteria tab in the Global Selection Criteria dialog

Table

Click the down arrow to select from an alphabetical list of all tables in all Archive Files referenced by the Restore Request or, when defining search criteria, the Select Archive File(s) dialog.

Tabs

The dialog has two tabs. Use the Column Criteria tab to define selection criteria for any column in the selected table, or the SQL tab to define an SQL WHERE clause for the table.

Note: If you define both column criteria and an SQL WHERE clause for a table, the specifications are logically ANDed.

Column Criteria Tab

Use the Column Criteria tab to specify criteria for one or more columns in the selected table.

Combine all column criteria with

Option for combining criteria for multiple columns. Select:

AND
A row must match selection criteria for all columns.

For example: CUSTNAME > 'M' AND STATE = 'NJ'

OR
A row must match selection criteria for any column.

For example: CUSTNAME > 'M' OR STATE = 'NJ'

Column Name

Name of each column. For convenience, you can rearrange the order in which the columns are displayed by dragging the grid row number. You cannot modify column names.

Since the data model of the production database may change over time, two or more versions of a table may be represented in listed Archive Files. For example, the ORDERS table in a recent Archive File may include a column, ORDER_SALESMAN that is not present in earlier files. Thus, a superset of column names from all versions of the selected table is displayed in the dialog.

Column Criteria

To enter selection criteria for a column, click the Operator grid cell and select an operator from the list. Enter a value in the Selection Criteria cell. The data types of values for selection criteria must be appropriate for the column and conform to SQL syntax. Any variables must be defined in the current Access Definition.

Notes:
  • You must use these formats when specifying date/time selection criteria:
    • Date ‘YYYY-MM-DD'
    • Time ‘HH:MM:SS'
    • Timestamp ‘YYYY-MM-DD HH:MM:SS.FFF[FFF]'
  • Note that the following separators are valid: colon (:), space ( ), slash (/), minus (-).
  • You cannot use variables on the Global Selection Criteria or Index Search Selection Criteria dialog.
  • The variable delimiter in a Restore Process is a colon (:) and cannot be modified.

To validate selection criteria, click outside the grid row. If the criteria is invalid, a message is displayed.

SQL Tab

For selection criteria that is more complex than can be defined on the Column Criteria tab of the Global Selection Criteria dialog, use the SQL tab of the Global Selection Criteria dialog to create an SQL WHERE clause.

For example, to select the desired rows from a table, you may need a combination of AND and OR logical operators.

Note: For details on rules that apply to SQL statements used internally by Archive, refer to SQL Grammar for Search and Restore.
Global Selection Criteria dialog with SQL as the active tab. The elements of the SQL tab are described as follows.

SELECT. . . FROM (table) WHERE

Enter the SQL WHERE clause of the SELECT statement.

  • To create an SQL WHERE clause, type directly into the SELECT text box or select column names and operators from the lists.
  • To delete an SQL WHERE clause, right-click the SELECT text box and select Remove SQL. You may also overtype with blanks, or use the Delete or Backspace key.
  • To validate an SQL WHERE clause, right-click the SELECT text box and select Validate SQL, or click outside the text box. If the statement is invalid, a message is displayed.

Columns

A list of the columns in the table. Select a column name to add it to the SQL WHERE clause, positioned ahead of the cursor.

Since the data model of the production database may change over time, two or more versions of a table may be represented in listed Archive Files. For example, the ORDERS table in a recent Archive File may include a column, ORDER_SALESMAN that is not present in earlier files. Thus, a superset of column names from all versions of the selected table is displayed in the dialog.

Operators

A list of valid operators and symbols you can use in the SQL WHERE clause. Select an operator to insert it before the cursor position.

Define Local Selection Criteria

You can define local selection criteria for any Archive File listed in the Restore Request Editor. Local selection criteria apply to a specific Archive File only and are typically used in a unplanned Restore Process that is run from the graphical user interface.

To define local selection criteria, right-click an Archive File name in the Restore Request Editor and click Apply Local Selection Criteria in the shortcut menu. The Local Selection Criteria dialog is displayed.

Local Selection Criteria dialog with Column Criteria as the active tab

The selected Archive File name and a list of tables in the file are displayed at the top of the Local Selection Criteria dialog. In many respects, the Local Selection Criteria dialog is like the Index Search Selection Criteria and Global Selection Criteria dialogs – the Column Criteria and SQL tabs on the three dialogs are exactly the same. For information about defining selection criteria or an SQL WHERE clause, refer to Define Search or Selection Criteria.

Variables

One of the main differences between the Local Selection Criteria and Global Selection Criteria dialogs is that the Local Selection Criteria dialog allows you to include variables on the Column Criteria and SQL tabs.

You can use the substitution variables specified in the current Access Definition for the Archive File. You can add, modify, or delete substitution variables for the current Archive File by browsing the Access Definition from the Restore Request Editor.

Note: On the Local Selection Criteria dialog, the Variable Delimiter is always a colon (:), and cannot be modified.

For more information about using variables, see the Common Elements Manual .

Point and Shoot

The Local Selection Criteria dialog includes a third tab, the Point and Shoot tab, which is not on the Index Search Selection Criteria or Global Selection Criteria dialog.

A Point and Shoot list consists of primary key values, in ASCII format, that are used as criteria to select Start Table rows. For a selective restore from the graphical user interface, it may be easiest or most convenient to select rows from the Start Table rather than to devise selection criteria or an SQL WHERE clause.

Use the Point and Shoot tab to select Point and Shoot options and browse, edit, or create a Point-and-Shoot list.

Local Selection Criteria dialog with Point and Shoot as the active tab. The elements of the Point and Shoot tab are described as follows.

Start Table

Name of the table designated as the Start Table in the original or a modified version of the Access Definition used to create the Archive File. To change the Start Table for the Restore Process, click Browse > Access Definition > Original from the Archive File List to open the Access Definition Editor. If the Access Definition has been modified, click Browse > Access Definition > Modified from the Archive File List to open the Access Definition Editor.

File Options

None
A Point and Shoot list is not used.
Local
A Point and Shoot list for the Archive File is stored with the Restore Request. A Local list is unavailable to other Archive Files, process requests, or Access Definitions. However, you can save a Local Point and Shoot list as a named Point and Shoot list.
File
The Point and Shoot list is named and saved as a file that can be referenced for use with other Archive Files, Access Definitions, and process requests, and shared with other users. A Point and Shoot file is stored in the directory you specify. To use an existing file or to create a new, named Point and Shoot file, select File, and type the file name.

Click Tools > Edit Point and Shoot from the menu to display the Point and Shoot Editor. Start Table rows that satisfy any selection criteria for the table are displayed. You can use the Point and Shoot Editor to select individual rows from the Archive File for restoration.

Primary Keys

The Start Table must have a primary key for Point and Shoot to function. The process uses primary key values to identify Start Table rows to be restored. The primary key values of the selected rows are saved in the Point and Shoot list.

Processing

The following considerations apply to Restore processing using Point and Shoot lists:

  • If you specify selection criteria, an SQL WHERE clause, or both, and use a Point and Shoot list, the criteria is logically ORed with the Point and Shoot selection to select data for restoration.
  • You can join other tables to the display for reference, but you can select rows from the Start Table only.
  • Archive Files on secondary media must be copied to a pre-defined disk location, which may delay processing and consume memory.

Point and Shoot Editor

The Point and Shoot Editor displays data from the Start Table in a browse window. A toolbar in the browse window allows you to select display options and menu choices that pertain to the display.

Point and Shoot Editor. The elements of the Point and Shoot Editor are described as follows.

Selected Rows

Number of selected Start Table rows to include in the Point and Shoot list.

Rows Not Found

Number of rows in the Point and Shoot list that are not listed on the grid. A number greater than zero may indicate that the Default Qualifier has changed since the Point and Shoot list was created or that the Point and Shoot list includes rows that do not match current selection criteria.

Browse Window

The first browse window in the Point and Shoot Editor displays the Start Table rows. A grid column labeled Select provides a check box for each row. Select the check box to include the row in the Point and Shoot list. Clear the check box to retract a selection.

Joining tables is useful for inspecting segments of related data to ensure that the appropriate sets of data are selected for restoration. You can join and display rows from related tables, using the Join button on the toolbar. Rows from each joined table are displayed in a separate browse window in the dialog.

In each browse window, data can be navigated and customized using the Find, Exclude, Include, Hide, and Lock options available from the grid heading shortcut menu. (See the Common Elements Manual for additional information.)

A browse window has the following components:

Table
The name of the table shown in the browse window. The first browse window in the Point and Shoot Editor always displays the Start Table.
Format
Columnar format button
or Side label format button
Click the Format button to switch the data display between columnar and side label format. The default format is set in Personal Options.

In columnar format, the column names are displayed across the top of the grid and the data is displayed beneath the headings. Note that the headings for primary key column(s) are in bold typeface.

Note: The Select grid column, with check boxes for selecting rows for the Point and Shoot list, is available in columnar format only.
Options
Options button
Click the Options button to display the Options menu.
Display Attributes
Switch between displaying and hiding column attribute information in the column headings.
Select All Rows
Select all rows in the browse window.
Invert All Row Selections
Reverse selected and unselected rows.
Deselect All Rows
Clear all previously selected rows.
Show Excluded Rows
Display all previously excluded rows (rows excluded using the Exclude command on the shortcut menu). To display excluded rows individually, right-click a row and click Show Next in the shortcut menu.

Display Multiple Tables (Join Tables)

In many cases, a table is related to two or more tables, creating different paths for joining and browsing the data. Thus, you can join more than one related table to a table. When several tables are joined to a table, the joined tables are "stacked" in a single edit window, in the order in which they were joined, with the most recently joined table displayed and the other tables in the stack hidden. You can display any table in the stack and join other related tables to any table in the stack.

Point and Shoot Editor with rows from two separate tables
Note: Only Start Table rows can be selected for a Point and Shoot list.

Only one relationship can be used in a join.

  • If a relationship between the tables exists, the table is joined automatically and related rows displayed in a new browse window.
  • If a relationship does not exist, the Create a New Relationship dialog opens.
  • If more than one relationship exists, the Select a Relationship dialog, listing the relationships, opens. You must select the relationship to use.

In a stack, the name of the displayed table appears in a drop-down list in the browse window. Click the arrow to display the list of tables in the stack. Select a table name from the list to display that table in the browse window.

When a stacked table is displayed, all subordinate joined tables are also displayed. When a stacked table is hidden, all subordinate joined tables are also hidden.