HP 3000 Manuals HP 3000 Manuals
Linking Files
The examples above have described the process of creating Groups from
data sets - - first two, and then three - - within a single database. It
is also possible to link KSAM and MPE files to data sets, and to link
data sets in one database with data sets in other databases.
The exact way in which INFORM/V links the files determines whether it is
possible to generate the requested report and, if so, what specific data
will be printed in the report. This subsection describes the ways in
which files can be linked, the default considerations that INFORM/V uses
in linking files, and the ways in which you can control the linking
process.
Direct Links
A link between two files that each contain one or more data elements
needed for a report is called a direct link.
When two files are linked, the value of the link element is retrieved
from the first file and is used to determine the correct record to
retrieve from the second file. Obviously, the link element must exist in
both files. Either file can be a detail data set, master data set, KSAM
file, or MPE file.
Regardless of what type of file it is, the file linked from is read
serially. It can be a detail data set, master data set, KSAM file, or
MPE file.
In contrast, the way that the file being linked to is read depends on the
type of file it is:
* If the file being linked to is an MPE file, it is read serially, and
there is no restriction on the element being linked to, except that
it exist in the file.
* If the file being linked to is a KSAM file, the link element must be
a key element in the file.
* If the file being linked to is a data set, either master or detail,
the link element must be a search item in the file. This is the only
requirement, even when the link is from a file outside the database.
Click here to view figure.
Figure 11-12. Files to be Directly Linked for Data Access
For example, Figure 11-12 shows three files that contain data elements
needed for a Group called ACCOUNT ORDERS (an asterisk marks a key item or
search item). The group will be used to get reports about the orders for
each customer account. The Group, called ACCOUNT ORDERS, consists of the
following data elements:
ACCOUNT
ADDRESS
OWNER
PROD-NO
UNIT-COST
By accessing the files SALESTAT and INVENTORY the user could obtain all
the elements in the Group; however, it is not possible to link these two
files, since there is no element common to both of them. The MPE file
WARRANTY contains elements needed for the Group and, in particular,
contains the element ACCOUNT, which is also a key element in the KSAM
file; hence a direct link can be made from WARRANTY to SALESTAT. In
addition, the MPE file contains two elements, PROD-NO and SHIP-DATE,
which are also search items in the detail data set; hence a direct link
can also be made from WARRANTY to INVENTORY.
The driving file is the one that INFORM/V accesses first. When an MPE
file is part of the picture, it is most efficient if it is the driving
file, since an MPE file must be serially read each time it is accessed
(see "How INFORM/V Picks the Driving File" later in this section).
In the example in Figure 11-12, the MPE file allows a direct link to the
other two files, and so the user wants to make it the driving file. This
is done by specifying the file name WARRANTY in response to the FILE
prompt and a link value of 1 when adding one of the elements from the
file WARRANTY to the Group (see Figure 11-13).
It is also important to pick elements that will allow INFORM/V to link
the driving file, which is WARRANTY in this example, directly with other
files that contain as many elements for the Group as possible.
The elements ACCOUNT and PROD-NO are the two possibilities. (Since
SHIP-DATE is not in the Group, it does not make sense to use it. OWNER
cannot be used because it is not a key element in the KSAM file.) The
element ACCOUNT links WARRANTY to SALESTAT, where ADDRESS can be
retrieved. The element PROD-NO links WARRANTY to INVENTORY, where
UNIT-COST can be retrieved. (Note that the values for ACCOUNT, OWNER,
and PROD-NO will all be retrieved from the MPE file, since it is accessed
first.) Since both data elements allow you to link to one other file
where one required element can be retrieved, it does not matter which one
we use.
After linking WARRANTY to SALESTAT through the element ACCOUNT, INFORM/V
will try to use the element in the Group with the next highest priority
link value to directly link WARRANTY or SALESTAT with INVENTORY - -
assuming that the element PROD-NO or UNIT-COST is requested for the
report. To make this most efficient, the element PROD-NO should be given
a link value of 2. The interaction is shown in Figure 11-13.
_______________________________________________
| |
| > ADD GROUP |
| GROUP ACCOUNT ORDERS|
| |
| ELEMENT UNIT-COST |
| ELEMENT ALIAS COST OF UNIT |
| FILE ! |
| |
| ELEMENT ADDRESS |
| ELEMENT ALIAS ! |
| |
| ELEMENT OWNER |
| ELEMENT ALIAS ! |
| |
| ELEMENT ACCOUNT |
| ELEMENT ALIAS |
| FILE WARRANTY |
| VALUE AS A LINK 1 |
| DISPLAY ELEMENT ON MENU (Y/N)?! |
| |
| ELEMENT PROD-NO |
| ELEMENT ALIAS PRODUCT NUMBER|
| FILE |
| VALUE AS A LINK 2 |
| DISPLAY ELEMENT ON MENU (Y/N)?! |
| |
| ELEMENT |
| > |
| |
| |
| |
| |
_______________________________________________
Figure 11-13. Establishing Direct Links between Files for Data Retrieval
Indirect Links
If two files containing elements needed for a report are linked through
other files that contain no elements needed for the report, the link is
called an indirect link. Indirect links can only occur between data sets
within one database. They cannot go from one database to another, nor
can they link a KSAM or MPE file to a data set.
There are two kinds of indirect links allowed: (1) a master set to
another master set through an intermediate detail, and (2) a detail set
to master set through two intervening sets, a master and then a detail.
The examples below illustrate each kind.
As an example of the first kind of indirect link, consider the database
shown in Figure 11-14.
Click here to view figure.
Figure 11-14. An Indirect Link through a Detail Set
The user wants to report the data element ZONING-DES from the master set
ZONING-MASTER and the element CITY-NAME from the master set CITY-MASTER.
An indirect link can be made by going through the detail set COMMERCIAL.
The master set ZONING-MASTER has the search item ZONING-CODE, which forms
a path to the detail set COMMERCIAL, and the master set CITY-MASTER has
the search item CITY-ABBR, which also forms a path to COMMERCIAL. When
the element ZONING-CODE is added to the Group, the prompt
DISPLAY ELEMENT ON MENU (Y/N)?>
will be answered N, since this element is only included to serve as a
link and is not wanted for inclusion in a report.
The second possibility for indirect linking, going through two
intermediate sets, is shown in Figure 11-15.
Click here to view figure.
Figure 11-15. Indirect Link through Two Intermediate Sets
In this example, the user wants to include in a report the data element
OCCUPIED from the detail set COMMERCIAL and the data element CITY-NAME
from the master set CITY-MASTER. An indirect link can be formed by going
through the detail set RESIDENTIAL. The detail set COMMERCIAL and the
detail set RESIDENTIAL both have the search item LIST-PRICE, which forms
paths to the common master set LIST-PRICE-MASTER. The detail set
RESIDENTIAL has a second search item CITY-ABBR which forms a path to the
master set CITY-MASTER.
How INFORM/V Picks the Driving File
The driving file is the one that is accessed first when INFORM/V creates
a report. This file is read serially, and at least one line of the
report will be generated for each record retrieved from it. The contents
of a report could therefore be quite different, depending on which file
is the driving file.
When INFORM/V is selecting the driving file, it first looks for a file
that meets the following two criteria:
* The file must contain an element having a positive link value
(greater than zero); it is not important whether this element is
included in the report.
* The file must also contain an element that is to be included in the
report.
What happens next depends on whether one, none, or more than one file
meet the two criteria.
1. If only one file meets the two criteria above, that file
will be chosen as the driving file.
2. If no files meet the two criteria above, the files which
contain an element to be included in the report are
considered in the following order:
MPE file
KSAM file
Detail data set
Master data set
If more than one file exists in the highest possible
category, the file with the most elements to be included
in the report is chosen as the driving file. If there
are two such files or more, one of them is arbitrarily
chosen.
3. If more than one file meets the two criteria above, the
file that has the highest priority link value specified
(lowest positive integer) is chosen as the driving file.
If more than one of these files has the highest priority
link value, then the driving file is chosen from among
them as in case 2.
If any of the files that contain elements for the Group is an MPE file,
it is desirable to use that file as the driving file. This is because
INFORM/V must perform a serial read every time it accesses an MPE file.
If the MPE file is the driving file, this serial read is done only once.
This process of picking the driving file is shown graphically in Figure
11-16.
Click here to view figure.
Figure 11-16. Picking the Driving File
You can ensure that a particular file is the driving file by doing the
following when adding elements to the Group.
* Add an element from that file to the Group.
* Specify the name of that file in response to the FILE prompt.
* Specify a positive link value in response to the LINK prompt.
The element used should be one that allows INFORM/V to link directly with
other files to obtain as many elements from the Group as possible.
The following example shows the importance of picking the right driving
file and of selecting the best element within that file for linking to
the other files needed for the Group.
Click here to view figure.
Figure 11-17. Specifying Link Values
Working with the database shown in Figure 11-17, the user wants to create
a Group called BORROWED BOOKS. The purpose of the Group is to generate
reports about books that are checked out of the library. The Group
consists of the data elements
TITLE
AUTHOR
CALL-NO
BORROWER
PHONE
Since the purpose of the Group is to report on books that are checked
out, it makes sense to access the detail set BOOKS-OUT as the first file;
since BOOKS-OUT has only one entry for every book checked out, there will
be at least one line of the report for each book that is checked out of
the library. That is, BOOKS-OUT contains the appropriate subset of data
for the reports that will be generated from the Group. The data sets
ALLBOOKS, TITLE, and CALL-NO, on the other hand, have one entry for each
book in the library; if one of these files is the first file accessed,
there will be at least one line of the report for every book in the
library. Accessing BORROWER or AUTHOR as the first file would also not
be satisfactory, since BORROWER has one entry for each person registered
to check out books from the library and AUTHOR has one entry for each
author.
To make BOOKS-OUT the driving file, the user specifies the file name
BOOKS-OUT in response to the FILE prompt and a link value of 1 in
response to the LINK prompt when adding an element from the file
BOOKS-OUT to the Group. The user needs to pick an element that will
allow INFORM/V to link BOOKS-OUT directly with other files to retrieve as
many elements for the Group as possible. The elements CALL-NO and
BORROWER are the two possibilities, since they are search items. The
element CALL-NO links BOOKS-OUT and ALLBOOKS, where TITLE and AUTHOR can
be retrieved; the data element BORROWER links BOOKS-OUT and BORROWER,
where only PHONE can be retrieved. Hence, the user picks the element
CALL-NO.
After linking BOOKS-OUT and ALLBOOKS through the element CALL-NO,
INFORM/V will try to use the element in the Group with the next highest
priority link value to directly link BOOKS-OUT or ALLBOOKS with BORROWER,
if PHONE is requested for the report. To make this most efficient, the
user gives BORROWER a link value of 2. The steps to add these elements
to the Group are shown in Figure 11-18.
________________________________________________
| |
| > ADD GROUP |
| GROUP BORROWED BOOKS |
| |
| ELEMENT TITLE |
| ELEMENT ALIAS ! |
| |
| ELEMENT AUTHOR |
| ELEMENT ALIAS ! |
| |
| ELEMENT PHONE |
| ELEMENT ALIAS TELEPHONE NUMBER|
| FILE ! |
| |
| ELEMENT CALL-NO |
| ELEMENT ALIAS CALL NUMBER |
| FILE BOOKS-OUT |
| VALUE AS A LINK 1 |
| DISPLAY ELEMENT ON MENU (Y/N)?! |
| |
| ELEMENT BORROWER |
| ELEMENT ALIAS |
| FILE |
| VALUE AS A LINK 2 |
| DISPLAY ELEMENT ON MENU (Y/N)?! |
| |
| ELEMENT |
| > |
| |
| |
| |
| |
________________________________________________
Figure 11-18. Interaction to Pick the Driving file and the Linking Elements
How INFORM/V Links Files
All elements in a Group that have been assigned a positive link value
form a prioritized list for use by INFORM/V's access algorithm when
linking files. Elements that have been added to a Group that will not be
displayed on INFORM/V Data Names Menu are included in this list if they
have been assigned a positive link value. The following steps outline
how INFORM/V links files. If, at any particular step, all files
containing elements needed for the report are linked, the steps following
that one do not occur.
1. INFORM/V first tries to use the element in the Group
with the highest priority link value to directly link
the driving file with any other files containing
elements needed in the report.
2. INFORM/V next tries to use the element in the Group with
the second highest priority link value to directly link
those files linked in step 1 with any other files
containing elements needed for the report.
3. Next, the elements in the group with the next highest
priority link value are used to try to directly link
those files linked in steps 1 and 2 with any other files
containing elements needed for the report, and so on
until the prioritized list of elements with positive
link values is exhausted.
4. INFORM/V next tries to use elements in the Group with a
link value of zero (the default) to directly link those
files linked in steps 1, 2, and 3 with any other files
containing elements needed for the report. The elements
with a link value of zero are tried in an arbitrary
order.
5. If all the files have still not been linked
successfully, INFORM/V tries to use elements that are
not in the Group to directly link any of the files
linked in steps 1, 2, 3, and 4 with any of the remaining
files. As always, a direct link is successful if the
element exists in both files and if any additional
requirements on the file being linked to are met (see
earlier discussion of direct links).
6. If still no link can be made and if the files that
INFORM/V is attempting to link are data sets, INFORM/V
will try the two allowable indirect links. If this
fails to link all the needed files, the report cannot be
generated, and the user will receive an error message.
Default File Access
INFORM/V provides defaults designed to maximize performance of the file
accesses necessary during report production. One such default, which was
explained in the discussion on the driving file, is that an MPE file will
be used as the driving file whenever possible. In addition, if file
names (in response to the FILE prompt) and link values that override the
defaults have not been specified, INFORM/V will try to following these
guidelines:
* Involve as few files as possible.
* Access a KSAM file in preference to a database, since there is less
overhead involved.
* Open as few databases as possible.
* Use master data sets whenever possible, since they will need to be
accessed only once per key value, whereas detail data sets and KSAM
files may require several accesses per key value.
When a file name is specified in response to the FILE prompt, the
INFORM/V access algorithm may not work efficiently, and possibly not at
all. It is best not to specify a file name in response to the FILE
prompt unless it is necessary to ensure that the correct values of an
element are retrieved. If a file name is specified when it is not
necessary to do so, it is possible that more files will be acessed than
INFORM/V would otherwise use. In the worst case, INFORM/V might be
unable to create a link - - when there is no link element to the
designated file.
It is also advisable to specify link values sparingly. Link values allow
you to override default processing, and in certain circumstances their
use will make a critical difference in the reports generated. However,
their use when they are not necessary may degrade performance.