Re: DB2 Views issue

My main suspect would be the IPL removing temporary indexes....

Since you mentioned that it was happening before the upgrade.

Don't suppose you have plan cache snapshots from when the statement was
running fine do you?

Without any historical data, you're just going to have to use VE to see
what the statement is doing and what indexes it's likely looking for.

Charles

On Mon, Aug 21, 2023 at 8:09 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

I have a complicated set of SQL views used by DB2 Web Query reports (and
other jobs)... last week I changed the where clause on a view called
V_INVOICES.

I did this by

1. Using ACS to Generate the SQL
2. Changing the "where" from WHERE IHYER# >= (YEAR(CURRENT TIMESTAMP)
- 3) AND IHREL# <> 0) to WHERE IHREL# <> 0)
IHYER# is a numeric representation of the year... like 2021

This view is referenced within another view, V_ORDERSA using a UNION.

I ran the report I needed, then changed the V_INVOICES where clause back
to it's original.

Long story... but, the response times on using V_INVOICES is now
ridiculous. Reports over the V_ORDERSA will not complete.
If I use RUNSQL Scripts or STRSQL on the green screen, the same is true...
STRSQL reports hundreds of millions of records read.

On top of that... we IPL'd Thursday morning and upgraded from 7.3 to 7.5
over the weekend (this issue was occurring last week as well).

No idea where to start.

TIA,
Greg
[Logo]<https://www.totalbizfulfillment.com/> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.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.