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

Jeff,

This sounds like poor-man's commitment control. How about going to a
single member and using actual out-of-the-box commit/rollback with the new
orders?

Dan

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Friday, April 25, 2008 11:51 AM
To: Midrange Systems Technical Discussion
Subject: Advice needed on a multimember DDS file to be converted to DDL

As I have noted at least once before, I have gone through and converted
almost all our DDS defined PF to be DDL defined. Plus all the associated
LF to Indexes/Views. I am now down to the last 2 PF, the order header and
order detail (ORHDR and ORDTL, respectively). I saved these for last
because they would be the hardest.

They are both multimember and have associated LF, also multimember.
There are 2 permanent members, 1 for current days orders and 1 for advance
orders. There are also hundreds of transient members added/removed daily,
normally removed within seconds of being added.
What happens is the sales people (called a DSR in the business) transmits
an order from his/her laptop, a unique member is added to the files, the
order gets placed into that member, gets processed, the results
transmitted back to the DSR immediately, then the order gets put into the
correct permanent member, and the unique member is removed.
This happens in 2-3 seconds. By putting each of these orders into it's
own member, it is kept logically separate. There are no performance
issues whatsoever with this approach. But, as we all know, multimember is
a no-no with SQL.

(The inventory transaction file that I converted a while back was also
multimember, but there were only 3 RPG programs touching it. So a heavy
redesign didn't take that long. Read on.)

There are 66 (!) RPG programs that touch one or both of these order files.
A CL wrapper with OVRDBF is what points RPG to the correct member. Many
of these CLs also have OPNQRYF in them. I am hitting my head against the
wall trying to come up with an elegant way to handle all this. I'm sure I
have to add a MEMBER field to the files to maintain the logical
separation. SQL comes to mind instead of OPNQRYF, but that means it needs
to be embedded into the RPG as opposed to being in the CL. That means a
cursor with the embedded SQL. 66 programs means a lot of work in that
instance. OTOH I don't want a kludge. I really want to get to a single
member.

Suggestions?

Thanks.

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