MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » December 2012

Re: SQL db2 usage/overhead on your system questions.



fixed

Hi Dieter

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

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", probably.

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

Regards
Vern

On 12/2/2012 10:35 AM, D*B wrote:
Vernon,

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.


Dieter








Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact