Hi all, me again with a READ question this time. If I specify a number of records to be read by the READ command is that limit per read loop (e.g. 5 records at a time) or is read one record at a time until 5 records have been read?
TIA,
Jennifer
I presume you are asking about code such as:
READ (5) MYVIEW IN PHYSICAL SEQUENCE
The (5) limits the number of iterations of the loop. In the code above , this is the same as the number of records that are read.
HOWEVER, if you were also to have a WHERE clause, e.g. READ (5) MYVIEW IN PHYSICAL SEQUENCE WHERE CODE NE 5
You could read MANY records before actually entering the loop five times, which is what the (5) is limiting.
steve
This is the code I am looking at:
READ(1) SALES-PRODUCT DESCENDING
WITH PRODUCT-KEY = #KEY-PROD-CNTL
So is this loop saying to find the first record that satisfies the condition of PRODUCT-KEY = #KEY-PROD-CNTL, and the read will loop until the record is found?
In a READ statement, the equal sign is an abbreviation for FROM, so in your example, you would retrieve records with PRODUCT-KEY greater than or equal to the value in #KEY-PROD-CNTL. After the first such record is processed, the “(1)” limit would be satisfied, and the loop would terminate.
In Natural 4 (mainframe) and Natural 6 (OpenSystems), READ syntax allows for multifetching. The following mainframe code sample would have Adabas retrieve 10 records at a time, but the loop would stop after 25 records were processed.
READ (25) MULTI-FETCH 10 SALES-PRODUCT
DESCENDING BY PRODUCT-KEY FROM #KEY-PROD-CNTLMinor correction to the last posting 
Since it is a READ DESCENDING, you would retrieve records with PRODUCT-KEY equal to or less than #KEY-PROD-CNTL.
In your example, the first record might indeed have PRODUCT-KEY equal to #KEY-PROD-CNTL. However, if there is no such PRODUCT-KEY on the file, you will read and process the first record with a smaller value of PRODUCT-KEY than specified.
Since you do not have a WHERE clause, you will read, at most, one record.
steve
So DESCENDING is not ordering the records? That changes the a lot then.
I am not sure what you mean. DESCENDING changes the direction of the READ.
Thus if you have:
READ(1) SALES-PRODUCT DESCENDING
WITH PRODUCT-KEY = #KEY-PROD-CNTL
and the value of #KEY-PROD-CNTL is 12345, and there is a record with that value, you will read just that one record.
HOWEVER, if this value does not exist on the file, the record you read might, for example, have a value of 12336.
steve
Perhaps Jennifer is comparing DESCENDING to ORDER BY in SQL.
In SQL, a set of records is retrieved, and ORDER BY manipulates the sequence after the fact.
A Natural READ DESCENDING reads an index in reverse sequence. The ordering is NOT done after the fact.
For example, for a file with key values from 1 to 99
READ file DESCENDING by KEY FROM 2retrieves records with key values 2 and 1. It does NOT read 2 through 99 and then sort them.
Ok, now I understand. I thought that the data is sorted first somehow.
The data is retrieved in sorted order. Unlike SQL which gets the set of data, then sorts it. Also, unlike SQL, if data is added while the READ is in progress, it will be included. That is, if you start your READ DESCENDING at 12345 and #9999 is added before you get to it, you will read 9999 (with SQL, the set of rows to satisfy the SELECT is determined first, then the rows are returned - database rows added after the set is built will not be seen).
In a sense, the READ only sees the record you are on and the next record to satisfy the sequence you are using, not the whole set that might satisfy your sequence.
It is important to reiterate Steve’s point.
READ(1) SALES-PRODUCT DESCENDING
WITH PRODUCT-KEY = #KEY-PROD-CNTL reads the first record with PRODUCT-KEY less than or equal to #KEY-PROD-CNTL. It does NOT “loop until the record is found”. Because of the (1) and that there is no WHERE clause, it will only ever read at most one record; this record may or may not match #KEY-PROD-CNTL.
Just one further note. I am guessing from the question Jennifer, that you are fairly new to Adabas and Natural. If this is so, Doug’s comment above, namely;
"In a sense, the READ only sees the record you are on and the next record to satisfy the sequence you are using, not the whole set that might satisfy your sequence. "
might seem at odds with Ralph’s earlier comment about MULTIFETCH. Yes, they are somewhat at odds with one another. MULTIFETCH is a new mainframe Natural facility. From what I can tell, it is still not being widely used (although for performance reasons it should be).
Ralph’s example was:
READ (25) MULTI-FETCH 10 SALES-PRODUCT
DESCENDING BY PRODUCT-KEY FROM #KEY-PROD-CNTL
The MULTIFETCH 10 means that Natural does indeed read more than one record at a time. Natural reads ten records at a time into a buffer. It then “reads” (from the buffer) until the buffer is exhausted, at which point it refills the buffer. Thus things can get “tricky”. Suppose the read gets records whose product-key are:
12345, 12341, 12338, 12322…, 12256
While I am reading and processing 12338, a new record is added with product-key 12257. I will NOT see the new record since the buffer is not updated when a record is added.
By contrast, suppose the new record had product key 12243. After reading through the buffer of ten (down to 12256), Natural will refill the buffer. Since 12243 will be in the next grouping of ten, (assume so anyways) we WILL see this added record.
Multifetch while maintenance (adds, deletes, updates) is going on, even if the maintenance is going on outside the program doing the multifetch, can be tricky.
steve
Hit “submit” before finishing my thought from halfway through the posting.
Back to Doug’s comment. In general, without MULTIFETCH, the statement is quite true. Doug was making the very important point that in general, Adabas/Natural is not like SQL; there are no “sets” as in SQL. Thus, processing is indeed one record at a time.
HOWEVER, Multifetch changes this somewhat. The size of a “set” is controlled by the specification after MULTIFETCH (ten in my example). However, note this is still not quite the concept of an SQL set.
Even if you have READ (10) MULTIFETCH 10…BY PRODUCT-KEY, you are reading ten records in product-key sequence; not reading them in some other sequence, then sorting the “set”.
Sorry for the long winded explanations. Sometimes seemingly simple questions have complicated answers in order to cover all contingencies (e.g. MULTIFETCH).
steve
Since Jennifer’s code is using READ (1), MULTIFETCH is not relevant. With READ(1), MULTIFETCH is not appropriate and would result in poorer performance.
Doug’s comment about READ (1) MULTIFETCH is of course correct. Sometimes threads get complicated by tangents (I am very guilty of this). In this case, I was trying to put into perspective a comment by Ralph re MULTIFETCH, and probably ended up adding to possible confusion. The point of my comment was simply to reinforce the point that when you MULTIFETCH you are not creating the equivalent of an SQL set.
steve
Natural handles the potential conflicts between muti-fetch and statement loop limits, so multi-fetch will not “result in poorer performance.” The following can be verified via DBLOG.
If the limit is 1, then multi-fetch is not invoked.
READ (1) MULTI-FETCH 10 file BY key /* No multi-fetchIf the limit is less than the multi-fetch amount, then multi-fetch is reduced.
READ (5) MULTI-FETCH 10 file BY key /* Multi-fetch 5Even with multiple iterations, multi-fetch will not exceed the limit.
READ (15) MULTI-FETCH 10 file BY key /* Multi-fetch 10; multi-fetch 5And, multi-fetch will not be invoked for records read in hold status.
READ (50) MULTI-FETCH 10 file BY key /* No multi-fetch
UPDATE /* because records are heldI’m feeling overwhelmed.
I was going to ask if it would be appropriate for me to translate a READ statement into a select statement but judging from the posts, doing so would be wrong. Even for simple READ statements?
And yes, I am completely new to Natural and database programming in general.