DB2 z/OS V8 - Unit switch performance figures

Martin Flavell

DB2 z/OS V8 - Unit switch performance figures
Hi,
 
We have been stress testing a new application in a new DB2 environment, and , according to Mainview, we are seeing very high values for Unit Switch Open/Close, see below for an example where 11 seconds was spent in Open/Close:
 
CATEGORY           #EVENTS AVG/EVENT  ELAPSED  %TOTAL
----------------   ------- --------- -------- -------
ELAPSED TIME                                         
  IN DB2                                26 s    99.81
  IN APPLICATION                        47 ms    0.18
 --TOTALS--                             26 s   100.00
WAITS IN DB2 (LOCAL)                                 
  LOCK/LATCH             1    972 ms   972 ms    3.74
  I/O WAIT             173  2,353 us   407 ms    1.57
  LOG WRITE I/O         15  1,175 us    18 ms    0.06
  OTHER READ I/O         4  1,029 us 4,115 us    0.01
  OTHER WRITE I/O        0      0 us     0 us    0.00
  UNIT SWITCH EVENTS                                 
  ..COMMIT/ROLLBK        1     16 ms    16 ms    0.06
  ..OPEN/CLOSE          11  1,005 ms    11 s    42.63
  ..SYSLGRNG            12  7,666 us    92 ms    0.35
  ..DATASPACE MGR        6    220 ms 1,321 ms    5.09
  ..OTHER               13  1,645 us    21 ms    0.08
DSMAX is defined as 9960, and we have about 2,500 open datasets. Everything is defined with CLOSE NO.
 
Would anyone know what the open/close figures are referring to, as we are sure that we are not closing and re-opening the datasets? Mainview says we are doing a lot of reopen from deferred close, could that be our problem? If so how can we stop datasets going into deferred close?

Regards,  

Martin Flavell 

-------------------------------------------------------------------------
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for OS/390
IBM Certified Database Administrator -- DB2 UDB V8.1 for Linux, Unix, Windows
-------------------------------------------------------------------------
Technical Consultant
Thesaurus Computer Services Ltd.
Technical Infrastructure and Professional Services

[login to unmask email]
Telephone:01908 246500.    Fax:01908 246555.    
WWW: www.i-tcs.com  

________________________________________________________________________

This email is confidential and intended solely for the use of the individual to whom it is 
addressed. Any views or opinions presented are solely those of the author and do not 
necessarily represent those of Thesaurus Computer Services. If you are not the 
intended recipient, be advised that you have received this email in error and that any 
use, dissemination, forwarding, printing, or copying of this email is prohibited. If you 
have received this email in error please notify the sender, or [login to unmask email], 
by e-mail or telephone +44 (0)1908-246500

Thesaurus Computer Services.
Bank House, 171 Midsummer Boulevard, Central Milton Keynes, MK9 1EB, United Kingdom

Company registration number: 2069568
________________________________________________________________________
Thesaurus Computer Services (www.i-tcs.com)
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org [link to http://www.idug.org/lsidug ] under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information [link to http://www.idug.org/lsconf], and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services [link to http://www.idug.org/lsms]

Avram Friedman

Re: DB2 z/OS V8 - Unit switch performance figures
(in response to Martin Flavell)
Martin
New Application in New DB2
We belive the page spaces were opened prior to the begining of the test
because ____________?

Discribe your test preperation procedure. Do you recover the tablespaces and
rebuild indexes?

You say everything is defined as CLOSE=NO (not the current popular
recomendaton) Does this include EVERYTHING including system objects? temp
and work space?

Is this member part of a datasharing group (even single member data sharing)?

Regards
Avram Friedmna

On Thu, 13 Dec 2007 10:02:13 +0000, [login to unmask email] wrote:

We have been stress testing a new application in a new DB2 environment,
and , according to Mainview, we are seeing very high values for Unit Switch
Open/Close, see below for an example where 11 seconds was spent in Open/

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Martin Flavell

Re: DB2 z/OS V8 - Unit switch performance figures
(in response to Avram Friedman)


Hi Avram
 
Answers below, thanks.
 
New Application in New DB2
We belive the page spaces were opened prior to the begining of the test
because ____________?
The number of currently/max open datasets doesn't change during the test.


Discribe your test preperation procedure.  Do you recover the tablespaces and
rebuild indexes?
Data is loaded with replace to all test tables (897 tables).

You say everything is defined as CLOSE=NO (not the current popular
recomendaton)  Does this include EVERYTHING including system objects? temp
and work space?
I haven't checked the system/temp tables, but all test tablespaces and indexes have CLOSE NO.

Is this member part of a datasharing group (even single member data sharing)?
It is part of a 2 system data sharing group, with the test coming from both sides.

Regards
Avram Friedmna

On Thu, 13 Dec 2007 10:02:13 +0000, [login to unmask email] wrote:

We have been stress testing a new application in a new DB2 environment,
and , according to Mainview, we are seeing very high values for Unit Switch
Open/Close, see below for an example where 11 seconds was spent in Open/

The IDUG DB2-L Listserv is only part of your membership in IDUG.  DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab.  While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.  If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
Thesaurus Computer Services (www.i-tcs.com)
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org [link to http://www.idug.org/lsidug ] under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information [link to http://www.idug.org/lsconf], and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services [link to http://www.idug.org/lsms]

Joel Goldstein

Re: DB2 z/OS V8 - Unit switch performance figures
(in response to Martin Flavell)
Adjust your PCLOSEN and PCLOSET in your Zparms..

Regards,
Joel

Joel Goldstein
Responsive Systems
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works......
Predicts Group Buffer Pool performance too!
www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416
----- Original Message -----
From: [login to unmask email]
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Thursday, December 13, 2007 5:02 AM
Subject: [DB2-L] DB2 z/OS V8 - Unit switch performance figures


Hi,

We have been stress testing a new application in a new DB2 environment, and , according to Mainview, we are seeing very high values for Unit Switch Open/Close, see below for an example where 11 seconds was spent in Open/Close:

CATEGORY #EVENTS AVG/EVENT ELAPSED %TOTAL
---------------- ------- --------- -------- -------
ELAPSED TIME
IN DB2 26 s 99.81
IN APPLICATION 47 ms 0.18
--TOTALS-- 26 s 100.00
WAITS IN DB2 (LOCAL)
LOCK/LATCH 1 972 ms 972 ms 3.74
I/O WAIT 173 2,353 us 407 ms 1.57
LOG WRITE I/O 15 1,175 us 18 ms 0.06
OTHER READ I/O 4 1,029 us 4,115 us 0.01
OTHER WRITE I/O 0 0 us 0 us 0.00
UNIT SWITCH EVENTS
..COMMIT/ROLLBK 1 16 ms 16 ms 0.06
..OPEN/CLOSE 11 1,005 ms 11 s 42.63
..SYSLGRNG 12 7,666 us 92 ms 0.35
..DATASPACE MGR 6 220 ms 1,321 ms 5.09
..OTHER 13 1,645 us 21 ms 0.08

DSMAX is defined as 9960, and we have about 2,500 open datasets. Everything is defined with CLOSE NO.

Would anyone know what the open/close figures are referring to, as we are sure that we are not closing and re-opening the datasets? Mainview says we are doing a lot of reopen from deferred close, could that be our problem? If so how can we stop datasets going into deferred close?

Regards,

Martin Flavell

-------------------------------------------------------------------------
IBM Certified Solutions Expert -- DB2 V7.1 Database Administration for OS/390
IBM Certified Database Administrator -- DB2 UDB V8.1 for Linux, Unix, Windows
-------------------------------------------------------------------------
Technical Consultant
Thesaurus Computer Services Ltd.
Technical Infrastructure and Professional Services

[login to unmask email]
Telephone:01908 246500. Fax:01908 246555.
WWW: www.i-tcs.com

________________________________________________________________________

This email is confidential and intended solely for the use of the individual to whom it is
addressed. Any views or opinions presented are solely those of the author and do not
necessarily represent those of Thesaurus Computer Services. If you are not the
intended recipient, be advised that you have received this email in error and that any
use, dissemination, forwarding, printing, or copying of this email is prohibited. If you
have received this email in error please notify the sender, or [login to unmask email],
by e-mail or telephone +44 (0)1908-246500

Thesaurus Computer Services.
Bank House, 171 Midsummer Boulevard, Central Milton Keynes, MK9 1EB, United Kingdom

Company registration number: 2069568
________________________________________________________________________

Thesaurus Computer Services (www.i-tcs.com)
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org [link to http://www.idug.org/lsidug ] under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information [link to http://www.idug.org/lsconf], and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services [link to http://www.idug.org/lsms]

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Avram Friedman

Re: DB2 z/OS V8 - Unit switch performance figures
(in response to Joel Goldstein)
Martin,
First there is a problem with the LISTSERV web interface that makes it almost
impossible for me to quote the original message so I am sorry about the
absence.

2500 open datasets for what I assume is 897 test table spaces
"Data is loaded with replace to all test tables (897 tables)."
seems right assuming no partitioning, 1.5 indexes per table
900 * 2.5 = 2200 + catalog, directory, temp, work

Are you running z/os 1.5 or above? What controls the max number of open
datasets in DB2 changes somewhat at z/os 1.5 related to how an operating
system control block called the TIOT is managed. At 1.5 it moves above the
line, in addition DB2 uses a variation of the TIOT called ETIOT that is
operating system dependent. I would atleast check your active system
PARMLIB (SYS1.PARMLIB) member ALLOCxx for the TIOT size ... I would think
it should be 64k. Is there a lot of stuff running on your test LPAR? there are
system wide constraints on the total number of open datasets.

"It is part of a 2 system data sharing group, with the test coming from both
sides."
I asked about data sharing because I wondered if sharing intent could have
anything to do with the times being experienced. I dont know if this time
would be lumped into open but it could be lengthy. Did you ever have more
than 2 datasharing members in this group? Are you seeing lots IDC messages
for the BSDS in the MSTR or DBM1 address space?

In the DBM1 address space msglog are you seeing a lot of intertwined
allocate / close messages like
IGD103I SMS ALLOCATED TO DDNAME SYS29255
IGD104I AAAA.DSNDBC.DDDDDDDD.XIIIIII.I0001.A170 RETAINED,
DDNAME=SYS29255

as compared to
IGD103I SMS ALLOCATED TO DDNAME SYS00781
IGD103I SMS ALLOCATED TO DDNAME SYS00782
IGD103I SMS ALLOCATED TO DDNAME SYS00783
IGD103I SMS ALLOCATED TO DDNAME SYS00784


This only applies for the acutual execution of your test
The interwined messages durring normal operation suggest allocation /
deallocation due to DSMAX or data sharing intent issues. Durring a utility
window a diffrent explanation is likely.

Does the problem disapate as the test progresses? Open at startup is not
unusual.


Hope this helps
Avram Friedman

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms