Table/View Selection

Edit online

Use the Select Tables/Views for AD panel to list the names of tables that are accessed in an Archive, Compare, or Extract Process or during an edit or browse session. This panel is displayed when you select an Access Definition from the Select Access Definitions panel or explicitly name one on the Choose an Access Definition panel. The Select Tables/Views for AD panel is also displayed when you select Option 1 TABLES from the ARCHIVE Process menu or the EXTRACT Process menu, or if you specify an Access Definition for either source in a Compare Process. (For details, see the appropriate User Manual.)

Unless indicated otherwise in this discussion, references to tables include Legacy Tables (for Move or Compare for IMS, VSAM, and Sequential File Data only) and Materialized Query Tables. The use of a Legacy Table or Materialized Query Table in an Access Definition is virtually identical to that of a table. Exceptions are noted, where appropriate.

Two Pages

When Access and Archive, Compare, or Move are available, this panel is presented on two panels, horizontally first and second “pages.” To indicate the presence of the relevant horizontal page, “MORE” with an appropriate direction arrow is presented on the panel. The direction of the arrow indicates whether the remaining prompts are first or second horizontally. Use the LEFT and RIGHT primary commands or the assigned function keys to scroll the pages horizontally.

Note: If you are licensed only for Access, only the first horizontal page is available. If Access is not licensed, then only the second horizontal page is available.

For this manual, licensing of all Optim™ components is assumed.

Initial Display

If you provide the name of an existing Access Definition on the Choose an Access Definition panel, the list of tables for that Access Definition is shown on the initial display of the Select Tables/Views for AD panel. If you are creating a new Access Definition, the initial display provides space to enter the name of a table. There are a variety of methods and commands available to help populate this list. For example, you can type the name of a single table and use the GET TABLES RELATED ALL command to populate the list with the names of all tables related to the first table.

Sample Display

The following figure shows the first horizontal page of the Select Tables/Views for AD panel for a hypothetical Access Definition named AD. Note the fully qualified name of the Access Definition on the title line, which is GRP.FOPDEMO.AD in the example.

Figure 1. Select Tables/Views for AD – Access Rights

-- Select Tables/Views for AD: GRP.FOPDEMO.AD ---------------------------------
Command ===>                                                  Scroll ===> PAGE

 Primary : COL,SEL,SQL,REL,POINT,GROUP,GET TABLES RELATED,INDENT,ARC,LIST SUBS
    Line : COL,SEL,SQL,ALL,GR(A),GP(A),GC(A),DR(A),OR(A),DP(A),OP(A),
           DC(A),OC(A),EXP,ARC,ACT,STA,UNL
                                                     Table 1 of 1       MORE>>
Default Creator ID ===> FOPDEMO                                             >>
Start Table        ===> ORDERS                                              >>
Start Table Options   : None
                                            ---- Access Rights ----
Cmd   Status    (CreatorID.)Table/View Name (S)el/(U)pd/(I)ns/(D)el  Type
--- ----------- ------------------------->> ----------------------- -------
*** ********************************* TOP *********************************
___             ORDERS                                 D            TABLE
*** ******************************* BOTTOM ********************************

Panel

The prompts on the Select Tables/Views for AD panel are:

Default Creator ID: The default Creator ID automatically prefixed to any Table/View Name specified without one. In the preceding figure, for example, the Creator ID “FOPDEMO” would be prefixed to the table name “ORDERS” to form the fully qualified name “FOPDEMO.ORDERS”.
Specify a 1 to 128 character Creator ID. When creating an Access Definition, the default Creator ID is the Db2® SQLID for the current user.

Start Table: The name of the table from which data is selected first. Specify the fully qualified name if the Creator ID is different from the Default Creator ID. You can specify a partial name, with wildcard characters, to display a table name list from which you can select the appropriate name.
The Start Table must be on the Table List. If you leave Start Table blank, the first name entered on the Table List is automatically inserted as the default Start Table. Conversely, if you enter a name in Start Table that is not listed in the Access Definition, the name is added to the Table List.

Start Table Options

Start Table selection criteria methods that apply. This is a display-only area that contains one or more of the following designations:

None: No special Start Table criteria have been specified.

Row List (POINT): Primary key values have been stored in a Row List and are available for any Archive or Extract Process.

Temp. Row List (POINT): Primary key values have been stored in a Row List and are available for the current Archive or Extract Process only.

Group Selection (GROUP): Group selection processing has been specified. (Group selection processing is available for Move and Compare only.)

Note: Start Table Options are relevant for Archive, Compare, and Move. If only Access is licensed, the value is always None.

See The Point-and-Shoot Facility for information about Row Lists. See Group Selection Processing for information about group selection processing.

Cmd

Line command entry area. Use the following line commands to complete the Table List and specify or remove criteria for the listed tables.

ACT: Display the Select an Action To Be Defined panel. (This command is available if Archive is licensed.)

ALL: Remove or drop any selection criteria, SQL WHERE clauses, or archive criteria for the specified table, so that all rows are selected.

ARC: Display the Specify Archive Criteria for AD panel. (This command is available if Archive is licensed.)

COL: Display the Describe Columns for AD panel.

EXP: Display the Expand - (Creator.ID)Table/View Name pop-up, which shows the table's full name. This command is useful in displaying Long Object Names (LONs) that cannot be displayed in full on the panel.

GR(n): Add names of tables that are parents or children to the selected table.

Note: A number or the letter “A” with the GR, GP, GC, DR, DP, DC, PR, PP, and PC commands indicate the number of levels or generations you want included. Specify a number from 1 through 9 to indicate the number of levels you want included, such as GR3, or specify the letter “A” to indicate that you want ALL levels included, such as GRA. If a number or “A” is not specified, the default is one level.

GP(n): Add names of tables that are parents to the selected table.

GC(n): Add names of tables that are children to the selected table.

DR(n): Using Db2 relationships, add names of tables that are parents or children to the selected table.

DP(n): Using Db2 relationships, add names of tables that are parents to the selected table.

DC(n): Using Db2 relationships, add names of tables that are children to the selected table.

OR(n): Using Optim relationships, add names of tables that are parents or children to the selected table.

OP(n): Using Optim relationships, add names of tables that are parents to the selected table.

OC(n): Using Optim relationships, add names of tables that are children to the selected table.

LR: Generate a selection list of tables related to the selected table.

SEL: Display the Specify Selection Criteria for AD panel.

SQL: Display the SQL WHERE Clause panel.

STA: Display the Criteria in Effect pop-up window, which lists the types of criteria in effect for the specified table.

UNLoad: Define and maintain an alternate source of data for a Db2 table to be used with EXTRACT, ARCHIVE or COMPARE processes instead of live Db2 data. The alternate source is a data set with unloaded Db2 data in one of the supported formats. The use of the Db2 Unloaded Data Set is supported only for Db2 tables and mutually exclusive with SQL and SEL criteria. Any selection criteria need to be applied while creating the unload data set with an unload utility.

Note: ISPF-like line commands are also available to copy, move, repeat, insert, or delete table names.

Status

One or more status indicators for the table:

ACT: Archive Actions are specified.

ARC: Archive index or date criteria are specified.

blank: All columns are selected with default display formatting. Default.

COL: Column attributes are specified.

REMOTE: The named table is an alias to another Db2 subsystem. Remote tables are not supported. You must remove the name of a table in REMOTE status to save the Access Definition.
If remote access is available, you can change the LOCATION to access remote tables.

SEL: Selection criteria are specified.

SQL: An SQL WHERE clause is specified.

TEMPTABLE: The table is a Db2 Temporary Table. It contains no data and must be defined as a reference table. It also cannot be used as a Start Table, joined in a browse or Point-and-Shoot session, or selected for a browse or edit session.

UNKNOWN

The named table is not known to Db2. UNKNOWN status indicates the table does not exist. Possible reasons for UNKNOWN status are:

The table name is mistyped.
The named table does not exist or has been dropped.
Prefixing the name with the default Creator ID results in a name for a table that does not exist.

You can save and use an Access Definition with any table or tables, other than the Start Table, in UNKNOWN status.

VIEWERR: The named view is not usable. Generally, this status indicates a conflict between the view definition and the underlying base tables. You can save an Access Definition with any view or views, other than the Start Table, in VIEWERR status.

Note: The Status area might not accommodate all the indicators that apply. See Table Status Indicators for more information.

Table/View Name

The names of tables and views to be processed (i.e., the Table List). Specify the fully qualified name if the Creator ID is different from the Default Creator ID.

The Table List must include at least one table or view name. If you delete all names and exit the Access Definition editor, the Access Definition is deleted.

A table can be referenced only once in an Access Definition; therefore, the Table List cannot reference both a table and one or more views, aliases, or synonyms for the table. (This restriction does not apply to Access.)

Generally, a Temporary Table on the list is ignored by Optim. For Move, however, you can specify a temporary table, but it must be a reference table and cannot be used as the Start Table. Since there is no data in the table, you cannot join to it during a browse session.

Access Rights

Access privileges. (Access only) The Access Rights privilege applies if more restrictive for the user than the Db2 authorization; otherwise, the authorization in the Db2 Catalog is used. Use Access Rights to restrictDb2 privileges for a user when editing or browsing data with the Access Definition.

S: Select only.

U: Update or select.

I: Insert, update, or select.

D: Delete, insert, update, or select.

The implied hierarchy is S, U, I, and D in order of increasing privilege. If no value is specified, D is assumed.

Type

The type of object. Possible values are:

TABLE: Table name. When the Db2 table has an Unloaded Db2 Data Source Definition then its status will be presented as TABLE/U.

S-MQT: System-maintained Materialized Query Table

U-MQT: User-maintained Materialized Query Table

VIEW: View name

S-TABLE: Synonym for a table

S-VIEW: Synonym for a view

A-TABLE: Alias for a table

A-VIEW: Alias for a view

LEGACY: Legacy Table name

When you scroll horizontally, the second page of the Select Tables/Views for AD panel is displayed.

Figure 2. Select Tables/Views for AD – Criteria Portion

-- Select Tables/Views for AD: GRP.FOPDEMO.AD ---------------------------------
Command ===>                                                  Scroll ===> PAGE

 Primary : COL,SEL,SQL,REL,POINT,GROUP,GET TABLES RELATED,INDENT,ARC,LIST SUBS
    Line : COL,SEL,SQL,ALL,GR(A),GP(A),GC(A),DR(A),PR(A),DP(A),PP(A),
           DC(A),PC(A),EXP,ARC,ACT,STA,UNL
                                                        Table 1 of 1 <<MORE
Default Creator ID ===> FOPDEMO                                             >>
Start Table        ===> ORDERS                                              >>
Start Table Options   : None
                                                 R D --Extract Parms--
Cmd   Status      (CreatorID.)Table/View Name    F A EveryNth RowLimit  Type
--- ----------- ------------------------------>> - - -------- -------- -------
*** ********************************** TOP ***********************************
___             ORDERS                             N   ____   ________ TABLE
*** ******************************** BOTTOM **********************************

Panel

Many prompts on this panel are also displayed on the left-hand page and are described previously in this section. The following prompts are displayed only on the right-hand portion.

Optim displays this page first when you select Option 1 TABLES from the ARCHIVE Process or EXTRACT Process menu.

RF

Identifier for a reference table, a table for which all rows are selected, unless selection criteria are specified for the table. Specify:

N: Select only the related rows.

Y: Use as a reference table.

Any table on the Table List, except the Start Table, can be treated as a reference table. Overtype the RF value with the desired designation or use the REF primary command to specify N or Y for all listed tables, other than the Start Table. Relationships for a reference table are not displayed on the Specify Relationship Usage panel because the relationships are irrelevant when processing reference table data. This label is Ref Tbl when the Select Tables/Views for AD panel is invoked during an Extract, Archive, or Compare Process.

DA

Indicator for the deletion of database rows after they are archived. (This prompt is displayed only if Archive is installed.) Specify:

N: Retain source rows after archiving.

Y: Delete source rows after archiving.

You can select any table on the Table List. Overtype the DA value with the desired designation or use the DAA primary command to specify N or Y for all listed tables.

Note: This label is DAA when the Select Tables/Views for AD panel is invoked from the ARCHIVE Process menu.

EveryNth: A numeric value used as a factor for selecting rows from the table. Specify any value from 1 through 65,535. Specify “10” for example, to select every 10th row.

This entry is unavailable when the Select Tables/Views for AD panel is invoked from the ARCHIVE Process menu, or if Move or Compare is not installed.

RowLimit: The maximum number of rows that can be selected from the table. Specify any value from 1 through your site maximum value.
You can use Row Limit as a check when processing data. For example, if you expect to archive fewer than 1000 rows from a table, a row limit causes the Archive Process to terminate if the number of archived rows exceeds 1000. The termination allows you to troubleshoot the specifications for the process. Row Limit is also useful to extract a limited set of data for a test database.

Beginning and end markers on both horizontal pages of the panel indicate the first and last table names in the list.