I think your English was fine. You wrote this statement -
"Local access to the database via embedded SQL STRSQL, QMQRY are hard to
seperate from RLA accessat the first glance."
That is why I asked if RLA had been added - I did not think so. And I
did assume that you meant that you were looking in DB monitor output for
RLA information. I think I mistook your meaning.
I have been thinking about a conclusion to make from this thread. That
is, in order to have a single analytical effort, use only SQL - then all
activity can be monitored. A small incentive, perhaps!
You did talk about using Navigator to analyze the results from running
the DB monitor - and Visual Explain is one of the tools there. I assumed
that VE would be something you were recommending.
So I am confused - now you say that you never find help from VE in some
environments. I might agree - but maybe not. The DB monitor, if you run
it in those heavy-transaction environment, will give you information
about what the optimizer did. This is helpful, in addition to the WRK*
commands that you mentioned - those are needed, too. Just running Visual
Explain over a test run of the query in Navigator will, naturally, be a
As to engines - well, the only real development is happening in SQE. So
I have found it very helpful to know that a query was run in CQE - which
is NOT being developed any further - it is what IBM calls "stabilized",
I want queries to be executed with the new engine - that is where all
new development will happen, and usually the best performance. This is
similar to the idea to use DDL instead of DDS to define database
objects, and to use SQL instead of native IO. There are still things
that cause a query to be run with the old engine - if I can change the
query to make it so that SQE is the engine, I am better off. I found
this to be an issue when I was running queries with *LANGIDSHR -
national language settings used to force queries to use the old engine -
that has changed, fortunately!
As to buggy - everything we and IBM writes have bugs - we are not
perfect. But i have not run into bugs in the DB monitor analysis or
Visual Explain - nothing that will keep me from using these valuable tools.
So I recommend you take another look at what has happened with Navigator
and Visual Explain and database monitors. They have improved along with
the excellent improvements in database, such as plan cache and index
recommendations and all of that.
If you want a commercial product that analyzes DB monitor output - take
a look at Centerfield Technology - www.centerfieldtechnology.com - I do
not work there - I used to, until 8 years ago. Very expert in database
On 12/2/2012 10:35 AM, D*B wrote:
maybe my english is not sufficient, I will try toclearify:
- the database monitor doesn’t have any measurment for RLA, that did not change! (BTW: I’m not very interested in RLA workload, I didn’t use RLA for > 10 years).
some other aspects:
its not of interest to me how many query engines a database has (silly idea to have more than one and even more silly talking about this) and how they are named. Using SQL I describe a set of records and the query engine does it work, hopefully with good performance.
In an environment with heavy transaction activity, thosands of SQL accesses, done by hundreds of programms I’ve never seen before, tools like Visual Explain don’t help anything.
My firts look would be to WRKSYSSTS, WRKDSKSTS and WRKACTJOB, the number of Serverjobs QZDASOINIT and QPWFxxx has some information of the number of connections, the total database workload is not easy to find, because the local connects and RLA could not be identified and seperated from the other workload.
nexr step would be to just start monitoring all jobs for an hour or more and then having a look to the results and there is only one tool, IBM renames it from time to time (they seem to have fun with this activity), some call it database Navigator, I called it “oops nerv” because its buggy.