3rd Natural 2 Conference
June 11-13, 1990
21st International Software AG Users' Conference
November 4-8, 1990
Northeast Oklahoma Area SAG Group
February 5, 1991
Alumni and Development System Needs
This paper discusses how hyper-descriptors are used within the University of Arkansas's Alumni and Development System. It will provide analysts with insight into how hyper-descriptors may be used to add functionality to applications and simplify or avoid programming/design problems. Data base administrators and systems engineers will be exposed to implementation considerations. Hyper-descriptors are only available to user's of Adabas version 5.
This document is organized into the following sections:
The University's DBA, Don Barnett, is responsible for making our work with hyper-descriptors possible. He demonstrated extreme patience in listening to ideas for the use of an unfamiliar and unproven technology and brought those ideas to reality by developing, testing and implementing the routines described in this paper.
Hyper-descriptors provide the means to use custom developed code to generate descriptor values. Software AG refers to this custom code as a "user-supplied algorithm". This code is plugged into Adabas by using hyper-descriptor exits and typically uses field values from the data base record to construct the hyper-descriptor value(s). Like normal descriptors, each hyper-descriptor is defined on one file and associated only with that file.
Hyper-descriptors most closely resemble super-descriptors. They are defined to Adabas like super-descriptors in that they have a two character name, a format and length, and a list of parent fields from which they are derived. Unlike super-descriptors, the limit on the number of parent fields is not 5 but instead 20. No subset of the parent fields may be specified, the whole field values are passed to the hyper-exit.
The definition of a hyper-descriptor also requires the specification of a hyper-descriptor exit number (1 to 31 is allowed) and any of the following options:
Multiple descriptor values may be generated per record, whether or not any parent field is an MU (or a PE)
Multiple descriptor values with periodic group occurances may be generated per record, again regardless of the parent field types
Suppress null values
Descriptor values must be unique
Hyper-descriptors are used in Adabas calls exactly like any other descriptor (complete with restrictions like no read logical on PE descriptors). Ideas for the application of hyper-descriptors are unlimited.
The need for a new Alumni and Development system had been discussed at the University of Arkansas for years. The old system was written in Basic and ran on a Wang 2200 mini-computer. Functionality was marginal, software and hardware maintenance a nightmare, and some reports ran for 10 to 12 hours. Obviously with Natural and Adabas we could build a better mouse trap.
One feature of the old system was a nagging concern, it allowed maintenance of name and address in mixed case but performed name searches regardless of case. In a new system we must certainly provide the equivalent functionality. In 1987, there just did not seem to be a good way to accomplish this. You could certainly store the name twice and index the upper case version, but esthetically this was not appealing. Then, after reading the Adabas 5 Concepts and Facilities documentation, a plan to address this need was formulated prior to the initiation of the project.
During the analysis of the system requirements, it became obvious that there was another feature that was needed but that would be difficult to provide with Adabas V4. It was desirable to provide an online list of donations or alumni dues payments in most recent to least recent sequence. Essentially a read descending capability. To address these and other needs, we choose to explore hyper-descriptors.
The five following features have been implemented using hyper-descriptors within the Alumni and Development System at the University of Arkansas. Each section will review the justification for, specific operations of, and alternatives to hyper-descriptors. Special considerations will be noted in some cases.
Figure 1. Upper case name search, histogram results
Please enter an ISN/SSN. DAMSPEM Summary PEople Maintenance - SPEM 05/09/90 16:51 Command: _ +------------------------------------------------------------------+ | PF8 or Select a name to view detail data. | ---------- | Search Name: BARNETT (Last/First Middle.) | Act: V IS | Search City: (Partial allowed on) | SSN: 000-0 | (Name, exact match ) | | (only on City. ) | Prefix : | Name Occurs | PrefName: | | Street1 : | BARNETT/G. DON 2 | Street2 : | BARNETT/SHERYL 1 | City : | BARNETT/SUSIE Q. 1 | | BARRETT/LEONORA 1 | Country : | BARRETT/LEONORA JANE 1 | County : | BARRETT/MARGARET 1 | Cur Emp : | BARRETT/MARGARET K. 1 | Spouse : | BARRON/JOHN 1 | | BARRON/JOHN DAVID 1 | Mem Type: | BATES/JENNIE BARABIN 1 | | | Comment: | | Enter-PF1- | PF1-Help PF3-Quit PF8-Frwd | Help +------------------------------------------------------------------+
Within a Development system, much of the data must be maintained in mixed case in order that generated correspondence be of a professional quality. Name and address are the most critical data elements of this type and are also commonly required as search fields to locate specific individuals. This application provides for up to four names per individual (41 characters each) stored in one MU field. Names are in the format last name, '/', first and/or middle name. The hyper-descriptor associated with this field converts all non-null names to upper-case, truncates them to 24 characters, and returns these as the descriptor values.
The name search facility is implemented as a help routine on the primary ID field. A histogram is performed using a starting value provided by the user. This value may be a partial name and is translated to upper case by specifying the attribute AD=T. If there is only one occurance of a selected value, the ID is obtained with a FIND and returned to the map. If multiple occurances of the name exist, a second screen is displayed with complete information for those individuals. See Figure 1 and Figure 2 for an example of these screens.
Figure 2. Upper case name search, Find results when duplicate names
Please enter an ISN/SSN. DAMSPEM Summary PEople Maintenance - SPEM 05/09/90 16:51 Command: _ +------------------------------------------------------------------+ | PF8 or Select a name to view detail data. | ---------- | Search Name: BARNETT (Last/First Middle.) | Act: V IS | Searc +----------------------------------------------------------+ SSN: 000-0 | | PF3 or mark selected record. | | | _ 10 FR M 1 of 2 | Prefix : | Na | Mr. G. Don Barnett Fayetteville AR 72701 | PrefName: | | Prarie Grove AR 72713 | Street1 : | x BA | | Street2 : | BA | | City : | BA | _ 13 FR M S 2 of 2 | | BA | G. Don Barnett, Jr. Fayetteville AR 72701 | Country : | BA | Springdale AR 72704 | County : | BA | | Cur Emp : | BA | | Spouse : | BA | _ | | BA | | Mem Type: | BA | | | | | Comment: | | | Enter-PF1- | PF1-H | PF1-Help PF3-Quit PF5-Rstrt | Help +------ +----------------------------------------------------------+
There are, of course, alternatives to using hyper-descriptors to solve this problem. As mentioned previously, the names could be stored twice with the indexed field being converted to upper-case (not a simple task in Natural since there is no system function to perform this operation). Another approach is to store the name in upper case and store a second copy in mixed case only if the mixed case version cannot be generated by a predefined algorithm. Neither of these alternatives seems desirable as compared to the hyper-descriptor.
Another variation of the name search is the need to look up people by city and name. This is very useful when the city is known and the spelling of the last name is not known or there are a large number of people with the same last name. Not only does our Alumni and Development System allow four names for an individual, it also allows four addresses which are stored in a periodic group. Theoretically an individual could have 16 city-name combinations, some of which may be redundant. There is certainly no need to store or list redundant entries for a name search. The CITY-NAME hyper-descriptor converts the names and citys to upper case, truncates each value to 16 bytes, and creates a list of all unique combinations as 32 byte descriptor values.
The same help routine previously mentioned provides name searches within a city whenever city is entered. The code to operate on this descriptor is separate, but the same screens are displayed in either case. See Figure 3 for an example of the city-name search.
Figure 3. Upper case city-name search, histogram results
Please enter an ISN/SSN. DAMSPEM Summary PEople Maintenance - SPEM 05/09/90 16:51 Command: _ +------------------------------------------------------------------+ | PF8 or Select a name to view detail data. | ---------- | Search Name: WIM (Last/First Middle.) | Act: V IS | Search City: FAYETTEVILLE (Partial allowed on) | SSN: 000-0 | (Name, exact match ) | | (only on City. ) | Prefix : | Name Occurs | PrefName: | | Street1 : | WIMBERLY/DAVID 1 | Street2 : | WIMBERLY/SALLY 1 | City : | WIMBERLY/SALLY J 1 | | WIMBERLY/W. DAVI 1 | Country : | WIMBERLY/WILLIAM 1 | County : | WOMACK/ROBERT 1 | Cur Emp : | WOODARD/JAN ELLE 1 | Spouse : | WOODRUFF/MARSHA 1 | | WRIGHT/BEVERLY A 1 | Mem Type: | YEE/KWET 1 | | | Comment: | | Enter-PF1- | PF1-Help PF3-Quit PF8-Frwd | Help +------------------------------------------------------------------+
The desire to list gift or dues activity for an individual in most recent to least recent date sequence provided another opportunity to use hyper-descriptors. Gift and dues activity are both stored on one Receipt file with a 'type' flag distinguishing them. The individual's ID (ISN) and date of the activity are also on the file, which together with the type provide the parent fields for the ISN-TYPE-DATE-PROCESSED hyper-descriptor. This routine inverts the date (reorders the values) and then concatenates it with the ID (ISN) and type fields just as a super-descriptor would. Natural date data types are used almost exclusively for dates at the university, therefore this field is stored internally and on Adabas in a P6 format. Inverting the date only requires subtracting the packed decimal value as a numeric from 1,000,000. The result is that more recent dates have smaller values (the same could be done for dates stored in the conventional YYMMDD format).
To list activity in most recent to least recent sequence, the application program performs a read logical with a starting value containing the ID and type desired and a zero date component. See Figure 4 for a screen demonstrating this use.
Figure 4. Gift activity listed in most recent to least recent sequence
Mark field, position cursor or enter code; or PF8 DAMDRII Development Receipts ISN Inquiry - DRII 06/06/90 08:51 Command: ____ Act: V ISN: 2 SSN: Date: 06/06/90 Dept: 2067 Parm: DcReg: Pd#: ------------------------------------------------------------------------------- ISN: 2 Name: Mrs. Jamie S. Ehrig Dec Dt: City: Fayetteville State: AR Zip: 72749 AddressStat: Sex: F Category: AL Marital Status: M Spouse ISN: 3 DateProc $ Amt MP M/HCd Sol Camp Acct Cd Dept Pledge ID J M C A 06/05/90 39.00 CK MSG ANFD 2469 2012 0 0 Y R 05/24/90 500.00 CK MSG ANFD 2888 2172 0 0 Y R Y Y 05/14/90 200.00 CK MSG ANFD 9541717 2044 0 0 Y Y Y 05/01/90 200.00 CK MSG ANFD 2918 2315 0 0 Y Y Y x 03/29/90 300.00 CK MSG ODMN 2918 2315 424 0 Y Y Y 03/29/90 300.00 CK MSG ANFD 2918 2315 467 0 Y R Y 03/19/90 200.00 CK MSG ANFD 2918 2315 0 0 Y R Y 03/19/90 3000.00 CK MSG ANFD 2918 2315 0 0 Y P Y 03/09/90 500.00 CK MSG ODMN 2918 2315 424 0 Y P 02/28/90 2.00 CK MSG NONC 2918 2315 0 0 Y Y 02/26/90 300.00 CK MSG ANFD 2918 2315 0 0 Y R Y 02/25/90 300.00 CK MSG ANFD 2918 2315 415 0 Y P Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12--- Help Menu Quit DCode RStrt Frwd
There are other solutions to this problem. The date could be inverted and stored as such within the data record (this was actually done during the application development and prior to implementation of the hyper-descriptor). A second field can also be defined where the date is maintained in an inverted format and a super-descriptor used with the inverted date. A 'read descending' feature is planned for a future release of Adabas, which will be most welcome. In the meantime, hyper-descriptors may provide the solution to a problem.
Adabas has historically disallowed a read logical on a descriptor contained within, or derived from a field within, a periodic group. Hyper-descriptors, however, allow the user to specify the type of the descriptor regardless of the parent elements used to derive it. Returning to our example above, the ID component of the ISN-TYPE-DATE-PROCESSED descriptor is a field contained within a periodic group. This allows multiple individuals, normally husband and wife, to be associated with a gift or dues payment and to be acknowledged differently (the other element in the group is an acknowledgment code). This hyper-descriptor is defined as an MU type so that an index value is created for each ID associated with the gift or dues payment. This allows the application to perform the read logical previously described. It also allows the same receipt to be listed for any individual associated with it. The only loss is the PE occurance which would otherwise be maintained with each descriptor value, which is no real loss in this application.
An interesting problem is created by this situation. Natural treats the read logical on a hyper-descriptor the same as for a super-descriptor, no THRU option is allowed. Therefore, the application program has to test each record read to see if it is for the ID and type desired. Given two consecutive IDs on the same receipt record (common for a husband and wife), that same record may be processed twice. This occurs when the read logical is started at the lower ID and there are no intervening descriptor values. The second time the record is read is via the descriptor value of the higher ID. Within the Alumni and Development system, the same receipt was being displayed on the screen twice. A solution is to rely on the fact that the read logical returns the records for a given descriptor value in ISN sequence. The application program can then test each record to see that the record is not only for the correct ID and type, but that the date is equal or earlier than the date from the previous record (remember it is inverted in the descriptor value) and, if the date has not changed, that the ISN is greater than the ISN of the previous record. The descriptor values and records representing this situation are shown in Figure 5.
Figure 5. MU type hyper-descriptor and data resulting from a read logical
Descriptor values: Data records: ID Type Date ID Type Date Amt --- ---- -------- ---- ---- -------- ------ 78 G 05/09/90 -----> 78 G 05/09/90 75.00 78 G 11/29/89 ---| 0 79 G 11/29/89 -| |-> 78 G 11/29/89 20.00 --| Same | 79 | physical ----> 78 G 11/29/89 20.00 | record 79 --|
Many applications have used the Adabas ISN as the key field to uniquely identify the records and entities stored on a file. Use of the ISN provides the most efficient random access via the GET statement, but also carries with it many cautions. The pros and cons of using the ISN as a key are not being discussed here, merely how it was used within the University's Alumni and Development system.
The constituent's ISN on our primary biographical file is the constituent's ID. This BIOG-ISN is carried on related files, like the Receipt file previously discussed, and even within the biographical file for things such as spouse's ID. Although the GET statement is the most efficient way to access records by ISN, it is not very user friendly. If provided with an invalid ISN, it generates an error. In order to validate IDs that might be entered at the keyboard, a descriptor is needed so that a FIND might be performed. Typically this is provided by defining the ISN field on the data record and indexing it. There are some disadvantages to this approach. It requires that the record first be stored without the ISN (unless user assigned ISNs are being used), the ISN obtained through *ISN, the record reread, and then the record updated with the ISN. Additionally the data storage associated with the ISN is a waste of space since the ISN is always available via *ISN and it is possible that the ISN value might be updated incorrectly.
Hyper-descriptors again provide a solution. The simplest possible hyper-descriptor is to return the value of the record's ISN as the descriptor value. No parent fields are needed although the syntax requires that at least one be specified. The resulting descriptor can be used to verify the existence of an ID or to FIND a record. The only disadvantage is that the ISN is not on the record, so after reading records you must grow accustomed to using *ISN.
The following comments are based on experiences at the University of Arkansas. Relevant product versions are noted, to the best of our recollection.
Predict 2.3.3 supports the definition of hyper-descriptors and generates correct ADACMP definitions. It will also generate DDMs, but all hyper-descriptors are coded as phonetic descriptors (perhaps there is some relationship between these types?). Use of phonetic descriptors is very limited, but the DDM can be easily changed using SYSDDM to recode the hyper-descriptors as super-descriptors. Predict 2.2.3 has no knowledge at all of hyper-descriptors. To get around that problem, define the desired hyper-descriptors as super-descriptors to Predict. The only critical thing is that the they have the correct format and length. The DDM Predict generates will then be acceptable and you will need to manually build the ADACMP definition. We have found no difference between a super-descriptor and a hyper-descriptor from Natural's viewpoint, including the DDM.
Each hyper-descriptor exit may contain the code for processing several different hyper-descriptors. Not only is the field name passed to the exit, but also the file number. The exit may be designed to process the same field differently based upon the file number.
The parent field values passed to the exit are in the same format as they are stored on Adabas. This is compressed format for most fields and includes MU or PE occurance counts. The descriptor values must also be returned in compressed format. Very strange results can occur if the descriptor values are not compressed properly, as we found out. Dealing with compressed fields significantly complicates the coding of a hyper exit. This task is not for the faint of heart or for inexperienced assembly language programmers. On the other hand, dealing with compressed fields allows one routine to be used for different fields of various lengths. For example, we have one routine that converts any compressed non-MU, non-PE alpha field to upper-case.
The ISN of the record being added or updated is passed to the hyper exit. The output parameter area also contains a place for the ISN, which if left blank indicates that the original ISN is to be used with the descriptor values returned. On the other hand, you are allowed to change the ISN! I would like to hear from anyone that comes up with an application that involves changing the ISN.
Adabas 5.1.4 has several problems dealing with hyper-descriptors. This version will compress and load a file with hyper-descriptors defined. You can also add a hyper-descriptor to an existing file and build the descriptor values. However, once a file is built, ADALOD cannot be used to add records to the file if it includes a hyper-descriptor. In addition, ADALOD cannot add records to the file even after the hyper-descriptor is deleted, Adabas thinks the FDT no longer matches the file. The first problem is fixed in Adabas 5.1.6 but the second one still persists. Apparently Adabas sets a bit within the FDT for the source fields of the hyper-descriptor, and this bit is not properly removed when the hyper-descriptor is deleted.
There is no way to check the validity of the hyper-descriptor values. ADAICK can only verify the format of the descriptor, not its contents. This is logical since by definition the value your exit returns is the value for the descriptor.
The hyper-descriptor exit and code associated with it must be treated with the utmost respect. Once linked in to Adabas, it becomes part of Adabas and can easily crash the data base. Yes, we have experience in this area also. The quality and integrity of the code in the hyper exit cannot be over-emphasized. Test it, test it and retest it before putting it into production.
Hyper-descriptors provide new features and ways to solve old problems. Dream up ways to take advantage of this facility but keep your feet on the ground. Perhaps hyper-descriptors that address common needs can be submitted and placed on the DBA tools tape to benefit all users.