HP 3000 Manuals HP 3000 Manuals
Appendix D HOW HP INFORM LINKS FILES TO GENERATE REPORTS When generating reports using HP Inform/3000, the user selects the desired elements from HP Inform/3000's Data Names Menu. If the user has chosen to report from elements organized into an HP Inform/3000 group, these elements may exist in various files - MPE files, KSAM files, or IMAGE data sets. HP Inform/3000 needs to be able to link these files so that the desired lines of the report can be generated. It is important for the database administrator to understand how HP Inform/3000 links the various files. The linking process determines whether it is possible to generate the requested report and, if so, what specific data will be printed in the report. Common elements (elements that exist in more than one file) are used to build links between the files which contain elements needed for the report. An element that is used in this way is called a "link element" . Before reading this appendix, you should read Using Commands to Define HP Inform/3000 Groups in Section III.
NOTE An element which has been defined as a CHILD element through the RELATE ELEMENT command string can not be used as a link.
DIRECT LINKS A link between two files, each of which contains the values of one or more 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. If the link is from an IMAGE master set, IMAGE detail set, KSAM file, or MPE file to an MPE file, the link element must simply exist in both files; if the file you are linking to, however, is an IMAGE master, IMAGE detail, or KSAM file, there are additional requirements on the link element. These are: 1. If the file you are linking to is a KSAM file, the link element must be a common element (must exist in both files) and must be a key item in the KSAM file. Note that when the link is from a KSAM file to another KSAM file, the link element need only be a key item in the second file accessed. 2. If the file you are linking to is an IMAGE detail set, the link element must be a common element and must be a search item in the detail set. These are the only requirements even when the link is from one database to another. Note that when the link is from a detail set to another detail set, the link element need only be a search item in the second file accessed. 3. If the file you are linking to is an IMAGE master set, the link element must be a common element and must be a search item in the master set. These are the only requirements even when the link is from one database to another. Note that when the link is from a master set to another master set, the link element need only be a search item in the second file accessed. (There are two additional ways that a data set can be linked to a master set as long as the link is within one database; these are explained below under Indirect Links.) The following table summarizes the above requirements for direct links according to what type of file is being linked to. Note that the file you are linking from can be any type - MPE, KSAM, IMAGE detail, or IMAGE master. FILE BEING LINKED TO REQUIREMENT ON LINK ELEMENT MPE Common element. KSAM Common element which is a key item in the KSAM file being linked to. DETAIL Common element which is a search item in the DETAIL data set being linked to. MASTER Common element which is a search item in the MASTER data set being linked to. EXAMPLE 1 at the end of this appendix demonstrates direct links from an MPE file to a KSAM file and from an MPE file to an IMAGE detail set. INDIRECT LINKS If two files containing elements needed for a report are linked through other files which contain no elements needed for the report, the link is called an "indirect link" . There are only two indirect links which HP Inform/3000 allows and both apply only to data sets within the same database. These links are the following: (1) If a master set needs to be linked to another master, a link can be made by going through a detail which has a search item in common with each of the masters. For example, assume the following is a partial representation of a data base: ZONING-MASTER CITY-MASTER -------------- ------------- \*zoning-code/ \*city-abbr / \zoning-des/ \city-name/ \ . / \ . / \ . /\ / \ . / \ . / \ / \ . / \ / \ / \ / \ / \ COMMERCIAL / ------------------ \ *zoning-code/ \ *city-abbr / \ / \ / \ / * search items \ / \__/ Suppose the report requests the data element "zoning-des" from the master set ZONING-MASTER and the data 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 an IMAGE path to the detail set COMMERCIAL, and the master set CITY-MASTER has the search item "city-abbr" which also forms an IMAGE path to COMMERCIAL. (2) If a detail set needs to be linked to a master set, a link can be made by going through a second detail which has a search item in common with the first detail (forming IMAGE paths to a common master) and a second search item forming an IMAGE path to the desired master. For example, assume the following is a partial representation of a database: &sigspace; LIST-PRICE-MSTR CITY-MASTER -------------- ------------- \*list-price / \*city-abbr / \ . / \city-name/ \ . / \ . / / \ . / \ / \ . / / \ . / \ / \ . / / \ / \ / \ / / \ / COMMERCIAL / \ RESIDENTIAL / \-----------------/ \-----------------/ \ *list-price / \ *city-abbr / \ occupied / \ *list-price / \ / \ / \ / \ / \_______/ \_______/ * search items Suppose the report requests 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 IMAGE paths to the common master set LIST-PRICE-MSTR); the detail set RESIDENTIAL has a second search item "city-abbr" which forms an IMAGE path to the master set CITY-MASTER. As stated earlier, these are the only two indirect links which HP Inform/3000 allows and both apply only to data sets within one database. THE DRIVING FILE The driving file is the file that is accessed first. Since at least one line of the report will be generated for each record retrieved from the driving file, the contents of a report could be quite different if the driving file is different. Files which meet the following criteria are considered: (1) the file must contain an element having a positive link value (greater than zero) which may or may not be included in the report, and (2) the file must contain an element to be included in the report. One, none, or more than one file might meet the above criteria: * If only one file meets these two criteria, then it will be chosen as the driving file. * If no files meet these two criteria, then files which contain an element to be included in the report are considered in the following order: MPE files KSAM files IMAGE detail data sets IMAGE master data sets 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. * If more than one file meets these two criteria, the file which 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 the above second case.
NOTE The driving file will always be read serially. It is desirable to use an MPE file as the driving file whenever there is one which contains an element to be included in the report; this is because HP Inform/3000 must perform a serial read every time it accesses an MPE file to retrieve a value for a line of the report. If the MPE file is the driving file, this serial read is done only once; if it is not, then a serial read is done for each value of the element used to link to the MPE file. This is not true for KSAM files or IMAGE data sets.
Hence, to ensure that a particular file is the driving file whenever an element which exists in that file is requested for a report, add an element from that file to the group, specify the name of that file in response to the FILE prompt, and specify a positive link value in response to the VALUE AS A LINK prompt. The element used should be one which allows HP Inform/3000 to link directly with other files to obtain as many elements from the group as possible. The file you choose to specify in this manner should: 1. be an MPE file (see the NOTE earlier) if the group contains elements which only exist in that file or if the MPE file is needed by HP Inform/3000 to link directly to other files; 2. contain the appropriate subset of data desired for reports which will be generated from that group. If more than one file satisfies (1) or (2), specify a link value of 1 for the file that contains the greatest number of elements from the group, 2 for the file that contains the next greatest number of elements from the group, and so on. Note that the more specific or limited a group is (that is, the less varied the kinds of reports that will be generated from it are), the more likely it is that a file which meets (2), above, can be specified. EXAMPLE 1 at the end of this appendix demonstrates choosing a file which meets (1), above. EXAMPLE 2 demonstrates choosing a file which meets (2), above. LINKING FILES All elements in a group that have been assigned a positive link value form a prioritized list for use by HP Inform/3000's access algorithm when linking files. Elements which have been added to a group which will not be displayed on HP Inform/3000's Data Names Menu are included in this list if they have been assigned a positive link value. The following steps outline how HP Inform/3000 links files. If, at any particular step, all files containing elements needed for the report are linked, the steps which follow that one do not occur. 1. HP Inform/3000 first tries to use the element in the group with the highest priority link value (lowest positive integer) to directly link the driving file with any other files containing elements needed for the report. 2. HP Inform/3000 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) - that is, the driving file and any files which were successfully linked to it - with any other files containing elements needed for the report. 3. Next, the element in the group with the next highest priority link value is 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 a positive link value is exhausted. 4. HP Inform/3000 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, HP Inform/3000 tries to use elements which 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 "Direct Links" earlier in this appendix). 6. If still no link can be made and if the files HP Inform/3000 is attempting to link are IMAGE data sets, HP Inform/3000 will try the two allowable indirect links. If this fails to link all the needed files, the report can not be generated and the HP Inform/3000 user will receive an error message. DEFAULT FILE ACCESS HP Inform/3000 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, HP Inform/3000 will try to follow these guidelines: * Involve as few files as possible in the access. * 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, while detail data sets and KSAM files may require several accesses per key value). Note that by specifying a file name in response to the FILE prompt, HP Inform/3000's access algorithm may not work efficiently and possibly not at all. It is best to not specify a file name in response to the FILE prompt unless 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 accessed than HP Inform/3000 would otherwise access. In the worst case, it may result in HP Inform/3000 being unable to create a link - when there is no link element to the designated file. HP Inform/3000 uses link elements to accomplish the above goals; the link elements are used in the order which produces maximum performance. The use of link values assigned to elements allows you to override this - to control which elements are tried to link files and in what order. However, the ability of HP Inform/3000 to maximize performance may be hampered by the improper use of link values. Example Assume the following files: SALESTAT WARRANTY INVENTORY (KSAM file) (MPE file) (IMAGE detail) |-----------| |-----------| \---------------------/ | del-flag | | account | \ *prod-no / | | | | \ *ship-date / |-----------| |-----------| \ backorderflg/ | account | | | \ unit cost / | (KEY) | | owner | \ / | | | | \ / |-----------| |-----------| \ / | | | prod-no | \ / | address | |-----------| \___/ | | | descrip | |-----------| | | | ytd-sales | |-----------| |-----------| | total | | sales-rep | |-----------| |-----------| | ship-date | | | |-----------| | owner | | wrnty-no | |-----------| |-----------| Suppose we wish to create a group called ACCOUNT ORDERS. The purpose of the ACCOUNT ORDERS group is to generate reports about the orders of each account. The group consists of the data elements: account address owner prod-no unit-cost By accessing the files SALESTAT and INVENTORY, we could obtain all the elements in the group; however, it is not possible to link these two files since there is no element which exists in both of them. The MPE file, however, also contains elements from the group and, in particular, contains the element "account" which exists in the KSAM file and which is a key item 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 also exist in the IMAGE detail set and which are search items in the IMAGE detail; hence a direct link can be made from WARRANTY to INVENTORY. Since the MPE file allows us to directly link to the two other files and since it is preferable to access an MPE file first, we want to make the MPE file the driving file. To accomplish this, we need to specify the file name WARRANTY in response to the FILE prompt and a link value of 1 in response to the VALUE AS A LINK prompt when adding an element from the file WARRANTY to the group. We would like to pick an element which will allow HP Inform/3000 to link WARRANTY directly with other files to retrieve as many elements from the group as possible. The elements "account" , "prod-no" , and "ship-date" are our three possibilities. Since "ship-date" is not in the group, we will use "account" or "prod-no" . The data element "account" links WARRANTY and SALESTAT, where "address" can be retrieved; the data element "prod-no" links WARRANTY and 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 us to link to one other file where one required element can be retrieved, it does not matter which one we use. After linking WARRANTY with SALESTAT through the element "account" , HP Inform/3000 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 "prod-no" or "unit-cost" was requested for the report. To make this most efficient, we can give "prod-no" a link value of 2. Thus, we could add elements to the ACCOUNT ORDERS group as follows: >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> < Assume the following database: LOCATION BORROWER CALL-NO SUBJECT AUTHOR TITLE --------- ----------- ---------- ---------- ---------- ---------- \*branch/ \*borrower \*call-no/ \*subject/ \*author / \*title / \city / \ phone/ \ / \ / \ / \ / \ /\ \ / \ /\ \ / \ / \ / \ / \ \ /\ /\ / \ \ / \ \ / \ / \ \ / \ \ \ / \ CATALOG \-----\ \ BOOK \ / \ \ \ / ------------------- --------------------- \ *call-no / \ *title / \ *borrower / \ *author / \ bor-date / \ *subject / \ *branch / \ *call-no / \ / \ pub-date / \ / \ publisher \_____/ \_______/ * search item Suppose we wish to create a group called BOOKS OUT. The purpose of the BOOKS OUT 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 BOOKS OUT group is to report on books that are checked out, it makes sense to access the detail set CATALOG as the first file; since CATALOG 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, CATALOG contains the appropriate subset of data which is desired for reports which will be generated from the BOOKS OUT group. BOOK, 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. (SUBJECT and LOCATION are not considered since they do not contain any elements from the group.) To make CATALOG the driving file, we need to specify the file name CATALOG in response to the FILE prompt and a link value of 1 in response to the VALUE AS A LINK prompt when adding an element from the file CATALOG to the group. We would like to pick an element which will allow HP Inform/3000 to link CATALOG directly with other files to retrieve as many elements from the group as possible. The elements "call-no" and "borrower" are our two possibilities since they are search items. The data element "call-no" links CATALOG and BOOK, where "title" and "author" can be retrieved; the data element "borrower" links CATALOG and BORROWER, where only "phone" can be retrieved. Hence, we pick the element "call-no" . After linking CATALOG and BOOK through the element "call-no" , HP Inform/3000 will try to use the element in the group with the next highest priority link value to directly link CATALOG or BOOK with BORROWER - assuming "phone" was requested for the report. To make this most efficient, we can give "borrower" a link value of 2. Thus, we could add elements to the BOOKS OUT group as follows: >ADD GROUP GROUP> books out < ELEMENT> title < ELEMENT ALIAS> ! < ELEMENT> author < ELEMENT ALIAS> ! < ELEMENT> phone < ELEMENT ALIAS> phone number < FILE> ! < ELEMENT> call-no < ELEMENT ALIAS> call number < FILE> catalog < 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> <