Re: Need ideas to reduce open/close in RPG program

I haven't followed this thread all the way through but here was my thought...

1) Create a keyed data queue.
2) Modify the existing program to read the existing data queue and write out to a keyed data queue. With the key being the "Control File" parameter.
3) Create a program that reads the keyed data queue based on a "key" parameter that is passed in to it. Submit 15 jobs calling this program passing the different possible "keys" for processing. Do the override & open that you do today but it's only needed once because that job is going to be responsible for the one data queue key & output file.

Mitch

On Dec 13, 2015, at 12:35 PM, Vinay Gavankar <vinaygav@xxxxxxxxx> wrote:

Thanks Buck for a detailed reply.

Here we have a single RPG program that is running all the time, receiving
data thru the data queue, reading the control file, overriding, opening,
updating and closing all the files for every update. There is no other RPG
program that is called 15 times for the update. As I had already indicated
in the original post, for the 'quick fix', we were thinking of defining all
the current 15 files in that same program, so they will be opened only
once. I was trying to see if there was any other quick fix option.

Adding the new column to the table was the long term option we had thought
of, but that would probably need the file to be converted to a
'partitioned' file, as the combined records from the 15 tables would
probably exceed the limit for a single member. (Nothing against partitioned
tables, they have lot of them). Since it is a 24x7 business, these kind of
things just take time to implement here due the amount of data. Since the
15 files are a 'moving' target, getting updated 24x7, they have to use
Mimix Promoters to copy the data into the new format and kep it updated.
Then once in 2 months they bring down the system for about 4 hours to
'install' software changes. Basically, any files that are locked all the
time can be 'changed' (format) only during that 4 hour window every 2
months.

Keeping the files open all the time is not an issue, as they have hundreds
of other files which are always open. I guess they use Save While Active
for their daily backups and all the files have Mimix running on 2 separate
boxes, one for Failover and another for Disaster Recovery.

On Sun, Dec 13, 2015 at 10:19 AM, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:

On 12 December 2015 at 12:11, Vinay Gavankar <vinaygav@xxxxxxxxx> wrote:

Buck: They do use performance gathering tools (iDoctor etc), and I think

it

showed that the process was doing open/close at the rate of about 1000

per

second (300K in a 5 minute period). Hence the panic.

The count of operations is much less important than the accumulated
elapsed time. For instance, if they told you that in the same second
they observed 1000 open/closes, they also observed 50000 MULT
operations, so therefore you must 'do something' about those MULTs.
When the elapsed time for the OPENs is .1 seconds and the elapsed time
of the MULTs is .0001 seconds, and the one, single page fault that is
occurring takes .4 seconds. This is an example, for illustration, but
I have seen altogether too many efforts to optimise for the wrong
issue.

I'm more than a little surprised that the analysts have focussed on
the number of OPENs, because they have 15 files, all of which need to
be updated every time. More interesting to me is the architecture
that has the process calling the same RPG program 15 times in order to
update those 15 files. Yes, that's 15 OPENs but that's not easily
avoided. But it's also 15 *INZSR, 15 internal sets of variables which
are initialised, 15 sets of file control blocks, etc, etc, etc.
Moving all 15 files into one RPG program wouldn't reduce the OPEN
count, but it would reduce all the overhead due to program startup.

Thinking strictly in terms of smallest change that may make a
measurable difference, that would be it. Instead of cycling through a
control file in order to issue OVRDBF and CALL RGPGM for each one, put
all the files in one program and update them all there. The tables
stay the same, the libraries stay the same, there's one different RPG
program and a smallish change to the 'driving' CL program. Run that
for a week and see how the elapsed time (not OPEN counts) changed.
Going back would be easy because it would be only 2 programs.

If that wasn't enough improvement (please tell me they have an actual
elapsed time target... N transactions per second) then the next 'least
intrusive' step is to keep the 15 files open all day. That's another
CLP and some more changes to the RPG that does the updates, in order
to not close the files between calls. There are ramifications though,
as the files will be allocated to this update process all day, so
there needs to be a way to signal the process to shut down gracefully
and release the locks for things like backup.

Personally, I find 'all day locking' to be more intrusive to the
system than Mark's idea of asynchronous updating, but that requires
some new plumbing (another dtaq, another dtaq monitor, etc). Weirdly,
it seems as if the analysts are more concerned with keeping the
programs the same/similar than they are about business processes. ie
N transactions per second vs N OPENs per second.

They are not very keen on on using SQL here, don't know why.

Glenn: To open all the files at once, wouldn't I have to 'define' all the
files (with actual names or dummy names) in the program?

Yes, of course but why would that be a problem for a 'quick fix?'
Typing is cheap compared to thinking, and thinking is cheap compared
to altering an in-production architecture.

Ken: Every update is done to every file defined in the control file. So
keeping a file open is not really an option.

I don't follow this. Put all 15 files in the 'updater' program, make
them USROPN and do a RETURN without a CLOSE or LR. The files will all
remain open. The only thing you need to add is a mechanism to CLOSE
them all for things like backups, IPL, etc.

Jon: Keeping 'all' the files open would imply defining all the files in

the

program, right?

It depends on what you mean by 'the program'. I can do this, and all
the files will stay open until end of job:
CL1:
OPNDBF DIVISION1
OPNDBF DIVISION2
CALL RPGDIV1
CALL RPGOTHER
CALL RPGDIV2

RPGDIV1:
FDIVISION1 USROPN
WRITE...
RETURN

RPGDIV2:
FDIVISION2 USROPN
WRITE...
RETURN

RPGOTHER:
FCUSTOMER
READ CUSTREC
SETON LR

RPG typically closes the files on exit, but you can alter that
behaviour with USROPN and RETURN. Files which a program does not
reference are... not affected. If DIVISION1 is open when RPGDIV2 is
called, it will remain exactly as it was when RPGDIV2 ends.

So, the RPG program that is doing the update does not need to
reference all 15 files in order for the job to keep all 15 files open.
But, as Jon notes, something must keep them open. In the above
example, that program is the CLP that kicks off the process.

Mark: It is a very 'elegant' solution, and I am going to shoot for that.

I

am not sure that they will accept it as a 'quick fix', but definitely as

a

long term solution.

I recommend this idea as a quick fix. But. The solution is a better
architecture. You face what many of us face, although instead of
multiple members, you're dealing with multiple libraries. The true
issue is that the database does not know what division (in my example)
a particular row belongs to, because the division is implied by the
library name. The solution is to add a new column to the tables to
store the division. Everything else - anything else - is a 'hack on
it' quick fix.
--buck
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.