IBM Mainframe Forum

Hi,

I just need know why When Statemt is not recommeded Along with READ and FIND statements ?

FIND EMP WITH AGE WHERE NAME EQ 'AJAY

In this case if We will use ACCEPT or Reject statemets then how they will work ?

Thanks in Advance

First, allow me to reword the question. The WHEN clause is used in the DECIDE statement, not a READ or FIND, and WHERE is not a statement, it is a clause. The question should be phrased:

"Why would someone recommend the avoidance of the WHERE clause in a READ or FIND statement?"

Let's consider what the WHERE clause does. When Adabas returns a record, Natural applies the conditions found in the WHERE clause. If the conditions result in TRUE, we fall into the loop. If the conditions result in FALSE, Natural issues another READ command to Adabas to retrieve the next record - the loop is not entered.

Consider this example. You need to know how many AJAYs live in Iceland. There are 1,200 employees on file.

If all the Icelandic employees are AJAYs, you'll needlessly read and reject all the records to the end of the file. That is, Adabas will return 'BOB' and the WHERE will reject it because the country is not ICELAND. Then 'CARTER' will be read and rejected, etc, etc.

With the FIND statement, the number of records needlessly read and rejected should be much smaller, because you wouldn't code a FIND to return much more than 100 records, due to the negative impact on performance.

Of course the READ could easily be fixed by coding an end of range. Below, you'll see a TO clause limit the READ to AJAY records.

When someone tells you not to use the WHERE clause, what they mean to say is "Be careful when you use the WHERE clause. Understand what you are doing." On the other hand, you should avoid using a WHERE when update logic is involved. (That is, WHERE and UPDATE or DELETE in the same loop.)

The reason ACCEPT/REJECT is recommended over WHERE is that it still gives you control. You can test for end of file before executing the ACCEPT/REJECT, and avoid reading to the end of the file. But ACCEPT/REJECT uses a bit more CPU than WHERE, and it can still cause the same issue when update logic is involved.

To stay out of trouble with WHERE, use READ/TO/WHERE or READ PHYSICAL/WHERE, and don't combine them with UPDATE/DELETE.

Hi,

"If the conditions result in TRUE, we fall into the loop" Can you please explain me how it happens ?

At execution time, your cataloged code is interpreted by Natural's Run-time Environment. When your READ statement is encountered, Natural sends a READ command to Adabas and waits for the record to be returned. Note that Natural does not execute the TO clause; this is processed by Adabas, and an end-of-file indicator is returned when the range-ending value is exceeded. If an end-of-file indicator is returned, Natural falls through the end of the loop (END-READ) (but having first invoked any AT BREAK statements and AT END OF DATA clause).

For each record returned by Adabas, Natural
. executes the THRU clause, and falls through the end of the loop if the range-ending value is exceeded. THRU requires an extra record to be read to exceed the range.
. applies the WHERE criteria. If the result is FALSE, Natural issues the next READ command to Adabas. The loop is not entered. Logically speaking, Natural executes an ESCAPE TOP.
. increments *COUNTER and enters the loop.
. If an ACCEPT statement results in FALSE, a REJECT statement results in TRUE, or the END-READ is encountered, Natural executes an internal ESCAPE TOP.