|
The program is doing a CHGPF so there is no "movement" of thethe scenes).
data...at least not by my program...I don't know what CHGPF does behind
them.
After the CHGPF, I am doing an update for the fields that need to be
"adjusted".
The RPG program did not use the CHGPF. There was an old file with
data and a new file that was empty. The RPG program read the old
file, moved all of the fields to the new record adjusting the fields
that needed the prefixed
0
and then writing the new file.
The journal is disabled automatically at the start of the flow before
the CHGPF is executed.
The SQL is running commit = *NONE.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Charles Wilt
Sent: Thursday, February 25, 2021 10:07 AM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Need some help with SQL case/when performance
Are you doing an
insert into ... (select .. from)
Or are you doing
update ...
What's the RPG program doing?
I'm highly suspicious of the idea that SQL case statement of the form
case when vendor = ' ' then ' ' else '0' || vendor end
takes 12hrs and the RPG equivalent takes only 20min.
Is the file being written to journaled by chance?
Are you (unintentially?) running the SQL under commitment control?
68M records in a single transaction is going to be _very_ slow...
For the case of a mass update with either SQL or RPG, I'd make sure
the file being written to is _NOT_ journaled.
Unless you happen to have 5117-HA Journal Performance installed and
turned on.
Charles
On Thu, Feb 25, 2021 at 6:49 AM <smith5646midrange@xxxxxxxxx> wrote:
We are converting files and I have a BIG performance problem.fields.
The files are being converted via CHGPF. Some of the fields contain
numeric values stored in alpha fields. The client does not like to
use null capable fields so I think they did this to identify "no
value". I also know this file is pretty old so null might not have
been widely used back when it was created.
For an example of the data.our 5 digit vendor is stored in a 5A field.
After the CHGPF, it is a 6A field so the vendor is stored as "12345 ".
I am running an SQL that inserts a 0 in front so it will be "012345".
However, not all vendors have a value. Some are blank and we don't
want to create
the value "0 ".
I used the logic (forgive the syntax if it is wrong)
Vendor = case when vendor = ' ' then ' ' else '0' || vendor end
The problem that I am having is the performance on this seems
horrible. We have a file of 68M records and it has 5 of these types
of
Thereforewill vary.
the case/when exists 5 times in the update. The SQL update ran for
about
12
hours. When I created an RPGLE with normal F Specs and reads and
writes, it ran in 20 minutes. I have run this several times on
several different machines and the performance seems similar on all
runs.
The dilemma with this is the values in the records vary. Of the 5
fields, how many of them in each record will be populated and which
ones
So, my options as I saw them were either using the case/when orRPGLE?
running 5 different SQL updates each updating a single field where a
clause selecting that field running over 68M rows each time. At the
time, the case/when seemed like the better option.
Does anyone have any thoughts on what might be going on that would
cause such an extreme difference between the SQL case/when and
native
Does
anyone have a different idea how to approach this using SQL? I can
go back to the RPG logic but it seems "old school".
This request is strictly for ideas on conversion. I can't change
the field types or allow null or change the programs that are using
----
Thanks in advance.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.
Help support midrange.com by shopping at amazon.com with our
affiliate
link: https://amazon.midrange.com
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.
Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
