Tables Tab

Use the Tables tab to list the names of tables that contain the desired data and to define specifications for data in the listed tables, including selection criteria, sampling parameters, and archive options.

Access definition editor tables tab, described below

Default Qualifier

You must provide a Default Qualifier, which determines the prefix for unqualified or partially qualified table names. A fully qualified table name is in three parts — dbalias.creatorid.tablename. The Default Qualifier is: dbalias or dbalias.creatorid.

dbalias
DB Alias of the database where a table is defined
(1 to 12 characters).
creatorid
Creator ID, Owner ID, or Schema Name (depending on DBMS) assigned to the table (1 to 64 characters).

Start Table

The name of the table from which data is selected first. Any listed table, except a table designated as a reference table, can be the Start Table. If you enter a table name that is not listed, it is automatically added to the list. If you do not specify a Start Table, the first name entered in the Table List becomes the Start Table.

Selection Criteria Indicator

A notation above the Start Table box indicates whether Group Selection or a Point and Shoot list applies to the Access Definition. Possible notations are:

Grouping in use
The Access Definition uses Group Selection factors. For information on Group Selection, see Group Tab.
Grouping not in use
The Access Definition does not use Group Selection factors.
Point and Shoot List in use
The Access Definition uses a named Point and Shoot file. See Edit Point and Shoot List for information on Point and Shoot data selection.
Local Point and Shoot List in use
The Access Definition uses a Local Point and Shoot list (saved with the Access Definition).
No Point and Shoot List in use
The Access Definition does not use a Point and Shoot list.

Table List Details

The grid on the Tables tab lists tables and views referenced by the Access Definition. Enter names directly in the Table/View grid column, or right-click to use shortcut menu commands to enter or edit entries on the list. The Access Definition can reference only one version of a table; you cannot reference a view or an alias of a table referenced by name. Also, an Access Definition can reference no more than 24,000 tables.

- General
Table/View
The name of a table or view.
Note: If a table name is not qualified with a DB Alias or Creator ID, the Default Qualifier is used.
Type
The type of object referenced by the table name is automatically displayed:
Table
Table name.
A-Table
Alias for a table.
S-Table
Synonym for a table.
View
View name.
A-View
Alias for a view.
S-View
Synonym for a view.
View Error
View is invalid and unusable.
Unknown
Object is unknown. Unknown may indicate the:
  • Referenced table no longer exists.
  • Fully qualified name that results when the Default Qualifier is applied does not reference a table.
  • Table name is entered incorrectly.
Inaccessible
Optim cannot connect to the database referenced by the DB Alias.
Note: From the Restore Request Editor and Archive Directory Maintenance Utility, the table may be inaccessible because it is restricted by Archive File Security.
DB Alias Unknown
DB Alias does not exist or is entered incorrectly.
DBMS
The name of the DBMS for a table is automatically displayed.
Table Specifications
Icons indicate the presence of selection criteria or other specifications for a table. Click an icon in this column to display the corresponding tab on the Table Specifications dialog. The icons are:
blank
No selection criteria are specified.
Columns are rearranged or headings or LOB options are specified for data displays, or application associations for LOB data are specified.
Selection criteria are specified.
An SQL WHERE clause is specified.
Sort criteria are specified for data displays.
Archive Actions are defined for action phases of an Archive, Delete or Restore Process.
Archive Index parameters are specified.
File Attachments are specified.
Ref Tbl (Reference Table)
Select the check box to designate a table as a reference or look-up table. Unless selection criteria are specified for the reference table, all rows are selected. Specify any table as a reference table, except the Start Table.
Note: Although relationships associated with reference tables appear on the Relationships tab (with Ref status) they are not traversed during an Extract or Archive Process.
- Archive Process
Delete Rows After Archive
Select the check box to delete the selected rows from the database table after the rows are copied to an Archive File. Archive Request options allow you to override this Access Definition specification for an Archive Process or defer execution of the Delete Process.
Note: When you save the Access Definition, Archive performs a cascading delete/update check and displays the Cascading Delete/Update Confirmation dialog if:
  • The Warn on Cascade Delete/Update option in either Product or Personal Options is set to Saving Access Definition or Always. (See Personal Options or refer to the Installation and Configuration Guide )
  • Delete Rows After Archive is selected for at least one table in the Access Definition.
  • The cascade delete or update affects at least one table that is not explicitly included in the Access Definition.

Click OK to return to the Access Definition Editor. (See Cascading Delete/Update Confirmation Dialog for more information about this dialog.)

Row Limit
Enter a numeric value to limit the number of rows extracted from the Start Table. Valid values are 1 through 999999999999 and apply to the Start Table only. This feature is useful for limiting the duration of an Archive Process, when timing is a consideration.
- Extract Process
Extract Parms
Enter numeric values to extract a sampling of rows or to limit the number of rows to extract. If using a Point and Shoot list to select Start Table rows, the Extract Process ignores any Every Nth and Row Limit parameters for the Start Table.
Every Nth
Enter a numeric value to specify a sampling factor for a table. For example, if you enter 5, the process extracts every 5th row in the table, beginning with the 5th row. Valid values are 1 through 9999.
Row Limit
Enter a numeric value to limit the number of rows extracted from a table. Valid values are 1 through 999999999999.
- Archive or Extract Process
Uncommitted Read
Select the check box to extract uncommitted rows from the database table. Selecting this option for tables with known performance problems may increase the speed of your Archive or Extract processes.

This option is available if the Extract using Uncommitted Read option in Product Options is set to Default Active or Default Inactive and the DBMS or version supports it. (Refer to the Installation and Configuration Guide .) Optim disables the option if regardless of the Product Options setting.

Note: If you choose to extract uncommitted rows, the relational integrity of data in the Archive or Extract File may be compromised. Use caution when inserting or restoring from any Archive or Extract File with uncommitted rows.

Shortcut Menu

Use the shortcut menu to edit entries on the Table List and define table specifications. Select a table by positioning the pointer in the appropriate Table/View grid cell. Right-click to open a shortcut menu for the selected table. Choose from the following:

shortcut menu to edit entries on the table list, described below
Remove
Remove the grid row.
Insert
Insert a blank grid row above the selected row.
Remove All Tables
Remove all rows from the grid.
Replace Table
Open the Select Table(s) dialog to choose a table name to replace the selected entry.
Add Tables
Open the Select Table(s) dialog to select one or more table names to add to the Table List.
Set as Start
Designate the table as the Start Table.
Create Optim Relationship
Open the Relationship Editor and designate the selected table in the Access Definition as the Parent table in a new relationship. Select a Child table from the Table Open dialog. If necessary, you can select Reverse Parent/Child tables from the Tools menu, before saving the new Optim Relationship.
Table Specifications
Open the Table Specifications submenu. See Table Specifications for more information.
Reset
Open the Reset submenu and select a table specification for removal.

Select Table(s) Dialog

When you select Add Tables or Replace Table from the shortcut menu, the Select Tables dialog is displayed. This dialog is also displayed when you use the Join command from the Table Editor.

select tables dialog, described below

The Select Table(s) dialog provides a list of tables for the selected database:

  • DB Aliases for available databases are listed on the left. To list tables in a database, double-click the DB Alias or overtype the DB Alias in the Pattern box.
  • Objects referenced by the selected DB Alias are listed in the Database Table grid in alphabetical order by Creator ID and Table Name. The type of object (table, view, alias, synonym), DBMS, and fully qualified name are provided.

Pattern

Use a Pattern to limit the list of database tables in the Select Table(s) dialog. After you specify a pattern, click Refresh to redisplay the list based on your criteria. See Use a Pattern for more information.

Show Only

Select a Show Only option to determine the type of object listed in the Select Table(s) dialog. Select All, Alias, Tables, Views, or Synonyms. The list is refreshed when you make a selection.

Note: The Show Only options are disabled if Find Tables Related to Table is selected.

Find Tables Related to Table

Select Find Tables Related to Table and enter a fully qualified table name to retrieve the names of related tables. You can use a pattern, specify the type of relationship, and limit the number of generations to consider.

Matching Table Pattern

Use a three-part pattern, dbalias.creatorid.tablename, to insert names of tables into the Access Definition table list. You can use the % (percent) wildcard to represent one or more characters or use the _ (underscore) wildcard to represent a single character in a table name.

Relationship

Select an option to include Parents, Children, or Both.

Levels to Search

Enter an explicit number of Levels to Search, or select the All Levels check box to include all levels. The value in the Levels to Search box is combined with the relationship option (Parents, Children, or Both) to determine the related tables.

Example

To understand how Find Tables Related to Table functions, assume that the following four tables are related as shown. The arrows indicate the direction of the relationships, from parent to child.

graphic showing relationships: customers is parent to orders; orders and items are parents to details

Following are examples of the sets of tables that match different combinations of relationship options and Levels to Search.

Relationship Levels Results
Children 1 Retrieve names of one generation of children. If the Related to Table name is CUSTOMERS, ORDERS is retrieved.
Parents 1 Retrieve names of one generation of parents. If the Related to Table name is DETAILS, ORDERS and ITEMS are retrieved.
Both 1 Retrieve names of one generation each of children and parents. If the Related to Table name is CUSTOMERS, ORDERS is retrieved. If the Related to Table name is ORDERS, CUSTOMERS and DETAILS are retrieved.
Children ALL Retrieve names of children, grandchildren, and so on. If the Related to Table name is CUSTOMERS, ORDERS and DETAILS are retrieved. If the Related to Table name is ORDERS, DETAILS is retrieved.
Parents ALL Retrieve names of parents, grandparents, and so on. If the Related to Table name is CUSTOMERS, which has no parents, no names are retrieved. If the Related to Table name is ORDERS, CUSTOMERS is retrieved. If the Related to Table name is DETAILS, CUSTOMERS, ORDERS and ITEMS are retrieved.
Both ALL Retrieve names of all tables (children, parents, grandparents, grandchildren, and so on). Use any table name in the example as the Related to Table name to obtain the same set of table names. This default option is the one used most often to retrieve table names for an entire data model.

Buttons

Select
Select one or more table names and use Select to add the names to the list of tables on the Tables tab of the Access Definition Editor.
Cancel
Use Cancel to return to the Access Definition Editor.
Refresh
Use Refresh to update the list of table names displayed to match the Pattern.
Select All Matching
Use Select All Matching to add all table names that match the criteria to the list of table names on the Tables tab of the Access Definition Editor.
Display
Use Display to display the names of all related tables that match the pattern.