DB2 governor, is setlimit clause measured across one SQL,UOW,connection?

[P. Adhia]

Hi,

>From reading manuals, it wasn't clear to me, if the DB2 governor's

setlimit configuration file clause, applies to each SQL, UOW or
connection. Only setlimit uowtime is specifically mentioned as elapsed
time since the start of UOW.

For example if I have,

setlimit cpu 60;
action force;

Does this mean, if an individual SQL statements exceeds 60 CPU second
usage, the connection will be forced off? Or when the CPU usage
accumulated across UOW (or connection) exceeds 60 seconds, the
connection is forced off? If the limit is not at SQL statement
granularity, is there a way I can force users who submit long running
SQLs and not force users who submit many short running SQLs even though
their accumulated CPU usage might exceed the limit?

TIA

P Adhia

[P Adhia]

P. Adhia wrote:

Hi,

From reading manuals, it wasn't clear to me, if the DB2 governor's
setlimit configuration file clause, applies to each SQL, UOW or
connection.

If anyone is interested, the correct answer is "limits are measured
across connection boundary". DB2 governor isn't very useful in my
particular situation, but helps to know anyway. It took a PMR and
talking to two support persons to figure out this piece of information,
which should have been in the manual.

It would be interesting to know what % of DB2 users are using some sort
of control mechanism that kills runaway queries to guarantee acceptable
database performance for the rest and how many of these users use DB2
governor, 3rd party or home-grown facilities.

P Adhia

[Jean-Marc Blaise]

Hi,

If you want to "kill" on a SQL statement basis, you could have a look to
Query Patroller (you must pay for it). This product is preemptive and based
on the timerons for the SQL that is going to be executed ...

* if the cost is above a limit, SQL query is simply "held" and can be
canceld by a DBA, so no harm on your server ... or run later.

* if the cost is below the limit, but the load on the server is above a
global limit, SQL query is "queued". If the load is fine, SQL query is
"running".

* if the cost is not costing so much, the query is not even managed by Query
Patroller.

It's a very nice product that can be completed by the DB2 Governor, that
kills after harm has been done, when QP stops before executing.

HTH,

JM

"P Adhia" <pa****@spamnot.yahoo.coma écrit dans le message de
news:34***************************@ALLTEL.NET...

P. Adhia wrote:

Hi,

From reading manuals, it wasn't clear to me, if the DB2 governor's
setlimit configuration file clause, applies to each SQL, UOW or
connection.

If anyone is interested, the correct answer is "limits are measured
across connection boundary". DB2 governor isn't very useful in my
particular situation, but helps to know anyway. It took a PMR and
talking to two support persons to figure out this piece of information,
which should have been in the manual.

It would be interesting to know what % of DB2 users are using some sort
of control mechanism that kills runaway queries to guarantee acceptable
database performance for the rest and how many of these users use DB2
governor, 3rd party or home-grown facilities.

P Adhia