Tables tab

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

Example of the Tables tab on the Access Definition Editor, described as follows.

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 for the database where a table is defined (1 - 12 characters).
creatorid
Creator ID, owner ID, or schema Name (depending on DBMS) assigned to the table (1 - 64 characters).

Start Table

The name of the table from which data is selected first. Any listed table, except a table that is 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 that is entered in the tables list becomes the start table.

Fast Migration

Select this option to use fast migration to extract Netezza® data. All tables that are referenced by the access definition must be Netezza tables. Fast migration supports the use of only the following features and options. All other features are inoperative.
  • Selection criteria
  • Inline compression
  • Delete after archive
  • Actions for the start and end of the extract process
  • Actions for the start and end of the delete process
In addition, files that are created by the use of fast migration can be used only in delete and load processing. Any rows that contain NULL values are not deleted in a delete process. For load, you can choose tables to load and all data in the selected tables is loaded (a selective restore is not possible). All other features are inoperative. In addition, browsing a fast migration file reveals only the number of records in the file.

Selection criteria indicator

A notation indicates whether group selection or a point and shoot list applies to the access definition. Possible notations include the following items:

Grouping in use
The access definition uses group selection factors. For information about 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. For information about point and shoot data selection, see Edit Point and Shoot List.
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 that are 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 that is referenced by name. Moreover, an access definition can reference no more than 24,000 tables.

- General
Table/View
The name of a table or view. If a table name is not qualified with a DB alias or creator ID, the default qualifier is used.
Type
The type of object that is referenced by the table name is automatically displayed:
Table
Table.
A-Table
Alias for a table.
S-Table
Synonym for a table.
View
View.
A-View
Alias for a view.
S-View
Synonym for a view.
View Error
View is invalid and unusable.
Unknown
Object is unknown. Unknown might indicate that one of the following situations exist:
  • The referenced table no longer exists.
  • The fully qualified name that results when the default qualifier is applied does not reference a table.
  • The table name is entered incorrectly.
Inaccessible
The solution cannot connect to the database referenced by the DB alias.
Note: From the Restore Request Editor and Archive Directory Maintenance Utility, the table might 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. Here are the icons.
blank
No selection criteria are specified.
Column specifications icon.
Columns are rearranged or headings or LOB options are specified for data displays, or application associations for LOB data are specified.
Selection criteria icon.
Selection criteria are specified.
SQL criteria icon.
An SQL WHERE clause is specified.
Sort criteria icon.
Sort criteria are specified for data displays.
Actions icon.
Actions are defined for action phases of an archive, delete, or restore process.
Archive index parameters icon.
Archive index parameters are specified.
File attachments icon.
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, except the Start Table, as a reference table.
Note: Although relationships associated with reference tables are shown 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. When you save the access definition, the solution checks for a cascading delete/update and displays the Cascading Delete/Update Confirmation dialog if any of the following are true:
  • 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 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. For more information about this dialog, see Cascading Delete/Update Confirmation dialog.

Row Limit
Enter a numeric value to limit the number of rows that are extracted from the start table. Valid values are 1 - 2000000000 and apply to the start table only. This feature is useful for limiting the duration of an archive process, when timing is a consideration. The solution does not process more than 2000000000 rows in any table.
- Extract process
Extract Parms
Enter numeric values to extract a sampling of rows or to limit the number of rows to extract. If you use 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 fifth row in the table, beginning with the fifth row. Valid values are 1 - 9999.
Row Limit
Enter a numeric value to limit the number of rows that are extracted from a table. Valid values are 1 - 2000000000.
- 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 might increase the speed of your archive or extract processes. If you choose to extract uncommitted rows, the relational integrity of data in the archive or extract file might be compromised. Use caution when you insert or restore from any archive or extract file with uncommitted rows.

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.)

Extract RowID
For Oracle tables only, option to include the hidden Oracle row ID when the table data is extracted. This feature is available for Oracle database tables only if
  • available as a Product Option, and
  • no unique index exists for the table that is processed.
Click to select one of these values from the selection list:
Never
Extracting the row ID is turned off. Never is the default.
Dynamic
Optim evaluates the data to determine whether unique indexes exist. If unique indexes are found, no row IDs are extracted. Row IDs are extracted if no unique indexes are found.
Always
Row IDs are extracted for every row.

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 these options:

Example of the shortcut menu that is used to edit entries on the table list, described as follows.
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. If necessary, you can select Reverse Parent/Child tables from the Tools menu, before you save the new relationship.
Table Specifications
Open the Table Specifications submenu. For more information, see Table Specifications.
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 Table(s) dialog is displayed. This dialog is also displayed when you use the Join command from the Table Editor.

Example of the Select Tables dialog, described as follows.

The Select Table(s) dialog lists tables from the selected database:

  • DB aliases for available databases are provided in the DB Alias list. To list tables in a database, double-click the DB alias name or type over the name in the Pattern box.
  • Objects that are 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. For more information, see Use a pattern.

Show Only

Show Only options are disabled if Find Tables Related to Table is selected. Select an option to determine the type of object that is listed in the Select Table(s) dialog. Select All, Alias, Tables, Views, or Synonyms. The list is refreshed when you make a selection.

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 table list. You can use the % (percent) wildcard to represent one or more characters. 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

Specify the 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 that shows these relationships: customers table 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 1 generation of children. If the Related to Table name is CUSTOMERS, ORDERS is retrieved.
Parents 1 Retrieve names of 1 generation of parents. If the Related to Table name is DETAILS, ORDERS and ITEMS are retrieved.
Both 1 Retrieve names of 1 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 that are 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.


Feedback