PLEASE ENTER A VALID LOCATION.

x

Create a list of valid locations by editing the following lines in the AUTODB2 FOCEXEC. Uncomment the -DEFAULT command and enter up to six location names. The start and end of the list must be enclosed in single quotation marks, and each name must be eight characters long, padded on the right with blanks if necessary:

-*=======================================================================
-* Change &LOC_LIST to contain possible location values, padded to 8
-* characters, separated by commas.  The entire string is enclosed in
-* single quotes.  Include a maximum of 6 locations.  The first entry is
-* the default. Example:
-* -DEFAULT &LOC_LIST='PROD    ,TEST    ,DEVELOPM'
-*                  <------>,<------>,<------>,<------>,<------>,<------>
-DEFAULT &LOC_LIST='

A blank location name designates the location where the plan was bound.

Top of page

Example: Creating a Location List

With the following list, blank will be the default value that appears on the main menu.

-DEFAULT &LOC_LIST='        ,LOCDSNA ,LOCDSNC '

Note: Specifying blank (' ') as the first option indicates that the default is the location where the plan was bound.

EX AUTODB2

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:
           USER01                      |            USER01              
          EMPINFO                                                       
                           ADDRESS                                      
 Key    Column Name                    | Key     Column Name            
---------------------------------------------------------------------------
      EID                              |       EID                      
      LN                               |       AT                        
      FN                               |       LN1                       
      HDT                              |       LN2                       
    FORMAT: A9                         |    Format: A9                   
    FORMAT: A15                        |    Format: A4                   
    FORMAT: A10                        |    Format: A20                  
    FORMAT: YYMD                       |    Format: A20                  
                                                                         
PF1=Help  PF2=Restart  PF3=End  PF4=Skip            PF7=Up   PF8=Down

x

The following is an example of the Main Menu for AUTODB2. Complete the entry fields on the Main Menu and press Enter to begin processing:

Main Menu     Master File Generation Facility for DB2                      
      Master Filename ================> autoemp                            
 Location => locdsna     Creator => USER01                                 
 TABLE => *                                                                
 Location values:          ,LOCDSNA ,LOCDB9A                               
        DATABASE NAME ================> *                                  
      Description will be a member of:                                     
        Master Target PDS => USER01.MASTER.DATA                            
        Access Target PDS => USER01.FOCSQL.DATA                            
        FOCDEF Target PDS => USER01.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 => USER01.FOCSQL.DATA                                    
                                                                           
PF1=Help PF2=Restart PF3=Exit PF4=Log PF5=MFD PF6=AFD PF9=Picture PF10=List

The following list describes the Main Menu entry fields:

Location

Is one of the values on the list of location values displayed immediately below this entry field. The first location on the list is displayed initially.

Table, column, and index information is retrieved from the catalog tables for the selected location. The location is included in a three-part table name in the Access File, if it is not blank.

The menu option 'Use Creator Name in AFD?=N' is ignored when a non-blank location is provided. That is, the creator is included in the three-part table name even when you specify to not include it on the main menu.

If the first option is blank (' '), the default is the location where the plan was bound.

Master Filename

Is the 1- to 8-character name you select for referring to the data in requests. This name must be a valid member name.

Creator Name (or *)

Is the 1- to 8-character creator name of the tables that you want to describe. Specify an asterisk (*) to select tables for all creators. The default is your user ID.

You can use the asterisk (*) wildcard character in the creator, table, and database name entry fields to create a list based on the provided pattern.

Note: You cannot enter a name that contains a dollar sign ($) in the creator, table name, or database name entry fields; however, with the asterisk (*) wildcard character, you can generate a list that includes names containing dollar signs. You can then choose tables from this list.

Table Name (or *)

Is the 1- to 18-character name of an existing table that you want to describe. An asterisk (*), the default, selects all tables.

Database Name (or *)

Is the 1- to 8-character name of the database that contains any or all tables you may select. An asterisk (*), the default, selects all databases. You can omit this value if you provide creator or table.

Master Target PDS

Is the fully-qualified data set name of the Master File PDS in which to store the Master File. Do not use quotation marks (single or double) in the data set name. The default is 'userid.MASTER.DATA'.

Access Target PDS

Is the fully-qualified data set name of the Access File PDS in which to store the Access File. Do not use quotation marks (single or double) in the data set name. The default is 'userid.FOCSQL.DATA'.

FOCDEF Target PDS

Is required only if you specify Yes (Y) in the "Use REMARKS for FOCDEF?" field. Is the fully-qualified data set name of the FOCDEF File PDS in which to store the TableTalk® Help file. Do not use quotation marks (single or double) in the data set name. The default is 'userid.FOCDEF.DATA'.

Replace Existing Description?

Specifies whether to overwrite existing Master and Access Files (Y/N). No (N) is the default. Yes (Y) replaces existing descriptions of the same name.

Read/Write Functionality

Indicates read-only or read/write access in the Access File. Read/Write (W), the default, allows MODIFY and MAINTAIN to update the RDBMS table. Read (R) is for reporting only. Note: If the adapter was installed for read-only access, this field displays an R and cannot be changed.

Date Display Format

Is a valid FOCUS USAGE date format for RDBMS columns described as dates. The default is YYMD.

Time Stamp Display Format

Is HYYMDm by default. A26 is also acceptable, for compatibility with prior releases.

Time Display Format

Is HHIS by default. A8 is also acceptable, for compatibility with prior releases.

Display Decimal when SCALE=0?

Yes (Y), the default, displays the decimal point for DECIMAL values with no fractional component (for example 123.). No (N) excludes the decimal point (for example, 123).

Use LABEL as Column Heading?

Selects FOCUS report column headings. No (N), the default, uses the column name for headings. Yes (Y) uses the RDBMS LABEL.

Use Remarks for FOCDEF?

Indicates whether to include RDBMS REMARKS as HELP for TableTalk. No (N), the default, excludes the remarks. Yes (Y) includes them. You must specify a FOCDEF Target PDS in order to select Y.

Use Creator Name in AFD?

Indicates whether to include the creator name in the TABLENAME attribute of the Access File. Yes (Y), the default, includes the creator name. No (N) includes only the table name.

x

You can implement the following functions from the Main Menu with PFkeys:

Key	Function	Description
PF1	Help	Accesses on-line help.
PF2	Refresh the List of Tables	Clears the existing list of tables maintained by AUTODB2 for this session. When you select tables from the Main Menu, information is gathered from the system catalogs. Each time you change the selection criteria, the new tables are appended to the existing list. Pressing PF2 purges this list without exiting AUTODB2. You receive the message "Enter new table selection criteria" when the list is successfully purged. No message is displayed if you have not yet created a list of tables.
PF3	Exit	Ends the AUTODB2 session and returns to FOCUS.
PF4	Log Default Menu Parameters	Saves the values that you want to see displayed as defaults on the Main Menu in future executions of AUTODB2. Your defaults are saved in member DB2$PARM in the parm data set indicated on the menu. This data set is either the PDS pre-allocated to DDNAME DB2$PARM, 'userid.FOCSQL.DATA', or the first data set allocated to DDNAME FOCSQL, whichever the system finds first (see The z/OS Parameter Log File for more detailed information on the search order). The following information is logged: Location Creator Name Table Name Database Name MFD Partitioned Data set Name AFD Partitioned Data set Name FOCDEF Partitioned Data set Name Replace existing description Read/Write Functionality Date Display Format Time Stamp Display Format Time Display Format Display Decimal when SCALE=0 Use LABEL as Column Heading Use REMARKS for FOCDEF Use Creator Name in AFD Note: The values you enter must pass all validation tests in order for the new default values to be logged.
PF5 PF6	TED MFD TED AFD	Allows you to edit, using TED, the Master (PF5) or Access (PF6) File whose name you entered (as mastername) in the Master Filename entry field on the Main Menu. You access member mastername in the data set entered as either Master Target PDS or Access Target PDS. Note: You can edit these files even if they were not created with AUTODB2.
PF9	Picture of MFD	Generates a diagram of the file entered in the Master Filename entry field on the Main Menu. After the picture is displayed, type any character and press Enter to return to the menu. To generate the picture in z/OS, the mastername entered as Master Filename must be a member of a data set allocated to DDNAME MASTER (the Master Target PDS is not used).
PF10	Table List	Displays a list of all tables that meet the screening criteria provided in Creator Name, Table Name, and Database Name.

Note: The PFkey options are available only if the values you enter on the screen pass all validation tests. See Common Errors for a discussion of common errors. See Using PFkeys From non-Main Menu Screens for PFkey options available on other screens.

After you specify the appropriate values on the initial screen, press Enter. AUTODB2 informs you that it is creating a list of tables with the following message:

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

It displays this message only for the first retrieval per AUTODB2 session, or when the selection criteria have changed since the previous retrieval within the session. AUTODB2 does not access the catalog a second time for the same immediate selections in a single session.

x

If you specified a location, the table selection screen displays the location parameters for the list of tables generated by your selections on the main menu.

When table information retrieval is complete, choose the tables to include in your Master and Access Files:

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.                   
                                                                              
                                                           Select  Write
 Location  Creator                                          (R/C)  (Y/N)
 --------  -------                                         ------  -----
           USER01                                                       
           USER01                                                       
           USER01                                                       
           USER01                                                       
DB2 TABLE: ADDRESS                                                     Y
DB2 TABLE: COURSE                                                      Y
DB2 TABLE: DATETIME                                                    Y
DB2 TABLE: DEDUCT                                                      Y
                                                                              
PF1=Help            PF3=End  PF4=Add Tables           PF7=Up  PF8=Down 

The Table Selection Screen displays, in alphabetical order, the names of all creator/table combinations that pass the selection criteria you provided on the Main Menu. You can choose up to 1024 tables to include in the description.

Identify the root table by placing r in its Select column. Choose all other tables to be included in the Master File by placing c in their Select columns. Indicate those tables that can be updated in this view by placing y in their Write columns.

Note:

• If the adapter was installed for read-only access, the Write column displays an N and cannot be changed.
• You may have to scroll through the list using the PF7 and PF8 keys in order to view all of the available tables.

The following message displays after you complete the table selections, if you selected at least one child table in addition to the root:

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

AUTODB2 retrieves column information from the catalog only the first time you select a particular table from the Table Selection Screen. You will not see this message when you make subsequent selections using the same tables. To erase this list and create a new one, return to the Main Menu with PF3, and restart with PF2.

x

When column information retrieval is complete, specify descendants, if any, for each table.

Note: You may have to scroll through the list using the PF7 and PF8 keys in order to view all of the available tables.

To give you the opportunity to identify all parent-child relationships, every table on your list, in turn, presents its own Child Selection Screen:

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

If the parent segment named at the top of the screen has no children, press PF4. Otherwise, place c in the Select column for each table that is a child of the parent table named at the top of the screen, and press Enter. Next, AUTODB2 displays a Common Column Selection Screen for each parent-child pair so you can identify the columns they share.

x

For each parent-child pair, identify the primary keys from the parent table and the foreign keys from the related table on the Common Column Selection Screen:

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: 
           USER01                      |            USER01               
          EMPINFO                                                        
                           ADDRESS                                       
 Key    Column Name                    | Key     Column Name             
-----------------------------------------------------------------------  
 1    EID                              |  1    EID                       
      LN                               |       AT                        
      FN                               |       LN1                       
      HDT                              |       LN2                       
    FORMAT: A9                         |    Format: A9                   
    FORMAT: A15                        |    Format: A4                   
    FORMAT: A10                        |    Format: A20                  
    FORMAT: YYMD                       |    Format: A20                  
                                                                         
PF1=Help  PF2=Restart  PF3=End  PF4=Skip        PF7=Up  PF8=Down

Note: To allow for the display of the maximum number of characters in the names of tables and other objects, additional information about these objects is displayed on lines below the list of names. For example, on the following screen, the formats for each table display on separate lines below the list of tables. On the left half of the screen, the format for the EID column is A9, for the LN column is A15, for the FN column is A10, and for the HDT column is YYMD:

Number the key fields from each table in sequence (from 1 to 16). Each primary key field must have the same format as its corresponding foreign key field. Use PF4 to skip this table if it was selected in error. This does not affect previous or subsequent selections. However, the "skipped" segments appear in subsequent lists when you return to the Child Selection Screen to define any additional paths in the hierarchy.

AUTODB2 guides you in describing the table relationships in top down, left to right order. Initially, it displays the root table and all of its selected children.

x

After you complete the Common Column Selection Screen for the root table, AUTODB2 displays a Child Selection Screen for the first child of the root. Assign children, if any, to this table.

Child selection continues down this first path until you press PF4 to select no descendants, or you exhaust the list of descendants. Only then does AUTODB2 display a Child Selection Screen for the second child of the root. This process continues until all tables have been assigned children or all possible descendants have been exhausted.

At this point, AUTODB2 generates the Master and Access Files and returns to the Main Menu. The message "DESCRIPTION CREATED" displays at the bottom of this Status Screen with an indication, if necessary, of how many duplicate field names were generated and how many unsupported data types were found.

Note: If duplicate field names were created, either edit them to be unique, or qualify them with the segment name when referencing them in requests.

For information about the newly generated file descriptions, see Results of the Master File Generation Facilities.

x

For each screen other than the Main Menu and Table Selection screens, you can erase your current selections and back up one screen with PF2. Pressing PF3 returns you to the Main Menu with all selections intact. To erase your selections from the Table Selection Screen, return to the Main Menu with PF3, and then restart with PF2.

From the Child Selection Screen, use PF5 to generate a diagram of your file structure. The description is created on disk and remains there even if you end the program with PF3. After the picture is displayed, type any character and press the Enter key to return to the menu. To generate the picture in z/OS, you must allocate the target master PDS to DDNAME MASTER. If a member with the selected master name exists in a data set concatenated in front of the target data set, the picture is generated from that member.

x

AUTODB2 maintains a temporary list of the Master Files generated during any one session. This list is refreshed at the beginning of each session and erased at the end of the session. You may retain the list by executing AUTODB2 (either on-line or in the background) with the following syntax

EX AUTODB2 MFDLIST=Y

The list resides in a temporary data set allocated to DDNAME AUTODB2L, with a disposition of MOD and record length of 114. It is the responsibility of the user to free or erase this file when it is no longer required. The file layout is:

Length	Columns	Description
8	1 - 8	Master Filename
8	9 - 16	Number of duplicate fieldnames
5	17 - 21	Number of unsupported data types
5	22 - 26	Number of long decimal fields truncated. This entry, required in prior releases, exists for upward compatibility.
44	27 - 70	Master Target PDS name
44	71 - 114	Access Target PDS name

x

With AUTODB2, you can save custom Main Menu defaults in a parameter log file for repeated use. You can also change the default data sets that display on the Main Menu.

To change the default data set names that appear the first time you execute AUTODB2, customize the following lines of code near the top of the AUTODB2 FOCEXEC. These are the only permanent data sets that AUTODB2 uses. All other data sets are temporary and are named by the system.

-SET &DSNP0=&USERID ||'.FOCSQL.DATA                     ';
-SET &DSNM0=&USERID ||'.MASTER.DATA                     ';
-SET &DSNF0=&USERID ||'.FOCSQL.DATA                     ';
-SET &DSND0=&USERID ||'.FOCDEF.DATA                      ';

Note: You must preserve the length of the data set name for each variable (the result of concatenating the userid with the string between the single quotation marks) at 44 characters. If necessary, pad the name with blanks to maintain the correct length. You may not change any other lines in the FOCEXEC.

x

The parameter log file, if there is one, is always a data set allocated to DDNAME DB2$PARM. z/OS identifies the parameter log file with the following steps:

1. If DDNAME DB2$PARM is allocated to a PDS prior to execution of AUTODB2, AUTODB2 uses member DB2$PARM as the parameter log file. If the member does not exist, AUTODB2 creates it. This allows the user to identify the "profile" data set prior to execution of AUTODB2 and is recommended for sites that have non-standard data set naming conventions.

   If this DDNAME is allocated to a sequential data set, AUTODB2 frees it, generates a message, and disables parameter logging.

   If DDNAME DB2$PARM is not allocated, AUTODB2 continues searching and attempts to allocate it in the steps that follow. AUTODB2 does not free this DDNAME upon exiting, assuming that it may be used again.

2. AUTODB2 allocates data set name 'userid.FOCSQL.DATA', the default name provided in the code as &DSNP0, as the parameter log file. If the default has been changed, it uses the new data set name. This assumes standard Information Builders naming conventions.

   This data set must be a PDS. If it is not, AUTODB2 displays a message and disables parameter logging.

3. If the first two steps fail to identify a parameter log file, AUTODB2 allocates the first data set allocated to DDNAME FOCSQL as the parameter log file. This assumes that the user's data set is allocated first in the concatenation of data sets to DDNAME FOCSQL.

   If the data set allocated to DDNAME FOCSQL is sequential, AUTODB2 displays a message and disables parameter logging.

   Note: AUTODB2 cannot function properly if the data set is not a PDS.

Parameter logging assumes that the user has write access to the target data set. An attempt to log parameters to a data set without write access results in a security abend.

x

• AUTODB2 is a FOCEXEC that has a corresponding Master File. The FOCEXEC and Master File must be from the same release of FOCUS. If you attempt to use the old version of the Master File with the new version of the FOCEXEC, the following message is generated:

  PROGRAM AND FILE DESCRIPTION DATES DO NOT MATCH: AUTODB2 date
  UNABLE TO EXECUTE AUTODB2 - PLEASE REINSTALL

  FOCEXEC execution is terminated, and you return to the FOCUS prompt.

• AUTODB2 no longer needs the DB2CAT Master and Access Files. These Master and Access Files remain on the distribution tape. Information Builders no longer certifies the accuracy of these Master and Access Files with future RDBMS catalog table changes.