On 12/10/14 5:27 PM, Evan Harris wrote:
A mapping file seems the logical way to go about it, and it seems like it
would naturally lend itself to some kind of caching approach, which might
just overcome your bottleneck concerns.
The problem with the mapping file approach is, as I said, the overhead
if it's done within the application's framework: its record structures
have, before a single user-defined field is added:
Fields for creation date, creation time, and creation user
Fields for last-update date, time, user, job, and program
An automatically generated ID field
A flag field.
That totals 86 bytes of overhead, per record, in addition to whatever
overhead is inherent to DB2/400.
The "protein" code is 3 characters. The "vegetable" or "starch" code is 8.
86 bytes of overhead for 11 bytes of mapping data, in a file that would
have to have a separate record for each valid combination of "protein"
and "vegetable," and the same, in another file, for each valid
combination of "protein" and "starch." Something about that strikes me
as being not much of an improvement over the initial hierarchical
solution that seemed so wrong.
Putting space for up to 10 "protein" codes, space-delimited, in each
"starch" and "vegetable" record, would add 39 to 41 characters
(depending on whether we need leading and/or trailing spaces for the
entire field) to each "starch" and "vegetable" record. With 20 "protein"
codes per record, it would be 79-81 characters. Then, the search would
work out to nothing more than, say, looking for all "starch" records for
which the "protein list" field contains the code for the desired "protein."