[MULTI-FETCH-clause]
[RECORDS ] [IN ] [FILE ]
view-name
|
||||||||||||||
[PASSWORD= operand2]
|
||||||||||||||
[CIPHER= operand3]
|
||||||||||||||
[WITH
REPOSITION ]
|
||||||||||||||
[sequence/range-specification] | ||||||||||||||
[STARTING WITH ISN= operand4]
|
||||||||||||||
[[IN ] SHARED HOLD [MODE =option]]
|
||||||||||||||
[SKIP [RECORDS ] IN
HOLD ] |
||||||||||||||
[WHERE
logical-condition]
|
||||||||||||||
statement | ||||||||||||||
END-READ
|
(structured mode only) | |||||||||||||
LOOP |
(reporting mode only) |
This document covers the following topics:
For an explanation of the symbols used in the syntax diagram, see Syntax Symbols.
Related Statements: ACCEPT/REJECT
|
AT BREAK
| AT START OF DATA
| AT END OF DATA
| BACKOUT TRANSACTION
| BEFORE
BREAK PROCESSING
| GET TRANSACTION
DATA
| DELETE
|
END TRANSACTION
| FIND
| HISTOGRAM
| GET
| GET
SAME
| LIMIT
|
PASSW
| PERFORM BREAK PROCESSING
| READLOB
| RETRY
| STORE
| UPDATE
| UPDATELOB
Belongs to Function Group: Database Access and Update
The READ
statement is used to read records from a database. The records can
be retrieved in physical sequence, in Adabas ISN sequence, or in the value sequence of a
descriptor (key) field. The READ
statement causes a processing loop to be
initiated.
See also the following sections in the Programming Guide:
Operand Definition Table:
Operand | Possible Structure | Possible Formats | Referencing Permitted | Dynamic Definition | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
operand1
|
C | S | N | P | I | B * | yes | no | ||||||||||||
operand2
|
C | S | A | yes | no | |||||||||||||||
operand3
|
C | S | N | yes | no | |||||||||||||||
operand4
|
C | S | N | P | I | B * | yes | no |
* Format B of operand1
and
operand4
may be used with a length of less
than or equal to 4.
Syntax Element Description:
Syntax Element | Description | ||
---|---|---|---|
operand1
|
Number of Records to be Read:
The number of records to be read may be limited by specifying
Example: READ (5) IN EMPLOYEES ... MOVE 10 TO CNT(N2) READ (CNT) EMPLOYEES ... For this statement, the specified limit has priority over a limit set with a
If a smaller limit is set with the profile or session parameter Notes:
|
||
ALL
|
ALL Option:
To emphasize that all records are to be read, you can optionally
specify the keyword The |
||
MULTI-FETCH-clause
|
MULTI-FETCH Clause:
See MULTI-FETCH Clause below. |
||
view-name
|
View Name:
As In reporting mode, view-name is the name of a
DDM if
no |
||
PASSWORD=operand2
|
PASSWORD and CIPHER Clauses:
These clauses are applicable only to Adabas or VSAM databases. They cannot be used with Entire System Server. The The |
||
WITH REPOSITION |
WITH REPOSITION Option:
This option is used to make the |
||
sequence/range-specification
|
Sequence/Range Specification:
This option specifies the sequence and/or the range of retrieval. See Sequence/Range Specification. |
||
STARTING WITH
ISN=operand4
|
STARTING WITH ISN Clause:
This clause applies only to Adabas and VSAM databases. Access to Adabas
This clause can be used in conjunction with a
Access to VSAM
This clause can only be used in physical sequence. The value supplied
( Examples
This clause may be used for repositioning within a For an example, see the program |
||
[[IN]
SHARED HOLD [MODE=option]] |
SHARED HOLD Clause:
Note: This clause can be used to place records being read in a "shared hold" state. A record can be put in shared hold by many users at the same time. As long as a record is in a shared hold state, it is protected from being updated, because it cannot be set into an exclusive hold by parallel users. This ensures data consistency for the record data, as no one can update the record while it is being processed. Especially if the same record is fetched with multiple statements to read
different MU/PE occurrences ( Although such a hold state is an efficient way to protect read sequences, it is
a basic and important matter when to release the record again from this
"soft lock". Since this question depends on individual
application aspects, different options can be selected with the
|
||
MODE Option
|
Hold Period
|
Explanation
|
|
C |
Only at the moment of reading the record. | Ensures only that the record version being read has been committed by the last user who updated the record. This option does not really set a lock in hold state, but checks only that the record is not in exclusive hold by another user at time of read. | |
Q |
Until the next record in a sequence is read. | Releases the record from shared hold when
|
|
S |
Until the logical transaction is terminated. | Releases the record from shared hold when a logical
transaction is terminated with an END
TRANSACTION or BACKOUT
TRANSACTION statement.
|
|
MODE=Q and MODE=S
ensure that the record being read cannot be updated concurrently by other users
until it has been released from hold again.
If the See also Example 8 - SHARED HOLD Clause below. |
|||
SKIP
RECORDS IN HOLD |
SKIP RECORDS Clause:
Note: Whenever a record is going to be read with hold, a Natural error NAT3145
(Adabas response code Although error NAT3145 is surely the right reaction to assure a "clean
data processing", sometimes it might be useful if a record in hold
could be skipped. If it is alright that such a record will not be processed and
the loop processing should continue, the If the If the record is already in hold and the Natural error NAT3145 would occur,
See also Example 9 - SKIP RECORDS Clause. |
||
WHERE
logical-condition
|
WHERE Clause:
The The syntax for a If a |
||
END-READ
|
End of READ Statement:
In structured mode, the Natural reserved keyword In reporting mode, the Natural statement |
||
LOOP |
Note:
This clause can only be used for Adabas or Db2
databases.
MULTI-FETCH
|
|
For more information, see the section MULTI-FETCH Clause (Adabas) in the Programming Guide or Multiple Row Processing (SQL) in the Natural for Db2 part of the Database Management System Interfaces documentation.
Note:
This option can only be applied if the underlying database is Adabasor VSAM.
With a WITH REPOSITION
option, you can make a READ
statement
sensitive for repositioning events. This allows you to reposition to another start value
within an active READ
loop. Processing of the READ
statement
then continues with the new start value.
A repositioning event is triggered by one of two ways when you use a READ
statement with the WITH REPOSITION
option:
When an ESCAPE TOP REPOSITION
statement is executed. At execution of an ESCAPE TOP REPOSITION
statement, Natural makes an instant branch to the loop begin and performs a restart;
that is, the database repositions to a new record in the file according to the
current content of the search value variable. At the same time, the loop-counter
*COUNTER
is reset to zero.
When a READ
loop tries to fetch the next record from the database and
the value of the system variable *COUNTER
is 0
.
Note:
If *COUNTER
is set to 0
within the
active READ
loop, processing of the current record is continued; no
instant branch to the loop begin is performed.
If the READ
statement has a loop-limit (e.g. READ (10) EMPLOYEES
WITH REPOSITION ..
) and a restart event was triggered, the loop gets
another 10 new records, no matter how many records where already processed until the
repositioning takes place.
If an ESCAPE TOP
REPOSITION
statement is executed, but the innermost loop is not
capable of repositioning (since the WITH REPOSITION
keyword is not set
in the READ
statement or the posted loop statement is anything else but
a READ
), a corresponding runtime error is issued.
Since the ESCAPE TOP
statement does not allow a reference, you can
only initiate a reposition event if the innermost processing loop is a READ
..WITH REPOSITION
statement.
A reposition event does not trigger the execution of the AT START OF DATA
section, nor does it
trigger the re-evaluation of the loop-limit operand (if it is a variable).
If the search value was not altered, the loop repositions to the same record like at initial loop start.
Three syntax options are available to specify the sequence and/or the range of retrieval.
Syntax Option 1: READ PHYSICAL
[IN ] [PHYSICAL ]
|
[SEQUENCE ]
|
Syntax Option 2: READ BY ISN
[IN ] [ISN ]
|
[SEQUENCE ]
|
||||||||||||||
ISN
|
|
operand6 | operand7 |
Syntax Option 3: READ BY DESCRIPTOR
[IN ] [LOGICAL ]
|
[SEQUENCE ]
|
||||||||||||||||
descriptor |
|
operand6 | operand7 | ||||||||||||||
|
operand6
TO
operand7
|
||||||||||||||||
|
operand6 |
Notes:
ENDING AT
,
THRU
and TO
may not be used. These comparators are also
valid for the HISTOGRAM
statement.
Operand Definition Table:
Operand | Possible Structure | Possible Formats | Referencing Permitted | Dynamic Definition | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
operand5
|
S | A | yes | no | ||||||||||||||||
operand6
|
C | S | A | N | P | I | F | B * | D | T | L | yes | no | |||||||
operand7
|
C | S | A | N | P | I | F | B * | D | T | L | yes | no |
* Format B of operand6
and
operand7
may be used only with a length of
less than or equal to 4.
Syntax Element Description:
Syntax Element | Description |
---|---|
READ IN PHYSICAL SEQUENCE
|
Read in Physical Sequence:
This option is used to read records in the order in which they are physically stored in a database. Note:
|
READ BY ISN
|
Read by ISN:
This option is used to read records in the order of Adabas ISNs (internal
sequence numbers) or VSAM RBAs (relative byte addresses of
ESDS) or RRNs (relative record numbers of RRDS) respectively.
Instead of using the keyword For
Note: |
READ IN LOGICAL SEQUENCE
|
Read in Logical Sequence:
This option is used to read records in the order of the values of a descriptor (key) field. If you specify a descriptor, the records will be read in the value sequence of the descriptor. A descriptor, subdescriptor, superdescriptor or hyperdescriptor may be used for sequence control. A phonetic descriptor, a descriptor within a periodic group, or a superdescriptor which contains a periodic-group field cannot be used. If you do not specify a descriptor, the default descriptor as specified in
the DDM (field Note: If the descriptor used for sequence control is defined with null-value suppression (Adabas only), any record which contains a null value for the descriptor will not be read. If the descriptor is a multiple-value field (Adabas only), the same record will be read multiple times depending on the number of values present. Note: |
ASCENDING | DESCENDING | VARIABLE | DYNAMIC
SEQUENCE
|
Ascending/Descending Order:
This clause only applies to Adabas, XML
databases, VSAM and SQL
databases. In a With this clause, you can determine whether the records are to be read in ascending or descending sequence.
|
STARTING
FROM
... ENDING AT/TO |
STARTING FROM/ENDING AT Clauses:
The The In order to limit the records to an end value, you may specify an
|
THRU/ENDING AT
|
THRU/ENDING AT Option:
If Internally, to determine the end of the range to be read, Natural reads one
record beyond the end value. If you have left the The |
TO
|
TO Option:
If the keyword |
The following list describes the functional differences between the usage of the
THRU/ENDING AT
and
TO
options.
THRU/ENDING AT | TO |
---|---|
When the READ loop terminates because the end value has been
reached, the view contains the first record "out-of-range".
|
When the READ loop terminates because the end value has been
reached, the view contains the last record of the specified range.
|
If a end value variable is modified during the READ loop, the
new value will be used for end value check on next record being read.
|
The end value variable will only be evaluated at READ loop
start. All further modifications during the READ loop have no
effect.
|
An incorrect range (for example, READ .. = 'B' THRU 'A' ) does
not cause a database error, but just returns no record.
|
An incorrect range results in a database error (for example, Adabas RC=61), because a value range must not be supplied in descending order. |
If a READ .. DESCENDING is used with the start and end values,
the start value is used to position in the file, whereas the end value is used
by Natural to check for "end-of-range". Therefore the start value
is higher than (or equal to) the end value.
|
Since both values are passed to the database, they have to appear in ascending order. In other words, the start value is lower than (or equal to) the end value, no matter if you are reading in ascending or descending order. |
In order to check for range overflow, the descriptor value has to appear in the underlying database view; that is, it must be returned in the record buffer. | The descriptor is not required in the record fields returned. |
The end value check for an Adabas multi-value field (MU-field) or a sub-/super-/hyper-descriptor is not possible and leads to syntax error NAT0160 at program compilation. | You may specify an end value for MU-fields and sub-/super-/hyper-descriptors. |
Can be used for all databases. | Can be used for all databases. |
Note:
The result of READ
/HISTOGRAM
THRU
/ENDING AT
might differ from the result of
READ
/HISTOGRAM
TO
if Natural and the accessed database reside on different platforms with
different collating sequences.
The Natural system variables *ISN
and *COUNTER
are
available with the READ
statement.
The format/length of these system variables is P10. This format/length cannot be changed.
The usage of the system variables is illustrated below.
System Variable | Explanation |
---|---|
*ISN |
The system variable Notes:
|
*COUNTER |
The system variable *COUNTER contains the number of times
the processing loop has been entered.
|
** Example 'REAEX1S': READ (structured mode) ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 PERSONNEL-ID 2 NAME 1 VEHIC-VIEW VIEW OF VEHICLES 2 PERSONNEL-ID 2 MAKE END-DEFINE * LIMIT 3 * WRITE 'READ IN PHYSICAL SEQUENCE' READ EMPLOY-VIEW IN PHYSICAL SEQUENCE DISPLAY NOTITLE PERSONNEL-ID NAME *ISN *COUNTER END-READ * WRITE / 'READ IN ISN SEQUENCE' READ EMPLOY-VIEW BY ISN STARTING FROM 1 ENDING AT 3 DISPLAY PERSONNEL-ID NAME *ISN *COUNTER END-READ * WRITE / 'READ IN NAME SEQUENCE' READ EMPLOY-VIEW BY NAME DISPLAY PERSONNEL-ID NAME *ISN *COUNTER END-READ * WRITE / 'READ IN NAME SEQUENCE STARTING FROM ''M''' READ EMPLOY-VIEW BY NAME STARTING FROM 'M' DISPLAY PERSONNEL-ID NAME *ISN *COUNTER END-READ * END
PERSONNEL NAME ISN CNT ID --------- -------------------- ----------- ----------- READ IN PHYSICAL SEQUENCE 50005800 ADAM 1 1 50005600 MORENO 2 2 50005500 BLOND 3 3 READ IN ISN SEQUENCE 50005800 ADAM 1 1 50005600 MORENO 2 2 50005500 BLOND 3 3 READ IN NAME SEQUENCE 60008339 ABELLAN 478 1 30000231 ACHIESON 878 2 50005800 ADAM 1 3 READ IN NAME SEQUENCE STARTING FROM 'M' 30008125 MACDONALD 923 1 20028700 MACKARNESS 765 2 40000045 MADSEN 508 3
Equivalent reporting-mode example: REAEX1R.
DEFINE DATA LOCAL 1 MYVIEW VIEW OF ... 2 NAME 1 #STARTVAL (A20) INIT <'A'> 1 #ATTR (C) END-DEFINE ... SET KEY PF3 ... READ MYVIEW WITH REPOSITION BY NAME = #STARTVAL INPUT (IP=OFF AD=O) 'NAME:' NAME / 'Enter new start value for repositioning:' #STARTVAL (AD=MT CV=#ATTR) / 'Press PF3 to stop' IF *PF-KEY = 'PF3' THEN STOP END-IF IF #ATTR MODIFIED THEN ESCAPE TOP REPOSITION END-IF END-READ ...
DEFINE DATA LOCAL 1 MYVIEW VIEW OF ... 2 NAME 1 #STARTVAL (A20) INIT <'A'> 1 #ATTR (C) END-DEFINE ... SET KEY PF3 ... READ MYVIEW WITH REPOSITION BY NAME = #STARTVAL INPUT (IP=OFF AD=O) 'NAME:' NAME / 'Enter new start value for repositioning:' #STARTVAL (AD=MT CV=#ATTR) / 'Press PF3 to stop' IF *PF-KEY = 'PF3' THEN STOP END-IF IF #ATTR MODIFIED THEN RESET *COUNTER END-IF END-READ ...
The following program reads records from the EMPLOYEES
file in logical
sequential order based on the values of the descriptor NAME
. A FIND
statement is then issued to the
VEHICLES
file using the personnel number from the EMPLOYEES
file as search criterion. The resulting report shows the name (read from the
EMPLOYEES
file) of each person read and the model of automobile (read
from the VEHICLES
file) owned by this person. Multiple lines with the same
name are produced if the person owns more than one automobile.
** Example 'REAEX2': READ and FIND combination ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 PERSONNEL-ID 2 FIRST-NAME 2 NAME 2 CITY 1 VEH-VIEW VIEW OF VEHICLES 2 PERSONNEL-ID 2 MAKE END-DEFINE * LIMIT 10 * RD. READ EMPLOY-VIEW BY NAME STARTING FROM 'JONES' SUSPEND IDENTICAL SUPPRESS FD. FIND VEH-VIEW WITH PERSONNEL-ID = PERSONNEL-ID (RD.) IF NO RECORDS FOUND ENTER END-NOREC DISPLAY NOTITLE (ES=OFF IS=ON ZP=ON AL=15) PERSONNEL-ID (RD.) FIRST-NAME (RD.) MAKE (FD.) (IS=OFF) END-FIND END-READ END
PERSONNEL FIRST-NAME MAKE ID --------------- --------------- --------------- 20007500 VIRGINIA CHRYSLER 20008400 MARSHA CHRYSLER CHRYSLER 20021100 ROBERT GENERAL MOTORS 20000800 LILLY FORD MG 20001100 EDWARD GENERAL MOTORS 20002000 MARTHA GENERAL MOTORS 20003400 LAUREL GENERAL MOTORS 30034045 KEVIN DATSUN 30034233 GREGORY FORD 11400319 MANFRED
** Example 'READSCND': READ (with DESCENDING SEQUENCE) ************************************************************************ DEFINE DATA LOCAL 1 EMPL VIEW OF EMPLOYEES 2 NAME 2 FIRST-NAME 2 BIRTH END-DEFINE * READ (10) EMPL IN DESCENDING SEQUENCE BY NAME FROM 'ZZZ' DISPLAY *ISN NAME FIRST-NAME BIRTH (EM=YYYY-MM-DD) END-READ END
** Example 'REAVSEQ': READ (with VARIABLE SEQUENCE) ************************************************************************ DEFINE DATA LOCAL 1 EMPL VIEW OF EMPLOYEES 2 NAME 2 FIRST-NAME 2 BIRTH * 1 #DIR (A1) 1 #STARTVALUE (A20) END-DEFINE * SET KEY PF7 PF8 * INPUT 'Select READ direction' // 'Press' 08T 'PF7' (I) 21T 'to read backward' / 08T 'PF8' (I) 'or' 'ENTER' (I) 21T 'to read forward' * IF *PF-KEY = 'PF7' MOVE 'D' TO #DIR MOVE 'ZZZ' TO #STARTVALUE ELSE MOVE 'A' TO #DIR MOVE 'A' TO #STARTVALUE END-IF * READ (10) EMPL IN VARIABLE #DIR SEQUENCE BY NAME FROM #STARTVALUE DISPLAY *ISN NAME FIRST-NAME BIRTH (EM=YYYY-MM-DD) END-READ END
DEFINE DATA LOCAL 1 #DIRECTION (A1) INIT <'A'> /* 'A' = ASCENDING 1 #EMPVIEW VIEW OF EMPLOYEES 2 NAME ... END-DEFINE ... READ #EMPVIEW IN DYNAMIC #DIRECTION SEQUENCE BY NAME = 'SMITH' INPUT (AD=O) NAME / 'Press PF7 to scroll in DESCENDING sequence' / 'Press PF8 to scroll in ASCENDING sequence' .. IF *PF-KEY = 'PF7' THEN MOVE 'D' TO #DIRECTION END-IF IF *PF-KEY = 'PF8' THEN MOVE 'A' TO #DIRECTION END-IF END-READ ...
** Example 'REASISND': READ (with STARTING WITH ISN) ************************************************************************ DEFINE DATA LOCAL 1 EMPL VIEW OF EMPLOYEES 2 NAME 2 FIRST-NAME 2 BIRTH * 1 #DIR (A1) 1 #STARTVAL (A20) 1 #STARTISN (N8) END-DEFINE * SET KEY PF3 PF7 PF8 * MOVE 'ADKINSON' TO #STARTVAL * READ (9) EMPL BY NAME = #STARTVAL WRITE *ISN NAME FIRST-NAME BIRTH (EM=YYYY-MM-DD) *COUNTER IF *COUNTER = 5 THEN MOVE NAME TO #STARTVAL MOVE *ISN TO #STARTISN END-IF END-READ * #DIR := 'A' * REPEAT READ EMPL IN VARIABLE #DIR BY NAME = #STARTVAL STARTING WITH ISN = #STARTISN MOVE NAME TO #STARTVAL MOVE *ISN TO #STARTISN INPUT NO ERASE (IP=OFF AD=O) 15/01 *ISN NAME FIRST-NAME BIRTH (EM=YYYY-MM-DD) // 'Direction:' #DIR // 'Press PF3 to stop' / ' PF7 to go step back' / ' PF8 to go step forward' / ' ENTER to continue in that direction' /* IF *PF-KEY = 'PF7' AND #DIR = 'A' MOVE 'D' TO #DIR ESCAPE BOTTOM END-IF IF *PF-KEY = 'PF8' AND #DIR = 'D' MOVE 'A' TO #DIR ESCAPE BOTTOM END-IF IF *PF-KEY = 'PF3' STOP END-IF END-READ /* IF *COUNTER(0290) = 0 STOP END-IF END-REPEAT END
READ EMPL-VIEW WITH NAME = ... IN SHARED HOLD MODE=Q /* Record in shared hold until next record is read. ... GET EMPL-VIEW *ISN /* The record remains unchanged! ... END-READ
READ EMPL-VIEW WITH NAME = ... /* Records found are put in hold while reading. SKIP RECORDS IN HOLD /* Records already held by other users are skipped ... /* to prevent error NAT3145. UPDATE END TRANSACTION END-READ
** Example 'READVISN': READ with DESCENDING/VARIABLE BY ISN ** ** Note: The ASCENDING and DESCENDING order for READ BY ISN can only be ** applied to ADABAS 8.6 (or higher) on mainframes. ************************************************************************ DEFINE DATA LOCAL 1 EMPLOY-VIEW VIEW OF EMPLOYEES 2 NAME 2 FIRST-NAME * 1 #DIR-ASCENDING (A1) CONST<'A'> 1 #DIR-DESCENDING (A1) CONST<'D'> * END-DEFINE * WRITE 'READ with ASCENDING BY ISN = 500' READ (5) EMPLOY-VIEW ASCENDING BY ISN = 500 DISPLAY NOTITLE *ISN NAME FIRST-NAME END-READ * WRITE / 'READ with DESCENDING BY ISN = 500' READ (5) EMPLOY-VIEW DESCENDING BY ISN = 500 DISPLAY *ISN NAME FIRST-NAME END-READ * WRITE / 'READ with VARIABLE ascending BY ISN = 500' READ (5) EMPLOY-VIEW VARIABLE #DIR-ASCENDING BY ISN = 500 DISPLAY *ISN NAME FIRST-NAME END-READ * WRITE / 'READ with VARIABLE descending BY ISN = 500' READ (5) EMPLOY-VIEW VARIABLE #DIR-DESCENDING BY ISN = 500 DISPLAY *ISN NAME FIRST-NAME END-READ END *
ISN NAME FIRST-NAME ----------- -------------------- -------------------- READ with ASCENDING BY ISN = 500 500 JENSEN HANS 501 JENSEN CLAUS 502 SOERENSEN KARL 503 ERIKSSEN JONAS 504 ANDERSEN ANITA READ with DESCENDING BY ISN = 500 500 JENSEN HANS 499 MARTINEZ MANUEL 498 OSEA ROBERTO 497 FERNANDEZ CARMEN 496 DE LA IGLESIA JORGE READ with VARIABLE ascending BY ISN = 500 500 JENSEN HANS 501 JENSEN CLAUS 502 SOERENSEN KARL 503 ERIKSSEN JONAS 504 ANDERSEN ANITA READ with VARIABLE descending BY ISN = 500 500 JENSEN HANS 499 MARTINEZ MANUEL 498 OSEA ROBERTO 497 FERNANDEZ CARMEN 496 DE LA IGLESIA JORGE