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 Files 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.
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.
For this manual, licensing of all Optim™ components is assumed.
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.
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: 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
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 ********************************
The prompts on the Select Tables/Views for AD panel are:
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.
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.
See The Point-and-Shoot Facility for information about Row Lists. See Group Selection Processing for information about group selection processing.
If remote access is available, you can change the LOCATION to access remote tables.
You can save and use an Access Definition with any table or tables, other than the Start Table, in UNKNOWN status.
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.
The implied hierarchy is S, U, I, and D in order of increasing privilege. If no value is specified, D is assumed.
When you scroll horizontally, the second page of the Select Tables/Views for AD panel is displayed.
Figure: 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
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 **********************************
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.
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.
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.
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.