Re: Advice needed on a multimember DDS file to be converted to DDL

OPNQRYF/OVRDBF is not a kludge to me.

Let me explain the MEMBER field use in another fashion.

The order number field is what separates each individual order.
The ship date determines what non-pending batch each order is ultimately placed into.

Field MEMBER does 1 job: separates orders into batches.

The batches are:
1) current orders
2) advance orders
3) pending orders (multiple batches could exist here)

When a DSR calls in a bunch of orders, they are transmitted one at a time to the System i (This is a vendor restriction, not mine. My end could handle multiple orders in a single transmission because the _order number field_ will keep orders separate.).

Here's what happens when it arrives:

1) That order is placed in it's own pending batch.
2) The order is allocated against inventory.
3) A results text file is prepared for the DSR.
4) If it is a current order (based on ship date), it is immediately placed in the current orders batch. Else it goes immediately into the advanced orders batch. That pending batch now no longer exists.
5) That results text file is transmitted to the DSR.

This all happens within 2-3 seconds. We have 4 phone lines and a VPN available for transmitting orders. Theoretically we could be receiving from up to 8 DSRs simultaneously, though it rarely goes above 3.

A pending batch only exists long enough for the results to be generated for the DSR. Then the pending batch vanishes because it gets 'absorbed' by one of the two permanent batches. The only reason MEMBER field exists is for these pending batches. Once it's in one of the permanent batches, the ship date determines whether it's current or advanced, and the order number keeps orders separate. From an IT dept view, field MEMBER serves no purpose after the order transmission is completely handled. Field MEMBER allows me to keep them logically separate without having to create/delete *FILE objects continually.

Make more sense?

Terrence Enger wrote:

At 12:51 2008-04-25 -0400, Jeff Crosby wrote asking for
advice about a pair (order header, order detail) of
multi-member physical files. There have been many
responses. Please let me join the party with a few
thoughts.

Jeff writes "OTOH I don't want a kludge." Me neither.
Still, that hasn't stopped me in the past <grin />.

I wonder, Jeff, what your ideal solution would look like?
Do you positively want to use embedded SQL? Or did you
mention it just as one way to select records with a
particular value of field MEMBER? Would the continued
presence of OVRDBF or OPNQRYF commands in the wrapper CL
programs be a kludge, in your opinion? Would the very
existence of wrapper CL programs be a kludge?

If I understand Jeff's thoughts correctly, the proposed
field MEMBER is being asked to do three (please pardon the
pun) distinguishable jobs.
(*) Field MEMBER distinguishes one incoming order from the
other incoming orders.
(*) Field MEMBER distinguishes incoming orders from the
accepted or otherwise "real" orders.
(*) Field MEMBER distinguishes today's orders from orders
for later shipment.
That is rather a lot to ask of one field.

The second distinction can be handled by a new pair of
physical files to hold the incoming orders. This may be a
natural thing to do if your business has the notion of
"accepting" an order and if the acceptance happens at this
time. Anyway, it may still be convenient at the programming
level. It may then be easier to invent other mechanisms for
the other two distinctions.

Lots of other points in the ensuing discussion caught my
attention, but I think it best to save my comments until
Jeff gives some indication of the direction of his interest.

In summary, I think it unlikely that any solution will be
entirely without kludge. But one can reasonably hope to
minimize the places where kludginess is manifest.

HTH,
Terry,
rent-a-geek and database bithead.