DSNDB07 (in a perfect world)

[login to unmask email]

DSNDB07 (in a perfect world)
Hi George,

We didn't think we had a problem either. However, an IBM performance
study showed that we had 200+ MS wait times on the DSNDB07 datasets
(four of them on four dedicated volumes) due to enqueuing on the
UCB. IBM said that in a perfect world we should have as many
DSNDB07 workfiles on separate UCBs as the tablespace with the most
partitions. We settled on ten and the wait times were reduced
significantly. Every little bit help...

Regards,

Michael Levine
Premier Data Services, Inc.



>Dave, you have a pool. You can paint white stripes on the bottom but it will
>still hold the same amount of water. DB2 will allocate multiple pagesets in
>each dataset using a LRU algorhythm.
>Just be glad you don't have any problems.
>
>George
>
>
>


>
>



[login to unmask email]

Re: DSNDB07 (in a perfect world)
(in response to Mike_Levine@TEKHELP.NET)
This problem probably (?) derives from the number of active page sets within
each 4K dataset. Try to keep the sorts from using the 4K work datasets at all,
if possible. This means a BIG dedicated DSNDB07 bufferpool (maybe 50,000
pages); you might try parm values of VPSEQT of 98, DWQT 75, VDWQT 50 , and max
SORTPOOL at 64,000K.

I agree that more 4Ks are better, as long as they're on different
logical/physical volumes; we have several databases with thirteen large 4K work
datasets.

Good luck, George



[login to unmask email]

Re: DSNDB07 (in a perfect world)
(in response to truman.g.brown@BELLATLANTIC.COM)
Hi George,

For the DSNDB07 tablespaces we have a dedicated 10,000 page bufferpool with
the following: VPSEQT=70, DWQT=10 and VDWQT=0 and sortpool=20,000K. We
have a 99.9% hit ratio and excellent response times. What would be the
rational for changing these parms? (we have a change freeze anyway). Are
these general recommendations for the DSNDB07 bufferpool?

Thanks,

Michael Levine
Premier Data Services, Inc.


>This problem probably (?) derives from the number of active page sets within
>each 4K dataset. Try to keep the sorts from using the 4K work datasets at all,
>if possible. This means a BIG dedicated DSNDB07 bufferpool (maybe 50,000
>pages); you might try parm values of VPSEQT of 98, DWQT 75, VDWQT 50 , and max
>SORTPOOL at 64,000K.
>
>I agree that more 4Ks are better, as long as they're on different
>logical/physical volumes; we have several databases with thirteen large 4K work
>datasets.
>
>Good luck, George
>
>
>


>
>



[login to unmask email]

Re: DSNDB07 (in a perfect world)
(in response to Mike_Levine@TEKHELP.NET)
Mike:

You don't want ANY I/O if you can help it. SORTPOOL should be large enough to
reduce the possibility of "runs" being written to disk which then incurs a
merger to get the results set(a "run" is a sort which is too large for the
SORTPOOL).

Obviously BP and SORTPOOL size will be relative to the amount of 4K activity you
have in the database, and of course I don't know what tools you have to monitor
your work dataset BP.

The I/O for 4K work datasets in the DSNDB07 BP will be almost all sequential,
therefore set VPSEQT to 98. You want to avoid reaching DWQT and VDWQT -
remember that those thresholds are normally set for non-DSNDB07 update activity;
you want to keep the 4K sorts in storage if at all possible. For that reason,
set DWQT to 70-80 and VDWQT to 40-50. Watch the activity in the work BP and try
to set both DWQT and VDWQT for a little higher than average max use for total
and single 4K respectively.

If you have the storage available, double your BP and SORTPOOL. What you want
to see on a DB2 PM statistics report is efficient activity under the SORT/MERGE
column.

Good luck, George




[login to unmask email] on 12/16/99 10:53:55 AM

Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: TRUMAN G. BROWN/EMPL/MD/Bell-Atl)
Subject: Re: DSNDB07 (in a perfect world)



Hi George,

For the DSNDB07 tablespaces we have a dedicated 10,000 page bufferpool with
the following: VPSEQT=70, DWQT=10 and VDWQT=0 and sortpool=20,000K. We
have a 99.9% hit ratio and excellent response times. What would be the
rational for changing these parms? (we have a change freeze anyway). Are
these general recommendations for the DSNDB07 bufferpool?

Thanks,

Michael Levine
Premier Data Services, Inc.


>This problem probably (?) derives from the number of active page sets within
>each 4K dataset. Try to keep the sorts from using the 4K work datasets at all,
>if possible. This means a BIG dedicated DSNDB07 bufferpool (maybe 50,000
>pages); you might try parm values of VPSEQT of 98, DWQT 75, VDWQT 50 , and max
>SORTPOOL at 64,000K.
>
>I agree that more 4Ks are better, as long as they're on different
>logical/physical volumes; we have several databases with thirteen large 4K work
>datasets.
>
>Good luck, George
>
>
>


>
>








Isaac Yassin

Re: DSNDB07 (in a perfect world)
(in response to truman.g.brown@BELLATLANTIC.COM)
Hi,
Lately I did some measurements in our system and changed the BP7 as follows:
60MB DWQT 90% VDWQT 90% VPSEQT 95%
work files are spread over 6 disks on 2 separate boxes (HDS 7700). It minimized
greatly the physical I/O to the workfiles and it occurs only on some batch
nightly runs.

--
Isaac Yassin

DBMS & IT Consultant

Tel: +972 9 9505172
Cel: +972 54 452793
Fax: +972 9 9560803


[login to unmask email] wrote:
>
> Mike:
>
> You don't want ANY I/O if you can help it. SORTPOOL should be large enough to
> reduce the possibility of "runs" being written to disk which then incurs a
> merger to get the results set(a "run" is a sort which is too large for the
> SORTPOOL).
>
> Obviously BP and SORTPOOL size will be relative to the amount of 4K activity you
> have in the database, and of course I don't know what tools you have to monitor
> your work dataset BP.
>
> The I/O for 4K work datasets in the DSNDB07 BP will be almost all sequential,
> therefore set VPSEQT to 98. You want to avoid reaching DWQT and VDWQT -
> remember that those thresholds are normally set for non-DSNDB07 update activity;
> you want to keep the 4K sorts in storage if at all possible. For that reason,
> set DWQT to 70-80 and VDWQT to 40-50. Watch the activity in the work BP and try
> to set both DWQT and VDWQT for a little higher than average max use for total
> and single 4K respectively.
>
> If you have the storage available, double your BP and SORTPOOL. What you want
> to see on a DB2 PM statistics report is efficient activity under the SORT/MERGE
> column.
>
> Good luck, George
>
> [login to unmask email] on 12/16/99 10:53:55 AM
>
> Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>
>
> To: [login to unmask email]
> cc: (bcc: TRUMAN G. BROWN/EMPL/MD/Bell-Atl)
> Subject: Re: DSNDB07 (in a perfect world)
>
> Hi George,
>
> For the DSNDB07 tablespaces we have a dedicated 10,000 page bufferpool with
> the following: VPSEQT=70, DWQT=10 and VDWQT=0 and sortpool=20,000K. We
> have a 99.9% hit ratio and excellent response times. What would be the
> rational for changing these parms? (we have a change freeze anyway). Are
> these general recommendations for the DSNDB07 bufferpool?
>
> Thanks,
>
> Michael Levine
> Premier Data Services, Inc.
>
> >This problem probably (?) derives from the number of active page sets within
> >each 4K dataset. Try to keep the sorts from using the 4K work datasets at all,
> >if possible. This means a BIG dedicated DSNDB07 bufferpool (maybe 50,000
> >pages); you might try parm values of VPSEQT of 98, DWQT 75, VDWQT 50 , and max
> >SORTPOOL at 64,000K.
> >
> >I agree that more 4Ks are better, as long as they're on different
> >logical/physical volumes; we have several databases with thirteen large 4K work
> >datasets.
> >
> >Good luck, George
> >
> >
> >
>
>
> >
> >
>
>
>
>
>
>
>
>



Joel Goldstein

Re: DSNDB07 (in a perfect world)
(in response to Isaac Yassin)
Message text written by DB2 Data Base Discussion List
>For the DSNDB07 tablespaces we have a dedicated 10,000 page bufferpool
with
the following: VPSEQT=70, DWQT=10 and VDWQT=0 and sortpool=20,000K. We
have a 99.9% hit ratio and excellent response times. What would be the
rational for changing these parms? (we have a change freeze anyway). Are
these general recommendations for the DSNDB07 bufferpool?<

++++++++++++++++++++++++
VPSEQT should be 95-98%. VDWQT should never be zero on a sort pool. You
would like
vdwqt and dwqt to be higher than the defaults, as long as you don't start
slamming into
spth. dmth, iwth for larger sorts.
Be espesially careful of the sortpool (20,000) parameter in the zparm.
This is max memory
PER USER, so several large sort users will drive the memory requirement for
DBM1 up[
vetry quickly and may impact system paging.
Regards,
Joel
|=======
| Joel Goldstein
| Responsive Systems Company
|=======
| WEB Site
| www.responsivesystems.com
|=======
| Buffer Pool Tool for DB2 & DASD/Xpert for DB2
| >>> The Answers for your performance problems <<<
|-------------------------------------------------------
| Tel.(800) DB2-EXPErt Tel.(800) 322-3973
| Tel.(732) 972-1261 Fax.(732) 972-9416
|=======



[login to unmask email]

Re: DSNDB07 (in a perfect world)
(in response to Joel Goldstein)
Just out of curiosity, do you know what the old values were? (I know they don't relate much to any one else's system but ...)




Isaac Yassin <[login to unmask email]> on 99/12/16 12:02:55 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Rohn Solecki/MTSCommunications/MTS)
Subject: Re: DSNDB07 (in a perfect world)




Hi,
Lately I did some measurements in our system and changed the BP7 as follows:
60MB DWQT 90% VDWQT 90% VPSEQT 95%
work files are spread over 6 disks on 2 separate boxes (HDS 7700). It minimized
greatly the physical I/O to the workfiles and it occurs only on some batch
nightly runs.

--
Isaac Yassin

DBMS & IT Consultant

Tel: +972 9 9505172
Cel: +972 54 452793
Fax: +972 9 9560803


[login to unmask email] wrote:
>
> Mike:
>
> You don't want ANY I/O if you can help it. SORTPOOL should be large enough to
> reduce the possibility of "runs" being written to disk which then incurs a
> merger to get the results set(a "run" is a sort which is too large for the
> SORTPOOL).
>
> Obviously BP and SORTPOOL size will be relative to the amount of 4K activity you
> have in the database, and of course I don't know what tools you have to monitor
> your work dataset BP.
>
> The I/O for 4K work datasets in the DSNDB07 BP will be almost all sequential,
> therefore set VPSEQT to 98. You want to avoid reaching DWQT and VDWQT -
> remember that those thresholds are normally set for non-DSNDB07 update activity;
> you want to keep the 4K sorts in storage if at all possible. For that reason,
> set DWQT to 70-80 and VDWQT to 40-50. Watch the activity in the work BP and try
> to set both DWQT and VDWQT for a little higher than average max use for total
> and single 4K respectively.
>
> If you have the storage available, double your BP and SORTPOOL. What you want
> to see on a DB2 PM statistics report is efficient activity under the SORT/MERGE
> column.
>
> Good luck, George
>
> [login to unmask email] on 12/16/99 10:53:55 AM
>
> Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>
>
> To: [login to unmask email]
> cc: (bcc: TRUMAN G. BROWN/EMPL/MD/Bell-Atl)
> Subject: Re: DSNDB07 (in a perfect world)
>
> Hi George,
>
> For the DSNDB07 tablespaces we have a dedicated 10,000 page bufferpool with
> the following: VPSEQT=70, DWQT=10 and VDWQT=0 and sortpool=20,000K. We
> have a 99.9% hit ratio and excellent response times. What would be the
> rational for changing these parms? (we have a change freeze anyway). Are
> these general recommendations for the DSNDB07 bufferpool?
>
> Thanks,
>
> Michael Levine
> Premier Data Services, Inc.
>
> >This problem probably (?) derives from the number of active page sets within
> >each 4K dataset. Try to keep the sorts from using the 4K work datasets at all,
> >if possible. This means a BIG dedicated DSNDB07 bufferpool (maybe 50,000
> >pages); you might try parm values of VPSEQT of 98, DWQT 75, VDWQT 50 , and max
> >SORTPOOL at 64,000K.
> >
> >I agree that more 4Ks are better, as long as they're on different
> >logical/physical volumes; we have several databases with thirteen large 4K work
> >datasets.
> >
> >Good luck, George
> >
> >
> >
>
>
> >
> >
>
>
>
>
>
>
>
>






Isaac Yassin

Re: DSNDB07 (in a perfect world)
(in response to Rohn.Solecki@MTS.MB.CA)
Hi,
My mistake - should have mentioned that.
60MB (15000 - pages) no change here (prev. round it was 40MB). DWQT 65% VDWQT
60% VPSEQT 99%.


[login to unmask email] wrote:
>
> Just out of curiosity, do you know what the old values were? (I know they don't relate much to any one else's system but ...)
>
> Isaac Yassin <[login to unmask email]> on 99/12/16 12:02:55 PM
>
> Please respond to DB2 Data Base Discussion List <[login to unmask email]>
>
> To: [login to unmask email]
> cc: (bcc: Rohn Solecki/MTSCommunications/MTS)
> Subject: Re: DSNDB07 (in a perfect world)
>
> Hi,
> Lately I did some measurements in our system and changed the BP7 as follows:
> 60MB DWQT 90% VDWQT 90% VPSEQT 95%
> work files are spread over 6 disks on 2 separate boxes (HDS 7700). It minimized
> greatly the physical I/O to the workfiles and it occurs only on some batch
> nightly runs.
>
> --
> Isaac Yassin
>
> DBMS & IT Consultant
>
> Tel: +972 9 9505172
> Cel: +972 54 452793
> Fax: +972 9 9560803
>
> [login to unmask email] wrote:
> >
> > Mike:
> >
> > You don't want ANY I/O if you can help it. SORTPOOL should be large enough to
> > reduce the possibility of "runs" being written to disk which then incurs a
> > merger to get the results set(a "run" is a sort which is too large for the
> > SORTPOOL).
> >
> > Obviously BP and SORTPOOL size will be relative to the amount of 4K activity you
> > have in the database, and of course I don't know what tools you have to monitor
> > your work dataset BP.
> >
> > The I/O for 4K work datasets in the DSNDB07 BP will be almost all sequential,
> > therefore set VPSEQT to 98. You want to avoid reaching DWQT and VDWQT -
> > remember that those thresholds are normally set for non-DSNDB07 update activity;
> > you want to keep the 4K sorts in storage if at all possible. For that reason,
> > set DWQT to 70-80 and VDWQT to 40-50. Watch the activity in the work BP and try
> > to set both DWQT and VDWQT for a little higher than average max use for total
> > and single 4K respectively.
> >
> > If you have the storage available, double your BP and SORTPOOL. What you want
> > to see on a DB2 PM statistics report is efficient activity under the SORT/MERGE
> > column.
> >
> > Good luck, George
> >
> > [login to unmask email] on 12/16/99 10:53:55 AM
> >
> > Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>
> >
> > To: [login to unmask email]
> > cc: (bcc: TRUMAN G. BROWN/EMPL/MD/Bell-Atl)
> > Subject: Re: DSNDB07 (in a perfect world)
> >
> > Hi George,
> >
> > For the DSNDB07 tablespaces we have a dedicated 10,000 page bufferpool with
> > the following: VPSEQT=70, DWQT=10 and VDWQT=0 and sortpool=20,000K. We
> > have a 99.9% hit ratio and excellent response times. What would be the
> > rational for changing these parms? (we have a change freeze anyway). Are
> > these general recommendations for the DSNDB07 bufferpool?
> >
> > Thanks,
> >
> > Michael Levine
> > Premier Data Services, Inc.
> >
> > >This problem probably (?) derives from the number of active page sets within
> > >each 4K dataset. Try to keep the sorts from using the 4K work datasets at all,
> > >if possible. This means a BIG dedicated DSNDB07 bufferpool (maybe 50,000
> > >pages); you might try parm values of VPSEQT of 98, DWQT 75, VDWQT 50 , and max
> > >SORTPOOL at 64,000K.
> > >
> > >I agree that more 4Ks are better, as long as they're on different
> > >logical/physical volumes; we have several databases with thirteen large 4K work
> > >datasets.
> > >
> > >Good luck, George
> > >
> > >
> > >
> >
> >
> > >
> > >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
>
>

--
Isaac Yassin

DBMS & IT Consultant

Tel: +972 9 9505172
Cel: +972 54 452793
Fax: +972 9 9560803



[login to unmask email]

Re: DSNDB07 (in a perfect world)
(in response to Isaac Yassin)
Hi,

I will try VPSEQT=95, DWQT=80 and VDWQT=80. We will also increase
the sortpool by 10,000K after the freeze.

Thanks for all your inputs!

Regards,

Michael Levine
Premier Data Services, Inc.


>Message text written by DB2 Data Base Discussion List
>>For the DSNDB07 tablespaces we have a dedicated 10,000 page bufferpool
>with
>the following: VPSEQT=70, DWQT=10 and VDWQT=0 and sortpool=20,000K. We
>have a 99.9% hit ratio and excellent response times. What would be the
>rational for changing these parms? (we have a change freeze anyway). Are
>these general recommendations for the DSNDB07 bufferpool?<
>
>++++++++++++++++++++++++
>VPSEQT should be 95-98%. VDWQT should never be zero on a sort pool. You
>would like
>vdwqt and dwqt to be higher than the defaults, as long as you don't start
>slamming into
>spth. dmth, iwth for larger sorts.
>Be espesially careful of the sortpool (20,000) parameter in the zparm.
>This is max memory
>PER USER, so several large sort users will drive the memory requirement for
>DBM1 up[
>vetry quickly and may impact system paging.
>Regards,
>Joel
>|=======
>| Joel Goldstein
>| Responsive Systems Company
>|=======
>| WEB Site
>| www.responsivesystems.com
>|=======
>| Buffer Pool Tool for DB2 & DASD/Xpert for DB2
>| >>> The Answers for your performance problems <<<
>|-------------------------------------------------------
>| Tel.(800) DB2-EXPErt Tel.(800) 322-3973
>| Tel.(732) 972-1261 Fax.(732) 972-9416
>|=======
>
>


>
>



[login to unmask email]

DSNDB07
(in response to Mike_Levine@TEKHELP.NET)
When placing the DSNDB07 work files on separate DASD volumes, should there be
any other files on the same volume?

Barbara Jo Nigh, DBA



[login to unmask email]

Re: DSNDB07
(in response to bjnigh@HOUSEHOLD.COM)
Only if the other files are VERY LOW activity. Also, if you size SORTPOOL and
work dataset bufferpool correctly (application and storage allowing), you'll
have minimum usage of the 4K work datasets.

George




[login to unmask email] on 12/30/99 01:41:04 PM

Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: TRUMAN G. BROWN/EMPL/MD/Bell-Atl)
Subject: DSNDB07



When placing the DSNDB07 work files on separate DASD volumes, should there be
any other files on the same volume?

Barbara Jo Nigh, DBA








HARBRY ARIZA

Re: DSNDB07
(in response to truman.g.brown@BELLATLANTIC.COM)
Barbara:

Is highly recomemmded specifie dedicated volumes to the dsndb07. These
volumes going to have high activity, that's why is not recommemded allocate
another datasets on those specific volumes. The same thing occur with the
sort bufferpool. Is better dedicated one bufferpool to the dsndb07.



>From: [login to unmask email]
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: DSNDB07
>Date: Thu, 30 Dec 1999 10:41:04 -0800
>
>When placing the DSNDB07 work files on separate DASD volumes, should there
>be
>any other files on the same volume?
>
>Barbara Jo Nigh, DBA
>
>
>
>
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com