what could be the actual the difference between *COUNTER and *NUMBER …
what could be the actual the difference between *COUNTER and *NUMBER …
From the System Variables manual or chapter:
This system variable contains the number of times a processing loop initiated by a FIND, READ, HISTOGRAM or PARSE statement has been entered.
® notation after *COUNTER is used to indicate the statement label or source-code line number of the FIND, READ, HISTOGRAM or PARSE statement. If ® is not specified, *COUNTER represents the number of times the currently active processing loop has been entered.
*COUNTER is not incremented if a record is rejected as a result of the criteria specified in a WHERE clause. *COUNTER is incremented if a record is rejected as a result of an ACCEPT/REJECT statement.
This system variable contains either of the following:
the number of records which were selected as a result of a FIND statement (as a result of the WITH clause);
the number of values selected as a result of a HISTOGRAM statement;
the end offset of the fetched segment as a result of a READLOB statement;
the end offset of the inserted segment as a result of an UPDATELOB statement.
® notation after *NUMBER is used to indicate the statement label or source-code line number of the associated statement. If ® is not specified, *NUMBER automatically refers to the innermost active FIND, HISTOGRAM or READLOB processing loop by default. The ® notation is always required, if *NUMBER refers to an UPDATELOB statement.
So, syntactically you can see that *NUMBER is only available for the FIND and HISTOGRAM statements, while *COUNTER is available for FIND, READ, HISTOGRAM and PARSE statements.
For a FIND statement, *NUMBER contains the count of *ISNs found by ADABAS matching the WITH search criteria. This is available as soon as a the FIND statement is executed, indicating how many *ISNs are in the ISN list built by ADABAS. *NUMBER does not change as each record is returned from ADABAS to the Natural program. *COUNTER is incremented by 1 for each record read by the FIND loop. When all records have been processed through the loop, and if no records were skipped due to WHERE criteria, after the END-FIND the two values should be equal.
You can easily confirm this by putting a DISPLAY inside the FIND loop for each record processed, showing the record key value, *NUMBER and *COUNTER. For example if the FIND WITH returns 10 records, *NUMBER will = 10 for each line displayed, and *COUNTER will show 1 on the first line, 2 on the second, and 10 on the last.
*NUMBER on a HISTOGRAM is slightly different, but the same concept. For each Descriptor/Super-DE value returned, *NUMBER contains the number of *ISNs found with that value. *COUNTER would count the number of Descriptor/Super-DE values read and returned to Natural, one at a time same as for the FIND statement.
Hope that answers your question.
Thank you very much George.
Why read does not return *Number?
Adabas does not build an ISN list when it processes a READ command, so there is no count of ISNs to return in *NUMBER . For a READ LOGICAL it only has to get the next ISN in the inverted list for the specified descriptor, get the data record for that ISN, and return both to the Natural program. For a READ PHYSICAL it reads the next record in the data blocks for the file and returns that record and its ISN to the Natural program. For a READ BY ISN, it returns the next record in the file in ISN sequence. A READ is basically requesting a sequential read through a file, based on the requested sequence - descriptor, physical location, or ISN.
Since a READ only processes 1 record at a time, *NUMBER could never be greater than 1. The developers of ADABAS, in their wisdom, decided that *NUMBER was not relevant for the READ statement and set its value to zero if it refers to a READ statement.
Hope that’s clear enough. “WHY” questions can sometimes get philosophical … but not tonight.
Thanks, George. I understand that Adabas does not create any ISN list for read command. Even Histogram does not create any ISN list but it returns *number from the inverted list but why can’t read?
***Correct me if I am wrong.
OK Shubhanshu , so you do want the “philosophical” discussion.
I believe it boils down to efficiency vs. usefulness. Adabas was originally designed for speed of access, data security (nothing lost over disk and system crashes), and adaptability (easy file & field changes, etc.) compared to the other DBMS’s of that time. Relational databases have made great progress over the years and may match or even best Adabas in these areas now, but SAG appears to have no interest in adding any more relational features to their product.
You didn’t explain why you would want the READ statement to return a count in *NUMBER, or what value you would expect to be returned. I could guess that you have a requirement to display a list screen showing records in a file and allowing the user to scroll up and down through the file in the sequence of a particular descriptor or super-descriptor. And maybe there is a request to also show on the screen the number of records listed so far, and the total records available to list or the total remaining records to list.
While Adabas could probably be enhanced to provide this total count, it would consume a lot of disk I/O and elapsed time. It would not make sense to me to provide this functionality on all READ commands, especially for programs run in batch. If all READ commands had to determine the record counts first, before displaying or processing any data, large multi-user systems with large, multi-million record files (very normal for production Adabas databases), would grind to a halt as Adabas tried to spin through the inverted lists to compute the record counts for each READ command.
Note that the *NUMBER values returned for FINDs and HISTOGRAMs are essentially free, consuming no extra system resources beyond what is done for the FIND and HISTOGRAM. For a FIND, once the qualifying records are identified and the ISN list is built, the number of ISNs in the list is readily available. Same for HISTOGRAM, since the inverted list for a descriptor contains each unique value, the count of ISNs containing each value, and a list of the ISNs of the records having that value (like a mini-ISN list). For each descriptor value read and returned to the Natural program, the ISN count is right beside it in the inverted list and easy to grab and plug into *NUMBER.
For the READ command, no such free ride exists. There are no internal counters or structures in Adabas that would easily provide these record counts. Just the inverted lists themselves, faster than reading the data records, but still extra work to read them.
So what if the user absolutely insists on a total record count for the file or descriptor, or whatever? If the list screens are produced by a READ LOGICAL for a descriptor/super-DE, possibly with optional starting and ending values, the programmer can simply precede the READ LOGICAL with a HISTOGRAM for the same descriptor/super-DE, looping through the whole inverted list (or using the starting and ending values if specified) and summing each unique value’s *NUMBER into a total record count. Just be aware on large, multi-million record files, I’ve seen a HISTOGRAM take from 30 seconds to several minutes, depending on the number of unique values in the inverted list, disk speed, cpu speed, system load, etc… Then it’s up to the users to decide if it’s worth the wait for that count. Performing the HISTOGRAM with MULTI-FETCH 100 would speed things up a bit, but probably not a lot.
Hope that explains the READ command a bit better. And just one programmer’s opinion of why Adabas works the way it does. Basically, it is what it is, and over the years we’ve learned to live with it, if not love it. Many of us old timers love the simplicity of it.
Good luck to you,
There’s one additional aspect:
READ does not always use a descriptor, which would a) make it even more inefficient and b) prevent alternatives like a HISTOGRAM
Two more considerations:
Adabas can’t give Natural a count for READ … THRU, since Adabas doesn’t know the ending value. The TO clause is a relatively recent Adabas enhancement.
For performance, you design your FIND statements to retrieve no more than 100 records, otherwise you use a READ. Consider the situation where other users are actively adding and deleting records while your FIND is determining *NUMBER = 10. It is possible for one of those 10 records to be deleted before it can be retrieved in your FIND loop (Natural return code 3113). The higher the *NUMBER, the higher the risk of a 3113.
The risk is even greater with READ. You won’t fail with a 3113, but you could see “incorrect” results due to activity between the calculation of *NUMBER and the retrieval of the records.
If records are added:
*counter 9 *number 10 *counter 10 *number 10 *counter 11 *number 10 *counter 12 *number 10 *** End of report ***
And if records are deleted
*counter 7 *number 10 *counter 8 *number 10 *** End of report ***