• DB2 query priority

    From Kdawg@21:1/5 to All on Tue Jul 24 11:38:36 2018
    Is there a good way to lower the priority of a query that is expected to run for several hours?

    Does this impact "Priority at which application agents work" ?

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Ian@21:1/5 to Kdawg on Wed Jul 25 11:24:09 2018
    On Tuesday, July 24, 2018 at 11:38:38 AM UTC-7, Kdawg wrote:
    Is there a good way to lower the priority of a query that is expected to run for several hours?

    Db2 Workload Management (WLM) is the best solution for this, but there is a relatively steep learning curve to implement it (and of course you need to be using an edition of Db2 which includes this feature).

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Kdawg@21:1/5 to Ian on Fri Jul 27 07:51:05 2018
    On Wednesday, July 25, 2018 at 2:24:11 PM UTC-4, Ian wrote:
    On Tuesday, July 24, 2018 at 11:38:38 AM UTC-7, Kdawg wrote:
    Is there a good way to lower the priority of a query that is expected to run for several hours?

    Db2 Workload Management (WLM) is the best solution for this, but there is a relatively steep learning curve to implement it (and of course you need to be using an edition of Db2 which includes this feature).

    What are the other alternative "non-best" solutions? Assuming someone does not have WLM.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Mladen Gogala@21:1/5 to Kdawg on Mon Jul 30 08:40:19 2018
    On Fri, 27 Jul 2018 07:51:05 -0700, Kdawg wrote:

    On Wednesday, July 25, 2018 at 2:24:11 PM UTC-4, Ian wrote:
    On Tuesday, July 24, 2018 at 11:38:38 AM UTC-7, Kdawg wrote:
    Is there a good way to lower the priority of a query that is expected
    to run for several hours?

    Db2 Workload Management (WLM) is the best solution for this, but there
    is a relatively steep learning curve to implement it (and of course you
    need to be using an edition of Db2 which includes this feature).

    What are the other alternative "non-best" solutions? Assuming someone
    does not have WLM.

    Probably "nice" utility on Unix systems.



    --
    Mladen Gogala
    Database Consultant
    http://mgogala.byethost5.com

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Troels Arvin@21:1/5 to Kdawg on Mon Jul 30 17:44:27 2018
    Hello,

    Kdawg wrote:
    Is there a good way to lower the priority of a query that is expected to
    run for several hours?

    Is it read-only, or a read-write query?

    In my opinion, long running queries make most trouble if they

    - collide with other sessions causing locks (and perhaps
    chains of locks)

    - make lots of changes which use up available transaction
    log space (consider adjusting MAX_LOG to protect other
    activities in the database from a big voracious query)

    - collide with backups (TRUNCATE, for example, can mean
    trouble in combination with backups)

    If it's a long read-write query: Can you structure the query, so that
    some of its work can happen in staging tables or temporary tables which
    don't risk causing locks?

    If (parts of) the query is read-only: Is it OK to run it in uncommitted
    read (UR) mode? UR means less locking, but also risk of data
    inconsistencies.

    Note: A very long query can cause transaction log trouble, even if it
    only make tiny little changes: If the query is one big transaction and
    the transaction is initiated by a change, then you may end up hitting a
    maximum of active transaction log files being used. Consider setting NUM_LOG_SPAN to protect your other database activities to be victim of
    your large query, due to this.

    --
    Regards,
    Troels Arvin

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)