The AUTODBC facility is a TSO CLIST or CMS EXEC and is invoked from TSO or CMS, respectively. 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. |
In this section: Option 1: Displaying ADUCOL Contents Option 2: Maintaining the ADUCOL Option 3: Generating Master and Access Files |
The 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 statements. As with any Master File, additional security may be added by including FOCUS DBA security functions.
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 in TSO and an EXEC in CMS. Therefore, to execute the AUTODBC facility, invoke it from the TSO or CMS operating system (not from MSO or 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.
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 ---------------------------------------------------- |
The entry fields and their acceptable values are:
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.
The Primary Option Menu, Screen B, appears after the initial logon screen and after each completed task or process:
B ------------------------------------------------- |
Each task or process is assigned an option number. The options are:
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.
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.
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 ------------------------------------------- |
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:
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 ------------------------------------------------- |
The entry fields and their acceptable values are:
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 --------------------------------------------------- |
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:
After the tables are selected, the next screen, Screen E3, displays.
E3 ---------------------------------------------------- |
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 or 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 ---------------------------------------------------- |
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, etc., 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 MVS data set or CMS file which it creates and displays the log name on the status screen.
E5 --------------------------------------------------- |
AUTODBC writes the new Master and Access Files to:
To use the generated Master and Access Files, copy them from the ADUMAST and ADUSQL datasets to the MASTER.DATA and FOCDBC.DATA datasets 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.
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.
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 or CMS:
F -------------------------------------------------- |
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:
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 or CMS environment.
Information Builders |