Re: DB2 Views issue

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.