RE: [EXTERNAL] Re: Analyzing RUNSQLSTMT jobs

Hi everyone
Here is my latest quirk/conundrum
I ran the reorgs this past weekend and the SQL query that was causing me heartache ran EXTRMELY quick on Sunday night, with no problems
However, the run on Monday night was STILL running in batch Tuesday morning – 11 hours later
Now heres the next kicker
I took a copy of the query that is being run, and changed it to create a file in a different library and ran it interactively
It finished in under 2 minutes
I have NO idea what to look at or even where to start
Does anyone have ANY ideas or hunches
As always – ALL answers gratefully accepted

Alan Shore
Solutions Architect
IT Supply Chain Execution

[cid:image001.png@01D777C5.3A4B1220]

60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx

‘If you're going through hell, keep going.’
Winston Churchill

From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Alan Shore via MIDRANGE-L
Sent: Tuesday, July 6, 2021 11:57 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: RE: [EXTERNAL] Re: Analyzing RUNSQLSTMT jobs

Thanks for your reply Mark

Re-use deleted records is NOT set on the files that I am looking at. I would DEFINITELY need to perform a RE-ORG on 3 files in particular THEN change them to re-use deleted records



Heres another question - hopefully you know the answer.

On at least 2 of the 3 files, there are some join logicals of BOTH of these 3 files

I was thinking of running 3 separate jobs at the same time doing a re-org on these 3 files

However - because of the join logicals - will that cause some conflict between the 2 re-orgs?




Alan Shore
Solutions Architect
IT Supply Chain Execution

[cid:image001.png@01D7725D.FBFDACB0]

60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx<mailto:ASHORE@xxxxxxxxxxxxxxxxxxxx>

‘If you're going through hell, keep going.’
Winston Churchill

From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Mark Waterbury
Sent: Tuesday, July 6, 2021 11:52 AM
To: Alan Shore via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>>
Subject: Re: [EXTERNAL] Re: Analyzing RUNSQLSTMT jobs

Alan,

A "mass purge" of database records could impact your applications performance if those physical files are not using REUSEDLT(*YES), so that a RGZPFM is needed to reorganize the PF member(s) to reclaim the space used by the deleted records.

After such a RGZPFM, any and all Logical File access paths over those PF members need to be updated; if the Logical Files (or physical files with keys) were not set to MAINT(*IMMED), this access path rebuild will occur on the next OPEN of that file. This could significantly slow down your applications, the first time each file is used after such a "re-org."

HTH,

Mark S. Waterbury

On Tuesday, July 6, 2021, 11:30:44 AM EDT, Alan Shore via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx%3cmailto:midrange-l@xxxxxxxxxxxxxxxxxx>>> wrote:

Thanks for your reply Vern
I am wading my way through the document that Jack sent, and it's a LOT of information to soak in
My problem is that a job that's presently running is still running from last night
Its been running for about 5 weeks or so - taking different times to complete - but NOTHING like it is today
However - in my analysis I have discovered that I believe a purge was recently run on a couple of the files
There are quite a number of deleted rows in these files
My question is - would the deleted rows impact SQL queries?
Forgive my ignorance as I haven't had a decent nights sleep in quite a few days and Im not thinking straight

Alan Shore
Solutions Architect
IT Supply Chain Execution

60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx<mailto:ASHORE@xxxxxxxxxxxxxxxxxxxx<mailto:ASHORE@xxxxxxxxxxxxxxxxxxxx%3cmailto:ASHORE@xxxxxxxxxxxxxxxxxxxx>>

'If you're going through hell, keep going.' --Winston Churchill

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

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx%3cmailto:support@xxxxxxxxxxxxxxxxxxxx>> for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com<https://amazon.midrange.com><https://amazon.midrange.com<https://amazon.midrange.com>>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l<https://lists.midrange.com/mailman/listinfo/midrange-l>
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l<https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com<https://amazon.midrange.com>