AUTODBC

In this section:

The AUTODBC facility is a TSO CLIST and is invoked from TSO. In order to generate Master and Access Files, the AUTODBC facility gathers column and table information from the ADUCOL repository. The repository is a small FOCUS database that stores information extracted from the Teradata Data Dictionary/Directory. Using AUTODBC, you can create this required repository or reuse one retained from a previous AUTODBC session.

he ADUCOL repository provides the following advantages:

The file generation process is straightforward. After you invoke the AUTODBC facility and you supply your Teradata logon ID and password, the Primary Option Menu is displayed. The Primary Option Menu lists all the tasks or processes that may be performed from AUTODBC. If the repositories already exist, you can select the option that generates file descriptions and complete a series of screens. A status screen indicates successful completion of the process.

Note: You can create file descriptions that describe one or more tables or views. (See Multi-Table Structures.)

The resulting pair of file descriptions may be used immediately or edited to include options such as DEFINE fields. As with any Master File, additional security may be added by including FOCUS DBA security functions.


Top of page

x
How to Use AUTODBC

In this section:

Before you begin your AUTODBC session, you should be aware of these requirements:

If you have any questions about these requirements, contact your Teradata database administrator or consult the appropriate installation guide.

AUTODBC is a CLIST. Therefore, to execute the AUTODBC facility, invoke it from the TSO operating system (not from FOCUS):

EX AUTODBC

Press the Enter key to display the security logon screen.

Note: For TSO users, the execution syntax may be site-specific. Please ask your Teradata database administrator for the correct syntax if the command shown above does not invoke AUTODBC.



x
Security Logon Screen

The security logon screen, Screen A, opens after you invoke the facility or if you specify Option 4 from the Primary Option Menu, Screen B:

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

The entry fields and their acceptable values are:

USERID

Is your Teradata user ID.

PASSWORD

Is your Teradata password. (Its display is suppressed.)

DIRECTORPGMID

Is the single character value of the TDP ID. Specify a blank space instead of the default (0) if your site does not require a Teradata Director Program ID (TDPID) specification during logon.

PARTITIONID

Defaults to DBC/SQL.

These values constitute your security profile, which provides access to the Teradata Data Dictionary/Directory. Your security profile is stored in a work file that is erased when you exit the facility to prevent unauthorized use of your user ID.

Press the Enter key after you complete the logon screen or press PF3 to exit the facility.

Note: When you first invoke the AUTODBC facility, the PF3 key is valid for exiting from the logon screen. Otherwise, you must specify Option 5 on the Primary Option Menu to exit the facility and to delete the current session’s work files. On subsequent screens, the PF3 key functions as a return key that displays a previous screen.



x
Primary Option Menu

The Primary Option Menu, Screen B, appears after the initial logon screen and after each completed task or process:

 B           -------------------------------------------------
            |            A U T O D B C                         |
            |          PRIMARY OPTION MENU                     |
             -------------------------------------------------
 
 
 
            AVAILABLE OPTIONS:
 
            1. DISPLAY ADUCOL CONTENTS
            2. ADUCOL MAINTENANCE
            3. GENERATE FOCUS MASTER AND ACCESS FILE DESCRIPTION
            4. REDEFNE TERADATA SECURITY PROFILE
            5. EXIT THIS FACILITY
 
 
            OPTION ===>
 
 
  ENTER= PROCESS

Each task or process is assigned an option number. The options are:

  1. DISPLAY ADUCOL CONTENTS

    Displays the contents (database and table names) of the ADUCOL repository.

  2. ADUCOL MAINTENANCE

    Enables you to create the ADUCOL repository or add entries to it.

  3. GENERATE FILE DESCRIPTIONS

    Prompts you with a series of screens to generate single- or multi-table Master and Access Files.

  4. REDEFINE TERADATA SECURITY PROFILE

    Enables you to correct or change your security profile.

  5. EXIT THIS FACILITY

    Enables you to save repositories and to exit the facility.

At this point, you may select tasks or processes in any order. Specify the option number and press the Enter key.

Subsequent sections explaining processes and sample screens are discussed by option number.



x
Option 1: Displaying ADUCOL Contents

In order to display the ADUCOL contents, select Option 1 from the Primary Option Menu and press the Enter key. The PAUSE message prompts you to press the Enter key again.

The AUTODBC facility produces a report of tables and views sorted by database names. FOCUS Hot Screen PF keys and commands are available. For example, you may scroll through the report or print it offline.

When you are finished, press either the Enter key or the PF3 key to return to the Primary Option Menu.



x
Option 2: Maintaining the ADUCOL

To create the ADUCOL repository or add entries to it, choose Option 2 and press the Enter key. The ADUCOL Maintenance screen, Screen D, appears as:

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

For each table or view, type the database name and table name in the appropriate columns. To move the cursor to a different column, use the Tab keys. To expand the input area for additional entries, scroll down using the PF8 key.

Press the PF7 key to review the entries. If the entries are correct, press the PF12 key to begin processing. When processing is successful, the Primary Option Menu is displayed again.

AUTODBC processing involves:

Note:



x
Option 3: Generating Master and Access Files

The AUTODBC file generation process varies, depending on the type of file description you intend to generate. If you intend to describe one table or view as a single-table Master File, you need to complete three screens. If you intend to generate a multi-table Master File (an embedded JOIN), you need to complete three screens, plus additional screens that indicate primary and foreign keys.

If the ADUCOL repository exists, select Option 3 from the Primary Option Menu and press the Enter key. If the repository does not exist, you must create it. (See Option 2: Maintaining the ADUCOL.)

The Master and Access File Generation screen, Screen E1, displays as:

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

The entry fields and their acceptable values are:

MASTER/ACCESS FILENAME

Is the member name or file name for the new Master File and Access Files, up to eight characters in length.

PROCESSING OPTION

Is 1 by default. Option 2 replaces current Master and Access Files with new ones. Option 1 does not. (An error message appears for Option 1 if file descriptions exist and you may specify another name or select Option 2.)

LONG FIELDNAMES

Is no (N) by default. Field names are truncated at 12 characters, alias names are blank in the Master File, and partial field declarations appear in the Access File. Yes (Y) includes aliases up to 30 characters in the Master File. (See Additional Topics, for long field name limitations.)

Press the Enter key to continue or the PF3 key to return to the Primary Option Menu.

Another screen, Screen E2, displays and lists the contents of the ADUCOL repository.

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

On this screen, choose the tables or views for processing and exclude any unwanted tables. For each table, indicate WRITE functionality by specifying Y or N in the WRITE= column. The default No (N) permits read access for both reporting and MODIFY or Maintain browsing. Yes (Y) permits read-write access for MODIFY and Maintain operations.

To exclude a table and remove it from the screen, use the space bar to erase (blank out) the database name. This does not affect the ADUCOL repository. The tables or views that remain on this screen are described in the resulting Master and Access Files.

Before you continue, press the PF7 key to review your choices and to refresh your screen. Extra or unwanted tables are omitted from the screen. Press the PF12 key to continue.

Note:

  • You may describe up to 1024 tables or views in a multi-table Master File.
  • Table entries cannot be added at this point. If you attempt to do so and press the PF12 key, an error message COLUMN DATA table NOT FOUND appears.

After the tables are selected, the next screen, Screen E3, displays.

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

This screen prompts you for table relationships. Each table entry is numbered on the left side of the screen. For each table, specify the number of its parent in the "Child of" column. Leave a blank for the first parent (the root) of the Master File.

If the screen displays one table for a single-table Master File, leave the "Child of " column blank and press the PF12 key.

If you are describing a multi-path structure, specify the number of the parent as many times as necessary.

To review your choices, press the PF7 key. Press the PF12 key to continue.

At this point, either a status screen indicates the successful generation of a pair of single-table file descriptions or additional screens prompt you for primary and foreign keys.

For a multi-table Master File, you must specify the primary and foreign keys (common columns that perform the embedded join) for each parent-dependent relationship. You may also specify multi-field embedded joins. For embedded join concepts and limitations, see Multi-Table Structures.

The following screen, Screen E4, appears twice—once for the dependent and then for the parent table. It displays the column names, data types, and column lengths for the table marked with the caret (>) symbol.

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

In the Position column on the screen, select the common field and number it 1. If another common field exists, mark it as 2, the next as 3, and so on, up to a limit of 16. Press the PF12 key to display column information for each parent-dependent relationship. An error message appears if the data type does not match.

To return to a previous screen and change a selection, press the PF3 key. Press the PF7 key to review your selection. When all screens are completed, press the PF12 key and a CREATING MASTER message appears.

For a multi-field embedded join, you may number up to 16 common fields. You must specify the fields in the same order and you must have an equal number of specified fields.

Note: The AUTODBC facility determines the order in which the table relationships are displayed. The order is not necessarily a specific top-to-bottom, left-to-right progression.

When AUTODBC completes the file generation process, a status screen, Screen E5, appears. Besides indicating success, the status screen lists the new file descriptions and where they are stored. If errors occur, AUTODBC logs the errors in a z/OS data set which it creates, and it displays the log name on the status screen.

 E5          ---------------------------------------------------  
            |                  A U T O D B C                    |
            |          MASTER AND ACCESS FILE GENERATION        |
             ---------------------------------------------------  
    AUTODBC SMMARY STATISTICS:
    MASTER FILE DESCRIPTION ===>
    ACCESS FILE DESCRIPTION ===>
            NO ERRORS FOUND
   ENTER= CONTINUE

AUTODBC writes the new Master and Access Files to Partitioned data sets as members. Partitioned data sets are allocated to ddnames ADUMAST and ADUSQL (Master and Access, respectively). If the ddnames are not allocated, the file descriptions are stored in data sets named ADUMAST.DATA and ADUSQL.DATA. If neither the ddnames nor data set names are available, AUTODBC dynamically allocates them.

To use the generated Master and Access Files, copy them from the ADUMAST and ADUSQL datasets to the MASTER.DATA and FOCDBC.DATA data sets after you exit the AUTODBC facility (Option 5).

To return to the Primary Option Menu, press the Enter key.

Newly generated file descriptions are discussed in Results of the Master File Generation Facilities.



x
Option 4: Redefining Your Teradata Security Profile

To redefine your security profile, select Option 4 from the Primary Option Menu and press the Enter key. For an explanation of the sample screen, see Security Logon Screen.



x
Option 5: Exiting AUTODBC

To exit the AUTODBC facility, select Option 5 from the Primary Option Menu and press the Enter key. The Utility Termination Options screen, Screen F, appears before you return to TSO:

 F           --------------------------------------------------
            |                A U T O D B C                     |
            |          UTILITY TERMINATION OPTIONS             |
             --------------------------------------------------
    DISPOSITION OPTIONS IN AUTODBC FILE SUPPORT:
        1. REATIN ADUCOL ONLY
        2. RETAIN ADUCOL AND ADUTMP ONLY
        3. SCRATCH ALL AUTODBC MAINTENANCE FILES
        OPTION ===>
  ENTER= PROCESS

You may retain one or both repositories for future AUTODBC sessions or erase the repositories and the current session's work files. The options are:

  1. RETAIN ADUCOL ONLY

    Saves the ADUCOL repository as ADU.ADUCOL.DATA.

  2. RETAIN ADUCOL AND ADUTMP ONLY

    Saves both ADUCOL and ADUTMP repositories. The ADUTMP repository is saved as ADU.ADUTMP.DATA. This option is recommended.

  3. SCRATCH ALL AUTODBC MAINTENANCE FILES

    Erases the repositories and temporary work files. TSO ADUMAST and ADUSQL data sets that store the generated file descriptions are not affected.

Note: If you did not proceed beyond the initial logon screen, press the PF3 key to return to the TSO environment.


Information Builders