Results of the Master File Generation Facilities

The AUTODB2, AUTOSQL, and AUTODBC facilities generate Master and Access Files containing the declarations described in Describing Tables to FOCUS, and Multi-Table Structures. This section discusses those cases in which they supply a different keyword or value. Minor changes may be required in some situations. As a rule, you can use the generated file descriptions, without changes, immediately after the AUTODB2, AUTOSQL, or AUTODBC session.

In this section: Common Errors AUTODB2 Sample Session AUTODBC Sample Session

In the Master File:

• The FILENAME value is the member name or file name you specified on the AUTODB2/SQL initial screen or on AUTODBC Screen E1.
• The SEGNAME value is:
• The DB2 or DB2 for VM table name.
• The Teradata table name, truncated to eight characters. If the first eight characters are not unique, the table name is truncated to six characters and a two-digit integer is appended to ensure uniqueness.

• The SEGTYPE value is always S0 for the root segment. For descendant segments it is either S0 or U, depending on the relationships between the various key fields.
• Field declarations for primary key columns are listed first, followed by those for non-key fields.
• For AUTODB2 and AUTOSQL, FIELDNAME and ALIAS values are the full RDBMS column names.
• For AUTODBC, if fields are not unique the FIELDNAME values are the Teradata column names truncated to eight characters plus the appended value Qnnn (where nnn is an integer). The Qnnn value is appended to ensure uniqueness. ALIAS values are the full Teradata column names.

When Y is specified for long field names, field names up to 12 characters and aliases up to 30 characters are included in the Master File. Otherwise, a truncated FIELD declaration of 12 characters with a blank ALIAS appears in the Master File and the complete column name appears in the Access File, as described in Additional Topics.

• Data types are determined as follows:

GRAPHIC and VARGRAPHIC data types are described with an ACTUAL attribute of Kn, and with a USAGE attribute of A(2n+2). LONG VARGRAPHIC data types are not supported.

AUTODB2 and AUTOSQL support the TIME and TIMESTAMP data types. TIME and TIMESTAMP data types are treated as either date-time or alphanumeric strings in the Master File, depending on the data type specified on the main menu. AUTODBC supports the TIMESTAMP data type as an alphanumeric string (A26).

AUTOSQL also supports DBAINT and DBAHW, two internal DB2 for VM data types used in some system catalog tables. They are described with an ACTUAL attribute of I4 and should be used for read-only access.

• Field lengths for USAGE and ACTUAL formats are calculated automatically. The generated field declarations omit the keywords USAGE and ACTUAL; only the values appear. Columns with RDBMS DATE data types are described with an ACTUAL attribute of DATE. Columns with VARCHAR data types longer than 254 characters are described with an ACTUAL attribute of text (TX).
• For AUTODB2 and AUTOSQL, the ACTUAL field length for DECIMAL(p,s) columns with missing data is P8 if p£15, P((p+1)/2) if p>15.
• For AUTODBC, the ACTUAL field lengths for numeric fields with missing data are either P8 for DECIMAL columns or I4 for SMALLINT columns.

• The MISSING parameter is included and is set ON if the RDBMS table definition allows NULLs.

In the Access File:

• The SEGNAME value matches the corresponding SEGNAME value in the Master File.
• The TABLENAME value:
• For AUTODB2 and AUTOSQL, depends on whether Y was entered on the Main Menu for the field "Use Creator Name in AFD?" If it was, the creator name and the table name compose the TABLENAME value. For AUTODB2, if a location was specified, the location, creator, and table name compose the TABLENAME value.
• For AUTODBC, the database name and the table name compose the TABLENAME value.

• KEYS and WRITE values are supplied.

AUTODB2, AUTOSQL, and AUTODBC use existing RDBMS indexes to determine the KEYS value and the order in which to place fields in the Master File. KEYS is set to zero if a unique index does not exist or if an RDBMS view is described.

Note:

In the case of a view, the unique indexes are described on the underlying tables, not on the view; they may not be valid as a primary key for the view. If possible, edit the Master and Access Files to reflect the index structure of the view's base tables.

• For AUTODB2 and AUTOSQL, the optional keyword DBSPACE is omitted. For AUTODBC, the optional keywords FALLBACK and KEYORDER are not supplied.
• For AUTODBC, KEYFLD and IXFLD values are the Teradata columns selected during Option 3 to relate tables. KEYFLD and IXFLD values match the fieldnames found in the Master File. For AUTODB2 and AUTOSQL, KEYFLD and IXFLD values were selected on the Common Column Selection screen.

You may need to edit the generated Master File or its corresponding Access File if:

• You prefer fieldnames different from the generated names.
• There are RDBMS data types in the tables or views that the data adapter does not support. A warning about unsupported data types appears on the Status Screen.

Field declarations are not generated for columns with unsupported data types. You can add the field declarations yourself; consult Describing Tables to FOCUS, for more information on describing them.

For DB2 and DB2 for VM, to identify the unsupported columns check the system catalog, SYSCOLUMNS, with a FOCUS report request. (See Additional Topics, for an example.)

AUTODB2 and AUTOSQL include all data types that FOCUS can access.

• A unique index does not exist, or you want to use a different index from the selected one. If the table or view has a primary key, specify a value greater than zero for the KEYS keyword and rearrange the fields in the Master File so that the columns comprising the primary key are described first.
• Certain fields are classified for security reasons. To add security, delete field declarations or include FOCUS DBA attributes.
• You require options such as DEFINE commands and KEYORDER.

Common Errors

AUTODB2, AUTOSQL, and AUTODBC provide an easy, straightforward approach for defining tables and views to FOCUS. Most errors occur at the data entry level and result in error messages. Many fields trigger validation tests that prompt you for a valid entry. Common errors include:

• Leaving the Master Filename entry field blank on the AUTODB2/SQL initial screen or AUTODBC Screen E1.
• For AUTODB2 or AUTOSQL, specifying an invalid userid for the Creator Name entry field.

For AUTODBC, specifying an invalid user ID or password on the security logon screen. If you are creating the ADUCOL repository and your security profile is incorrect, you receive the FOCUS message:

TERADATA ID OR PASSWORD INCORRECT: ENTER 'Y' TO CONTINUE

After you press Y, the Primary Option Menu appears. Select Option 4 and correct your specified values or select Option 5 to exit the facility.

• For AUTODB2 and AUTOSQL, leaving the Table Name entry field blank or specifying a nonexistent table or view. In these cases, no records are retrieved and you return to the Main Menu.

For AUTODBC, specifying a nonexistent table or view when you are creating the ADUCOL repository.

• Specifying an invalid character for the Read/Write Functionality entry field.
• Specifying a duplicate member name or file name for the Master Filename entry field. If the REPLACE option is specified, AUTODB2, AUTOSQL, and AUTODBC overwrite the existing members or files.

At the Status Screen level, you may see one of the following warning messages:

• n DUPLICATE. The tables or views contain duplicate column names. You can edit these field names to make them unique, or qualify them with the segment name when referencing them in requests.
• n UNSUPPORTED. The table or view contains some columns with data types not supported by FOCUS (LONG VARGRAPHIC, for example).
• For AUTODBC:

NO KEY DETECTED. The table or view does not possess a primary key. Edit the generated file descriptions accordingly to specify a primary key column.

ALL DATATYPES WITHIN TARGET SQL TABLE ARE UNSUPPORTED. All of the columns possess data types that AUTODBC does not support.

The status screen also displays the name of the error log (either a MVS data set or CMS file) that AUTODBC creates when errors occur.

Other circumstances can also affect processing:

• In MVS, permanent PDSs are too small to receive the generated file descriptions as members.
• In CMS, there is not enough disk space on the A disk.
• You do not have proper authorization for SELECT privileges on the system catalog tables.

AUTODB2 Sample Session

This sample session executes the AUTODB2 facility to generate Master and Access Files based on the existing EMPINFO, ADDRESS, and PAYINFO tables; it assigns the name AUTOEMP to the new file descriptions.

Note: Lowercase values represent user input.

First, execute the AUTODB2 FOCEXEC from the FOCUS command line:

>ex autodb2

AUTODB2 displays the Main Menu with its pre-set defaults.

Type autoemp in the Master Filename entry field and press Enter:

Main Menu Master File Generation Facility for DB2 Master Filename ================> AUTOEMP Location => Creator => USER1 Table => * Location values: Database Name ================> * Description will be a member of: Master Target PDS => USER1.MASTER.DATA Access Target PDS => USER1.FOCSQL.DATA FOCDEF Target PDS => USER1.FOCDEF.DATA Replace Existing Description?=> N (Y/N) Read/Write Functionality =====> W (R=Read,W=Write) Date Display Format ==========> YYMD Time Stamp Display Format ====> HYYMDm Time Display Format ==========> HHIS Display Decimal when SCALE=0?=> Y (Y/N) Use LABEL as Column Heading? => N (Y/N) Use Remarks for FOCDEF? ======> N (Y/N) Use Creator Name in AFD? =====> Y (Y/N) Use Long Fieldnames? =========> Y (Y/N) Parm File => USER1.FOCSQL.DATA PF1=Help PF2=Restart PF3=Exit PF4=Log PF5=MFD PF6=AFD PF9=Picture F10=List

The default values in the creator, table, and database name fields establish a pattern that selects all tables for creator USER1. AUTODB2 indicates that it is retrieving table information from the catalog:

**=============================================================** ** AUTODB2 is retrieving TABLE information from catalog. ** ** Please wait... ** **===============================================================**

Next, AUTODB2 presents the Table Selection Screen. Designate EMPINFO as the root by placing r in its Select column; designate ADDRESS and PAYINFO as children by placing c in their Select columns:

Master: Master File Generation Facility for DB2 AUTOEMP ==Table Selection== Place an 'R' next to the Table to be the root of the Master. Place a 'C' next to all other Tables to be described as children. Enter 'Y' next to all selected Tables that will be updated. Location Creator Name DB2 Table Name Select (R/C) Write (Y/N) -------- ------------ -------------- ------------ ----------- USER1 ADDRESS c Y USER1 DEDUCT Y USER1 EMPINFO r Y USER1 FUNDTRAN Y USER1 PAYINFO c Y USER1 SALINFO Y PF1=Help PF3=End PF4=Add Tables PF7=Up F8=Down

AUTODB2 indicates that it is gathering column information about those tables:

**=============================================================** ** AUTODB2 is retrieving COLUMN information from catalog. ** ** Please wait... ** **===============================================================**

Now, AUTODB2 displays a Child Selection Screen for the root table, EMPINFO. To define ADDRESS and PAYINFO as children of EMPINFO, place c in their Select columns:

Master: Master File Generation Facility for DB2 AUTOEMP ==Child Selection== Place a 'C' next to the descendants of Parent: USER1 EMPINFO Location Creator Name DB2 Table Name Select (C) -------- ------------ -------------- ------------ USER1 ADDRESS c USER1 PAYINFO c PF1=Help PF2=Restart PF3=End PF4=None PF5=Picture PF7=Up PF8=Down

In a more complicated structure, some of the child tables could be descendants of other child tables; for example, PAYINFO could be a child of EMPINFO, and ADDRESS could be a child of PAYINFO. When necessary, AUTODB2 displays additional Child Selection Screens. However, in this example, all tables in the structure have been accounted for, so no additional Child Selection Screens display.

Next, identify the primary key from EMPINFO and the foreign key from ADDRESS on the Common Column Selection Screen. Identify EID as the primary key for the EMPINFO table by placing 1 in its Key column. Indicate that EID is the corresponding foreign key in the ADDRESS table by placing 1 in its Key column:

Master: Master File Generation Facility for DB2 AUTOEMP ==Common Column Selection== Number the primary key columns | Number the corresponding foreign (in sequence) in the Parent Table: | key columns in the Child Table: USER1 EMPINFO | USER1 ADDRESS | Key Column Name Format | Key Column Name Format ----------------------------------------------------------------------- 1 EID A9 | 1 EID A9 LN A15 | AT A4 FN A10 | LN1 A20 HDT YYMD | LN2 A20 DPT A10 | LN3 A20 CSAL P9.2 | ANO I9 CJC A3 | OJT F9.2 | BONUS_PLAN I9 | HDTT HYYMDm | HT HHIS | | PF1=Help PF2=Restart PF3=End PF4=Skip PF7=Up PF8=Down

Since no other fields participate in the relationship, press Enter.

AUTODB2 displays another Common Column Selection Screen, for the EMPINFO and PAYINFO tables. Identify the primary and foreign keys that relate those two tables:

Master: Master File Generation Facility for DB2 AUTOEMP ==Common Column Selection== Number the primary key columns | Number the corresponding foreign (in sequence) in the Parent Table: | key columns in the Child Table: USER1 EMPINFO | USER1 PAYINFO | Key Column Name Format | Key Column Name Format --------------------------------------------------------------------- 1 EID A9 | 1 EID A9 LN A15 | DI YYMD FN A10 | PI F9.2 HDT YYMD | SAL D12.2 DPT A10 | JBC A3 CSAL P9.2 | CJC A3 | OJT F9.2 | BONUS_PLAN I9 | HDTT HYYMDm | HT HHIS | | PF1=Help PF2=Restart PF3=End PF4=Skip PF7=Up PF8=Down

Press the Enter key.

At this point, AUTODB2 creates the Master and Access Files. The Main Menu includes the "DESCRIPTION CREATED" message at the bottom of the screen:

Main Menu Master File Generation Facility for DB2 Master Filename ================> AUTOEMP Location => Creator => USER1 Table => * Location values: Database Name ================> * Description will be a member of: Master Target PDS => USER1.MASTER.DATA Access Target PDS => USER1.FOCSQL.DATA FOCDEF Target PDS => USER1.FOCDEF.DATA Replace Existing Description?=> N (Y/N) Read/Write Functionality =====> W (R=Read,W=Write) Date Display Format ==========> YYMD Time Stamp Display Format ====> HYYMDm Time Display Format ==========> HHIS Display Decimal when SCALE=0?=> Y (Y/N) Use LABEL as Column Heading? => N (Y/N) Use Remarks for FOCDEF? ======> N (Y/N) Use Creator Name in AFD? =====> Y (Y/N) Use Long Fieldnames? =========> Y (Y/N) Parm File => USER1.FOCSQL.DATA DESCRIPTION CREATED-3 DUPLICATE PF1=Help PF2=Restart PF3=Exit PF4=Log PF5=MFD PF6=AFD PF9=Picture F10=List

Three duplicate field names were created. Either edit them to be distinct, or qualify them with the segment name in requests.

To see a picture of the structure created, press PF9:

 NUMBER OF ERRORS=     0
 NUMBER OF SEGMENTS=   3  ( REAL=    3  VIRTUAL=   0 )
 NUMBER OF FIELDS=    22  INDEXES=   0  FILES=     1
 TOTAL LENGTH OF ALL FIELDS=  186
SECTION 01
             STRUCTURE OF SQLDS    FILE AUTOEMP  ON 01/29/02 AT 16.23.36
 
         EMPINFO
 01      S0
***************
*EID         **
*LN          **
*FN          **
*HDT         **
*            **
***************
 **************
       I
       +-----------------+
       I                 I
       I ADDRESS         I PAYINFO
 02    I S0        03    I S0
**************    ***************
*EID         **   *EID         **
*AT          **   *DI          **
*LN1         **   *PI          **
*LN2         **   *SAL         **
*            **   *            **
***************   ***************
 **************    **************
 
TYPE ANY CHARACTER AND PRESS ENTER TO CONTINUE  >

As indicated on the screen, type any character and press Enter to continue.

To edit The AUTOEMP Master File (member AUTOEMP in data set USER1.MASTER.DATA), press PF5:

$$$ CREATED BY AUTODB2 ON 01/29/02 AT 16.20.50 BY USER1
FILENAME=AUTOEMP,SUFFIX=SQLDS,$
 
SEGNAME='EMPINFO',SEGTYPE=S0,$
FIELD=EID               ,EID              ,A9     ,A9    ,MISSING=OFF,$
FIELD=LN                ,LN               ,A15    ,A15   ,MISSING=OFF,$
FIELD=FN                ,FN               ,A10    ,A10   ,MISSING=OFF,$
FIELD=HDT               ,HDT              ,YYMD   ,DATE  ,MISSING=OFF,$
FIELD=DPT               ,DPT              ,A10    ,A10   ,MISSING=ON,$ 
FIELD=CSAL              ,CSAL             ,P9.2   ,P4    ,MISSING=OFF,$
FIELD=CJC               ,CJC              ,A3     ,A3    ,MISSING=OFF,$
FIELD=OJT               ,OJT              ,F9.2   ,F4    ,MISSING=ON,$ 
FIELD=BONUS_PLAN        ,BONUS_PLAN       ,I9     ,I4    ,MISSING=OFF,$
FIELD=HDTT              ,HDTT             ,HYYMDm ,HYYMDm,MISSING=OFF,$
FIELD=HT                ,HT               ,HHIS   ,HHIS  ,MISSING=OFF,$
 
SEGNAME='ADDRESS',SEGTYPE=S0,PARENT='EMPINFO',$
FIELD=EID               ,EID              ,A9     ,A9    ,MISSING=OFF,$
FIELD=AT                ,AT               ,A4     ,A4    ,MISSING=OFF,$
FIELD=LN1               ,LN1              ,A20    ,A20   ,MISSING=OFF,$
FIELD=LN2               ,LN2              ,A20    ,A20   ,MISSING=OFF,$
FIELD=LN3               ,LN3              ,A20    ,A20   ,MISSING=OFF,$
FIELD=ANO               ,ANO              ,I9     ,I4    ,MISSING=OFF,$
 
SEGNAME='PAYINFO',SEGTYPE=S0,PARENT='EMPINFO',$
FIELD=EID               ,EID              ,A9     ,A9    ,MISSING=OFF,$
FIELD=DI                ,DI               ,YYMD   ,DATE  ,MISSING=OFF,$
FIELD=PI                ,PI               ,F9.2   ,F4    ,MISSING=OFF,$
FIELD=SAL               ,SAL              ,D12.2  ,D8    ,MISSING=OFF,$
FIELD=JBC               ,JBC              ,A3     ,A3    ,MISSING=OFF,$
$DUPLICATE=EID               ,COUNT= 3,SEGNAME=EMPINFO
$DUPLICATE=EID               ,COUNT= 3,SEGNAME=ADDRESS
$DUPLICATE=EID               ,COUNT= 3,SEGNAME=PAYINFO

To exit from the FOCUS TED editor, press PF3, or type FILE to save any changes you made.

To edit the AUTOEMP Access File (member AUTOEMP in data set USER1.FOCSQL.DATA), press PF6. Notice the KEYFLD and IXFLD values that define the embedded JOIN:

$$$ CREATED BY AUTODB2 ON 01/29/02 AT 16.20.50 BY USER1
$$$ FILENAME=AUTOEMP,SUFFIX=SQLDS,$
 
SEGNAME='EMPINFO',TABLENAME='"USER1"."EMPINFO"',
KEYS=01,WRITE=YES,KEYORDER=LOW,$
 
SEGNAME='ADDRESS',TABLENAME='"USER1"."ADDRESS"',
KEYS=02,WRITE=YES,KEYORDER=LOW,
KEYFLD=EID,
IXFLD=EID,$
 
SEGNAME='PAYINFO',TABLENAME='"USER1"."PAYINFO"',
KEYS=02,WRITE=YES,KEYORDER=LOW,
KEYFLD=EID,
IXFLD=EID,$

To exit from the FOCUS TED editor, press PF3, or type FILE to save any changes you made.

AUTODBC Sample Session

This section describes a sample TSO session where the AUTODBC facility generates Master File and Access Files based on existing tables DPBRANCH, DPVENDOR, and DPINVENT. (See File Descriptions and Tables, for DBC table definitions.) The new file descriptions, created as a result of this sample session, will be named DEMO for demonstration. Session results are provided after the status screen at the end of this section.

Note: Lowercase values represent user input; uppercase values represent TSO or AUTODBC responses.

First, the AUTODBC CLIST is executed from the TSO command level:

READY
ex autodbc

The security logon screen displays with its defaults as shown below:

A ------------------------------------------------------- | A U T O D B C | | RELATIONAL TABLE DESCRIPTOR FACILITY FOR FOCUS/DBC | | INFORMATION BUILDERS, INCORPORATED | -------------------------------------------------------- TERADATA USERID =====> jane TERADATA PASSWORD ====> TERADATA DIRECTOR PGM => 0 TERADATA PARTITION ID => DBC/SQL SUPPLY THE REQUIRED TERADATA LOGON INFORMATION ENTER= PROCESS PF3= EXIT (INITIAL ENTRY ONLY)

The Teradata user ID and password (suppressed display) are specified. AUTODBC provides the default values, 0 and DBC/SQL, for the Teradata Director Program ID and Partition ID entry fields.

The Enter key is pressed to continue and the Primary Option Menu appears.

When the Primary Option Menu displays, ADUCOL MAINTENANCE, Option 2, is selected. Screen D appears blank for this session, since the ADUCOL repository does not exist.

D ------------------------------------------------------ | A U T O D B C | | ADUCOL MAINTENANCE | ------------------------------------------------------- IDENTIFY RELATION DESCRIPTIONS TO BE REFRESHED WITHIN ADUCOL: DATABASENAMES TABLENAMES --------------------------- --------------------------------- => 1 JANE DPBRANCH => 2 JANE DPVENDOR => 3 JANE DPINVENT => => => => => => => PF3= RETURN PF7= TOP/REVIEW PF8= DOWN PF12= PROCESS

Three table entries are specified on the blank screen. The database names for this session are JANE. Then the PF7 key is pressed to review the entries. Each entry is now numbered and in uppercase.

The PF12 key is pressed to construct the ADUCOL repository.

When processing is complete, the Primary Option Menu reappears. Option 3 is selected to generate a multi-table Master File and a corresponding Access File.

The Master and Access File Generation screen, Screen E1, displays with its defaults.

E1 ----------------------------------------------------- | A U T O D B C | | MASTER AND ACCESS FILE GENERATION | ----------------------------------------------------- MASTER/ACCESS FILENAME ==> demo PROCESSING OPTION =======> 2 OPTIONS: 1= NEW DESCRIPTION ONLY 2= NEW OR REPLACE USE LONG FIELD NAMES ===> y SUPPLY THE REQUIRED VALUES PF3= RETURN ENTER= PROCESS

DEMO is specified as the name for the new file descriptions. Instead of the default, Option 2 is specified to replace existing DEMO file descriptions. To incorporate long fieldnames into the Master File, Y is specified.

The Enter key is pressed.

Screen E2 appears and lists all tables available for file generation. At this point, tables are selected for the file descriptions. Extra tables are excluded by using the space bar to "blank out" the database names. However, for this session, all three tables are required for the DEMO file descriptions.

E2 --------------------------------------------------- | A U T O D B C | | MASTER AND ACCESS FILE GENERATION | --------------------------------------------------- IDENTIFY THOSE RELATIONS PARTICIPATING IN THE FOCUS VIEW: DATABASENAMES TABLENAMES WRITE= ------------------------- -------------------------- ------ => 1 JANE DPBRANCH N => 2 JANE DPVENDOR N => 3 JANE DPINVENT N => => => => => => => PF3= RETURN PF7= TOP/REVIEW PF8= DOWN PF12= PROCESS

WRITE functionality remains unchanged. The default (N) indicates read-only access. The resulting DEMO file descriptions may be used only for reporting.

After the PF12 key is pressed, a STAND BY message displays.

Table relationships are specified on the next screen, Screen E3:

E3 -------------------------------------------------------- | A U T O D B C | | MASTER AND ACCESS FILE MAINTENANCE | -------------------------------------------------------- IDENTIFY THE RELATIONSHIP FROM AMONG SELECTED RELATIONS: CHILD DATABASENAMES TABLENAMES OF ------------------------ -------------------------- ------ => 1 JANE DPBRANCH => 2 JANE DPVENDOR 3 => 3 JANE DPINVENT 1 => => => => => => => PF3= RETURN PF7= TOP/REVIEW PF8= DOWN PF12= PROCESS

The DPBRANCH table acts as the root; its "Child of" column is left blank. The DPBRANCH table has the DPINVENT table as a descendent. The value 1 in the "Child of" column for the DPINVENT table indicates that its parent is DPBRANCH. The DPINVENT table has the DPVENDOR table as a descendent. The value 3 in the "Child of" column for the DPVENDOR table indicates that its parent is DPINVENT.

This produces a single-path structure. The PF12 key is pressed to continue.

The next screen, Screen E4, prompts for the common field (either primary key column or foreign key column) that will perform the embedded JOIN. The screen appears twice for each parent-child relationship. It displays columns, data types, and column lengths for the current table, denoted by the caret (>).

E4 ------------------------------------------------------- | A U T O D B C | | MASTER AND ACCESS FILE GENERATION | -------------------------------------------------------- IDENTIFY THE PRIMARY/FOREIGN (KEYFLD/IXFLD) RELATIONSHIP FOR: PARENT RELATION ==> DPINVENT > CHILD RELATION ===> DPVENDOR -POSITION-COLUMNNAME- -DATATYPE- => VENDOR_CITY CF 5 => VENDOR_NAME CF 5 => 1 VENDOR_NUMBER I2 2 => => => => => => => PF3= RETURN PF7= TOP/REVIEW PF8= DOWN PF12= PROCESS

In this session, Screen E4 displays the contents of the DPVENDOR table, as denoted by the caret (>) symbol. The DPVENDOR table acts as the descendent and the VENDOR_NUMBER column is specified as the foreign key.

When the PF12 key is pressed, the same screen appears. It displays the contents of the parent table, DPINVENT. The DPINVENT table also contains a VENDOR_NUMBER column and it is specified as the primary key.

The PF12 key repeats the screen two more times. It displays the contents of the DPBRANCH and DPINVENT tables. The BRANCH_NUMBER column that exists in both tables is specified as the common column.

After the common columns are selected for each relationship, the PF12 key is pressed to generate the file descriptions.

The CREATING MASTER and CREATING ACCESS FILE messages display while AUTODBC generates the file descriptions. The status screen, Screen E5, appears when the process is complete.

E5 ------------------------------------------------------- | A U T O D B C | | MASTER AND ACCESS FILE GENERATION | ------------------------------------------------------- AUTODBC SUMMARY STATISTICS: MASTER FILE DESCRIPTION ===> DEMO IN PO DDNAME ADUMAST ACCESS FILE DESCRIPTION ===> DEMO IN PO DDNAME ADUSQL NO ERRORS FOUND ENTER= CONTINUE

The AUTODBC facility successfully generates the DEMO Master and Access Files. They are stored in partitioned data sets allocated to ddnames ADUMAST and ADUSQL.

The Enter key is pressed to return to the Primary Option Menu.

This concludes the sample session. At the Primary Option Menu, users may exit the AUTODBC facility or continue to generate file descriptions for their applications.

Note:

• In TSO, the generated Master and Access Files are allocated to the ADUMAST and ADUSQL data sets. In order to use them, users should copy or move them to the MASTER.DATA and FOCDBC.DATA data sets after exiting the AUTODBC facility (Option 5).
• In CMS, the generated file descriptions are written to the A disk.

The resulting DEMO Master File describes a single-path structure. Notice the PARENT values and the Q002 field suffixes:

JANE.MASTER.DATA(DEMO)          SIZE=50    LINE=0
00000 * * * TOP OF FILE * * *
00001
00002
00003    FILENAME=DEMO  ,SUFFIX=SQLDBC,$
00004
00005
00006    SEGNAME=DPBRANCH,SEGTYPE=S0,$
00007
00009  FIELD=BRANCH_NUMBE,
00010    ALIAS=BRANCH_NUMBER,
00011    I5     ,I2     ,MISSING=OFF,$
00012  FIELD=BRANCH_NAME,
00013    ALIAS=BRANCH_NAME,
00014    A5     ,A5     ,MISSING=OFF,$
00015  FIELD=BRANCH_MANAG,
00016    ALIAS=BRANCH_MANAGER,
00017    A5     ,A5     ,MISSING=OFF,$
00018  FIELD=BRANCH_CITY,
00019    ALIAS=BRANCH_CITY,
00020    A5     ,A5     ,MISSING=OFF,$
00021  SEGNAME=DPINVENT,PARENT=DPBRANCH,SEGTYPE=S0,$
00023
00024  FIELD=BRANCH_NQ002,
00025    ALIAS=BRANCH_NUMBER,
00026    I5     ,I2     ,MISSING=OFF,$
00027  FIELD=VENDOR_NQ002,
00028    ALIAS=VENDOR_NUMBER,
00029    I5     ,I2     ,MISSING=OFF,$
00030  FIELD=PRODUCT,
00031    ALIAS=PRODUCT,
00032    A5     ,A5     ,MISSING=OFF,$
00033  FIELD=NUMBER_OF_UN,
00034    ALIAS=NUMBER_OF_UNITS,
00035    I5     ,I2     ,MISSING=OFF,$
00036  FIELD=PER_UNIT_VAL,
00037    ALIAS=PER_UNIT_VALUE,
00038    P10.2     ,P5     ,MISSING=OFF,$
00039  SEGNAME=DPVENDOR,PARENT=DPINVENT,SEGTYPE=S0,$
00041
00042  FIELD=VENDOR_NUMBE,
00043    ALIAS=VENDOR_NUMBER,
00044    I5     ,I2     ,MISSING=OFF,$
00045  FIELD=VENDOR_NAME,
00046    ALIAS=VENDOR_NAME,
00047    A5     ,A5     ,MISSING=OFF,$
00048  FIELD=VENDOR_CITY,
00049    ALIAS=VENDOR_CITY,
00050    A5     ,A5     ,MISSING=OFF,$
00051 * * * END OF FILE * * *

In the corresponding DEMO Access File, notice the KEYFLD and IXFLD values that perform the embedded JOINs.

JANE.FOCDBC.DATA(DEMO)          SIZE=34    LINE=0

00000 * * * TOP OF FILE * * *
00001
00002
00003
00004
00005    SEGNAME=DPBRANCH,
00006    
00007    TABLENAME=JANE.DPBRANCH,
00008
00009    KEYS=0 ,WRITE=NO,$
00010
00011
00012
00013    SEGNAME=DPVENDOR,
00014
00015    TABLENAME=JANE.DPVENDOR,
00016
00017    KEYS=0 ,WRITE=NO,
00018
00019    KEYFLD=VENDOR_NQ002,
00020
00021    IXFLD=VENDOR_NUMBER,$
00022
00023
00024
00025    SEGNAME=DPINVENT,
00026
00027    TABLENAME=JANE.DPINVENT,
00028
00029    KEYS=0 ,WRITE=NO,
00030
00031    KEYFLD=BRANCH_NUMBER,
00032
00033    IXFLD=BRANCH_NQ002,$
00034
00035 * * * END OF FILE * * *