Current SQL being processed for a job - IBM AS400

This is a discussion on Current SQL being processed for a job - IBM AS400 ; I am trying to determine the best way to get the current SQL for a job. I have tried using the API QUSRJOBI with format JOBI0900 and I have also tried using the tool in iSeries Navigator titled "Current SQL ...

+ Reply to Thread
Results 1 to 5 of 5

Thread: Current SQL being processed for a job

  1. Current SQL being processed for a job

    I am trying to determine the best way to get the current SQL for a
    job. I have tried using the API QUSRJOBI with format JOBI0900 and I
    have also tried using the tool in iSeries Navigator titled "Current
    SQL for a job" but after several reviews of the data that I get back,
    it appears that the API is returning me the last completed SQL for a
    job and not necessarily the current SQL statement that is being
    processed.

    I have a couple of jobs that are taking a life of their own and I
    haven't been able to pinpoint what they are trying to do. They will
    consume a large % of CPU for about 10 to 15 minutes. I have started
    database monitors and have had the problem happen while the monitors
    are running, but when I review the data collected nothing points to
    15
    minute query being processed.

    Is there any way to dump the job information to find out EXACTLY what
    is being processed at that time?


    Also and FYI: This is a java application that can run probably 1,000
    different types of statements. I am having this particular problem
    at
    only 1 client site that I know of out of about 75 different clients
    all running on the System i. IBM has assured us that PTF levels are
    fine.


    Thanks in advance. Your help is greatly appreciated.



  2. Re: Current SQL being processed for a job

    For clarification, the reason it appears that I am not getting the
    current active SQL is because I can take that statement and it runs in
    seconds not minutes. That has led me to wonder if I am truly getting
    the current active statement being processed. Thanks

  3. Re: Current SQL being processed for a job

    On Jan 22, 6:51*am, RTR wrote:
    > For clarification, the reason it appears that I am not getting the
    > current active SQL is because I can take that statement and it runs in
    > seconds not minutes. *That has led me to wonder if I am truly getting
    > the current active statement being processed. *Thanks


    My experience with the "Last SQL Statement" feature of the iSeries
    Navigator work-with-job function is that it correctly returns the last
    SQL statement that has been parsed by the query engine. I've had to
    work on similar issues to yours, i.e. long running CPU-intensive
    queries, and I've been able correctly to identify the offending SQL
    statement in this manner.

    It sounds as if you have an indexing problem, or possibly an access
    plan problem. We recently had a recurrence of a problem we've had
    before (I began a thread on it in c.s.i.a.m.), in which a SQL
    statement issued by a remote server, and which ordinarily runs in a
    couple of minutes, began taking hours, or even not completing at all,
    following an IPL. It turned out that the query access plan was
    flushed from cache following the IPL. I solved it by making a copy of
    the dynamic query, identified via the "Last SQL Statement"
    functionality, and embedding it in an SQLRPGLE program, which will
    preserve its access plan across IPLs. When the remote server now
    issues its dynamic SQL statement, the query engine finds the access
    plan in the SQLRPGLE program, and the query now runs in a couple of
    minutes, as before. At least, this is the explanation I gave to IT
    management and the remote server administrator; and I'm sticking to it
    until someone shows otherwise!

  4. Re: Current SQL being processed for a job

    > I am trying to determine the best way to get the current SQL for a
    > job.


    > I have a couple of jobs that are taking a life of their own and I
    > haven't been able to pinpoint what they are trying to do. They will
    > consume a large % of CPU for about 10 to 15 minutes. I have started
    > database monitors and have had the problem happen while the monitors
    > are running, but when I review the data collected nothing points to
    > 15
    > minute query being processed.


    Without knowing anything about your query and the underlying database,
    I'd suppose that the query is building a temporary table, may be
    executing a full scan table or "anding" and/or "oring" two or more "evi"...


    The total time of a query is - roughly - the sum of time spent during
    "optimization", "open" and "run" phases:
    using *detailed* DbMon and then Visual Explain you may "easily" find the
    queries and control their timings... the problem, IMHO, arises when you
    search among a lot of informations that detailed Db Monitors may collect :-)
    Among them you may find the exact timings...

    There are queries to run against detailed dbmon (many of them are
    shipped with iSeries Navigator) that may suggest you directly which
    indexes the system would like to have (even if some of them are needed
    for statistics).


    > Also and FYI: This is a java application that can run probably 1,000
    > different types of statements. I am having this particular problem
    > at
    > only 1 client site that I know of out of about 75 different clients
    > all running on the System i. IBM has assured us that PTF levels are
    > fine.


    Both cqe and sqe decide how to run a query depending on a lot of factors
    related to i5/OS & DB2 environment and workload:
    even with the same query every situation may be different (on the same
    server).


    > Thanks in advance. Your help is greatly appreciated.


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

  5. Re: Current SQL being processed for a job

    >> For clarification, the reason it appears that I am not getting the
    >> current active SQL is because I can take that statement and it runs in
    >> seconds not minutes. That has led me to wonder if I am truly getting
    >> the current active statement being processed. Thanks


    > My experience with the "Last SQL Statement" feature of the iSeries
    > Navigator work-with-job function is that it correctly returns the last
    > SQL statement that has been parsed by the query engine. I've had to
    > work on similar issues to yours, i.e. long running CPU-intensive
    > queries, and I've been able correctly to identify the offending SQL
    > statement in this manner.


    > It sounds as if you have an indexing problem, or possibly an access
    > plan problem. We recently had a recurrence of a problem we've had
    > before (I began a thread on it in c.s.i.a.m.), in which a SQL
    > statement issued by a remote server, and which ordinarily runs in a
    > couple of minutes, began taking hours, or even not completing at all,
    > following an IPL. It turned out that the query access plan was
    > flushed from cache following the IPL. I solved it by making a copy of
    > the dynamic query, identified via the "Last SQL Statement"
    > functionality, and embedding it in an SQLRPGLE program, which will
    > preserve its access plan across IPLs. When the remote server now
    > issues its dynamic SQL statement, the query engine finds the access
    > plan in the SQLRPGLE program, and the query now runs in a couple of
    > minutes, as before. At least, this is the explanation I gave to IT
    > management and the remote server administrator; and I'm sticking to it
    > until someone shows otherwise!


    *I* remember that thread ;-)

    Like that time, let me suggest again - if at V5R4 - to check Index
    Advisor & Plan Cache, you may find rather easily both the query and - if
    needed - the indexes advised without having to monitor the job;
    checking the "related indexes" of a table in Index Advisor list, you may
    also discover if the query engine has decided to build a temporary index
    as they are listed with a clear description :-)
    And, from Index Advisor, you may easily "jump" to Plan Cache and the sql
    statements run against that particular table ;-)

    Imho, rebuilding the temporary indexes after every (frequent) ipl is
    time you may avoid to loose simply building permanent indexes...


    After indexing, you still may find that - with a given (complex) query -
    sqe spends a lot of time optimizing the access plan (compared to the
    time spent for "opening" and "running" the query itself) even if - in
    our systems - it does not eat a lot of cpu like using two or more evi;
    in my experience may be you are running a very complex query with a lot
    of joined files (or a simple query against very complex views with many
    joined files): as sqe tries to change the order of the files in the
    query to achieve the best result (given the indexes, statistics and a
    lot of other environment related informations), you may shorten this
    time imposing an INI file with "FORCE_JOIN_ORDER" parm set to "*YES".
    In this particular case you'd have to balance between the time needed to
    decide the access plan and the time needed to read the data itself, this
    last one depending on the total amount of data (for few rows it may not
    be worth to spend a lot of time reordering the query) and/or the
    "optimize for/fetch first" clause (i.e "first i/o" or "all i/o").


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

+ Reply to Thread