DB2 query priority

[Kdawg]

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" ?

[Ian]

Post by Kdawg
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).

[Kdawg]

Post by Ian

Post by Kdawg
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.

[Mladen Gogala]

Post by Kdawg

Post by Ian

Post by Kdawg
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.

[Troels Arvin]

Hello,

Post by Kdawg
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.