[DB2-L ZOS] Monitor Tools which use Wall Time

Philip Sevetson

[DB2-L ZOS] Monitor Tools which use Wall Time
We're using a third-party midrange OLAP tool against our DB2 V7 z/OS
database. The application server occasionally times out on large
requests; when it does, the DB2 server (subsystem) doesn't get the message
and the database thread continues running. The application server timeout
is based on elapsed time, and DB2 RLF controls threads based on CPU time.
This is leading to large amounts of CPU cycle waste in environments where
we can't afford it.

I'd be interested in hearing from vendors about their offerings which can
monitor/cancel DB2 threads based on multiple criteria including CORRID,
AUTHID, and elapsed time.

"Bueller? Fry? Anyone?"


--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Myron Miller

Re: [DB2-L ZOS] Monitor Tools which use Wall Time
(in response to Philip Sevetson)
I don't know about monitor tools that can monitor/cancel threads. IMF (now
from BMC) used to do this, but not sure if it can any more. It had all kinds
of operator capabilities that were programmable.

But if you are using Db2 connect, you can eliminate some of this problem with
some settings on your db2 connect server. use QUERYTIMEOUT=0 and
INTERRUPT_ENABLED to cancel threads when the user system cancels them. Also
don't use KEEPCONNECT=x where x>0. I've found that this will hold threads
active even when they're not active.

Myron


--- [login to unmask email] wrote:
> We're using a third-party midrange OLAP tool against our DB2 V7 z/OS
> database. The application server occasionally times out on large
> requests; when it does, the DB2 server (subsystem) doesn't get the message
> and the database thread continues running. The application server timeout
> is based on elapsed time, and DB2 RLF controls threads based on CPU time.
> This is leading to large amounts of CPU cycle waste in environments where
> we can't afford it.
>
> I'd be interested in hearing from vendors about their offerings which can
> monitor/cancel DB2 threads based on multiple criteria including CORRID,
> AUTHID, and elapsed time.
>
> "Bueller? Fry? Anyone?"
>
>
> --Phil Sevetson
> Database Administration
> Wakefern Food Corporation CISD
> mailto:[login to unmask email]
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
> "Join or Leave the list". If you will be out of the office, send the SET
> DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can
> be reached at [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
>


__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: [DB2-L ZOS] Monitor Tools which use Wall Time
(in response to Myron Miller)
Myron,

Thanks for the info. I hadn't heard of those settings; I'll forward this
info to our DB2-Connect Guru.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]






Myron Miller <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/10/2003 06:43 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: Re: [DB2-L ZOS] Monitor Tools which use Wall Time


I don't know about monitor tools that can monitor/cancel threads. IMF
(now
from BMC) used to do this, but not sure if it can any more. It had all
kinds
of operator capabilities that were programmable.

But if you are using Db2 connect, you can eliminate some of this problem
with
some settings on your db2 connect server. use QUERYTIMEOUT=0 and
INTERRUPT_ENABLED to cancel threads when the user system cancels them.
Also
don't use KEEPCONNECT=x where x>0. I've found that this will hold threads
active even when they're not active.

Myron


--- [login to unmask email] wrote:
> We're using a third-party midrange OLAP tool against our DB2 V7 z/OS
> database. The application server occasionally times out on large
> requests; when it does, the DB2 server (subsystem) doesn't get the
message
> and the database thread continues running. The application server
timeout
> is based on elapsed time, and DB2 RLF controls threads based on CPU
time.
> This is leading to large amounts of CPU cycle waste in environments
where
> we can't afford it.
>
> I'd be interested in hearing from vendors about their offerings which
can
> monitor/cancel DB2 threads based on multiple criteria including CORRID,
> AUTHID, and elapsed time.
>


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steen Rasmussen

Re: [DB2-L ZOS] Monitor Tools which use Wall Time
(in response to Philip Sevetson)
Good afternoon Phil,
Since you explicitly asked for vendor solutions, I can't help responding.
If you are licensed for just ONE of CA's DB2 products, and you are on the P01 release, you actually get this component for free. The component is called Thread termination. You can set up profiles based on PLAN / Connection-id / Job name / Auth-id / Correlation-id and Connection-type. These profiles are attached to a Threshold Profile where you can define thresholds for Elapsed time, INDB2-elapsed time, INDB2 CPU time IDLE time, GETP requests, Total SQL's. Finally you can define to issue warnings, hard cancels or soft cancel when threshold is violated - and you also have a log available to trace what, when and where.

Feel free to contact me offline if you want to discuss further.

Kind regards
Steen Rasmussen
Computer Associates
Senior Consultant






---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Aurora Dellanno

Re: [DB2-L ZOS] Monitor Tools which use Wall Time
(in response to Steen Rasmussen)
Hi Phil,

my old employers' tool, Omegamon for DB2, could do stuff like this.

ciao!

Aurora Emanuela Dell'Anno
Database Analyst
Data Services Group - Bank of America
tel. 66192
ext. 0208 760 6192
<mailto:[login to unmask email]>
[login to unmask email]

* std. disclaimer * MY OPINIONS ARE MY OWN AND NOT THOSE OF MY EMPLOYER

no trees were killed in sending this message. However, a large number of
electrons were seriously inconvenienced :-)

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: 10 December 2003 22:33
To: [login to unmask email]
Subject: [DB2-L ZOS] Monitor Tools which use Wall Time



We're using a third-party midrange OLAP tool against our DB2 V7 z/OS
database. The application server occasionally times out on large requests;
when it does, the DB2 server (subsystem) doesn't get the message and the
database thread continues running. The application server timeout is based
on elapsed time, and DB2 RLF controls threads based on CPU time. This is
leading to large amounts of CPU cycle waste in environments where we can't
afford it.

I'd be interested in hearing from vendors about their offerings which can
monitor/cancel DB2 threads based on multiple criteria including CORRID,
AUTHID, and elapsed time.

"Bueller? Fry? Anyone?"


--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]
----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm


Notice to recipient:
The information in this internet e-mail and any attachments is confidential
and may be privileged. It is intended solely for the addressee. If you are
not the intended addressee please notify the sender immediately by
telephone. If you are not the intended recipient, any disclosure, copying,
distribution or any action taken or omitted to be taken in reliance on it,
is prohibited and may be unlawful.
When addressed to external clients any opinions or advice contained in this
internet e-mail are subject to the terms and conditions expressed in any
applicable governing terms of business or client engagement letter issued by
the pertinent Bank of America group entity.
If this email originates from the U.K. please note that Bank of America,
N.A., London Branch, Banc of America Securities Limited and Banc of America
Futures Incorporated are regulated by the Financial Services Authority.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Don Weigend

Re: [DB2-L ZOS] Monitor Tools which use Wall Time
(in response to Aurora Dellanno)
Where do I find these "QUERYTIMEOUT=0 and INTERRUPT_ENABLED" in DB
Connect. I looked in the client settings as well as the advanced
settings for one of the database alias entries.

Don Weigend
DB2 OS/390 Sysprog
IBM Certified Solutions Expert - DB2 V7 Database Administration OS/390
West Bend Mutual Insurance Company


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Myron Miller
Sent: Wednesday, December 10, 2003 5:44 PM
To: [login to unmask email]
Subject: Re: [DB2-L ZOS] Monitor Tools which use Wall Time

I don't know about monitor tools that can monitor/cancel threads. IMF
(now
from BMC) used to do this, but not sure if it can any more. It had all
kinds
of operator capabilities that were programmable.

But if you are using Db2 connect, you can eliminate some of this problem
with
some settings on your db2 connect server. use QUERYTIMEOUT=0 and
INTERRUPT_ENABLED to cancel threads when the user system cancels them.
Also
don't use KEEPCONNECT=x where x>0. I've found that this will hold
threads
active even when they're not active.

Myron


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steve Whittaker

Re: [DB2-L ZOS] Monitor Tools which use Wall Time
(in response to Don Weigend)
You'll find them under Program Files/SQLLLIB/db2cli.ini ...they would be in there if they are set at all. If not you can add querytimeoutinterval=0 to the end of the file to turn it off.

--Steve....

-----Original Message-----
From: Weigend, Don [mailto:[login to unmask email]
Sent: Thursday, December 11, 2003 9:16 AM
To: [login to unmask email]
Subject: Re: [DB2-L ZOS] Monitor Tools which use Wall Time


Where do I find these "QUERYTIMEOUT=0 and INTERRUPT_ENABLED" in DB
Connect. I looked in the client settings as well as the advanced
settings for one of the database alias entries.

Don Weigend
DB2 OS/390 Sysprog
IBM Certified Solutions Expert - DB2 V7 Database Administration OS/390
West Bend Mutual Insurance Company


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Myron Miller
Sent: Wednesday, December 10, 2003 5:44 PM
To: [login to unmask email]
Subject: Re: [DB2-L ZOS] Monitor Tools which use Wall Time

I don't know about monitor tools that can monitor/cancel threads. IMF
(now
from BMC) used to do this, but not sure if it can any more. It had all
kinds
of operator capabilities that were programmable.

But if you are using Db2 connect, you can eliminate some of this problem
with
some settings on your db2 connect server. use QUERYTIMEOUT=0 and
INTERRUPT_ENABLED to cancel threads when the user system cancels them.
Also
don't use KEEPCONNECT=x where x>0. I've found that this will hold
threads
active even when they're not active.

Myron


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Rich Gugel

Re: [DB2-L ZOS] Monitor Tools which use Wall Time
(in response to Steve Whittaker)
Query timeout interval is documented on the cli parameters from the
following link, If I remember correctly you need to edit the db2cli.ini
file in the common section to put this in.

http://www-306.ibm.com/software/data/db2/udb/ad/v8/cli.html

Interupt enable is on the dcs options (connect version 8), but I think it
was there on version 7 as well.

-----Original Message-----
From: Weigend, Don [mailto:[login to unmask email]
Sent: Thursday, December 11, 2003 8:16 AM
To: [login to unmask email]
Subject: Re: [DB2-L ZOS] Monitor Tools which use Wall Time


Where do I find these "QUERYTIMEOUT=0 and INTERRUPT_ENABLED" in DB
Connect. I looked in the client settings as well as the advanced
settings for one of the database alias entries.

Don Weigend
DB2 OS/390 Sysprog
IBM Certified Solutions Expert - DB2 V7 Database Administration OS/390
West Bend Mutual Insurance Company


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Myron Miller
Sent: Wednesday, December 10, 2003 5:44 PM
To: [login to unmask email]
Subject: Re: [DB2-L ZOS] Monitor Tools which use Wall Time

I don't know about monitor tools that can monitor/cancel threads. IMF
(now
from BMC) used to do this, but not sure if it can any more. It had all
kinds
of operator capabilities that were programmable.

But if you are using Db2 connect, you can eliminate some of this problem
with
some settings on your db2 connect server. use QUERYTIMEOUT=0 and
INTERRUPT_ENABLED to cancel threads when the user system cancels them.
Also
don't use KEEPCONNECT=x where x>0. I've found that this will hold
threads
active even when they're not active.

Myron


**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Myron Miller

Re: [DB2-L ZOS] Monitor Tools which use Wall Time
(in response to Rich Gugel)
Don,
You must set QUERYTIMEOUTINTERVAL=0 in your DB2CLI.INI file found in SQLLIB
whereever you put DB2 Connect. It must be set explicity there and cannot be
set any other way.

INTERRUPT_ENABLED can be set via DCS commands. Or you can take the easy way
and if you've defined your DB2 Connect OS/390 connection as physically residing
on a host, you can set it using CCA, under HOST or AS/400 options. "Disconnect
if client generates an interrupt(INTERRUPT_ENABLED).

I also set there, the additional parm of Disconnect on all SQL300xx return
codes (D).

Myron


--- "Weigend, Don" <[login to unmask email]> wrote:
> Where do I find these "QUERYTIMEOUT=0 and INTERRUPT_ENABLED" in DB
> Connect. I looked in the client settings as well as the advanced
> settings for one of the database alias entries.
>
> Don Weigend
> DB2 OS/390 Sysprog
> IBM Certified Solutions Expert - DB2 V7 Database Administration OS/390
> West Bend Mutual Insurance Company
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
> Behalf Of Myron Miller
> Sent: Wednesday, December 10, 2003 5:44 PM
> To: [login to unmask email]
> Subject: Re: [DB2-L ZOS] Monitor Tools which use Wall Time
>
> I don't know about monitor tools that can monitor/cancel threads. IMF
> (now
> from BMC) used to do this, but not sure if it can any more. It had all
> kinds
> of operator capabilities that were programmable.
>
> But if you are using Db2 connect, you can eliminate some of this problem
> with
> some settings on your db2 connect server. use QUERYTIMEOUT=0 and
> INTERRUPT_ENABLED to cancel threads when the user system cancels them.
> Also
> don't use KEEPCONNECT=x where x>0. I've found that this will hold
> threads
> active even when they're not active.
>
> Myron
>
>
> **********************************************************************
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to whom they
> are addressed. If you have received this email in error please notify
> the system manager.
>
> This footnote also confirms that this email message has been swept by
> MIMEsweeper for the presence of computer viruses.
>
> www.mimesweeper.com
> **********************************************************************
>
>
---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
> page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
> "Join or Leave the list". If you will be out of the office, send the SET
> DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can
> be reached at [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm


__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm