Subject: Natural READ statement options
Title: MULTIFETCH and REPOSITION together
Here is my sample program.
0010 DEFINE DATA LOCAL
0020 /*
0030 01 EMP VIEW OF EMPLOYEES
0040 02 PERSONNEL-ID
0050 02 FIRST-NAME
0060 02 NAME
0070 02 MIDDLE-NAME
0080 02 MAR-STAT
0090 02 SEX
0100 02 BIRTH
0110 01 #START-VAL (A10)
0120 01 #CURRENT-VAL (A10)
0130 END-DEFINE
0140 *
0150 FORMAT PS=20
0160 READ MULTI-FETCH 15 EMP
0170 WITH REPOSITION
0180 BY PERSONNEL-ID STARTING FROM #START-VAL
0190 #START-VAL := #CURRENT-VAL := PERSONNEL-ID |
0200 WRITE
0210 *COUNTER
0220 *ISN
0230 PERSONNEL-ID (AL=8)
0240 FIRST-NAME (AL=12)
0250 NAME (AL=12)
0260 MAR-STAT (AL=1)
0270 SEX (AL=1)
0280 BIRTH (AL=6)
0290 END-READ
0300 AT END OF PAGE
0310 INPUT 'Start value:' #START-VAL
0320 IF #START-VAL EQ '.' 210
0330 STOP
0340 END-IF
0350 IF #START-VAL NE ' ' AND
0360 #START-VAL NE #CURRENT-VAL
0370 ESCAPE TOP REPOSITION
0380 END-IF
0390 END-ENDPAGE
0400 END |
First I run it WITHOUT the REPOSITION to see the results of the MULTI-FETCH.
 Fig. 1
The first call is physical and calls 2-15 represent those included in the record buffer for
MULTI-FETCH. This would continue to show across several screens. The behavior is just as expected.
Now we are ready to look more closely at MULTI-FETCH and REPOSITION.
 Fig. 2
 Fig. 3
 Fig. 4
Now for the Adabas log trace:
 Fig. 5
 Fig. 6
 Fig. 7
Three questions:
- Can Adabas’ MULTIFETCH hurt you?
- How does one determine Natural’s MULTI-FETCH value?
- How does Adabas’ MULTIFETCH affect ET/BT commands?
Answer to #1
The answer lies in looking at the program listing and figures 5, 6 and 7.
Note: Always be careful of pulling back more records than you actually need. For online programs I use a simple rule - whatever count of detail records is needed to fill a screen that is the value I use for my MULTI-FETCH value.
Answer to #2
An interesting observation was made by one of the participants on Sag-l. If you set the
MULTI-FETCH value to 20, you are executing at best 1 physical call in 20 which is a reduction of 95% of the physical calls. A value of 50 computes to 98% reduction, and so on. A method for determining that max is to write an online adhoc program with all the fields in the Natural view that are defined to the program in question. Use TEST DBLOG to watch the Adabas activity.
You will know without having to calculate the maximum number of records one can return in a MULTI-FETCH request. Once you see a call w/o ‘<’ symbol and with ‘MA’ in the OP column, you know this is a physical call.
Answer to #3
When using Adabas’ MULTIFETCH option, only a subset of the records held by the current transaction is released. The records to be released from hold status are specified in the ISN buffer. However, If MULTIFETCH is set with ADARUN PREFETCH=YES, the "P" option is automatically used for ET/BT commands (the "M" option is automatically used for all other commands). |
