Limitation of execution time

Peter Horner

Limitation of execution time
Hi list

We have users that do adhoc reporting and thereby running dynamic SQL
against
the db. Some users have found out real funny ways to write SQL code. In
fact it is so
funny, that our machine laughs sometimes more than 30 minutes about it.

You may laugh at my funny english by now, but at least I know how to write
my SQL :-)

Is there a way to set a limitation to resources
- as execution time
- i/o
- etc.

I'd like to set those on our zOS DB2 V6 and SUN SOLARIS UDB V7.1.

Thank you in advance !
Peter Horner

-----------------------------------------------------------------------------------

Peter Horner, ISDD
Bank Julius Baer & Co. Ltd.
Hohlstrasse 602, CH-8010 Zurich, Switzerland
Telephone +41 (58) 887 44 04, Fax +41 (58) 887 44 75
www.juliusbaer.com
-----------------------------------------------------------------------------------
























































*****Disclaimer*****
This message is for the addressee only and may contain confidential or
privileged information. You must delete and not use it if you are not the
intended recipient. It may not be secure or error-free. All e-mail
communications to and from the Julius Baer Group may be monitored.
Processing of incoming e-mails cannot be guaranteed. Any views expressed in
this message are those of the individual sender. This message is for
information purposes only. All liability of the Julius Baer Group and its
entities for any damages resulting from e-mail use is excluded. US persons
are kindly requested to read the important legal information presented
after clicking here: http://www.juliusbaer.com/maildisclaimer



Peter J Krawetzky

Re: Limitation of execution time
(in response to Peter Horner)
Use the db2 governor for UDB. It has several different parms you can use to
limit resources. Go to IBM's website for the documentation.

Peter J. Krawetzky
IBM Certified Solution Expert DB2 UDB V7.1 Database Administration for Unix,
Windows and OS/2



-----Original Message-----
From: Peter Horner [mailto:[login to unmask email]
Sent: Thursday, December 12, 2002 7:22 AM
To: [login to unmask email]
Subject: Limitation of execution time


Hi list

We have users that do adhoc reporting and thereby running dynamic SQL
against the db. Some users have found out real funny ways to write SQL code.
In fact it is so funny, that our machine laughs sometimes more than 30
minutes about it.

You may laugh at my funny english by now, but at least I know how to write
my SQL :-)

Is there a way to set a limitation to resources
- as execution time
- i/o
- etc.

I'd like to set those on our zOS DB2 V6 and SUN SOLARIS UDB V7.1.

Thank you in advance !
Peter Horner

----------------------------------------------------------------------------
-------

Peter Horner, ISDD
Bank Julius Baer & Co. Ltd.
Hohlstrasse 602, CH-8010 Zurich, Switzerland
Telephone +41 (58) 887 44 04, Fax +41 (58) 887 44 75 www.juliusbaer.com
----------------------------------------------------------------------------
-------
























































*****Disclaimer*****
This message is for the addressee only and may contain confidential or
privileged information. You must delete and not use it if you are not the
intended recipient. It may not be secure or error-free. All e-mail
communications to and from the Julius Baer Group may be monitored.
Processing of incoming e-mails cannot be guaranteed. Any views expressed in
this message are those of the individual sender. This message is for
information purposes only. All liability of the Julius Baer Group and its
entities for any damages resulting from e-mail use is excluded. US persons
are kindly requested to read the important legal information presented after
clicking here: http://www.juliusbaer.com/maildisclaimer






This e-mail, including attachments, is intended for the exclusive use of the
person or entity to which it is addressed and may contain confidential or
privileged information. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified that
any dissemination, distribution or copying of this e-mail is prohibited. If
you think that you have received this e-mail in error, please advise the
sender by reply e-mail of the error and then delete this e-mail immediately.
Thank you. Aetna



Mark Anzmann

Re: Limitation of execution time
(in response to Peter J Krawetzky)
Peter-

Not sure about on Solaris, but on the Z/OS box, you should be able to use the Resource Limiting Facility that comes with DB2 to limit these dynamic queries. There is some decent documentation out there on how to set the process up.

If you need more help, let me know and I will send some documentation that we developed in-house.

HTH

Mark

Mark Anzmann
Tech Services Manager
DHRIS Program - Team Accenture
Phone: 410-238-1213
email: [login to unmask email]

>>> [login to unmask email] 12/12/02 07:22AM >>>
Hi list

We have users that do adhoc reporting and thereby running dynamic SQL
against
the db. Some users have found out real funny ways to write SQL code. In
fact it is so
funny, that our machine laughs sometimes more than 30 minutes about it.

You may laugh at my funny english by now, but at least I know how to write
my SQL :-)

Is there a way to set a limitation to resources
- as execution time
- i/o
- etc.

I'd like to set those on our zOS DB2 V6 and SUN SOLARIS UDB V7.1.

Thank you in advance !
Peter Horner

-----------------------------------------------------------------------------------

Peter Horner, ISDD
Bank Julius Baer & Co. Ltd.
Hohlstrasse 602, CH-8010 Zurich, Switzerland
Telephone +41 (58) 887 44 04, Fax +41 (58) 887 44 75
www.juliusbaer.com
-----------------------------------------------------------------------------------
























































*****Disclaimer*****
This message is for the addressee only and may contain confidential or
privileged information. You must delete and not use it if you are not the
intended recipient. It may not be secure or error-free. All e-mail
communications to and from the Julius Baer Group may be monitored.
Processing of incoming e-mails cannot be guaranteed. Any views expressed in
this message are those of the individual sender. This message is for
information purposes only. All liability of the Julius Baer Group and its
entities for any damages resulting from e-mail use is excluded. US persons
are kindly requested to read the important legal information presented
after clicking here: http://www.juliusbaer.com/maildisclaimer






Rodney KRick

Limitation of execution time
(in response to Mark Anzmann)
Peter,

give a look at the "Administration Guide", chapter 5.5.2
<snip>
Resource limit facility (governor)

DB2's resource limit facility (governor) lets you perform the following
activities:
Set warning and error thresholds by which the governor can inform users
(via your application programs) that a certain processing limit might be
exceeded for a particular dynamic SELECT, INSERT, UPDATE, or DELETE
statement. This is called predictive governing.
Stop a currently executing dynamic SQL statement (SELECT, INSERT, UPDATE,
or DELETE) that exceeds the processor limit that you have specified for
that statement. This is sometimes called reactive governing, to
differentiate its function from that of predictive governing, a function
that is also handled by the resource limit facility. The resource limit
facility does not control static SQL statements whether or not they are
executed locally or remotely.
Restrict bind and rebind activities to avoid performance impacts on
production data.
Restrict particular parallelism modes for dynamic queries.
</snip>

HTH

Mit freundlichen Grüßen / Best regards,

Rodney Krick
Hänchen & Partner GmbH
Calwer Straße 1
D-71034 Böblingen

James D. Hannis

Re: Limitation of execution time
(in response to Rodney KRick)
For the DB2 OS/390 z/OS platform, look into the Resource Limit Facility.
It can be used
to limit or 'govern' dynamic SQL statements that exceed a specified
processor limit.

See page 581 of the DB2 OS/390 and z/OS Administration Guide - Volume 2
(Version 7).

Regards,

Jim Hannis
Database - WHQKO
(847) 700-4359
Pager (877) 861-7495

-----Original Message-----
From: [login to unmask email]
[SMTP:[login to unmask email]
Sent: Thursday, December 12, 2002 6:22 AM
To: [login to unmask email]
Cc: [login to unmask email]
Subject: Limitation of execution time

Hi list

We have users that do adhoc reporting and thereby running dynamic SQL
against
the db. Some users have found out real funny ways to write SQL code.
In
fact it is so
funny, that our machine laughs sometimes more than 30 minutes about
it.

You may laugh at my funny english by now, but at least I know how to
write
my SQL :-)

Is there a way to set a limitation to resources
- as execution time
- i/o
- etc.

I'd like to set those on our zOS DB2 V6 and SUN SOLARIS UDB V7.1.

Thank you in advance !
Peter Horner

---------------------------------------------------------------------
--------------

Peter Horner, ISDD
Bank Julius Baer & Co. Ltd.
Hohlstrasse 602, CH-8010 Zurich, Switzerland
Telephone +41 (58) 887 44 04, Fax +41 (58) 887 44 75
www.juliusbaer.com
---------------------------------------------------------------------
--------------
























































*****Disclaimer*****
This message is for the addressee only and may contain confidential
or
privileged information. You must delete and not use it if you are not
the
intended recipient. It may not be secure or error-free. All e-mail
communications to and from the Julius Baer Group may be monitored.
Processing of incoming e-mails cannot be guaranteed. Any views
expressed in
this message are those of the individual sender. This message is for
information purposes only. All liability of the Julius Baer Group and
its
entities for any damages resulting from e-mail use is excluded. US
persons
are kindly requested to read the important legal information
presented
after clicking here: http://www.juliusbaer.com/maildisclaimer



visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of
the list can

David S Waugh

Re: Limitation of execution time
(in response to James D. Hannis)
And if your funny users are running their funny SQL using QMF, you should also look into using the QMF Governor. It also limits/governs dynamic SQL (with more options that what the Resourcee Limit Facility gives you).

David Waugh, NCW
DSW Consulting & Services
Former DB2 Sysprog, now clueless DBA
===============
For all you fans of Assembler Language:
Newly proposed assembler instructions!
===============
BOHP Bribe Operator for Higher Priority
BOI Byte Operator Immediately
CUN Cancel all User Numbers


---------- Jim Hannis <[login to unmask email]> writes:

For the DB2 OS/390 z/OS platform, look into the Resource Limit Facility.
It can be used
to limit or 'govern' dynamic SQL statements that exceed a specified
processor limit.

See page 581 of the DB2 OS/390 and z/OS Administration Guide - Volume 2
(Version 7).

Regards,

Jim Hannis
Database - WHQKO
(847) 700-4359
Pager (877) 861-7495

-----Original Message-----
From: [login to unmask email]
[SMTP:[login to unmask email]
Sent: Thursday, December 12, 2002 6:22 AM
To: [login to unmask email]
Cc: [login to unmask email]
Subject: Limitation of execution time

Hi list

We have users that do adhoc reporting and thereby running dynamic SQL
against
the db. Some users have found out real funny ways to write SQL code.
In
fact it is so
funny, that our machine laughs sometimes more than 30 minutes about
it.

You may laugh at my funny english by now, but at least I know how to
write
my SQL :-)

Is there a way to set a limitation to resources
- as execution time
- i/o
- etc.

I'd like to set those on our zOS DB2 V6 and SUN SOLARIS UDB V7.1.

Thank you in advance !
Peter Horner

---------------------------------------------------------------------
--------------

Peter Horner, ISDD
Bank Julius Baer & Co. Ltd.
Hohlstrasse 602, CH-8010 Zurich, Switzerland
Telephone +41 (58) 887 44 04, Fax +41 (58) 887 44 75
www.juliusbaer.com
---------------------------------------------------------------------
--------------
























































*****Disclaimer*****
This message is for the addressee only and may contain confidential
or
privileged information. You must delete and not use it if you are not
the
intended recipient. It may not be secure or error-free. All e-mail
communications to and from the Julius Baer Group may be monitored.
Processing of incoming e-mails cannot be guaranteed. Any views
expressed in
this message are those of the individual sender. This message is for
information purposes only. All liability of the Julius Baer Group and
its
entities for any damages resulting from e-mail use is excluded. US
persons
are kindly requested to read the important legal information
presented
after clicking here: http://www.juliusbaer.com/maildisclaimer



visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of
the list can



Neil Courtney

Re: Limitation of execution time
(in response to David S Waugh)
it also sounds like your funny users need some serios training on how to
code SQL. Perhaps if they actually know what they are doing wrong, they
will change their ways and do it correctly? Hence you will no longer need a
govenor of any sort.

Cheers,
Neil.
Just a clueless application hacker.

-----Original Message-----
From: David S Waugh [mailto:[login to unmask email]
Sent: Friday, December 13, 2002 12:07 PM
To: [login to unmask email]
Subject: Re: Limitation of execution time


And if your funny users are running their funny SQL using QMF, you should
also look into using the QMF Governor. It also limits/governs dynamic SQL
(with more options that what the Resourcee Limit Facility gives you).

David Waugh, NCW
DSW Consulting & Services
Former DB2 Sysprog, now clueless DBA
===============
For all you fans of Assembler Language:
Newly proposed assembler instructions!
===============
BOHP Bribe Operator for Higher Priority
BOI Byte Operator Immediately
CUN Cancel all User Numbers


---------- Jim Hannis <[login to unmask email]> writes:

For the DB2 OS/390 z/OS platform, look into the Resource Limit Facility.
It can be used
to limit or 'govern' dynamic SQL statements that exceed a specified
processor limit.

See page 581 of the DB2 OS/390 and z/OS Administration Guide - Volume 2
(Version 7).

Regards,

Jim Hannis
Database - WHQKO
(847) 700-4359
Pager (877) 861-7495

-----Original Message-----
From: [login to unmask email]
[SMTP:[login to unmask email]
Sent: Thursday, December 12, 2002 6:22 AM
To: [login to unmask email]
Cc: [login to unmask email]
Subject: Limitation of execution time

Hi list

We have users that do adhoc reporting and thereby running dynamic SQL
against
the db. Some users have found out real funny ways to write SQL code.
In
fact it is so
funny, that our machine laughs sometimes more than 30 minutes about
it.

You may laugh at my funny english by now, but at least I know how to
write
my SQL :-)

Is there a way to set a limitation to resources
- as execution time
- i/o
- etc.

I'd like to set those on our zOS DB2 V6 and SUN SOLARIS UDB V7.1.

Thank you in advance !
Peter Horner

---------------------------------------------------------------------
--------------

Peter Horner, ISDD
Bank Julius Baer & Co. Ltd.
Hohlstrasse 602, CH-8010 Zurich, Switzerland
Telephone +41 (58) 887 44 04, Fax +41 (58) 887 44 75
www.juliusbaer.com
---------------------------------------------------------------------
--------------
























































*****Disclaimer*****
This message is for the addressee only and may contain confidential
or
privileged information. You must delete and not use it if you are not
the
intended recipient. It may not be secure or error-free. All e-mail
communications to and from the Julius Baer Group may be monitored.
Processing of incoming e-mails cannot be guaranteed. Any views
expressed in
this message are those of the individual sender. This message is for
information purposes only. All liability of the Julius Baer Group and
its
entities for any damages resulting from e-mail use is excluded. US
persons
are kindly requested to read the important legal information
presented
after clicking here: http://www.juliusbaer.com/maildisclaimer



visit the DB2-L webpage at http://listserv.ylassoc.com. The owners of
the list can








David S Waugh

Re: Limitation of execution time
(in response to Neil Courtney)
Really good point Neil! That's where you really should concentrate - always better to work on the front end of a problem (before it starts) than the back end (after the havoc has been wreaked).

Not so "clueless" after all - you zeroed in on the primary solution; governors are secondary solutions.

David Waugh, NCW
DSW Consulting & Services
Clueless DB2 UDB Win/NT DBA

---------- Neil Courtney <[login to unmask email]> writes:

From: Neil Courtney <[login to unmask email]>
To: [login to unmask email]
Subject: Re: Limitation of execution time
Date: Fri, 13 Dec 2002 15:00:20 +1300

it also sounds like your funny users need some serios training on how to
code SQL. Perhaps if they actually know what they are doing wrong, they
will change their ways and do it correctly? Hence you will no longer need a govenor of any sort.

Cheers,
Neil.
Just a clueless application hacker.

-----Original Message-----
From: David S Waugh [mailto:[login to unmask email]
Sent: Friday, December 13, 2002 12:07 PM
To: [login to unmask email]
Subject: Re: Limitation of execution time

And if your funny users are running their funny SQL using QMF, you should also look into using the QMF Governor. It also limits/governs dynamic SQL (with more options that what the Resourcee Limit Facility gives you).

David Waugh, NCW
DSW Consulting & Services
Former DB2 Sysprog, now clueless DBA
===============
For all you fans of Assembler Language:
Newly proposed assembler instructions!
===============
BOHP Bribe Operator for Higher Priority
BOI Byte Operator Immediately
CUN Cancel all User Numbers


---------- Jim Hannis <[login to unmask email]> writes:

For the DB2 OS/390 z/OS platform, look into the Resource Limit Facility.
It can be used to limit or 'govern' dynamic SQL statements that exceed a specified processor limit.

See page 581 of the DB2 OS/390 and z/OS Administration Guide - Volume 2
(Version 7).

Regards,

Jim Hannis
Database - WHQKO
(847) 700-4359
Pager (877) 861-7495

-----Original Message-----
From: [login to unmask email]
[SMTP:[login to unmask email]
Sent: Thursday, December 12, 2002 6:22 AM
To: [login to unmask email]
Cc: [login to unmask email]
Subject: Limitation of execution time

Hi list

We have users that do adhoc reporting and thereby running dynamic SQL
against the db. Some users have found out real funny ways to write
SQL code.
In fact it is so funny, that our machine laughs sometimes more than
30 minutes about it.

You may laugh at my funny english by now, but at least I know how to
write my SQL :-)

Is there a way to set a limitation to resources
- as execution time
- i/o
- etc.

I'd like to set those on our zOS DB2 V6 and SUN SOLARIS UDB V7.1.

Thank you in advance !
Peter Horner