Re: [External] IPL causing SQL index Issues

[x y <xy6581@xxxxxxxxx>]

A better answer: If your SQL apps look at physical files (which they
should, BTW) without LF's or indexes, it is likely MTI's are being
created.  If you IPL over the weekend and everything is crawling on Monday
morning, this may be the reason: apps are trying to execute SQL and SQL is
backed up trying to find (or build) the access path.

Look at the file SYSIXADV--it details the MTI's and provides guidance for
creating SQL indexes, the permanent version of MTI's.  Articles have been
published about SYSIXADV; Google is your friend.

My best guess is your old and new environments aren't the same.  One thing
that comes to mind is you've lost access path sharing on your new system:
if you have *DLY or *REBLD LF's and didn't restore *IMMED shared access
path files, you might be stuck with database housekeeping after an IPL.

If you have any daemons running, check the database setup for all the
tables they touch.

Do you have any high record-count files with large numbers of deleted
records? The system will grind away as it traverses those deleted records
with record-level IO and you just have to wait it out (just do a reorg and
be done with it).

Finally, try not IPL'ing just to confirm the problem is not some app going
crazy.

On Thu, Sep 5, 2024 at 7:02 AM Aaron Kuckuk via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

> We IPL Frequently.   Probably too frequently, but that is a different
> topic.
>
> We have the same IPL frequence on our old and new hardware, so that hasn't
> changed.
>
> Our start up QSTRUP process is the same on both our old/new hardware.
>
> Here is what I know
>  -  Old hardware, no issues
>  -  New hardware, SQL "goes nuts" after the IPLs for a few hours then the
> system seems to fix itself.
>  - We created permanent indexes which seems to have made the problem
> better (for now).
>
> I think I'm really looking for a system setting/configuration somewhere in
> regards to SQL, or MTI's or perhaps IPL settings.
> I just don't know where to look to see those settings.
>
> Aaron
>
>
>
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
> Aaron Kuckuk via MIDRANGE-L
> Sent: Thursday, August 29, 2024 8:46 AM
> To: midrange-l@xxxxxxxxxxxxxxxxxx
> Cc: Aaron Kuckuk <AKuckuk@xxxxxxxxxxxx>
> Subject: [External] IPL causing SQL index Issues
>
> We recently swapped out (upgraded) our hardware of our IBM.
>
> Since then, when we IPL, we have jobs that "go nuts" and take up all the
> CPU in the QUSRWRK Subsytem.
> We have to ENDJOB *IMMED them.    This continues for a few hours, but
> after few hours later, this phenomenon stop happening.
> These QUSRWKR jobs are created when other systems run SQL over the
> database.
>
> We are almost positive this is due to the system deleting temporary file
> indexes when the IPL happens.
>
> We are perplexed as to why did this start happening with the new
> hardware?    Is anyone aware of a system setting or some other place that
> cause indexes NOT to be deleted as part of an IPL?
>
> We know we can solve this by creation of permanent indexes.  However, we
> have users writing their own queries at any time so we really don't have a
> way to prevent someone from writing a poorly written query that would just
> cause this to re-occur.
>
> Thanks
> Aaron
> --
> 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.
>
>
> CAUTION: This email originated from outside of the organization. Do not
> click links or open attachments unless you recognize the sender and know
> the content is safe. (spf=pass)
> --
> 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.