AW: SQL resource usage and monitoring

... so you are not yet on Release 7.1! AFAIK the NLS functions (such as
UPPER, LOWER) were not supported before by the SQE.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"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!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Darryl Freinkel
Gesendet: Tuesday, 17.3 2015 00:27
An: Midrange Systems Technical Discussion
Betreff: Re: SQL resource usage and monitoring

Yes, I found the problem was using UPPER.

I removed it and the time went from 18 hours to 20 minutes.

UPPER caused the system to use CQE....

Thanks

Sent from my iPad

On Mar 14, 2015, at 5:17 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 14-Mar-2015 08:59 -0500, Darryl Freinkel wrote:
I have a complex SQL script that used to run in about 3 minutes and
processed through 700k records.

I made a bunch of changes and one of the changes has resulted in the
run time going out to 12 hours.

May be great value in obtaining the pre-change copy of the script and

comparing\running each of them step-by-step to compare\contrast.

Quite common for such a radical change is multiple-file queries for which

a predicate between files is [since] missing; that omission having resulted
in a cross-product result. Another issue [and possibly in combination] is a
change that resulted in the Classic Query Engine (CQE) performing the query
instead of the SQL Query Engine (SQE), and the particular query likely is
lacking an efficient SQL-like implementation available from the classic
optimizer which was not designed originally with an intention solely to
implement SQL queries. Likely resolutions are to avoid whatever operations
require a fallback on CQE [e.g. shared-weight sequences for collation and
selection] or [for joins anyhow] to provide the query more if even seemingly
redundant predicates [or statistics if running SQE] to better limit
selection.

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_i5_54/rzaj
q/queryoptimize.htm>
_Query Engine Overview_

I have all the advised indexes required by the system.

Advised indexes are those which the optimizer presumes would be useful

for the queries being run. Additional indexes on the data can provide
statistics about the data that may yield alternate preferences, thus changes
to what had previously been advised; i.e. having only\all the advised
indexes does not imply that the ideal keyed access paths exist for
implementing queries. Adding RI and Check Constraints provide even more
information than just statistics; known logical constraints on the data can
narrow focus to specific data rather than all indexed data.

I am trying to monitor system usage and found the following:
- CPU utilization is constantly around 40% (2 hours)
- Disk IO across all 10 drives is 1% or less (1 hours)

On the face of it, it appears the system is idle whilst it is
processing the data.

Unlikely /idle/, more likely just not visible at the job level. The Work

With System Activity (WRKSYSACT) command shows LIC tasks that are not
visible via the Work With Active Jobs (WRKACTJOB) interface. Unless all of
that 40% was known to be taken by other work than the long-running query, a
noticeable portion of that was probably the query.

Looking at the job status, for status transitions [in WRKACTJOB], could

be easy and more telling than much else, if indeed there were any /wait/
conditions; e.g. something like DEQW, LCKW, xxxW, then those are visible
there. If the job is not changing status, and for example remains
continually in RUN status, then either work is progressing, or any waits are
not those exposed outside of the LIC; the long-running database operations
are typically exposed in the job status as IDX for building an access path
or as RUN for selecting and reading data records. If the program stack
appears to be unchanging, then a LIC dump of the process will show the
effective stack of the LIC processing.

Does anyone have any advice on how to track this down.

My first pass is usually just to look at the queries to decide which
are the most likely to be difficult to optimize and\or complete, and
then run and investigate those separately. However running the script
with the query timeout option [the query governor] set and ensuring
inquiry messages are prompted, allows the optimizer to reveal prior to
each query to be run, which are presumed to take longer than a
specified limit or an option allows forcing the optimizer to reveal
the presumptions of the query costing, irrespective of any limit. See
Change Query Attributes (CHGQRYA) for the Query Time(out) Limit
(QRYTIMLMT) parameter; the value zero suggests to stop every request
and inform of the cost before a reply allows continuing. That feature
also enables similar limits, for query temporary storage requirements,
via the Query Storage Limit (QRYSTGLMT) parameter. See the following,
along with the related and parent links at the bottom of the page that
refer to what also m

ight be of interest:

<http://www.ibm.com/support/knowledgecenter/api/content/ssw_i5_54/rzaj
q/testperfwithqrygov.htm> _Testing performance with the query
governor_

If not separating each query with the forced timeout inquiry, I might

insert a CALL to a routine that logs the current timestamp along with
something to identify which statement is being run; if only to get timings
for each, reviewed just once or separate runs compared to see any [lack of]
consistency. Because I prefer to see the job traces, I might surround each
request with requests to start and end a trace; later print the trace
information for the request(s) that are suspect as poor-performers.

With the old script having the same pre\post processing inserted around

the statements to effect logging as were included in the updated script, the
timing differences on a per-statement basis are easier to fathom. Other
tooling like the DB Monitor should assist in the same way, but I generally
prefer my own logging. For example, one script might have combined two
queries into one; with my own logging I may be able to logically group the
/same/ work easier than can be done with other tooling. If the script is
really long vs just a few queries, I might run the old and new concurrently
side-by-side instead of using any logging.

We are on V5r4 so no IBM support?

AFaIK individual support contracts are available to be purchased, even

when a release since has had general support ended. There are also non-IBM
service providers that presumably could similarly assist.

Do you know how I can use the data base monitor to analyze what is
going on?

There are some /canned queries/ documented that can be run on the DBMON

output to make certain inquiries about a specific query or a batch of
queries. I have seen some published on the web, perhaps in articles at one
of the news sites; some may be included in the iNav database feature, I do
not recall. As I recall there was a simple way to query which statements
were run via CQE vs SQE. A really old document that has some information
that may assist, followed by links to some progressively newer documents
[which may have /related topics/ links included]:

<http://www.redbooks.ibm.com/redpapers/pdfs/redp0502.pdf> ??-???-2000
Using AS/400 Database Monitor and Visual Explain To Identify and Tune
SQL Queries

<http://www.redbooks.ibm.com/abstracts/sg246092.html> 16-Sep-2004
DB2 Universal Database for iSeries Administration: The Graphical Way
on V5R3

<http://www.redbooks.ibm.com/abstracts/sg246654.html> 26-May-2006 SQL
Performance Diagnosis on IBM DB2 Universal Database for iSeries

<http://www.redbooks.ibm.com/abstracts/tips0624.html> 08-Aug-2006 upd:
12 August 2008 Index Advisor Surfaces Maintained Temporary Indexes
(MTI) Activity on DB2 for i5/OS

<http://www.redbooks.ibm.com/abstracts/sg246598.html> 27-Sep-2006
Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS

<http://www.redbooks.ibm.com/abstracts/sg247326.html> 12-Mar-2007
OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4

Why can I not see where the processing is? It's almost like there is
a wait happening?

The easy answer is "the lack of knowing [best] where to look". Hopefully

some of the above will assist. Having worked on the DB2 for i, I tend to
rely much more on what I know should happen, without much reliance on the
various feedback mechanisms beyond debug messages and maybe VE, paired with
my knowledge of both the actual data and the DDL that defined the files, the
relations [including RI] and indexes... plus consideration for anything
extra like read triggers, fieldprocs, or UDFs that might inhibit quick
access.

--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.