Re: just checking to see if record exists - SETLL on multi format logical versus individual files

Gqcy wrote:

Has anyone experienced the following?

This was a weird problem we have been struggling with for about
2 weeks. This Process is for our Bill of material, where used,
replace, deduct quantities, remove zero quantities, etc...

We had a case where we needed to modify almost every bill of
material structure we have, I think it was deduct some number of
rivets.

We fired off the job in batch, and after about 4 hours, we saw
that we were moving at a snail's pace, job would not finish for
about 20 hours...

we found the place where we were bogging down was where we check
to see of a specific part exists in the files. We have 3 bill of
material files. We _had_ been using a multi-format logical over
the 3 files (this is where we changed the program to GREATLY
improve performance). The file was opened for input, as we have
other files defined to handle the updates, deletes, and adds.

We would do a SETLL then check to see if we have a %equal
condition.

We needed to SETLL on each record format, because we needed to
check different things between different BOM files if we would
find a record.

We put the program into interactive debug: We found that the
SETLL on the first format went very fast. Record was not found
(the manual says that the file cursor goes to eof). We found that
the next SETLL on the second format, and the third format took
about 2 SECONDS longer than the first.

We first thought we had some sort of override problem, since we had many layers of member overrides, and library overrides to get
just the data set we wanted, but we couldn't get anything performing better...

We then removed the multi-format logical file, and instead
defined each file on its own. The process now performs fine.

I am guessing that there is some type of overhead in getting the
file cursor to each different physical file in a multi-format
logical that was eating up CPU cycles, but I'm not sure what...

Although there is overhead [potential, beyond the first touch] in access to each member, that is almost surely not the issue. Because the MFLF is one index created over all three dataspaces, any access to the member.data is deferred to when the row is actually found in the index. The real problem is almost positively the translation of the SETLL request to the LIC database; i.e. in how the positioning request is effected by the database. To avoid the performance issue, the [re]design to have separate files is better in the given instance.

The PERFM problem is a side effect of the member number being a part of the key [a hidden key of the index] such that potentially up to all of the index entries may be processed to find a row. For a row not found, I suppose that could possibly always be the case.? I really can not recall the full details, but the condition has been warned of for several releases in the documentation, because that performance problem was so often reported. I would probably be better able to recall from the actual layout of the file to include the cardinality of the data for the requests. See the "Note:" in
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/dbp/rbafocrlff.htm

For update processes negatively impacted by the above noted MFLF processing, i.e. unnecessarily held-up during the reading of so many index entries, there was an APAR MA34131 in v5r3. Note that this is not for the SETLL requester, but the impact on other jobs as updaters.

FWiW that APAR gives some additional text regarding the avoidance of the original SETLL problem, and perhaps some better details about the origin of the additional CPU required for the SETLL request:

"The essence of those recommendations <ed: the noted in
the above documentation URL into InfoCenter> are
1. use rpg CHAIN in place of SETLL. or
2. replace MFLF with individual logical files, one for each
format.

In this case, CHAIN works better than SETLL because
the CHAIN operation can position to the correct record/key
using the Index (using a GETKY request), while the
SETLL/READE will position prior to all records for the
SETLL request and then do READE requests (using GETKY next
equal) until it gets to the Data Space/physical file that
the application is interested in processing."

That APAR text is part of the PTF MF40864 documented at:
http://www-01.ibm.com/support/docview.wss?uid=nas3e10692dd9ebb70be8625727d0057e3ba

Regards, Chuck