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

FWIW:

I have gone the OVRDBF/OPNQRYF route. It is working out quite nicely.
There are some places where SQL has been a good fit (like changing the
MEMBER for an order) and other places where RPG record-level-access is
better. Sometimes within the same program. Use the best tool for the job
I guess.

Jeez, I'm starting to sound like Joe Pluta. ;)

Terrence Enger wrote:

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?


Yes, indeed it does.

( My stance on performance issues is that machines should
work hard while people sit back and do more important
things. Still, I cannot stop my inner bithead from
commenting that on the one hand creation of a couple of
*FILE objects via CRTDUPOBJ is not overwhelmingly expensive
in comparison to job initiation and termination, while on
the other hand multiple orders received within one job can
be handled by just one (pair of) CRTDUPOBJ into QTEMP before
the first order followed by CLRPFMs for subsequent orders.
Oh, well, no matter. )

There remains the distinction between current orders and
advance orders. It would seem natural just to replace each
pair of two members with two files of one member each; a
UNION view supports the queries which must ignore the
distinction. No new field, no changed level ids, no need to
recompile the RPG (although you might choose to replace
OVRDBF in a CL program with EXTFILE() in the RPG).

How close does this sound?

Cheers,
Terry.




--
Jeff Crosby
UniPro Foodservice/Dilgard
260-422-7531
Opinions expressed are my own and not necessarily those of my company. Unless I say so.