.PAGE SIZE 58,80 .LAYOUT 2, 2 .RIGHT MARGIN 80 .CENTER;Record Definition Tutorial .BLANK .CENTER;B.#Z.#Lederman .BLANK .CENTER;2572 E.#22nd St. .CENTER;Brooklyn, N.Y. 11235 .NOTE Abstract This session will supply examples and suggestions which go beyond the material in the Datatrieve manuals, and show how various types of problems may be solved using the options available within the record definition. The material will include some comparisons between different approaches to the same problem, the use of VIEWS (which are created from record definitions), and methods of transferring data from one domain to another, which also depends in part upon record definitions. .END NOTE .TITLE Record Definition Tutorial .SUBTITLE B.#Z.#Lederman .PARAGRAPH It is not unreasonable to state that the record definition is the foundation of any Datatrieve application, as it is the reference by which all data is stored and retrieved. Therefore, the first rule for any application is: .BLANK ^&KNOW YOUR APPLICATION\& .BLANK from which immediatly follows: .BLANK ^&KNOW YOUR DATA\& .BLANK I find that the best way to work out record definitions is with two very simple pieces of equipment: a pencil, and a piece of paper marked off in squares such as graph paper, or a printer form layout sheet, or CRT display form, or an old coding sheet. By marking off the fields, using one square per byte, the number of data items, the length of each field, and it's alignment and relationship to other fields are easily determined. This is especially important with the REDEFINES clause, which will be shown later. .PARAGRAPH Often, an unsuspected benefit of taking an existing manual operation and implementing it on Datatrieve is that the people involved must sit down and figure out exactly what pieces of data they are dealing with, and in what manner: this is often the first time anyone actually does this, and they are often supprised by the amount of data involved. .PARAGRAPH Some applications move onto Datatrieve almost automatically. If you are using a pre-printed form (and almost every company has some sort of printed form for orders, absence reports, pencil requisitions, etc.) then one can simply copy the fields into the record definition: there are cases of new users moving applications like this in one day. If your records are not as well organized, then you must analyze them yourself. If you are using Datatrieve to read an existing file created by some other program, then it is necessary to obtain the file record layout and follow it. .PARAGRAPH Keep in mind that, while it is nice to get a good record definition at the beginning, it is always possible to define a new domain and record and read the data from the old domain to a new one, so if you have 10,000 records stored, and find you need to add a field, don't panic. Examples of this will be given. .PARAGRAPH While one could use ADT or follow the simple examples in the manuals and develop many useful applications with Datatrieve, there is a much wider range of applications which may be addressed with a few simple techniques. For example, suppose the YACHTS domain was being used in a show room, where the customers are allowed to look up data at a terminal, but the seller doesn't want the price to appear. Using the simplified record definition for YACHTS, here are two possible solutions. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 14 Original Second View Definition Definition DEFINE DOMAIN LOOK OF YACHTS USING 01 BOAT. 01 BOAT. 01 LOOK OCCURS FOR YACHTS. 06 BUILDER PIC X(10). 06 BUILDER PIC X(10). 03 BUILDER FROM YACHTS. 06 MODEL PIC X(10). 06 MODEL PIC X(10). 03 MODEL FROM YACHTS. 03 SPEC. 03 SPEC. 06 RIG PIC X(6). 06 RIG PIC X(6). 03 RIG FROM YACHTS. 06 LOA PIC XXX. 06 LOA PIC XXX. 03 LOA FROM YACHTS. 06 DISP PIC 9(5). 06 DISP PIC 9(5). 03 DISP FROM YACHTS. 06 BEAM PIC 99. 06 BEAM PIC 99. 03 BEAM FROM YACHTS. 06 PRICE PIC 99. 06 FILLER PIC XX. ; ; ; .BLANK.JUSTIFY.FILL The definition on the left is for the whole domain which the showroom owner will use, and is the short definition given in the manual in the optimization chapter. PDP-11 users who are short of pool space should look at this chapter, and compare the short definition, which uses much less pool space by having fewer clauses, with the definition created by the installation package. The customers could use the view on the right, which does not have the price, or a second domain using the record definition in the center could be used to access the ^&SAME FILE\& as is used for YACHTS. This shows two useful features. First, it is possible to have more than one domain access the data in a single file: this makes it possible to look at the data in more than one way, with more than one record definition. The only restriction is that the user must document the domains accessing each file so that, if it is ever neccesary to change a file, the domains to be affected will be known. There is also an important difference between the domains and the view: you cannot store or erase records in a view, but you can do all operations on the second domain. The limitation on views can be bad or good, depending upon the application: in this example, you probably would not want customers to add or erase records, so using a VIEW would be one way of preventing this. .PARAGRAPH The second domain shows the use of the special field type FILLER to "skip" over data in a record. The data is still there, and may be accessed by the original domain, but not by the second domain: if you also protect the record definition itself to be execute but not read, the user will never see the filler field (it doesn't appear in SHOW#FIELDS), and will not know there is data there. This may be extended for use in 'hiding' fields. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 12 01 CUSTOMER. 03 ADDRESS. 06 STREET PIC X(10). 06 CITY PIC X(10). 06 STATE PIC X(2). 06 ZIP PIC 9(5). 03 FILL-ENG. 06 FILLER PIC X(10). 03 ENG REDEFINES FILL-ENG. 06 ENGINEER PIC X(10). ; .BLANK.JUSTIFY.FILL If you normal command PRINT you get: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 4 STREET CITY STATE ZIP .BLANK 2572 E. 22 BROOKLYN NY 11235 .BLANK.JUSTIFY.FILL but if you say PRINT#ADDRESS,#ENG the result is: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 4 STREET CITY STATE ZIP ENGINEER .BLANK 2572 E. 22 BROOKLYN NY 11235 LEDERMAN .BLANK.JUSTIFY.FILL Thus the ENGINEER field is always available, but will not print out unless specifically asked for. This can also be a pool saving technique for very large records, or may be used to control access to information where several users must access the same file by doing something like this: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 12 01 ALL-DEPT-REC. 01 DEPT-A-REC. 02 DEPT-B-REC. 03 DEPT-A. 03 DEPT-A. 03 FILLER PIC X(5). 06 BUDGET PIC 999. 06 BUDGET PIC 999. 06 MANAGER PIC XX. 06 MANAGER PIC XX. 03 DEPT-B. 03 FILLER PIC X(10). 03 DEPT-B. 06 BUDGET PIC 999. ; 06 BUDGET PIC 999. 06 MANAGER PIC XX. 06 MANAGER PIC XX. 03 DEPT-C. 03 FILLER PIC X(5). 06 BUDGET PIC 999. ; 06 MANAGER PIC XX. ; .BLANK.JUSTIFY.FILL This is a very small example, but it shows how a single file may be accessed by one domain having access to all fields, and by several other domains, accessing only some of the data. Each of the smaller domains can read and write only their own data, and the big domain could be used for report giving all of the data. This is an alternative to having seperate domains for each department, and using a view to tie them together for reports. (For PDP-11 and PRO users, each of the smaller record definitions uses less pool than the big definition, allowing more complicated procedures to be used (or more sort space, etc.). If a record definition is very large (hundreds of bytes), then the only way to access it usefully in Datatrieve-11 may be to have more than one domain each access a portion of the record.) .PARAGRAPH Another approach to the same problem would be to use a VIEW. First, a definition may be given for the domain which holds data for all departments. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 7 01 BUDGET-REC. 10 DEPARTMENT PIC XX. 10 PROJECT PIC X(10). 10 AMOUNT PIC 9(6)V99 EDIT-STRING $$$$,$$$.00. 10 MANAGER PIC X(10). ; .BLANK.JUSTIFY.FILL The person in charge of budgets would have full access to this domain, and so could access all of the data. .BLANK Each individual department would have their own VIEW defined like this: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 7 DEFINE DOMAIN AA-BUDGET OF BUDGET USING 01 AA-BUDGET OCCURS FOR BUDGET WITH DEPARTMENT = "AA". 10 PROJECT FROM BUDGET. 10 AMOUNT FROM BUDGET. 10 MANAGER FROM BUDGET. ; .BLANK.JUSTIFY.FILL This definition should be protected so that the department can execute it, but not read or modify it, otherwise they might want to change the definition to allow access to other departments. Because the selection criteria is fixed, they will see only their own department's data. This configuration would be of greatest use when the different departments must read the information, but only the central controller will enter or erase it. .PARAGRAPH Another very useful feature of the REDEFINES clause is that it allows one to look at the data in a domain in more than one way within a single domain. An application for this could be a file which has more than one record type in a single file. The author does not reccomend this for new applications, but there may be existing files set up like this (COBOL and RPG are often the source) which one would like to access with Datatrieve. Consider a file with data that looks like this: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 7 Key Type .BLANK 0001 N Lederman Bart Z ! Name information 0001 A 2572 E 22nd New York NY 11235 ! Address information 0001 P 38 DPG 2222 Distributed Proc ! Business information 0001 T 212-555-5555 718-555-5555 ! Telephone numbers .BLANK.JUSTIFY.FILL A possible record definition is: .BLANK.NO JUSTIFY.NO FILL DEFINE RECORD MULTI-REC 01 MULTI-REC. 03 KEY PIC 9999 EDIT-STRING ZZZ9. 03 TYPE PIC X. 03 NAME-PAGE. 06 LAST PIC X(14). 06 FIRST PIC X(12). 06 M PIC X. 06 N PIC X. 03 ADDRESS-PAGE REDEFINES NAME-PAGE. 06 STREET PIC X(11). 06 CITY PIC X(10). 06 STATE PIC XX. 06 ZIP PIC 99999. 03 PERSONNEL-PAGE REDEFINES NAME-PAGE. 06 FLOOR PIC 99 EDIT-STRING Z9. 06 SECTION PIC XXX. 06 CLOCK PIC 9999. 06 DEPARTMENT PIC X(19). 03 TELEPHONE-PAGE REDEFINES NAME-PAGE. 06 BUSINESS PIC X(10) EDIT-STRING XXX-XXX-XXXX. 06 HOME PIC X(10) EDIT-STRING XXX-XXX-XXXX. 06 FILLER PIC X(8). ; .BLANK.JUSTIFY.FILL The first part of the definition is for the fields which do not change (the key and the type, which are common to all records). The next part is the definition for the first record, the name fields. Because this is the first definition (highest in the hirarchy), it is used by default when accessing the data. If the data is printed, the result is: .BLANK.NO JUSTIFY.NO FILL KEY TYPE LAST FIRST M N .BLANK 1 N Lederman Bart Z 1 A 2572 E 22ndNew York NY112 3 5 1 P 38DPG2222Distr ributed Pr o c c 1 T 21255555557185 555555 .BLANK.JUSTIFY.FILL Notice how all records have been printed as if they were NAME-PAGE as this is the first group in the hirarchy, but because I have the redefined fields, I can also access the data in different ways. Each redefines has it's own group name, which makes access much easier as I can specify a group name for one whole page, and note that a redefines must never be longer than the original field and/or group. In other applications, you should be certain the longest group comes first, or that you fill the first group to be as long as the longest group. It is acceptable for the redefines to be shorter than the original field, but I prefer to fill all groups in for my own reference. In this case, the telephone data is shorter, and the additional length is made up with FILLER. With the redefines, a simple procedure will access the data correctly. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 11 DEFINE PROCEDURE PRINT-MULTI READY MULTI FOR MULTI BEGIN IF TYPE EQ "N" PRINT NAME-PAGE IF TYPE EQ "A" PRINT ADDRESS-PAGE IF TYPE EQ "P" PRINT PERSONNEL-PAGE IF TYPE EQ "T" PRINT TELEPHONE-PAGE END END-PROCEDURE .BLANK.JUSTIFY.FILL When this procedure is invoked, the data prints like this: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 17 LAST FIRST M N .BLANK Lederman Bart Z .BLANK STREET CITY STATE ZIP .BLANK 2572 E 22nd New York NY 11235 .BLANK FLOOR SECTION CLOCK DEPARTMENT .BLANK 38 DPG 2222 Distributed Proc .BLANK BUSINESS HOME .BLANK 212-555-5555 718-555-5555 .BLANK.JUSTIFY.FILL If you have more than one set of records, the following sets will not have headers when they print out (this is the normal way the Datatrieve PRINT command behaves) but the data will print out using the correct field definitions. The same technique may be used to select the proper page for storing, and so on. Individual fields of each page may be accessed simply by using the name of the field, at any time: Datatrieve will go through the record hirarchy, as it would for any other domain, to resolve the field references. .PARAGRAPH An alternative to the procedure is to define two seperate domains for the data. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 11 DEFINE RECORD BASE-REC 01 BASE. 03 KEY PIC 9999 EDIT-STRING ZZZ9. 03 TYPE PIC X. 03 LAST PIC X(14). 03 FIRST PIC X(12). 03 M PIC X. 03 N PIC X. ; DEFINE DOMAIN BASE USING BASE-REC ON MULTI.SEQ; .BLANK DEFINE RECORD OTHER-REC 01 OTHER. 03 KEY PIC 9999 EDIT-STRING ZZZ9. 03 TYPE PIC X. 03 ADDRESS-PAGE. 06 STREET PIC X(11). 06 CITY PIC X(10). 06 STATE PIC XX. 06 ZIP PIC 99999. 03 PERSONNEL-PAGE REDEFINES ADDRESS-PAGE. 06 FLOOR PIC 99 EDIT-STRING Z9. 06 SECTION PIC XXX. 06 CLOCK PIC 9999. 06 DEPARTMENT PIC X(19). 03 TELEPHONE-PAGE REDEFINES ADDRESS-PAGE. 06 BUSINESS PIC X(10) EDIT-STRING XXX-XXX-XXXX. 06 HOME PIC X(10) EDIT-STRING XXX-XXX-XXXX. ; DEFINE DOMAIN OTHER USING OTHER-REC ON MULTI.SEQ; .BLANK.JUSTIFY.FILL Now, I can define a view of these two domains to bring all of the seperate records together into what will look like one single record: .BLANK.NO JUSTIFY.NO FILL DEFINE DOMAIN MUL OF BASE, OTHER USING 01 MULT OCCURS FOR BASE WITH TYPE EQ "N". 06 LAST FROM BASE. 06 FIRST FROM BASE. 06 M FROM BASE. 06 N FROM BASE. 03 ADDRESS-PAGE OCCURS FOR OTHER WITH TYPE EQ "A" AND OTHER.KEY=BASE.KEY. 06 STREET FROM OTHER. 06 CITY FROM OTHER. 06 STATE FROM OTHER. 06 ZIP FROM OTHER. 03 PERSONNEL-PAGE OCCURS FOR OTHER WITH TYPE EQ "P" AND OTHER.KEY=BASE.KEY. 06 FLOOR FROM OTHER. 06 SECTION FROM OTHER. 06 CLOCK FROM OTHER. 06 DEPARTMENT FROM OTHER. 03 TELEPHONE-PAGE OCCURS FOR OTHER WITH TYPE EQ "T" AND OTHER.KEY=BASE.KEY. 06 BUSINESS FROM OTHER. 06 HOME FROM OTHER. ; .BLANK.JUSTIFY.FILL The reason for the two domains is to be able to use the key field to tie together the appropriate seperate records. The BASE domain will occur once for each group of associated records, and the KEY field will be used to retrieve the other records of the same group. When this view is readied and printed, it looks like this (I have added a second set of data records to show that the view works properly): .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 7 LAST FIRST M N STREET CITY STATE ZIP FLOOR SECTION .BLANK Lederman Bart Z 2572 E 22nd New York NY 11235 38 DPG 2222 Distributed Proc 212-555-5555 212-555-5555 Hackinbush Hugo Z 11 Julius Hialeah FL 33999 13 MRX 1313 Sales _& Promotion 305-555-3131 305-555-1476 .BLANK.JUSTIFY.FILL It appears wrapped around here as there are only 80 columns on this page, but on 132 column paper, all fields print out with their headers. This view would be very useful in "flattening" the data record so it could be processed as other domains are. .PARAGRAPH Another use of the redefines can be for break fields. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 13 DEFINE RECORD TTN-REC 01 TTN. 03 PORT PIC 999. 03 BREAK REDEFINES PORT. 06 B1 PIC 99. 06 FILLER PIC X. 03 GROUP PIC 999. 03 SWITCH PIC 9. 03 TRUNK PIC 9999. 03 COMMENTS PIC X(21). ; .BLANK.JUSTIFY.FILL Although PORT is an integer number, I am using the DISPLAY data type so I can redefine it as a two digit field. The reason can be seen when reporting the data. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 12 DEFINE PROCEDURE RPT-TTN READY TTN REPORT TTN ON TTN.RPT SET REPORT-NAME="Show Breaks with Redefines" SET COLUMNS-PAGE=50 PRINT COL 1, PORT, COL 8, GROUP, COL 20, SWITCH, COL 24, TRUNK, COL 40, COMMENTS AT BOTTOM OF B1 PRINT COL 1, "------------------------------------------------" END-REPORT END-PROCEDURE .BLANK.JUSTIFY.FILL When this procedure is invoked, the resulting report is: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 29 Show Breaks with Redefines 10-Mar-83 Page 1 .BLANK PORT GROUP SWITCH COMMENTS .BLANK 040 347 4 1154 88/89 041 347 4 1155 88/89 042 347 4 1156 88/89 043 347 4 1157 88/89 044 347 4 1417 88/89 045 347 4 1440 88/89 046 347 5 4521 88/89 047 347 5 4522 88/89 ------------------------------------------------ 050 347 5 4523 88/89 052 131 7 5311 TYPE 0 053 131 7 5312 TYPE 0 054 131 7 5313 TYPE 0 055 131 7 5314 TYPE 0 056 131 7 5315 TYPE 0 057 131 7 5316 TYPE 0 ------------------------------------------------ 060 131 7 5317 TYPE 0 061 131 7 5320 TYPE 0 062 131 7 5321 TYPE 0 ------------------------------------------------ .BLANK.JUSTIFY.FILL As may be seen, by having a field which acts on the first two digits of PORT, it is possible to put in a break line every 'n' entries, something which would be difficult otherwise. (Incedentally, the ports are numbered in octal, which is why there is no port 048 or 049, but the system works just as well in decimal.) Not shown is a LINES-PAGE command: if you want groups of records to print out on successive pages with the breaks aligned, you will have to set the number of lines per page to match the group breaks. It may be noted that there is no SORTED#BY clause in the report statement: it is not neccessary to sort the data if it is already in the proper order, as it will be if a sequential file is reported in it's present sequence, or if an indexed file is reported in the order of it's primary key. (One of the uses for indexed files can be to keep data ordered.) If there is no SORTED#BY clause, Datatrieve will issue a warning that unsorted records are being reported, but will then report and allow a break on any field: if there is a SORTED#BY clause, then only fields which were sorted can have breaks, and in this case I want the report in the order of PORT, not the order of B1. Reports also come out faster if you don't have to sort the domain first, and exhaustion of sort pool space is avoided. .PARAGRAPH A commonly used feature is variable length records, as in the FAMILIES domain. While certainly useful, variable length records have some draw backs, including more difficult access to the fields in the variable length portion, and having to know the maximum number of variable occurences when defining the domain. There is an alternative approach using two files and a view. First, for comparison, is a shortened record definition for the FAMILY domain. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 10 DEFINE RECORD SHORT-FAMILY-REC 01 FAMILY. 03 FATHER PIC X(10). 03 MOTHER PIC X(10). 03 NUMBER-KIDS PIC 99 EDIT-STRING Z9. 03 KIDS OCCURS 0 TO 10 TIMES DEPENDING ON NUMBER-KIDS. 06 KID PIC X(10). 06 AGE PIC 99 EDIT-STRING Z9. ; .BLANK DEFINE DOMAIN FAMILY USING SHORT-FAMILY-REC ON FAMILY.DAT; .BLANK.JUSTIFY.FILL The alternative uses one domain for the fixed data, and one for the variable occurence data, with one field in common to tie the two together. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 7 DEFINE RECORD PARENT-REC 01 PARENT. 03 KEY PIC 999 EDIT-STRING ZZ9. 03 FATHER PIC X(10). 03 MOTHER PIC X(10). ; .BLANK.TEST PAGE 7 DEFINE RECORD OFFSPRING-REC 01 OFFSPRING. 03 KEY PIC 999 EDIT-STRING ZZ9. 03 KID PIC X(10). 03 AGE PIC 99 EDIT-STRING Z9. ; .BLANK DEFINE DOMAIN PARENT USING PARENT-REC ON PARENT.DOM; .BLANK DEFINE FILE FOR PARENT KEY=KEY(NO DUP); .BLANK DEFINE DOMAIN OFFSPRING USING OFFSPRING-REC ON OFFSPRING.DOM; .BLANK DEFINE FILE FOR OFFSPRING KEY=KEY(DUP); .BLANK.JUSTIFY.FILL The two domains are then connected with a view: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 9 DEFINE DOMAIN HOUSEHOLD OF PARENT, OFFSPRING USING 01 HOUSEHOLD OCCURS FOR PARENT. 03 FATHER FROM PARENT. 03 MOTHER FROM PARENT. 03 KIDS OCCURS FOR OFFSPRING WITH OFFSPRING.KEY EQ PARENT.KEY. 06 KID FROM OFFSPRING. 06 AGE FROM OFFSPRING. ; .BLANK.JUSTIFY.FILL When printed, the HOUSEHOLD domain looks just like the FAMILY domain, without the NUMBER-KIDS field. .PAGE.NO JUSTIFY.NO FILL FATHER MOTHER KID AGE .BLANK JIM ANN URSULA 7 RALPH 3 JIM LOUISE ANNE 31 JIM 29 ELLEN 26 DAVID 24 ROBERT 16 JOHN JULIE ANN 29 JEAN 26 JOHN ELLEN CHRISTOPHR 0 ARNIE ANNE SCOTT 2 BRIAN 0 SHEARMAN SARAH DAVID 0 TOM ANNE SUZIE 6 PATRICK 4 BASIL MERIDETH BEAU 28 BROOKS 26 ROBIN 24 JAY 22 JILL 20 WREN 17 ROB DIDI JEROME RUTH ERIC 32 CISSY 24 NANCY 22 MICHAEL 20 TOM BETTY MARTHA 30 TOM 27 GEORGE LOIS FRED 26 JEFF 23 LAURA 21 HAROLD SARAH HAROLD 35 CHARLIE 31 SARAH 27 EDWIN TRINITA ERIC 16 SCOTT 11 .BLANK 2.JUSTIFY.FILL This view can be readied, printed, reported, examined, etc. just like the FAMILY domain, but there are several important differences. First, because they are two seperate domains, it is not neccessary to know how many occurences there are for the variable portion, and there is no limit to the number of variable records. Also, because there are two seperate domains, it is possible to work on each portion seperatly, and protect each portion seperatly. We had an application where the fixed portion was the basic information on a communications circuit (the customer name, location, date due, and so on), and the variable portion was a record entered each time a workman attended to the circuit: the domains were protected so the workmen could read the fixed portion but never had write or modify access to it, but could write to the variable domain. This mixed protection cannot be done on a single domain with variable occurs. Another benefit is in Datatrieve-11, where pool space may be saved by readying only the portion required for a given application, rather than always having to use the larger single domain. .PARAGRAPH One drawback is that one cannot STORE to a view, but a simple procedure solves this: .BLANK.NO JUSTIFY.NO FILL DEFINE PROCEDURE STORE-FAMILY DECLARE PROMPT PIC X. DECLARE KEY-FIELD PIC 9999. READY PARENT WRITE READY OFFSPRING WRITE KEY-FIELD = MAX KEY OF PARENT ! Get the last key used KEY-FIELD = KEY-FIELD + 1 ! make it the next key WHILE *."Y to store a family" CONT "Y" BEGIN STORE PARENT USING BEGIN ! Use the common key KEY = KEY-FIELD FATHER = *.FATHER MOTHER = *.MOTHER END PROMPT = *."Y if there are any kids" WHILE PROMPT CONT "Y" BEGIN STORE OFFSPRING USING BEGIN ! Use the common key KEY = KEY-FIELD KID = *."Kid's name" AGE = *.AGE END PROMPT = *."Y for another kid" END END FINISH RELEASE KEY-FIELD RELEASE PROMPT END-PROCEDURE .BLANK.JUSTIFY.FILL Note that a temporary field KEY-FIELD is used to obtain the key (either by prompting or as is done here, by obtaining the last key previously used), and this field is used to store the same value in both domains, thus insuring the fields will match. The prompting for repeats could easily be made more sophisticated than this simple example. .PARAGRAPH The field KEY is used to tie the two domains together, but need not appear in the final view. I have used the name KEY for this field to emphasize the fact that this is a good application for a keyed field in an indexed file. Note the condition on matching KIDS, where the two KEY fields are matched: if KEY were not a keyed field in domain OFFSPRING, then for every record in PARENTS, ^&EVERY RECORD IN OFFSPRING WOULD HAVE TO BE SEARCHED FOR A MATCHING FIELD.\& While it is possible for the variable length portion domain to be a sequential file, don't complain when it takes several hours to print out a few records. In the fixed length portion domain, it is not absolutly neccessary for the KEY field to actually be a key, but making it so takes advantage of another aspect of keyed fields, that of preventing duplicates. If there were duplicates in PARENTS, than one set of KIDS would be assigned to more than one set of PARENTS, a confusing situation to say the least. In most applications, it is desireable for each set of variable records to be assigned to one fixed record, though the use of a VIEW will allow you to have multiple associations, one more possible advantage of the VIEW over the variable occurs for some applications A similar use of key fields not shown here is the NO#CHANGE attribute, which can prevent a field from being modified, regardless of what access or priviledges a user has to that domain. This can be very useful in protecting data from accidental or deliberate modification. .PARAGRAPH The technique of tying two domains together with a matching field can be extended to do something else the occurs clause can not do: a domain (view) with more than one variable portion. To the previous definition, I will now add: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 7 DEFINE RECORD ANIMAL-REC 01 PETS. 03 KEY PIC 999 EDIT-STRING ZZ9. 03 NAME PIC X(10). 03 SPECIES PIC X(10). ; .BLANK DEFINE DOMAIN ANIMALS USING ANIMAL-REC ON ANIMAL.DOM; .BLANK DEFINE FILE FOR ANIMALS KEY=KEY(DUP); .BLANK.TEST PAGE 12 DEFINE DOMAIN HOUSEHOLD OF PARENT, OFFSPRING, ANIMALS USING 01 HOUSEHOLD OCCURS FOR PARENT. 03 FATHER FROM PARENT. 03 MOTHER FROM PARENT. 03 KIDS OCCURS FOR OFFSPRING WITH OFFSPRING.KEY EQ PARENT.KEY. 06 KID FROM OFFSPRING. 06 AGE FROM OFFSPRING. 03 PETS OCCURS FOR ANIMALS WITH ANIMALS.KEY EQ PARENT.KEY. 06 NAME FROM ANIMALS. 06 SPECIES FROM ANIMALS. ; .BLANK.JUSTIFY.FILL The same rules about keys, etc. apply to this view. I have chosen to match the PETS with the PARENTS, but I could have added an additional field to the OFFSPRING domain if I had wanted PETS to be matched to OFFSPRING. When printed, the first few entries look like this: .BLANK.TEST PAGE 16.NO JUSTIFY.NO FILL FATHER MOTHER KID AGE NAME SPECIES .BLANK JIM ANN URSULA 7 RALPH 3 GAYLORD CAT FREDDY PARAKEET JIM LOUISE ANNE 31 JIM 29 ELLEN 26 DAVID 24 ROBERT 16 RAGMOMMA DOG JOHN JULIE ANN 29 JEAN 26 .BLANK.JUSTIFY.FILL It is not neccessary for every entry in the PARENTS portion to have either OFFSPRING, or ANIMALS, or both, and neither OFFSPRING nor ANIMALS require the presence of the other. However, because of the way HOUSEHOLDS was defined, there must be a PARENT record if an OFFSPRING or ANIMAL record with the same key is to appear in the view. If PETS had been matched to OFFSPRING, then an OFFSPRING would have to be present for PETS to appear. .PARAGRAPH An alternative method of combining data from two domains in VAX-Datatrieve (and DTR-20) is to use the CROSS statement. Instead of the VIEW joining PARENT and OFFSPRING, I could use something like this: .BLANK PRINT PARENT CROSS OFFSPRING OVER KEY .BLANK This would give me: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 38 KEY FATHER MOTHER KEY KID AGE .BLANK 1 JIM ANN 1 URSULA 7 1 JIM ANN 1 RALPH 3 2 JIM LOUISE 2 ANNE 31 2 JIM LOUISE 2 JIM 29 2 JIM LOUISE 2 ELLEN 26 2 JIM LOUISE 2 DAVID 24 2 JIM LOUISE 2 ROBERT 16 3 JOHN JULIE 3 ANN 29 3 JOHN JULIE 3 JEAN 26 4 JOHN ELLEN 4 CHRISTOPHR 0 5 ARNIE ANNE 5 SCOTT 2 5 ARNIE ANNE 5 BRIAN 0 6 SHEARMAN SARAH 6 DAVID 0 7 TOM ANNE 7 PATRICK 4 7 TOM ANNE 7 SUZIE 6 8 BASIL MERIDETH 8 BEAU 28 8 BASIL MERIDETH 8 BROOKS 26 8 BASIL MERIDETH 8 ROBIN 24 8 BASIL MERIDETH 8 JAY 22 8 BASIL MERIDETH 8 WREN 17 8 BASIL MERIDETH 8 JILL 20 10 JEROME RUTH 10 ERIC 32 10 JEROME RUTH 10 CISSY 24 10 JEROME RUTH 10 NANCY 22 10 JEROME RUTH 10 MICHAEL 20 11 TOM BETTY 11 MARTHA 30 11 TOM BETTY 11 TOM 27 12 GEORGE LOIS 12 JEFF 23 12 GEORGE LOIS 12 FRED 26 12 GEORGE LOIS 12 LAURA 21 13 HAROLD SARAH 13 CHARLIE 31 13 HAROLD SARAH 13 HAROLD 35 13 HAROLD SARAH 13 SARAH 27 14 EDWIN TRINITA 14 ERIC 16 14 EDWIN TRINITA 14 SCOTT 11 .BLANK.JUSTIFY.FILL By specifying the fields I want to print in the CROSS statement I could suppress the KEY fields, but I let them print out this time to show what is happening. Note that the way this particular CROSS statement was entered results in picking up only those parents who have at least one offspring. The CROSS statement can often be thought of as a short way to do a VIEW, and regarding it as such may help you see what it is doing. Note particularly what was said before about OFFSPRING having to be keyed for fast retrieval: you can see here that the second domain listed in the CROSS statement is taking the same place as the second domain in our VIEW example; therefore, it should also be keyed if the CROSS statement is to execute quickly. Just as with the view, if both domains are keyed it doesn't matter which comes first, but if only one is keyed it should given last in a CROSS statement for best results. Because the CROSS can be implemented in a single statement, it is easier to use during interactive sessions, and when you are investigating relationships between various domains and groups of data. If you find a particular relationship which you expect will be used many times, you may want to convert your CROSS into a VIEW, as this will fix the relationship and you will be able to ready the domain and print records without having to remember what the joining conditions are. .PARAGRAPH Lest it be thought that I am completly against the use of the OCCURS clause, I will now show a good use for it: de-blocking records. With some other languages, a person will sometimes write more than one logical record or associated group of data into what the operating system and Datatrieve consider to be a single record. An example file containing some names, looks like this: .NO JUSTIFY.NO FILL.BLANK Dump of DB2:[300,3]DEBLOCK.SEQ;1 - File ID 4556,33,0 Record number 01. - Size 512. bytes .BLANK 000000 W o l f J F l y 000020 w h e e l 000040 M a h a t m a K J e e 000060 v e s 000100 H u g o Z H a c 000120 k i n b u s h 000140 O t i s C r i 000160 b b l e c o b l i s 000200 S Q Q u a 000220 l e 000240 S a m G r u 000260 n i o n 000300 000320 000340 000360 000400 000420 000440 000460 000500 000520 000540 000560 000600 000620 000640 000660 000700 000720 000740 000760 .BLANK *** EOF *** .JUSTIFY.FILL.BLANK This data is all one physical record as there is no seperation between logical records (each name), but it can be "de-blocked" with the proper record definition. .NO JUSTIFY.NO FILL.BLANK.TEST PAGE 9 DEFINE RECORD DEBLOCK-REC 01 DEBLOCK-REC. 03 FIELDS OCCURS 16 TIMES. 06 FIRST PIC X(12). 06 MI PIC X. 06 LAST PIC X(14). 06 FILLER PIC X(5). ; .JUSTIFY.FILL.BLANK Note that there is no "fixed" portion to this definition: everything is in the "variable" portion (inner list) of the record definition. When a domain with this record definition is readied and printed, it looks like this: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 14 FIRST MI LAST .BLANK Wolf J Flywheel Mahatma K Jeeves Hugo Z Hackinbush Otis Cribblecoblis S Q Quale Sam Grunion .BLANK 5 .JUSTIFY.FILL The blank lines at the bottom are due to the fact that the entire inner list is printed by default, and the unused entries are filled with blanks. This record definition can be made to print better looking data with the redefines and computed by expressions. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 13 DEFINE RECORD DEBLOCK-TWO 01 DEBLOCK-TWO. 08 FIELDS OCCURS 16 TIMES. 16 DUMMY. 24 FILLER PIC X(32). 16 N REDEFINES DUMMY. 24 FIRST PIC X(12). 24 MI PIC X. 24 LAST PIC X(14). 16 NAME PIC X(29) COMPUTED BY FIRST||" "|MI||" "|LAST. ; .BLANK.JUSTIFY.FILL Once again, space is allocated with filler, then redefined with the real fields to "hide" them. The COMPUTED#BY may supprise persons who expect that only math functions can be used with this clause, but it works just as well with character strings. The effect of this is to squeeze down the three fields to a single field with blank spaces removed. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 9 NAME .BLANK Wolf J Flywheel Mahatma K Jeeves Hugo Z Hackinbush Otis Cribblecoblis S Q Quale Sam Grunion .BLANK.JUSTIFY.FILL Processing the name in this way removes one of the objections many people have to computer output; that it looks too "regimented" or too ridgedly organized. Removing the blanks makes the names look more as they would when written or typed by a person: simple little things like this can significantly improve the appearance of a report. The use of the COMPUTED#BY in the record definition allows us still to enter the first and last names seperately so we can, for example, sort the data by last name or otherwise access the inidividual fields, and then use the concatenated name where desired. .PARAGRAPH It was stated that if a record definition is found not to meet the requirements of an application, it can be changed. The rules for changing an existing record definition (without having to change the domain or file used by the domain) reduce to a simple requirement: the length of the record cannot change. This condition results in the following rules: .BLANK Field names, group names, query headers, query names, and edit strings may always be changed, as they do not affect the length of the record, but watch out for any views which access that record definition: if you change a field or group name, any views which use that field must be changed so that their corrisponding group and field names match. .BLANK Query headers, query names, and edit strings may be added or deleted. .BLANK Group names may be added or deleted, provided they do not cause REDEFINES or OCCURS boundries to be crossed. .BLANK REDEFINES fields or groups may be added or deleted. .BLANK As a general rule, PICTURE cannot be changed, and elementary fields cannot be added or deleted. There are a few cases where a change can be made, very, VERY, ^&VERY\& carefully, when the length of a field will not change. For example: .BLANK 03 A PIC 999. can be changed to 03 A PIC XXX. .BLANK but the data can no longer be used as a number, nor can leading zeroes be suppressed. Going the other way, from PIC#XXX to PIC#999 may cause very strange numbers to appear. .BLANK 03#A#PIC#99#USAGE#IS#INTEGER. can change to 03#A#PIC#999#USAGE#IS#INTEGER. .BREAK but not to 03#A#PIC#9999#USAGE#IS#INTEGER. .BLANK because the first two are 2 bytes in length and the last is 4 bytes in length. .BLANK Fields can be combined if the total length is the same: .BLANK 03 CITY PIC X(10). .BREAK 03 STATE PIC X(2). .BLANK can both be replaced with .BLANK 03 CITY-STATE PIC X(12). .BLANK There is one change which is always allowed, and that is to replace any field or combination of adjacent fields with FILLER of the same length. .BLANK A VALID#IF statement can also be added, deleted, or modified. It should be noted that VALID#IF applies only when storing or modifying data with Datatrieve: it does not check data which is already in the domain. Thus it is entirely possible to store data in a field, then add a VALID#IF clause which makes that data invalid: no more data of that kind may be added, but the existing data will be unchanged. This is similar to the condition stated above where PIC#XXX could be changed to PIC#999: if the data in the field happens to all be numeric digits, the data will be correct, but if there is anything else (including leading blanks), Datatrieve will assume they are supposed to be numeric digits, and this will result in some strange numbers being printed out. Datatrieve will not check the existing data, but will not allow you to modify or store a new field with non-numeric data. .PARAGRAPH If all of this scares you, it should be noted that the worst that can happen is that if you do change the length of the record definition, when you READY the domain you will get an error message telling you that the record lengths don't match, or you will read the data and get strange results. As long as you extract a copy of your definition before you start, and ready the domain for read only the first time after you change the definition and look at the data, it is very unlikely that you can damage your data, and the worst that can happen is you will have to make additional corrections to your record definition, or go back to the old one. .PARAGRAPH When changes are required which will not fit the above rules, such as adding an additional elementary field, then a new file will be needed to hold the new length record. One way to transfer information is shown under "Creating New Domains from Old" in the Datatrieve manual. This basically depends on the FOR statement, which reads the old domain one record at a time. Suppose I defined an address file like this: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 8 DEFINE RECORD OLD-ADDR-REC 01 OLD-ADDR-REC. 03 NAME PIC X(20). 03 STREET PIC X(20). 03 CITY PIC X(10). 03 STATE PIC X(2). ; .BLANK and I have stored some data, .BLANK.TEST PAGE 5 NAME STREET CITY STATE .BLANK B. Z. Lederman 2572 E. 22nd St. Brooklyn NY Hugo Z. Hackinbush 11 Julius Ct. Hialeah FL .BLANK.JUSTIFY.FILL and then I realize I forgot the Zip code. I can define a new record and corrisponding domain: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 9 DEFINE RECORD NEW-ADDR-REC 01 NEW-ADDR-REC. 03 NAME PIC X(20). 03 STREET PIC X(20). 03 CITY PIC X(12). 03 STATE PIC X(2). 03 ZIP PIC 99999. ; .BLANK.JUSTIFY.FILL with an additional field and an increased field size for CITY, and move the data. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 4 READY OLD-ADDR READY NEW-ADDR WRITE FOR OLD-ADDR STORE NEW-ADDR USING NEW-ADDR-REC=OLD-ADDR-REC .BLANK and the data will now be .BLANK.TEST PAGE 5 NAME STREET CITY STATE ZIP .BLANK B. Z. Lederman 2572 E. 22nd St. Brooklyn NY 00000 Hugo Z. Hackinbush 11 Julius Ct. Hialeah FL 00000 .BLANK.JUSTIFY.FILL Note that ZIP was filled with default characters, which in the case of numeric fields is zero, and that the city field has been moved. When one says USING#new=old, Datatrieve will match up fields with the same name in moving data: any new fields get zeroes or blanks. If the new fields are at the end of the record, it would be faster to move the data outside of Datatrieve using one of the RMS utilities (CNV or IFL on the 11, CONVERT on the VAX), or SORT, any of which can pad the new records and will transfer data between files faster than Datatrieve. If you are removing a field from the end of the definition, the same rule applies, as the utilities will truncate long records. If you are changing fields in the middle as was done here when the size of CITY was increased, then the SORT utility can be used, but if it is a one time only change, it will be easier (if slower) to use Datatrieve than to set up the sort commands. For example, suppose I decided I needed a second address line. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 10 DEFINE RECORD NEW-ADDR-REC 01 NEW-ADDR-REC. 03 NAME PIC X(20). 03 STREET PIC X(20). 03 SECOND-LINE PIC X(20). 03 CITY PIC X(12). 03 STATE PIC X(2). 03 ZIP PIC 99999. ; .BLANK Using the same statement as before for conversion yields: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 5 NAME STREET LINE CITY STATE ZIP .BLANK B. Z. Lederman 2572 E. 22nd St. Brooklyn NY 00000 Hugo Z. Hackinbush 11 Julius Ct. Hialeah FL 00000 .BLANK.JUSTIFY.FILL with the new line filled with blanks. One can do more than take the defaults, however. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 11 DEFINE RECORD NEW-ADDR-REC 01 NEW-ADDR-REC. 03 ENTRY PIC 99. 03 NAME PIC X(20). 03 STREET PIC X(20). 03 CITY PIC X(12). 03 STATE PIC X(2). 03 ZIP PIC 99999. 03 ENTRY-DATE USAGE IS DATE. ; .BLANK.JUSTIFY.FILL This time I want to add an entry number, and a date, so I have to give Datatrieve a few more commands. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 20 DEFINE PROCEDURE CONVERT-ADDR READY OLD-ADDR READY NEW-ADDR WRITE DECLARE COUNTER PIC 99. COUNTER=0 FOR OLD-ADDR BEGIN COUNTER=COUNTER + 1 STORE NEW-ADDR USING BEGIN ENTRY=COUNTER NAME=NAME STREET=STREET CITY=CITY STATE=STATE ENTRY-DATE="TODAY" END END RELEASE COUNTER FINISH END-PROCEDURE .BLANK.JUSTIFY.FILL Here I have a temporary variable which will automatically count up the number of entries and store it in the new domain: also, the ENTRY-DATE will automatically be time stamped (on the PDP-11, it will have only the date, not the time) as entered. Note there is still no ZIP=... command: as I have no ZIP data in the old domain, I will let the new domain be filled with the default value of zero. I could also put in a prompt here and have someone enter the zip code during conversion, but on a long domain this would be very tedious, so it is better to convert automatically and modify the individual zip codes later. The new data is: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 6 ENTRY ENTRY NAME STREET CITY STATE ZIP DATE .BLANK 01 B. Z. Lederman 2572 E. 22nd St. Brooklyn NY 00000 11-Mar-83 02 Hugo Z. Hackinbush 11 Julius Ct. Hialeah FL 00000 11-Mar-83 .BLANK.JUSTIFY.FILL One can go on from here to make more elaborate changes if desired: the basic idea is that Datatrieve can be made to convert data from one domain to another if it should happen that a record definition needs to be changed, and this can be simplified though a careful choice of field names. As another example, suppose I had to change 5 digit zip codes to 9 digit. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 6 DEFINE RECORD OLD-ADDR-REC 01 OLD-ADDR-REC. 03 NAME PIC X(20). 03 ZIP PIC 9(5). ; .BLANK.TEST PAGE 8 DEFINE RECORD NEW-ADDR-REC 01 NEW-ADDR-REC. 03 NAME PIC X(20). 03 ZIP-CODE. 06 ZIP PIC 9(5). 06 PLUS4 PIC 9(4). ; .BLANK.JUSTIFY.FILL Because I have defined the field ZIP in both domains, the data can be directly transferred. .BLANK.NO JUSTIFY.NO FILL FOR OLD-ADDR STORE NEW-ADDR USING NEW-ADDR-REC=OLD-ADDR-REC .BLANK.TEST PAGE 5 NAME ZIP PLUS4 .BLANK B. Z. Lederman 11235 0000 Hugo Z. Hackinbush 33999 0000 .BLANK.JUSTIFY.FILL It would probably be better if PLUS4 had an edit string to suppress zeroes, and there was a way to print out the dash that joins the two parts of the ZIP code. .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 9 DEFINE RECORD NEW-ADDR-REC 01 NEW-ADDR-REC. 03 NAME PIC X(20). 03 ZIP PIC 9(5). 03 DASH PIC XXX COMPUTED BY " - " QUERY-HEADER "". 03 PLUS4 PIC 9(4) EDIT-STRING Z(4). ; .BLANK.TEST PAGE 5 NAME ZIP PLUS4 .BLANK B. Z. Lederman 11235 - Hugo Z. Hackinbush 33999 - .BLANK.JUSTIFY.FILL The zip suffix doesn't print because of the zero suppression, but the data in the domain looks like this: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 3 B. Z. Lederman 112350000 Hugo Z. Hackinbush 339990000 .BLANK.JUSTIFY.FILL .PARAGRAPH Just to give one last example, I will show how to get the data from FAMILIES to the two domains PARENT and OFFSPRING shown earlier. .BLANK.NO JUSTIFY.NO FILL DEFINE PROCEDURE CHANGE-FAMILY DECLARE COUNTER PIC 999. COUNTER=0 READY FAMILY READY PARENT WRITE READY OFFSPRING WRITE FOR FAMILY BEGIN COUNTER = COUNTER + 1 STORE PARENT USING BEGIN KEY = COUNTER FATHER = FATHER MOTHER = MOTHER END FOR KIDS STORE OFFSPRING USING BEGIN KEY = COUNTER KID = KID AGE = AGE END END FINISH RELEASE COUNTER END-PROCEDURE .BLANK.JUSTIFY.FILL As may be seen, there is a "loop within a loop". The FOR#FAMILY moves through the domain and picks up each set of parents, and whithin this the FOR#KIDS moves through the inner list to pick up each kid within a family. The use of the declared variable COUNTER insures that each offspring has the same key as the corrisponding parent. .PARAGRAPH A final note: the length of each field is important when using the REDEFINES clause, when the data must be read by other programs, or when data is to be transferred between different systems (for example, from a VAX to a PDP-11 or PRO). There is a hidden "gotcha" that should be kept in mind: .BLANK.NO JUSTIFY.NO FILL.TEST PAGE 6 DEFINE RECORD GOTCHA 01 GOTCHA. 03 A PIC X. 03 B PIC 99 USAGE IS INTEGER. ; .BLANK.JUSTIFY.FILL It might be thought that this record is 3 bytes long, and on a VAX it is, but on a PDP-11 it isn't: it is ^&4\& bytes long, because of something called word alignment. INTEGER, REAL, DOUBLE and DATE must start on a word boundry, which is an even number of bytes: if they don't, Datatrieve inserts a hidden byte to align the data; in this case, between fields A and B. This byte takes up space in the record but can never be accessed, unless you change the definition. As a general principle, space should not be wasted, and if the application must be transported between a PDP-11 and a VAX the records must match, so in this instance, it would be better to reverse the order of the fields, so the INTEGER would be on an even boundry, and the record would then be 3 bytes long; or put an extra one byte field between A and B and use the space to store some other data field; or use the ALIGNMENT clause, which will make the VAX force word alignments in the same manner as the PDP-11, which will insure compatibility. .PAGE