Putting a Cap on QZDASOINIT CPU % ?
JohnO
We run JDEdwards OneWorld on our iSeries servers, but also have
various data mining and query users hitting the JDE databases with
SQL. We find that these SQL's can really hog the system and degrade
performance for the JDE users - particularly when the query is not
well designed or has nested subqueries etc. It's not uncommon for a
couple of QZDASOINIT jobs to grab 25% CPU each.
Is there any way to cap CPU consumption for a class of jobs? Is there
any other way to tame these rogue queries? Even turning the run
priority down seems to have little effect.
Thanks for any tips,
JohnO
Stefano P.
Hi :-)
> We run JDEdwards OneWorld on our iSeries servers, but also have
> various data mining and query users hitting the JDE databases with
> SQL. We find that these SQL's can really hog the system and degrade
> performance for the JDE users - particularly when the query is not
> well designed or has nested subqueries etc. It's not uncommon for a
> couple of QZDASOINIT jobs to grab 25% CPU each.
In my experience (systems from V4R3 to V5R4), it's quite usual to have
such a cpu consumption per job for an sql query;
the problem arise when you have many of these jobs running together :-|
And, that's more, those query consume more cpu and disk resources when
there is lack of appropriate indexing...
> Is there any way to cap CPU consumption for a class of jobs?
You may change the job description for those jobs, giving them a
different class (with different time slice and run priority);
as those are "sql" jobs, you could benefit from moving them in a
separate memory pool too.
This is a work management issue...
I don't remember exactly but, as far as I remember, job description and
class for QZDASOINIT jobs is quite aggressive...
> Is there
> any other way to tame these rogue queries?
I would (and am about to - on the systems that I control) work on "work
management" (class, jobd and memory pool) on one side and on query
attribute on the other.
> Even turning the run
> priority down seems to have little effect.
I suppose it has no effect because you change priority after having seen
that job running (too much):
at this time the access plan it's already started.
From an "sql point of view", you could decide to "change the query
attribute" of those jobs using CHGQRYA:
you may both use a "less aggressive" qaqqini and limit resources use.
Imho, the simpler way to change query attribute for every QZDASOINIT job
would be by mean of the routing step in the subsystem definition,
something like
CHGQRYA bla bla bla
TRFCMD (I suppose to QCMD)
(sorry, I cannot check for exact values on the systems in this moment).
As those jobs are sql queries, check for the existence of the
appropriate indexes ;-)
> Thanks for any tips,
> JohnO
HTH
Stefano P.
--
"Niuna impresa, per minima che sia,
può avere cominciamento e fine senza queste tre cose:
e cioè senza sapere, senza potere, senza con amor volere"
[Anonimo fiorentino, XIV sec.]
(togliere le "pinzillacchere" dall'indirizzo email ;-)
Thad Rizzi
See that you have the proper indexes built. If you are at V5R4 you
can look at the index advisor in iSeries Navigator. If not you could
run some of the SQL jobs in debug and look at the suggested access
paths.
JohnO
wrote:
Thanks Stefano!
There is a slight complication - the JDE clients also connect to
QZDASOINIT for their queries. They are 'good guys' and rarely cause a
problem. We want them to have fast response so they should get top
priority. It's just the QZDASOINIT's serving requests from particular
ip addresses or particular user profiles (the data miners) that I want
to slow down.
JohnO
>
> - Show quoted text -
Hi Thad.
The problem is that these query users can come up with crazy queries
that I cannot predict. Worse still, they have a habit of doing stupid
things such as nesting subqueries that can get called thousands of
times by the outer query. Or they call stored procedures that invoke
subqueries.
If I had my way I would ban these pests but I don't have that option!
Cheers
John
Dieter Bender
you can seperate the workload by ip adress. create your own subsystem with
the prestart jobs needed for odbc access (QZDA*) and start this subsystem
in your startup program. in the next step you can rooute jobs with known ip
adresses to the new subsystem using oops nerv (right click here and left
click there, its so easy that you have to search the way each time you use
it).
Dieter Bender
JohnO
> Hi,
>
> you can seperate the workload by ip adress. create your own subsystem with
> the prestart jobs needed for odbc access (QZDA*) and start this subsystem
> in your startup program. in the next step you can rooute jobs with known ip
> adresses to the new subsystem using oops nerv (right click here and left
> click there, its so easy that you have to search the way each time you use
> it).
>
> Dieter Bender
>
Hi Dieter,
This sounds like the kind of approach I was hoping for. What is this
'oops nerv' ?
Stefano P.
Oh, it's a newsgroup :-)
> There is a slight complication - the JDE clients also connect to
> QZDASOINIT for their queries. They are 'good guys' and rarely cause a
> problem. We want them to have fast response so they should get top
> priority. It's just the QZDASOINIT's serving requests from particular
> ip addresses or particular user profiles (the data miners) that I want
> to slow down.
May be QIBM_QZDA_SQL2 (or QIBM_QZDA_SQL1) user exit may help you:
depending on user profile (or ip address, sometime a bit more difficult
to find) you can change some job attributes as you want:
i.e. CHGJOB and CHGQRYA to consume less resources
but only for those "bad guy", not JDE clients ;-)
Dieter Bender
Hi,
some people call it Operations Navigator and its part of client access and
sometimes it works
Dieter Bender
JohnO
> > Hi Dieter,
>
> > This sounds like the kind of approach I was hoping for. What is this
> > Hi,
>
> some people call it Operations Navigator and its part of client access and
> sometimes it works
>
Oh right, heh!
JohnO
wrote:
Ok, I'll get started then!
Cheers,
JohnO
JohnO
> Hi,
>
> some people call it Operations Navigator and its part of client access and
> sometimes it works
>
> Dieter Bender
>
>
>
> JohnO wrote:
> > On Nov 30, 8:32 pm, Dieter Bender <dieter.ben...@t-online.de> wrote:
> >> Hi,
>
> >> you can seperate the workload by ip adress. create your own subsystem
> >> with the prestart jobs needed for odbc access (QZDA*) and start this
> >> subsystem in your startup program. in the next step you can rooute jobs
> >> with known ip adresses to the new subsystem using oops nerv (right click
> >> here and left click there, its so easy that you have to search the way
> >> each time you use it).
>
> >> Dieter Bender
>
> > Hi Dieter,
>
> > This sounds like the kind of approach I was hoping for. What is this
>
Hi again!
I don't seem to be able to move jobs between subsystems with Ops Nav.
Which tree were you clicking in? I was trying to do this under Work
Management/Subsystems/<subsystemname>
I cannot drag the jobs anwyere, and right clicking does not get me
anything that allows a change to he job other than holding or ending
it.
I am logged in with QSECOFR authority.
Cheers
JohnO
Newbie_Neil
One of our customers, running V5R2, has had a problem with QZDASOINIT
taking the whole CPU when running Query. There is a PTF to cure the
problem, but we've just deleted the QZDASOINIT job as it's only
happened a couple of times.
Cheers,
Neil
JohnO
Hi Neil,
This one is on a fairly up-to-date V5R4. We occasionally see one of
these jobs go crazy on CPU% while not appaering to do anything and
also end it, sometimes reqquiring ENDJOBABN to do it. That's only
happening once every few months though.
Stefano P.
>> taking the whole CPU when running Query. There is a PTF to cure the
>> problem, but we've just deleted the QZDASOINIT job as it's only
>> happened a couple of times.
> This one is on a fairly up-to-date V5R4. We occasionally see one of
> these jobs go crazy on CPU% while not appaering to do anything and
> also end it, sometimes reqquiring ENDJOBABN to do it. That's only
> happening once every few months though.
V5R4... have you enabled the "autonomic" creation of temporary index?!
Especially when those "bad guys" (the data miners :-) change the "where"
or "join" or "order by" conditions (for their own needs) V5R4 sqe engine
may decide to create temporary indexes...
My two cents
JohnO
wrote:
I've not seen the config setting for that but I'm certain that it does
create temporary indexes at times. But I can see that when it happens
and it's not too often.
The main problem is definitely with nested subqueries being executed
1000s of times by the outer query, or the outer query selecting a UDF
that contains SQL.
Stefano P.
> The main problem is definitely with nested subqueries being executed
> 1000s of times by the outer query, or the outer query selecting a UDF
> that contains SQL.
...at this point if I were you I'd change job properties (run priority
and time slice) when they connect:
if it's possible to recognize them by mean of user profile (in which
case it would be rather simple: QIBM_QZDA_INIT user exit,
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzaik/userexitex3.htm)
then in the user exit program you may decide to change job attribute;
it's rather easy, you can use only command language
(and use chgqrya too ;-)
If you need ip address too then search for the appropriate api...
May be I'm a bit rude (with users ;-) but sometimes it's necessary to
act this way :-)
Cheers
Dieter Bender
Hi,
as far as I remember it was something with server jobs and there it was the
context menu (right click), I love this cinema for mice...
Dieter Bender
JohnO
wrote:
> [cut]
>
> > The main problem is definitely with nested subqueries being executed
> > 1000s of times by the outer query, or the outer query selecting a UDF
> > that contains SQL.
>
> ...at this point if I were you I'd change job properties (run priority
> and time slice) when they connect:
> if it's possible to recognize them by mean of user profile (in which
> then in the user exit program you may decide to change job attribute;
> it's rather easy, you can use only command language
> (and use chgqrya too ;-)
> If you need ip address too then search for the appropriate api...
>
> May be I'm a bit rude (with users ;-) but sometimes it's necessary to
> act this way :-)
>
> Cheers
> Stefano P.
>
Yeah, that sounds like the way to go. And as a bonus I get to learn
whoto write a user-exit!
Thanks Stefano!
JohnO
>
> - Show quoted text -
Hey Stefano, that couldn't have been easier - I don't care about the
ip address just the user name so it was just a two line change to the
sample program to check for the names of interest and if found then
CHGJOB to drop it's priority. And it seems to work too!
Thanks
JohnO
Stefano P.
>>>> The main problem is definitely with nested subqueries being executed
>>>> 1000s of times by the outer query, or the outer query selecting a UDF
>>>> that contains SQL.
>>> ...at this point if I were you I'd change job properties (run priority
>>> and time slice) when they connect:
>> Yeah, that sounds like the way to go. And as a bonus I get to learn
>> whoto write a user-exit!
> Hey Stefano, that couldn't have been easier - I don't care about the
> ip address just the user name so it was just a two line change to the
> sample program to check for the names of interest and if found then
> CHGJOB to drop it's priority. And it seems to work too!
I used this system to control (and decide) who can and who cannot
connect through odbc:
the "good guys" are in a authorization list (easy to check with command
language), for the others the user exit program sends a message to
hstlog (ODBC000 :-).
> Thanks
> JohnO
Greetings
JohnO
wrote:
Whoops! Better re compile my PGM so it sets authority based on owner!
These QZDASOINIT jobs are a bit odd. They seem to live on after a
client ends it's connection and can be reused by another client. I'm
unsure yet whether the INIT exit gets rerun in this case as a 'good
guy' could connect to a QZDASOINIT left in a downgraded state by a
'bad guy'.
Also when a new connection is made to an old QZDASOINIT job the job
log seems to get cleared - there's no trace of the old activity with
it's previous customer.
Karl Hanson
<snip>
>
> These QZDASOINIT jobs are a bit odd. They seem to live on after a
> client ends it's connection and can be reused by another client. I'm
> unsure yet whether the INIT exit gets rerun in this case as a 'good
> guy' could connect to a QZDASOINIT left in a downgraded state by a
> 'bad guy'.
>
> Also when a new connection is made to an old QZDASOINIT job the job
> log seems to get cleared - there's no trace of the old activity with
> it's previous customer.
>
They are prestart jobs:
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/rzaks/rzaksprestartentryx.htm
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/rzaks/rzaksprestarttype.htm
On a command line:
==> go cmdpje
==> go cmdpj
--
Karl Hanson