Thanks for your responses...
Charles, we've IPL'd before without any issues like this. I mean, it could have been slow - but nothing near to this issue.
Running Visual Explain from Run Sql Scripts... I have used this before to create indexes. But I have NEVER seen a visual chart like this. It is enormous. Many, many Temporary Hash Tables
What's odd is that I do not see any Temporary Indexes being created. Or maybe I'm not looking in the right place?
The statement ran for 30 minutes and did not produce any output before I stopped the request.
I do have permanent indexes over the database files involved - these were created years ago using Index Advisor.
-----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.
midrange.com
