RE: [IE] Re: Mirroring by RRN slow for large file

Thanks guys,

You're right of course, since I overlooked the fact that table scanning relates to SQL updates and native access by RRN should be quick. (I'm guessing that iTera does not replicate using SQLl)

The updates and deletes to the large file always occur on the production system during a batch job that may run for several hours.
But the latency is definitely a figment of the target machine since the job on the source system has long since finished and all the journals have been received on the target and remain pending while slowly replicated.
The file has 4 access paths and a view over it but these are maintained on the source system as well while the production job is running and there is no significant performance hit there despite the fact that the batch job actually uses SQL for the I/O.
The file reuses deleted records which you would think may contribute to adding new records but this is necessary because of the huge number of regular deletes and puts and the difficulty of reorganizing the file as it quickly grows in size.

The mirroring vendor does not seem to have been much help. The file was recently given its own journal to mitigate latency on all the other production files, which has helped there but this does not seem to have helped with the large file.
It seems the latency arises when the number of journals pending replication on the target rises towards 10 million after which it struggles to catch up. When it drops beneath that it seems to catch up quickly.

Regards, Peter


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Nathan Andelin
Sent: Monday, 14 August 2017 3:02 AM
To: Midrange Systems Technical Discussion
Subject: [IE] Re: Mirroring by RRN slow for large file

Peter,

I haven't actually tested the performance of RRN vs. keyed index access on IBM i, but the IBM i index search may actually return a RRN if it works like some of the other DBMS products I've worked with. So a known RRN may be the quickest way to find records.

I'm not that familiar with iTera, but I wonder if it may have a configuration setting that controls the queue on the target system?

Nathan.


On Sat, Aug 12, 2017 at 11:39 PM, Peter Connell <Peter.Connell@xxxxxxxxxxx>
wrote:

I assume that mirroring software (ITERA) that uses journals for
replication to another IBM i5 performs updates and deletes on the
target system by using the RRN stored in each journal to replicate the
update or delete.
This is a reasonable assumption since the mirroring software knows
nothing about any index it might use to achieve the update of delete
via an access path.

A large file on the source system of 170 million records which may
attract several million updates and deletes in a day results in these
being queued for long periods on the target system before the actual
replication event occurs to keep the file in synch.
I presume that the substantial latency observed between the source and
target updates and deletes is because the record be updated or deleted
is being located via RRN.
Perhaps this is because DB2 has to do a table scan to find the record
which is slower than an update via a unique index.

It's not clear if creating an index whose primary key is RRN would
help otherwise the only solution may be to do a custom replication
that reads the journal, and extracts the unique index to do an update or delete.

Regards, Peter

############################################################## This
correspondence is for the named person's use only. It may contain
confidential or legally privileged information, or both. No
confidentiality or privilege is waived or lost by any mistransmission.
If you receive this correspondence in error, please immediately delete
it from your system and notify the sender. You must not disclose, copy
or rely on any part of this correspondence if you are not the intended
recipient. Any views expressed in this message are those of the
individual sender, except where the sender expressly, and with
authority, states them to be the views of Equifax. If you need
assistance, please contact Equifax :- Australia
www.equifax.com.au/contact New Zealand www.equifax.co.nz/contact
##############################################################
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: http://amzn.to/2dEadiD
############################################################## This correspondence is for the named person's use only. It may contain confidential or legally privileged information, or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any views expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the views of Equifax. If you need assistance, please contact Equifax :- Australia www.equifax.com.au/contact New Zealand www.equifax.co.nz/contact ##############################################################