Hi, please any tips for avoiding the following error during debug session at Natural One:
Error messages / full error message screenshot / log file:
NAT1009 Program interrupted after 12000 database calls.
Detailed explanation of the problem:
I’m debugging a program that will run in batch mode.
I tried using LIMIT command but it seems not related.
In the “Natural Server” view => Properties , I’ve put “MADIO=35000” but it seems it is not working, because our *ERROR-TA has recorded this:
MADIO or MAXCL EXCEEDED .
DEFAULT MADIO = 12000 , DEFAULT MAXCL = 5000
CURRENT MADIO = 42 , CURRENT MAXCL = 10
Here is the main structre of the program:
FR1. FOR #YEAR = #PARAM-INI TO #PARAM-FIN
RD1. READ ORIGIN-FILE WITH #DESCRIPTOR-ORIGIN = #YEAR
FN1. FIND NUMBER DESTINY-FILE WITH #DESCRIPTOR-DESTINY = *ISN(RD1.)
IF *NUMBER (FN1.) NE 0
ESCAPE TOP
END-IF
RD2. READ COMPLEMENTARY-DATA WITH #DESCRIPTOR-COMPL = #DESCRIPTOR-ORIGIN
...
END-READ /* RD2.
...
/* MOVE DATA FROM ORIGIN AND COMPLEMENTARY TO DESTINY FIELDS
ST1. STORE DESTINY-FILE
END-READ /* RD1.
END-FOR /* FR1.
This process loops trough some years, appending new records from origin into destiny with some complementary data.
Any deliberations are welcome. Thanks in advance.
Question related to a free trial, or to a production (customer) instance?
Production instance, using at Development environment.
For your READ loops, do you have an exit to the loops? READ file with de = ‘value’ is the equivalent of “read file with de starting with ‘value’”. That is, unlike a FIND that returns only de = ‘value’, it will return all de/sp values greater than or equal to the starting value.
Thus, “READ ORIGIN-FILE WITH DESCRIPTOR-ORIGIN = #YEAR” will read STARTING FROM #YEAR through end of file DESCRIPTOR-ORIGIN. With the nested RD2 doing the same thing, you could have a lot of wasted I/Os driving up your MADIO count.
My goal is to optimize these three searches of the program. The 1009 error is just an obstacle.
I am using the debugger to get this *NUMBER values of all the searches, to later experiment some variants of READ, and maybe some different main logical.
I undestand that the lower the *NUMBER, the better is the search. Is that correct?
*NUMBER is applicable to the FIND statement, not the READ statement. Only *COUNTER and *ISN are supplied by the READ statement. As a generalization, if a FIND will return 20% or less of the possible records, it will be more efficient than a READ PHYSICAL (the exact efficiency will depend on lots of things, such as the ratio of records per block, organization of data relative to the descriptor being used, etc). Generally, yes, you want to minimize *NUMBER for FINDs and *COUNTER (actual count of records read if WHERE clause not used).
You may not need your FOR loop - just “READ ORIGIN-FILE WITH DESCRIPTOR-ORIGIN = #PARAM-INI TO #PARAM-FIN”. How many records do you expect to read with RD2? You have the STORE outside of this loop, so I’m guessing only 1 or perhaps just a few - in which case, a FIND will likely be most efficient.
At R2 it is expected only one record, because #DESCRIPTOR-COMPL is unique, so I’ll replace for FIND.
About the FOR loop I’ve already been considering removing it.
I appreciate the suggestions, thanks!
