Consultor Eletrônico

Status: Unverified

FACT(s) (Environment):

Oracle DataServer

SYMPTOM(s):

FOR EACH looping

FOR EACH never ends

Following code looping:
FOR EACH KM1SITP USE-INDEX KM1SITL1 NO-LOCK:
RUN prog/gbcbsp.p(C-Job,I-Nb_Copy, C-Site, KM1SITP.M1SITE).
END.

CAUSE:

The difference between the FOR EACH and the OPEN QUERY PRESELECT is that the second construct preselects the records for the query using the SHARE-LOCK and building the record list with the ROWIDs.
In the first construct, FOR EACH statement uses NO-LOCK and forces the usage
of index KM1SITL1, but it reads the records in each block iteration.

Therefore there must be something in the gbcbsp.p that changes the fields
used in the KM1SITL1 index and "OPEN QUERY PRESELECT" is not affected because it builds the result list using ROWIDs at the beginning and that doesn't change.

We did not receive a copy of the program gbcbsp.p to confirm our analysis.
We also don't know which index is use in the OPEN QUERY PRESELECT ("compile xref" would give the clue).

FIX:

Use the code:
DEFINE QUERY Site FOR KM1SITP.
OPEN QUERY Site PRESELECT EACH KM1SITP BY M1SITE.
REPEAT:
GET NEXT Site.
IF NOT AVAILABLE(KM1SITP) THEN LEAVE.
RUN prog/gbcbsp.p(C-Job,I-Nb_Copy, C-Site,KM1SITP.M1SITE).
END.