Hello Fellows,
I would like to know if some one of you can tell me in which rare cases the PREFETCH using by update loop may improve performance. I though that this kind of process may be affected in negative way with PREFETCH.
Best Regards,
Hi Osman,
The case where you have to read the entire file but you are actually updating only a fraction of them would be where PREFETCH/MULTIFETCH makes sense. Just be sure not to place your records read on update hold, but instead once you detect that this is a record you need to update, pass the ISN to a subroutine outside your READ loop where you GET the record with UPDATE hold and perform it there.
Hi Brian,
Let me see if I’ve got that right, when you said I should use the GET statement outside of the READ loop is that I create a internal subroutine that specify the GET ISN statement and its related UPDATE, for example:
READ.
READ filename by Superdes
.
.
.
PERFORM GET-ISN
.
.
END-READ.
.
.
DEFINE SUBROUTINE GET-ISN
GET.
GET filename *ISN (READ.)
UPDATE (GET.)
END TRANSACTION
END-DEFINE
END
What’s the difference if I write the ET logic inside the READ loop?
Best regards
If you put the UPDATE inside the READ loop, even if you are referencing your UPDATE back to a GET instead of the READ, the Natural object code is still written in such a way to make the L3’s become L6’s when Adabas is called (it doesn’t take chances that this isn’t what you want to do). By making the UPDATE be outside of the READ…END-READ block, the READs will be L3’s and the GETs will be L4’s instead of L1’s.
What Brian said about the L3 and L6 is not correct. A READ LOGICAL statement generates an L3 unless an UPDATE or DELETE is attached to it. There is no problem with placing the GET and UPDATE within the READ loop.
Here’s a sample program:
DEFINE DATA LOCAL
1 EMP VIEW EMPLOYEES
2 PERSONNEL-ID
END-DEFINE
R.
READ (10) EMP BY PERSONNEL-ID
G.
GET EMP *ISN (R.)
UPDATE (G.)
END-READ
ENDAnd here’s a listing of the generated Adabas commands:
ZZADACMD LEN Consulting LLC 06/23/2010 13:55
Natural Utilities ZZUtils v3.0
Natural Module ADABAS Command Analysis Page: 1
Library: ZZUTILS Modules: L3L6 - L3L6 Parms: ,0,N,N,N
Module Line ADABAS Command File
L3L6 0050 L3 Read Logical 4 EMPLOYEES-FILE
L4 Get with hold 4 EMPLOYEES-FILE
A1 Update 4 EMPLOYEES-FILE
----- End of Command List ----- Within Natural, the MULTI-FETCH clause is ignored if the READ has an update attached, to stop you from getting into trouble.
Outside of Natural, a Prefetch/Multi-fetch buffer can be allocated for an L4/L5/L6, but this is generally frowned upon, as Osman suggested, because of the performance hit from the ETs releasing and re-holding buffered records. Like SAG and most DBAs, I don’t recommend it, but it is possible to get small performance improvements for these commands if you keep the Prefetch/Multi-fetch buffer size very small. Be sure to test this before implementing in a Production application.
Hi Ralph,
What you suggested is keep using small buffer to the MULTI-FETCH or PREFETCH options to avoid get in trouble and to gain a little improve performance, didn’t you?
Thanks a lot (Ralph and Brian)
Yes, Osman, that is what I meant. No guarantee that you will see an improvement, though. Try it in a test environment with various PREFETCH buffer sizes to see if any would help.
Ralph,
As your command log shows, as did TEST DBLOG for me, indeed the READs were L3’s, not the L6’s I claimed they would be.
Wasn’t this a problem before though? I know this was a gotcha at some point. Why is it I thought that the UPDATE inside the READ block would make all those read records be placed on hold?
I don’t know, Brian. Perhaps you forgot your medication one day. Perhaps you were medicated one day. 
I don’t remember Natural having this problem.
I’ve seen this type of situation before. You discover a bug, and you come up with a workaround that becomes standard practice, only to find much later that the original problem has long since been resolved. You just never have time to revisit the problem to see that it’s gone.
At one time RESET *COUNTER would fail at execution time, so I replaced it with ASSIGN *COUNTER = 0. I used the ASSIGN statement for years, only to find that the bug’s duration was one patch level.
ps Rhetorical question: What sent you north to in my “home and native land?”
What was the motivation behind almost everything in the history of mankind? A woman, of course!
My fiancée is Canadian, and so I am working and living here in the GTA now. I still work for Eaton Corp, but lucky for me Eaton has it’s Canadian HQ (Eaton Yale Company) in close proximity to Toronto (Burlington).
I still am a Penguins fan, though, and shall always be.
thanks for all your suggestions 
Currently we have a report which shows the payment totals of each branch with coding using AT BREAK:
AT BREAK OF #Branch
Write // 5X ‘Branch Total’ SUM(#amount) 5X COUNT(#amount) ‘ITEMS’
NEWPAGE
END-BREAK
New requirement is to show a Total and Count per page. When it comes to the last page of that branch, the Page Total and Count must appear before the Branch Total and Count.
New code added:
AT END OF PAGE
Write // 5X ‘Page Total’ SUM(#amount) 5X COUNT(#amount) ‘ITEMS’
END-ENDPAGE
Results:
When a branch has multiple pages, each page shows the Page Total and Count correctly except on the last page of the branch, it shows only the Branch Total and Count, but no Page Total and Count.
The Page Total and Count appear after NEWPAGE.
Question:
‘AT BREAK OF #Branch’ does not seem to encounter it is also the end of the logical page. How do I get the END PAGE code to work within the AT BREAK?
Reginia
I don’t know how your post was attached to an existing thread with a new subject line, but I suggest that you resubmit your question in a new post under Natural for Mainframes or Natural for Open Systems, depending on your platform.
First, a note about one of Natural’s most frequently misunderstood statements. NEWPAGE does not start a new page. It sets a flag, which is tested for before any WRITE (PRINT, DISPLAY) statement. See/run the program below
- THIS PROGRAM DEMONSTRATES THAT NEWPAGE DOES NOT IMMEDIATELY
- START A NEWPAGE. IT MERELY SETS A FLAG WHICH IS CHECKED BEFORE
- NATURAL WRITES ANYTHING. IN THE EXAMPLE BELOW SEE IF YOU CAN
- FIGURE OUT WHAT THE VALUE OF #A WILL BE AT THE TOP OF THE
- SECOND PAGE
- SURPRISED? EVEN THOUGH THE XYZ WAS MOVED TO #A AFTER THE
- NEWPAGE, THE NEWPAGE COMMAND MERELY SET A FLAG WHICH
- DID NOT CAUSE A NEWPAGE UNTIL THE WRITE STATEMENT TO PAGE TWO
DEFINE DATA LOCAL
1 #A (A3) INIT <‘ABC’>
END-DEFINE
*
AT TOP OF PAGE
WRITE 10T ‘#A IS’ #A
END-TOPPAGE
*
WRITE 5/10 ‘THIS IS PAGE ONE OUTPUT’
NEWPAGE
MOVE ‘XYZ’ TO #A
WRITE 5/10 ‘THIS IS PAGE TWO OUTPUT’
*
END
Your requirement is indeed a tricky one. Assume the final page for a Branch has only five lines of output. The AT BREAK is executed. What you really want to be able to do is something like:
AT BREAK OF #BRANCH
WRITE last page SUM and COUNT
WRITE totals for Branch
NEWPAGE
END-BREAK
The obvious problem is you cannot “access” the last page functions until you actually reach the bottom of the page.
An approach which is not pretty, but will work, is to manually code the page system functions.
ADD #AMOUNT TO ##AMOUNT-SUM
ADD 1 TO #AMOUNT-COUNT
Then in the AT END OF PAGE clause you could
WRITE ‘page total’ #AMOUNT-SUM #AMOUNT-COUNT ‘ITEMS’
RESET #AMOUNT-SUM #AMOUNT-COUNT
In the AT BREAK clause you would:
AT BREAK OF #BRANCH
WRITE // ‘page total’ #AMOUNT-SUM #AMOUNT-COUNT ‘ITEMS’ // 5X ‘Branch Total’ SUM(#amount) 5X COUNT(#amount) ‘ITEMS’
NEWPAGE
END-BREAK
As noted above, not pretty, but it should work.
AT END OF PAGE and AT BREAK occur independently. AT BREAK typically happens while printing in the middle of the page - before the AT END PAGE occurs.
Here’s a working example. It uses a switch (#B) to avoid an end-of-page line after a break and tests for an end of data condition to avoid an additional end-of-page line after the final break.
DEFINE DATA LOCAL
1 EMP VIEW EMPLOYEES
2 PERSONNEL-ID
2 NAME
2 SALARY (1)
1 #B (L)
1 #COUNT-P (P7)
1 #SAL-P (P9)
1 #SAL-B (P9)
END-DEFINE
FORMAT PS=10
READ (24) EMP BY PERSONNEL-ID FROM '99' TO '999999'
ADD 1 TO #COUNT-P
ADD SALARY (1) TO #SAL-P
ADD SALARY (1) TO #SAL-B
DISPLAY GIVE SYSTEM FUNCTIONS
PERSONNEL-ID
NAME
SALARY (1)
AT BREAK OF PERSONNEL-ID /6/
ASSIGN #B = TRUE
WRITE 'page ' #COUNT-P T*SALARY (1) #SAL-P
/ 'break ' COUNT (PERSONNEL-ID) T*SALARY (1) #SAL-B
IF OLD (PERSONNEL-ID) <> PERSONNEL-ID
THEN
NEWPAGE
END-IF
RESET #B #SAL-B
END-BREAK
AT END OF PAGE
IF NOT #B
THEN
WRITE 'at end' COUNT (PERSONNEL-ID) T*SALARY (1) #SAL-P
END-IF
RESET #COUNT-P #SAL-P
END-ENDPAGE
AT END OF DATA
ASSIGN #B = TRUE
WRITE 'final ' COUNT (PERSONNEL-ID)
END-ENDDATA
END-READ
ENDThank you to Ralph and Steve for the suggestions. It is now working.
yaa absuletely you can
Using MULTIFETCH in conjunction with updates can increase performance but it can also decrease performance. Which result you get depends on how well you understand how this works.
Before you pursue this topic I recommend that you think about what your expectations are.
How does MULTIFETCH improve performance? It reduces the number of communication calls to the database. It does not reduce the number of records processed by the application. Using z/OS as an example, MULTIFETCH reduces the number of SVC calls. If the SVC call constitutes 3% (I made this number up) of the total “cost” of an Adabas command then MULTIFETCH=10 will reduce the SVC “overhead” by 90% (9/10) or 2.7% of the total cost. Setting MULTIFETCH=20 reduces the overhead by 95% (19/20) or 2.85% of the total cost. As you can see the benefits of MULTIFETCH quickly diminish. Multifetching 20 records instead of 10 only saves an additional 0.15%.
If the SVC overhead is 1% instead of 3% then MULTIFETCH=10 would only reduce the total cost by 0.9%. MULTIFETCH=20 would save you 0.95%, an increase of only 0.05%. Set your MULTIFETCH expectations realistically.
How can MULTIFETCH hurt performance in an update loop? If you set MULTIFETCH=10 for an UPDATE loop then all 10 records are placed on hold. If you issue an ET after processing the first record then all 10 records are released from HOLD and Adabas has to put records 2 thru 9 back on hold via HI (Hold ISN) command(s). Those extra commands hurt performance.
How can you prevent this performance hit? If you MULTIFETCH 10 records then only issue an ET after 10 records. Adabas will not have to “rehold” any records. You can set the number of records that are MULTIFETCHed to 10 by setting the MULTIFETCH parameter PREFNREC=10.
Happy MULTIFETCHing!
Regards,
Wayne