.PAGE SIZE 62, 60 .RIGHT MARGIN 60 .CENTER ^&DATATRIEVE Novice Questions _& Answers\& .COMMENT Chair: Dana Schwartz .BLANK 2.CENTER Joe H. Gallagher .BLANK.CENTER 4GL Solutions .BLANK.CENTER Kansas City, MO .BLANK 2.CENTER Chris Wool .BLANK.CENTER DuPont Corp. .BLANK.CENTER Wilmington, DE .BLANK 2.CENTER B.#Z.#Lederman .BLANK.CENTER Brooklyn, N.Y. .BLANK 2.CENTER Larry Jasmann .BLANK.CENTER U.S. Coast Guard .BLANK.CENTER Burke VA .BLANK 2.CENTER Transcribed by B.#Z.#Lederman .TITLE DATATRIEVE Novice Questions _& Answers .SUBTITLE DT012 Spring 1986 Dallas .NOTE Abstract .BLANK 2 This is a transcription of a panel presentation which answers some of the most common questions asked by new DATATRIEVE users. The transcription may paraphrase some questions or answers for clarity, and the transcriber apologizes in advance for any misspelled names: the usual convention of placing square brackets around interpretations or material supplied by the editor is followed in this paper. DTR is used in this paper as an abbreviation for DATATRIEVE. .END NOTE .RIGHT MARGIN 55 .BLANK 3.TEST PAGE 5 Dana Schwartz: I already have a record definition for a domain, and I want to add a field in the middle of the record. How do I do that? .PARAGRAPH (Joe:) This is relatively easy. Ready the existing domain under an alias, for example: .BLANK.NO FILL READY#FOO#AS#OLD#READ This brings the old definition into memory and sets it aside so you can read the data in the domain. EDIT#FOO__RECORD to bring the record definition into the buffer, and add the new field(s). Exit from the editor, and the new definition is now in the dictionary. DEFINE#FILE#FOR#FOO .. to create a new file for the revised record definition. READY#FOO#AS#NEW#WRITE to ready the new domain. NEW#=#OLD to move all of the old data into the new domain. .BLANK.FILL This will leave default values (blanks or zeroes) in the new fields. .PARAGRAPH If you want to put something into the new fields, you can do the following (in place of NEW#=#OLD): .BLANK.NO FILL FOR OLD STORE NEW USING BEGIN NEW__REC = OLD__REC NEW__VARIABLE = constant END .BLANK.FILL It is quite easy to add a new variable and restructure a domain with a few statements. One caution: if the system crashes while you have NEW and OLD readied, the definition will be in the new form, while the data is in the old form. You may have to delete the new (partially filled) file, and the new record definition and go back to the old ones (or rename them to save the new definitions). .BLANK 3.TEST PAGE 5 Rand Wilson, Wilson Concrete: I've done restructuring a few times, and it seems to compress the data (the new file occupies less space). Will you comment on that? .PARAGRAPH (Chris:) You are using an indexed file? (Yes) You have also done multiple deletes before restructuring? (Yes) When you delete records, you don't delete everything: records have what is called a Record File Address (RFA). When you delete a record, the RFA cannot be re-used, so there is a little piece of information which is still in the file. When you restructure the file and rewrite the data, you are eliminating all of the little pieces of unusable space. If you are doing a lot of deletes (or storage of new records) on a file, you should run CONVERT fairly often to rewrite the file. One thing you have to watch out for is VMS V4 CONVERT, which reduces the amount of blocks used, but not allocated: if you start with an 8000 block file and after conversion you only need 3000 blocks, 8000 are still allocated. That is a bug which is known and should be fixed some time in the future. If you are tight on quotas you may have to use FDL to define a new file of the proper size and have CONVERT use the new definition to set a file of the proper size. CONVERT is a DCL level command, and is faster than using DTR to read one file just to write to another. If you are adding a field to a large data file (more than 1000 - 2000 records), you may want your NEW domain (from the previous question) to be a sequential file, and then use CONVERT to change it to an indexed file. DTR is not the most optimum tool for populating and empty indexed file from both performance and time standpoints, because DTR populates it by descending primary key one record at a time. (Larry:) one final comment: if you have reasonably large files, you should get familiar with the RMS utilities and how to tune files. It can pay big dividends in disk space and especially in performance. .BLANK 3.TEST PAGE 5 Kevin Cullan, Vitamix Corp: I have a situation where I have a lot of zoned numeric fields, they are used as key values, and DTR doesn't seem to recognize them as keys. I've been forced to define them as unsigned numeric, and that isn't what I really want to do. Is there some way around this problem? I realize that if they really are zoned numeric they won't be sorted in the order normally expected. [Editor's note: the sign flag, which you don't see when the number is printed, can change the apparent sort order if the data is taken as characters or numbers without sign flags.] (Question from panel: is there a reason why you have to use zoned numeric?)# I have to use zoned numeric for compatibility with another language. There does not seem to be any reason why it shouldn't work. (Question from panel: I assume you define the fields in DTR and then do a DEFINE#FILE#KEY#=#field: have you ever looked at the FDL description of the file to see what data type DTR is using?)# I don't create the file with DTR I do it with FDL, and the field is defined as STRING. .PARAGRAPH (Bart:) That might be the problem. DTR may be recognizing your data as numeric and will not be sending it out to RMS as a string. As a first shot, I'd create a file with DTR with a zoned numeric field as a key, and then look at the file DTR creates to see what data type it is expecting for that field. What we usually recommend in these cases is to always define the file with DTR first, to insure that the keys have the proper data type, and are in the correct location. Then, if you want to, by all means use FDL to optimize the file for your application, but use DTR first to get the keys in the right place. .BLANK Kevin Cullan: What I've done is to use unsigned numeric, and that does work. .BLANK (Chris:) The other thing to do is to run DTR in DEBUG mode and do an RSE to see if DTR identifies that as an indexed read or not. [Rather than run the DTR image directly, issue the command DEBUG#SYS$SYSTEM:DTR32 using the appropriate directory and file name for your DTR image. After DEBUG issues some informational messages, enter the command GO and you will see the normal DTR prompt. From that point you use DTR as you normally would, but you will receive extra informational messages about such things as the keys being used for record retrieval.] .BLANK Kevin Cullan: I have not done a DEBUG, but from the time it takes I believe it is not doing indexed reads. It takes hours if the field is zoned versus seconds for unsigned numeric. I can see how zoned variables could cause problems, and am wondering if they are not allowed as keys. I'd prefer DATATRIEVE to do what I ask, whether it thinks it's a good idea or not. .BLANK Actually, DTR usually allows you to do what you want, even if it is a poor idea, such as crosses over fields which are not keys. We may have to pursue this problem in the campground. .BLANK #.TEST PAGE 5 Wayne Heiderman, (?) International: Do you have any recommendations on bucket sizes? .PARAGRAPH (Bart:) On the PDP-11, use the smallest bucket size that will hold the data. On the VAX, increasing bucket size may or may not get you anything. If you are reading through the file sequentially, a larger bucket size may help you; if you are retrieving a particular record and are likely to immediately use the next record(s) then an increased bucket size may help; if your accesses are more random, and are scattered throughout the file, an increased bucket size won't help and may even hurt a little. If you want to get fancy, you can use FDL to examine the file definition: a larger bucket size which will flatten the index structure usually improves accessing the file, but it's usually a case of try it on your particular data file and application and see what happens. (Chris:) I'd also suggest you look for efficient use of the buckets. If you have a 600 byte record, then you don't want a 2 block bucket size, as you may only have one record per block and about 400 bytes unused space. A bucket size of 3 might be better: you don't want a large amount of unused space. You do want some extra space for the RFAs mentioned earlier, otherwise if you delete one record there may not be enough space for a new record and RFA and again space will be wasted, so do leave a little space for future deletes. .BLANK 3.TEST PAGE 5 Frank Schipani, Emery University. I manage a comparatively large database with DTR and due to various changes in the database I have to use CONVERT a great deal. One feature I miss that is available on other systems that I don't have in CONVERT is a field description language, so if I am going to insert a field in the middle of a record is to go through an involved procedure. [Editor's note: a process using RMS utilities and sometimes COBOL was described.]# Is there any prospect of enhancing CONVERT. .PARAGRAPH (Bart:) I don't think a data restructure facility will be added to CONVERT. However, if you are doing things like that often you may want to use the SORT utility, which does have a field description language and a way to restructure records. SORT might be faster than DTR though not as fast as CONVERT. .BLANK Frank Schipani: SORT is going to have to sort a very large file that doesn't really need to be sorted. .BLANK (Bart:) I believe that MERGE has the same field description capability, and I believe there is also a qualifier that you can use to tell SORT that the data is close to being sorted already, or use /STABLE to tell it that it has less work to do. .BLANK (Chris:) I guess I don't understand why you would have a problem doing a redefine as was described [in the first question]. .BLANK Frank Schipani: it took two weeks as a background job. .BLANK (Chris:) Were you going from an indexed file to an indexed file? (Yes) O.K., that is your problem. You should try going indexed to sequential to restructure the data. .BLANK Frank Schipani: That's true but then it would still take overnight to do that. There should be a faster way: for example, if I use COBOL to unload to a sequential file, the same file that takes [] hours in DTR takes 8 minutes. .BLANK (Chris:) what you can also do is write a special record definition which has only three large fields. One goes from the beginning of the record to the place the new data will be inserted, then a field for the new space, and a third to cover the data to the end of the record. This will increase speed by reducing the time DTR takes for a field by field copy. This lets you stay in DTR rather than having to write in COBOL. .BLANK Frank Schipani: that's true. .BLANK (Bart:) also, when you define your sequential file, did you also pre-allocate space in the output file? When you move very large amounts of data, you want to go from one disk to another if possible, and you want to open the file with the space allocated all at once at the beginning rather than having to get it in chunks while the program is running. Otherwise, going from an indexed file to a sequential file should be about as fast in DTR as any other language unless you are doing a lot of fancy things with the fields. .BLANK Frank Schipani: I think that MOVE#CORRESPONDING works faster than whatever DTR is using. I think now that the difference was more like 1/2 hour versus 8 minutes. .BLANK 3.TEST PAGE 5 Lisa Axelrod, EG_&G.: I have a record definition I cannot change, and it has a history element which occurs 18 times, and has numeric and non-numeric information. I can't find any way to get "down there" unless FIND a unique record; and if they are all blank, how do I get down to a particular one? .BLANK (Chris:) what you are trying to do is find the first blank record? .BLANK Lisa Axelrod: no, I have 18 occurrences, and depending on what is happening, I may have to get to the nth element to store something in it. .BLANK (Chris:) there are several things you can do. Are you in a procedure, or doing this interactively? Is it within a BEGIN-END block so you can't do FIND and SELECT? .BLANK Lisa Axelrod: I'll do it any way I can. .BLANK (Chris:) [To do an interactive query,] you find the record you want to operate on, and SELECT it. Then you do a FIND giving the name of the list (the name on the OCCURS clause). This gives you what appears to be a collection of all of the data in the occurs clause. You can then do a PRINT#ALL, and if you want the 5th one say SELECT#5, and you can then print, modify, store, etc. .BLANK (Don Stern:) if you check the April 1986 issue of the newsletter, there was some "magic" printed from the Anaheim symposia on using running counts to get to a particular occurs clause record. (Chris:) that is better for procedures, rather than interactive queries which may be better with FIND#/#SELECT. (Bart:) The trick is to get one particular record, then treat the OCCURS clause as if it was another domain: that is what you have to remember. There was also an earlier newsletter that shows another method of getting to OCCURS clauses. [The two articles referred to are both transcriptions of Wombat Magic sessions. The first was by Diana Washburn, doing "subscripting" or "indexing" an OCCURS, printed in the ^&Wombat Examiner\&, Volume 6 Numbers 1_&2, April 1985 (combined Winter/Spring issue), pages 30 to 35. The second was by Rowland W.#Fox, Doing a Modify to the nth Field in an OCCURS, and is on page DTR-20 of the Combined Newsletter for April 1986, Volume#1, Number#8.] .BLANK 2.TEST PAGE 5 Doak Bane (?) Coleson Inc. I have a problem getting a date field defined as a date field in FMS. It does not seem to go into DTR as a date field. Is there some way around this? .BLANK (Chris:) The simple thing is that you cannot use FMS date fields with DTR date fields. The field in FMS must be character. .BLANK Doak Bane: I found that out. I had defined a date field and found that FMS sent more characters than I had defined. (Was the form defined for a domain or using a DISPLAY__FORM?) It used DISPLAY__FORM. .BLANK (Chris:) The simple thing to do then is to set the edit string in DTR to the way you want it, then use a character field in FMS (if you want MM/DD/YY then the FMS field must be 8 characters), then you can do a PUT__FORM#fms__field#=#FORMAT(date__field). This will force DTR to output the date to FMS as specified by the edit string. When you read it in, do a field#=#GET__FORM#fms__field, which DTR will read as ordinary characters and convert to a date as normal. It's the same with TDMS [as it is with FMS]. .BLANK 3.TEST PAGE 5 Wayne Heiderman: I'm dumping some information from an IBM mainframe, and it has a dollar figure with an overpunch on the last digit. Is there an easy way to get rid of it? .BLANK (Joe:) That particular problem has been addressed, and has been published in the newsletter. Basically, the method is to use define the last character of the field (the one that has the sign) separately from the rest of the numeric field, and use tables to convert the character that appears here to extract the sign and value for that field. You then use a COMPUTED#BY field or equivalent to reconstruct the number by multiplying the leading digits by 10 (the least significant digit was stripped off, remember), multiply by the sign (+1 or -1) from the sign table, and add the least significant digit from the conversion table. [An alternate method used by Bob Lott to convert DEC COMP__5 fields to IBM format using a CHOICE statement to change the least significant digit was published in the ^&Wombat Examiner\&, Volume#5, Number#4, Fall 1984, pages 46 and 47.] .BLANK 3.TEST PAGE 5 Paul Merbeme, Contro Co.: We have several records which were built with DIBOL and which have many dates in them. How do we convert that to DTR? (Question from Chris: what format are the dates in? Are they 6 characters, or 8 characters?) I believe they are 6 characters. (Chris: like two digit month, two digit day, etc.?) Yes. .PARAGRAPH (Chris:) it turns out that DTR is smart enough to do this during file restructuring. Your old file record would be PIC#X(6), the new file would have USAGE#DATE, both fields have the same name, if you do an NEW#=#OLD [as in the first question] DTR will do the date conversion. (Larry:) in some cases where the dates in the old files are not matching up correctly, for example they go year, month, day, then you can separate them into separate fields, and re-shuffle them around [as in a COMPUTED#BY], and as long as you can put it into something that looks like one of the DTR date formats it will do the conversion. Sometimes you have to put in the "/" characters, etc., but you should be able to make it work. .BLANK Paul Merbeme: I've tried that with EXTRACT to try flipping around the fields, with some success. .BLANK (Chris:) do you have the capability of changing that data file? .BLANK Paul Merbeme: no, otherwise the DIBOL program has to change. I don't have any problems printing out the dates, [but I want to use the date field in selection expressions to find certain dates]. .BLANK (Chris:) presently, a COMPUTED#BY field cannot have a USAGE#DATE. (Joe:) do you have the ability to add an additional field at the end of the record, so that DTR could read the file and put in the data in USAGE#DATE format in the extra field? .BLANK Paul Merbeme: No, I can't change the file. I could copy the whole thing over, but it would take a lot of space and time. .BLANK (unidentified:) why not just define the field in DTR with an edit string of "MMDDYY"? .BLANK (Joe:) it would not be a DATE data type, to be used for proper date comparisons. DTR treats the DATE data type in a very special manner. There is a difference between a data type of date, and numeric values corresponding to a month, day, and year. (Bart:) It sounds like you want to prompt somebody in DTR for a date, and then find the one in your file. (We want a starting and ending date). Doing a BETWEEN on data types other than USAGE#DATE will be rather difficult. .BLANK Paul Merbeme: I don't remember the exact solution, but basically I changed the date to strings. .BLANK (Chris:) you can also convert the date to a numeric value using year * 10,000 + month * 100 + day, to yield a numeric value which will sort dates properly. Then your starting and ending dates would also be PIC#9(6), and this format will compare properly. .BLANK Paul Merbeme: I have tried this. .BLANK (Bart:) I believe that if the COMPUTED#BY is done with the FN$--- function that converts an ASCII string to a date type, that perhaps that will yield the proper COMPUTED#BY field type. I have used this to convert dates. The format must be DD-MMM-YYYY, and you may need a table to convert the numeric month to JAN, FEB, MAR, etc. (Don Stern:) If you are willing to do the conversion in a FOR loop, you can declare a temporary variable of USAGE#DATE outside the FOR loop, and then use FN$STRING__EXTRACT to put it back into a date form, then do the math on the declared variable. (Chris:) we've got it: FN$DATE does the conversion from ASCII characters to 64 bit date type, so you can change your MM/DD/YY to a USAGE#DATE. (Bart:) I've used it, and I'm fairly certain the date input has to be DD-MMM-YYYY. I had to do a field#=#FN$DATE(day|"-"|month#via#mon__tab|"-19"|year) to get the conversion to work. The result was a standard USAGE#DATE field that did all of the normal DTR date functions and searches. (Joe:) I think the converted month must be upper case. [Converting the numeric month via a table yields an upper case month.] .BLANK 3.TEST PAGE 5 Ken Fox, Shearing Plough (?). I heard earlier this week that VMS was allowing shared access to sequential files. Does this mean we should expect DTR to follow in the near future? .BLANK Andy Schneider, DEC: [Editor's note: though not scheduled as a panelist, the person primarily responsible for the VAX-Datatrieve product obviously could not resist the opportunity to answer the question.] Please, I repeat, ^&please\&, don't use shared sequential file access with DTR if you are going to write to the file: let me explain why. DTR, when it connects to a sequential file, connects to END-OF-FILE. If you connect to END-OF-FILE, and your neighbor [sharing the file] connects to END-OF-FILE, and you are writing records to the file, and then your neighbor decides to add records, he is trying to add to his END-OF-FILE, but your END-OF-FILE is past that so he is going to write over your records because the END-OF-FILE markers don't get updated [from one user to another]. We are frantically looking at the correct method to do this, working with the RMS developers to determine what the right method is. For now, the watchword is CAUTION. Version 3.4 warns the users of the pitfalls. The problem has to be addressed by RMS as well as DTR. .BLANK (unidentified:) in the current version of DTR, will it even let you have more than one person write to a shared sequential file? .BLANK Andy Schneider: sure. What DTR does when you say READY#domain#SHARED, DTR does not look at what type of file it is, it simply passes the request off to RMS. Up until VMS#V4.4, RMS did support shared writes to sequential files if they were 512 byte fixed length records, and DTR will work with that file. The rejection comes from VMS. .BLANK (unidentified:) on shared files, if a file is locked by another user, is the only switch I have to set the DTR WAIT__LOCK switch, or are there also CDD and VMS switches I have to set for my users to wait before access? .BLANK Andy Schneider: you're talking about records in an RMS file? (Yes) Yes, WAIT__LOCK is the only switch you have to set to have the DTR user stall until the record is free. (Not CDD?) No, you are not in the CDD at that point. This has no effect on your data files. [There were some comments about dictionary sharing, which was lost as the tape changed sides.] If you set CDD wait, then it should not affect multiple users executing procedures, but will effect users if one person is editing a procedure. .BLANK 3.TEST PAGE 5 (unidentified) It was stated at a previous session that SELECT is not supported within a BEGIN-END block. I'd like a clarification of what "not supported" means, because in an application I wrote I have several people sharing update access to a single record in an indexed file (to increment a counter), and in order to release the record so the other users can get to it it is necessary to de-select the record. SELECT#NONE apparently works. .BLANK Andy Schneider: SELECT#NONE does work, but let me explain. There are two restrictions in DTR that people seem to stumble over every 5 minutes: the restriction that you can't use a FIND and you can't use a SELECT within a BEGIN-END block. Now, if you try to do it, DTR won't tell you "YOU CAN'T DO THAT!". The reason we say it isn't [supported] is because of the way DTR parses, compiles and executes a BEGIN-END block. DTR treats the entire BEGIN-END block as one statement, not a bunch of separate ones. If you have a FIND within a BEGIN-END block it will work; for example: .NO FILL BEGIN #####FIND#YACHTS END .FILL will work. However, if you have FIND#--- and SELECT in the BEGIN-END block, it won't work, because when it goes through and tries to generate the context for the SELECT, the FIND hasn't taken place, UNLESS: there was a FIND done outside the BEGIN-END block, and in that particular case the context for the SELECT is driven off of that FIND. As long as that FIND is for the domain the SELECT is supposed to be on, that's O.K. If the SELECT is in a loop, you will probably be selecting the same record over and over again, I believe, [because the context has already been set outside of the loop]. .BLANK (user:) That's exactly what I want to do: I want to SELECT the same record over and over again, and de-select the same record. .BLANK Andy: well, in that case it's not a restriction. We designed DATATRIEVE just for you! [laughter] Next question! .BLANK Larry: I think you should realize that at nearly every symposia someone comes up with something that's never been done, or we never ever conceived of being done in DTR, and I think you just blew Andy's mind for this symposia. .BLANK Andy: any other new features you'd like us to put in like that? [more laughter] .BLANK 5.TEST PAGE 5 (user:) I'd also like compiled procedures. .BLANK Andy: I was probably supposed to talk about this at some time. Any of you who have seen wishlists in the past have seen our response, which is, "that's a good idea, we will consider it for the future". Well, we spent about a month looking at compiled DTR, and what it would mean to compile DTR language. With the current architecture of the product, there would be so many restrictions that READY#YACHTS might not even work properly, because of it's flexibility and the point in it's path at which it resolves certain specifications. We investigated at a gut level how many man-months it would take to re-design DTR, and I won't give the figure but it's very large. At this stage in DATATRIEVE's stable life, and I say stable because we put in a lot of effort to make DTR a stable product, without bugs, it just doesn't seem to really work [to make it compile]. .BLANK (user:) what about the intermediate stages in the parse/compile/execute process? Does it have to be stored at the text level? .BLANK Andy: sometimes, maybe. There are certain things we do opening the dictionary and parsing things (described in the internals session), and it turns out that the earliest stage we could break the process and store the results and guarantee that it would work is at the lexical analysis stage, which is essentially where it is now in the CDD. We could find some simple cases where it could work at a later stage, but you couldn't do anything with database products, forms products, or any distributed work. This eliminates a lot of functionality from DTR. We are not throwing the idea away: I can fairly safely say that compiling DTR (the language) is something you will not see. The concept of compiling an entire application is something we are very seriously looking at for whatever else may come down the pike in this area.