> Hi All,

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 ;-)

On Nov 28, 12:28 pm, JohnO wrote:
> Hi All,
>
> 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

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.

On Nov 29, 7:28 pm, "Stefano P."
wrote:
> > Hi All,
>
> 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 ;-)

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.

On Nov 30, 6:45 am, Thad Rizzi wrote:
> On Nov 28, 12:28 pm, JohnO wrote:
>
>
>
>
>
> > Hi All,
>
> > 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
>
> 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.- Hide quoted text -
>
> - 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

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


JohnO wrote:

> On Nov 29, 7:28 pm, "Stefano P."
> wrote:
>> > Hi All,
>>
>> 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 ;-)
>
> 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.

On Nov 30, 8:32 pm, Dieter Bender 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
'oops nerv' ?

> Thanks Stefano!

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 ;-)


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 ;-)

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 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
> 'oops nerv' ?

On Dec 1, 11:16 pm, Dieter Bender wrote:
> > Hi Dieter,
>
> > This sounds like the kind of approach I was hoping for. What is this
> > 'oops nerv' ?- Hide quoted text -
> > Hi,
>
> some people call it Operations Navigator and its part of client access and
> sometimes it works
>

Oh right, heh!

On Dec 1, 11:10 pm, "Stefano P."
wrote:
> > Thanks Stefano!
>
> 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 ;-)
>
> 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 ;-)

Ok, I'll get started then!

Cheers,
JohnO

On Dec 1, 11:16 pm, Dieter Bender wrote:
> 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 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
> > 'oops nerv' ?- Hide quoted text -
>
> - Show quoted text -

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/

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

JohnO wrote:
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

> On Dec 1, 11:16 pm, Dieter Bender wrote:
>> 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 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
>> > 'oops nerv' ?- Hide quoted text -
>>
>> - Show quoted text -
>
> 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/
>
> 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

Hi John

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

On Dec 3, 8:37 pm, Newbie_Neil wrote:
> Hi John
>
> 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

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.

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

> 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
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 ;-)

On Dec 4, 8:01 pm, "Stefano P."
wrote:
> >> 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.
> > 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
> 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 ;-)

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.

[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
case it would be rather simple: QIBM_QZDA_INIT user exit,
http://publib.boulder.ibm.com/infoce...serexitex3.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
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 ;-)

JohnO wrote:

>
> 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/infoce...tartentryx.htm
http://publib.boulder.ibm.com/infoce...estarttype.htm

On a command line:
==> go cmdpje
==> go cmdpj

--
Karl Hanson