RE: DB2 Views issue -- MIDRANGE-L

No CTE's in these views... I have an IPL scheduled for tomorrow to apply the most recent HIPER PTF and Group.

I could investigate like you suggested, but I need to move on.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Monday, August 21, 2023 3:01 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: DB2 Views issue

Just had a thought...

Are you using CTE's at all in the views?

There was a recent change regarding shared CTEs.. one that's referenced
more than once in a statement.

with CTE as (...)
... from CTE ...
... from CTE ...

Prior to the change, the OS would determine rather or not to
materialize the CTE results or query the data twice, whichever is faster.
In order to be compatible with the SQL Standards, the SQE was updated to
always matericalize the CTE results.

More info here:
https://www.ibm.com/support/pages/implementation-changes-shared-common-table-expressions

Charles

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

So I gave up and Restored the two views from a backup done a week ago...
First run was 18 seconds... second was 612 ms.

So I generated the SQL and compared to what I was running (saved as a PC
file) to re-create the view.

Two differences.
1. What I was running used the "system" name for the internal views I was
joining. The generated SQL displayed the "long" names.
2. What I was running was missing column heading and text on a few fields.

Other than that.. no differences.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Charles Wilt
Sent: Monday, August 21, 2023 1:08 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: DB2 Views issue

I'd compare the VE for each...

In particular the Highlighted "most expensive" steps.

Charles

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

Correction... it took 7 seconds to complete on the DR box.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Birgitta Hauser
Sent: Monday, August 21, 2023 10:40 AM
To: 'Midrange Systems Technical Discussion' <

midrange-l@xxxxxxxxxxxxxxxxxx

Subject: RE: DB2 Views issue

Could it be when running it with ACS a temporary Index (MTI) was created
...
which can be (very) time consuming. But then it can be used from
everywhere.
So when you run the query from STRSQL the MTI was created and could be
used.

You may run your query through visual explain and then check if there is

a

MTI used. ... if so it would be a good idea to create this index
permanently.

As an aside when running IPL all MTIs are deleted, so next time the are
needed they have to be (re)created.
... and may be not only a single index was missing.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization - Education - Consulting on IBM i

IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training

them

and keeping them!"
"Train people well enough so they can leave, treat them well enough so

they

don't want to. " (Richard Branson)
"Learning is experience . everything else is only information!" (Albert
Einstein)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of

Greg

Wilburn
Sent: Monday, 21 August 2023 16:09
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx

Subject: DB2 Views issue

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.

--
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.

--
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.

--
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.

--
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.