Joining database files: Summary of Rules

The following topics summarize the rules that you must follow when you join database files.

Rules for joining database files: Requirements

The principal requirements for join logical files are:

• Each join logical file must have:
  • Only one record format, with the JFILE keyword specified for it.
  • At least two physical file names specified on the JFILE keyword. (The physical file names on the JFILE keyword do not have to be different files.)
  • At least one join specification (J in position 17 with the JFLD keyword specified).
  • A maximum of 31 secondary files.
  • At least one field name with field use other than N (neither) at the field level.
• If only two physical files are specified for the JFILE keyword, the JOIN keyword is not required. Only one join specification can be included, and it joins the two physical files.
• If more than two physical files are specified for the JFILE keyword, the following rules apply:
  • The primary file must be the first file of the pair of files specified on the first JOIN keyword (the primary file can also be the first of the pair of files specified on other JOIN keywords).

    Note:

    Relative file numbers must be specified on the JOIN keyword and any JREF keyword when the same file name is specified twice on the JFILE keyword.

  • Every secondary file must be specified only once as the second file of the pair of files on the JOIN keyword. This means that for every secondary file on the JFILE keyword, one join specification must be included (two secondary files would mean two join specifications, three secondary files would mean three join specifications).
  • The order in which secondary files appear in join specifications must match the order in which they are specified on the JFILE keyword.

Rules for joining database files: Join Fields

The rules to remember about join fields are:

• Every physical file you are joining must be joined to another physical file by at least one join field. A join field is a field specified as a parameter value on the JFLD keyword in a join specification.
• Join fields (specified on the JFLD keyword) must have identical attributes (length, data type, and decimal positions) or be redefined in the record format of the join logical file to have the same attributes. If the join fields are of character type, the field lengths may be different.
• Join fields need not be specified in the record format of the join logical file (unless you must redefine one or both so that their attributes are identical).
• If you redefine a join field, you can specify N in position 38 (making it a neither field) to prevent a program using the join logical file from using the redefined field.
• The maximum length of fields used in joining physical files is equal to the maximum size of keys for physical and logical files (see Appendix A, "Database File Sizes").

Rules for joining database files: Fields in Join Logical Files

The rules to remember about fields in join logical files are:

• Fields in a record format for a join logical file must exist in one of the physical files used by the logical file or, if CONCAT, RENAME, TRNTBL, or SST is specified for the field, be a result of fields in one of the physical files.
• Fields specified as parameter values on the CONCAT keyword must be from the same physical file. If the first field name specified on the CONCAT keyword is not unique among the physical files, you must specify the JREF keyword for that field to identify which file contains the field descriptions you want to use.
• If a field name in the record format for a join logical file is specified in more than one of the physical files, you must uniquely specify on the JREF keyword which file the field comes from.
• Key fields, if specified, must come from the primary file. Key fields in the join logical file need not be key fields in the primary file.
• Select/omit fields can come from any physical file used by the join logical file, but in some circumstances the DYNSLT keyword is required.
• If specified, key fields and select/omit fields must be defined in the record format.
• Relative file numbers must be used for the JOIN and JREF keywords if the name of the physical file is specified more than once on the JFILE keyword.

Rules for joining database files: Miscellaneous

Other rules to keep in mind when using join logical files are:

• Join logical files are read-only files.
• Join record formats cannot be shared, and cannot share other record formats.
• The following are not allowed in a join logical file:
  • The REFACCPTH and FORMAT keywords
  • Both fields (B specified in position 38)