Bufferpool parms for TEMP/DSNDB07 DB

john matthews

Bufferpool parms for TEMP/DSNDB07 DB
I have just allocated the TS's for TEMP database. Should any of the
bufferpool parms have specific values for either the TEMP DB or the DSNDB07
DB?

----------------------------------------------------------
IMPORTANT WARNING: This email (and any attachments) is only intended for the use of the person or entity to which it is addressed, and may contain information that is privileged and confidential. You, the recipient, are obligated to maintain it in a safe, secure and confidential manner. Unauthorized redisclosure or failure to maintain confidentiality may subject you to federal and state penalties. If you are not the intended recipient, please immediately notify us by return email, and delete this message from your computer.
----------------------------------------------------------

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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: Bufferpool parms for TEMP/DSNDB07 DB
(in response to john matthews)
Some of the answer depends on what version you're running and how much RAM
you have. We keep TEMPDB and DSNDB07 in BP1 and let nothing else into
that pool. Other sites probably have two separate pools for each of
TEMPDB and DSNDB07.

We have VPSEQT, DWQT, and VDWQT all set higher than the defaults for BP1;
the object is to minimize physical writes from this pool.

Whatever you do, don't let DSNDB07/workfile databases mix in with regular
databases unless you're desperately short on system physical memory.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]






"Matthews, John" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/16/2004 08:25 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: [DB2-L] Bufferpool parms for TEMP/DSNDB07 DB


I have just allocated the TS's for TEMP database. Should any of the
bufferpool parms have specific values for either the TEMP DB or the
DSNDB07
DB?

----------------------------------------------------------
IMPORTANT WARNING: This email (and any attachments) is only intended for
the use of the person or entity to which it is addressed, and may contain
information that is privileged and confidential. You, the recipient, are
obligated to maintain it in a safe, secure and confidential manner.
Unauthorized redisclosure or failure to maintain confidentiality may
subject you to federal and state penalties. If you are not the intended
recipient, please immediately notify us by return email, and delete this
message from your computer.
----------------------------------------------------------

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

John Lendman

Re: Bufferpool parms for TEMP/DSNDB07 DB
(in response to Philip Sevetson)
There is an unwritten rule were we normally put DSNDB07 in to BP7. (I guess it is written now)

All the rest of the advise holds true. You need to make sure it is by itself.

And by TEMPDB you mean the Global temp tables, any unique BP would work for it.




John C. Lendman
IBM Certified Solutions Expert
IBM Certified Database Administrator
DB2 Universal Database V8.1 for zOS
Office 561-357-7523
Cell 772-485-1592
Email [login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of [login to unmask email]
Sent: Friday, December 17, 2004 8:31 AM
To: [login to unmask email]
Subject: Re: Bufferpool parms for TEMP/DSNDB07 DB



Some of the answer depends on what version you're running and how much RAM you have. We keep TEMPDB and DSNDB07 in BP1 and let nothing else into that pool. Other sites probably have two separate pools for each of TEMPDB and DSNDB07.

We have VPSEQT, DWQT, and VDWQT all set higher than the defaults for BP1; the object is to minimize physical writes from this pool.

Whatever you do, don't let DSNDB07/workfile databases mix in with regular databases unless you're desperately short on system physical memory.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]





"Matthews, John" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>


12/16/2004 08:25 PM
Please respond to DB2 Database Discussion list at IDUG



To: [login to unmask email]
cc:
Subject: [DB2-L] Bufferpool parms for TEMP/DSNDB07 DB



I have just allocated the TS's for TEMP database. Should any of the
bufferpool parms have specific values for either the TEMP DB or the DSNDB07
DB?

----------------------------------------------------------
IMPORTANT WARNING: This email (and any attachments) is only intended for the use of the person or entity to which it is addressed, and may contain information that is privileged and confidential. You, the recipient, are obligated to maintain it in a safe, secure and confidential manner. Unauthorized redisclosure or failure to maintain confidentiality may subject you to federal and state penalties. If you are not the intended recipient, please immediately notify us by return email, and delete this message from your computer.
----------------------------------------------------------

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Avram Friedman

Re: Bufferpool parms for TEMP/DSNDB07 DB
(in response to John Lendman)
TEMP/DSNDBD07 should be in there own buffer pool.
I suggest a single buffer pool for both vs one for each

The usage characteristic that warrants a unique buffer pool is tables
are created, populated, processed and destroyed within the life span of
a single thread.

Concerning the issue of vertical and horizontal write thresholds, my
advice is some what non standard.

The standard recommendation is to avoid I/O to the work data sets. As
such the write thresholds can be set high.
I believe a corollary to this conventional view point is the work and
temp data sets can be allocated small, not much larger in total than the
work temp buffer pool its self because after all the plan is never to do
I/O to them. No need for several data sets or volume path isolation
considerations. The standard suggestion when viewed in light of the
corollary is nonsense. Work and temp data sets are among the most
aggressively managed data sets in most shops. Extent failures,
reallocations, and relocations are common concerning these data sets.
They are written to and read from, often frequently.

Its also the case that rows are tend to be created in mass rapidly.

I suggest setting the write thresholds low. A high write threshold may
result in application delays or slow downs as the buffer pool becomes
full. Many of the instances I have seen of DMC (Data Manager Critical)
have been associated with the work / temp buffer pool. If buffers
happen to be written out too early by a highly efficient chained IO
(many buffers written with a single EXCP) there is some small over head
but the data will still be retrievable from the buffers if the space has
not been reused.

I would like to stress that my view with regards to this issue is
unusual so buyer beware ... Think out your strategy and do what ever
testing, verification and validation is indicated for your shop.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Matthews, John
Sent: Thursday, December 16, 2004 8:26 PM
To: [login to unmask email]
Subject: Bufferpool parms for TEMP/DSNDB07 DB

I have just allocated the TS's for TEMP database. Should any of the
bufferpool parms have specific values for either the TEMP DB or the
DSNDB07 DB?

----------------------------------------------------------
IMPORTANT WARNING: This email (and any attachments) is only intended
for the use of the person or entity to which it is addressed, and may
contain information that is privileged and confidential. You, the
recipient, are obligated to maintain it in a safe, secure and
confidential manner. Unauthorized redisclosure or failure to maintain
confidentiality may subject you to federal and state penalties. If you
are not the intended recipient, please immediately notify us by return
email, and delete this message from your computer.
----------------------------------------------------------

------------------------------------------------------------------------
---------
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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited.


---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

William Proctor

Re: Bufferpool parms for TEMP/DSNDB07 DB
(in response to Avram Friedman)
Do you have hiperpools to backup your pool for DSNDB07?



Bill Proctor

Database Administrator (Adabas/DB2)

DB2 UDB V7.1 Database Administration for OS/390

Texas Guaranteed Student Loan Corp.

Austin, Texas

Phone: 512-219-4847





-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Friday, December 17, 2004 7:31 AM
To: [login to unmask email]
Subject: Re: Bufferpool parms for TEMP/DSNDB07 DB




Some of the answer depends on what version you're running and how much
RAM you have. We keep TEMPDB and DSNDB07 in BP1 and let nothing else
into that pool. Other sites probably have two separate pools for each
of TEMPDB and DSNDB07.

We have VPSEQT, DWQT, and VDWQT all set higher than the defaults for
BP1; the object is to minimize physical writes from this pool.

Whatever you do, don't let DSNDB07/workfile databases mix in with
regular databases unless you're desperately short on system physical
memory.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]







"Matthews, John" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>

12/16/2004 08:25 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: [DB2-L] Bufferpool parms for TEMP/DSNDB07 DB




I have just allocated the TS's for TEMP database. Should any of the
bufferpool parms have specific values for either the TEMP DB or the
DSNDB07
DB?

----------------------------------------------------------
IMPORTANT WARNING: This email (and any attachments) is only intended
for the use of the person or entity to which it is addressed, and may
contain information that is privileged and confidential. You, the
recipient, are obligated to maintain it in a safe, secure and
confidential manner. Unauthorized redisclosure or failure to maintain
confidentiality may subject you to federal and state penalties. If you
are not the intended recipient, please immediately notify us by return
email, and delete this message from your computer.
----------------------------------------------------------

------------------------------------------------------------------------
---------
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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

John Lendman

Re: Bufferpool parms for TEMP/DSNDB07 DB
(in response to William Proctor)
No you do not. The only data that can be in hiperpools is clean data. Data that does not change. This is not the case for DSNDB07, this data changes with every read.


John C. Lendman
IBM Certified Solutions Expert
IBM Certified Database Administrator
DB2 Universal Database V8.1 for zOS
Office 561-357-7523
Cell 772-485-1592
Email [login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Proctor, William
Sent: Friday, December 17, 2004 9:22 AM
To: [login to unmask email]
Subject: Re: Bufferpool parms for TEMP/DSNDB07 DB



Do you have hiperpools to backup your pool for DSNDB07?



Bill Proctor

Database Administrator (Adabas/DB2)

DB2 UDB V7.1 Database Administration for OS/390

Texas Guaranteed Student Loan Corp.

Austin, Texas

Phone: 512-219-4847





-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of [login to unmask email]
Sent: Friday, December 17, 2004 7:31 AM
To: [login to unmask email]
Subject: Re: Bufferpool parms for TEMP/DSNDB07 DB




Some of the answer depends on what version you're running and how much RAM you have. We keep TEMPDB and DSNDB07 in BP1 and let nothing else into that pool. Other sites probably have two separate pools for each of TEMPDB and DSNDB07.

We have VPSEQT, DWQT, and VDWQT all set higher than the defaults for BP1; the object is to minimize physical writes from this pool.

Whatever you do, don't let DSNDB07/workfile databases mix in with regular databases unless you're desperately short on system physical memory.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]








"Matthews, John" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>

12/16/2004 08:25 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: [DB2-L] Bufferpool parms for TEMP/DSNDB07 DB




I have just allocated the TS's for TEMP database. Should any of the
bufferpool parms have specific values for either the TEMP DB or the DSNDB07
DB?

----------------------------------------------------------
IMPORTANT WARNING: This email (and any attachments) is only intended for the use of the person or entity to which it is addressed, and may contain information that is privileged and confidential. You, the recipient, are obligated to maintain it in a safe, secure and confidential manner. Unauthorized redisclosure or failure to maintain confidentiality may subject you to federal and state penalties. If you are not the intended recipient, please immediately notify us by return email, and delete this message from your computer.
----------------------------------------------------------

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Dave Nance

Re: Bufferpool parms for TEMP/DSNDB07 DB
(in response to John Lendman)
John,
You are right its not typically recommended to back up BP7 with
hiperpools. However, there are quite a few shops out there doing that
and get a performance benefit by having the hiperpool. In fact, if you
look back in the archives, I think, someone talks about the benefits
they got by allocating the hiperpool.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 12/17/04 9:29:29 AM >>>

No you do not. The only data that can be in hiperpools is clean data.
Data that does not change. This is not the case for DSNDB07, this data
changes with every read.


John C. Lendman
IBM Certified Solutions Expert
IBM Certified Database Administrator
DB2 Universal Database V8.1 for zOS
Office 561-357-7523
Cell 772-485-1592
Email [login to unmask email] -----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Proctor, William
Sent: Friday, December 17, 2004 9:22 AM
To: [login to unmask email]
Subject: Re: Bufferpool parms for TEMP/DSNDB07 DB


Do you have hiperpools to backup your pool for DSNDB07? Bill
ProctorDatabase Administrator (Adabas/DB2)DB2 UDB V7.1 Database
Administration for OS/390Texas Guaranteed Student Loan Corp.Austin,
TexasPhone: 512-219-4847
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of [login to unmask email]
Sent: Friday, December 17, 2004 7:31 AM
To: [login to unmask email]
Subject: Re: Bufferpool parms for TEMP/DSNDB07 DB
Some of the answer depends on what version you're running and how much
RAM you have. We keep TEMPDB and DSNDB07 in BP1 and let nothing else
into that pool. Other sites probably have two separate pools for each
of TEMPDB and DSNDB07.

We have VPSEQT, DWQT, and VDWQT all set higher than the defaults for
BP1; the object is to minimize physical writes from this pool.

Whatever you do, don't let DSNDB07/workfile databases mix in with
regular databases unless you're desperately short on system physical
memory.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]



"Matthews, John" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/16/2004 08:25 PM
Please respond to DB2 Database Discussion list at IDUG
To: [login to unmask email]
cc:
Subject: [DB2-L] Bufferpool parms for TEMP/DSNDB07 DB


I have just allocated the TS's for TEMP database. Should any of the
bufferpool parms have specific values for either the TEMP DB or the
DSNDB07
DB?

----------------------------------------------------------
IMPORTANT WARNING: This email (and any attachments) is only intended
for the use of the person or entity to which it is addressed, and may
contain information that is privileged and confidential. You, the
recipient, are obligated to maintain it in a safe, secure and
confidential manner. Unauthorized redisclosure or failure to maintain
confidentiality may subject you to federal and state penalties. If you
are not the intended recipient, please immediately notify us by return
email, and delete this message from your computer.
----------------------------------------------------------

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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

"MMS <fhmail.firsthealth.com>" made the following annotations.
------------------------------------------------------------------------------
This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.
=====

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

john matthews

Re: Bufferpool parms for TEMP/DSNDB07 DB
(in response to Dave Nance)
No - we haven't ventured into that arena as yet

-----Original Message-----
From: Proctor, William [mailto:[login to unmask email]
Sent: Friday, December 17, 2004 6:22 AM
To: [login to unmask email]
Subject: Re: Bufferpool parms for TEMP/DSNDB07 DB



Do you have hiperpools to backup your pool for DSNDB07?



Bill Proctor

Database Administrator (Adabas/DB2)

DB2 UDB V7.1 Database Administration for OS/390

Texas Guaranteed Student Loan Corp.

Austin, Texas

Phone: 512-219-4847





-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of [login to unmask email]
Sent: Friday, December 17, 2004 7:31 AM
To: [login to unmask email]
Subject: Re: Bufferpool parms for TEMP/DSNDB07 DB




Some of the answer depends on what version you're running and how much RAM
you have. We keep TEMPDB and DSNDB07 in BP1 and let nothing else into that
pool. Other sites probably have two separate pools for each of TEMPDB and
DSNDB07.

We have VPSEQT, DWQT, and VDWQT all set higher than the defaults for BP1;
the object is to minimize physical writes from this pool.

Whatever you do, don't let DSNDB07/workfile databases mix in with regular
databases unless you're desperately short on system physical memory.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]








"Matthews, John" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>

12/16/2004 08:25 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: [DB2-L] Bufferpool parms for TEMP/DSNDB07 DB




I have just allocated the TS's for TEMP database. Should any of the
bufferpool parms have specific values for either the TEMP DB or the DSNDB07
DB?

----------------------------------------------------------
IMPORTANT WARNING: This email (and any attachments) is only intended for
the use of the person or entity to which it is addressed, and may contain
information that is privileged and confidential. You, the recipient, are
obligated to maintain it in a safe, secure and confidential manner.
Unauthorized redisclosure or failure to maintain confidentiality may subject
you to federal and state penalties. If you are not the intended recipient,
please immediately notify us by return email, and delete this message from
your computer.
----------------------------------------------------------

----------------------------------------------------------------------------
-----
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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


----------------------------------------------------------
IMPORTANT WARNING: This email (and any attachments) is only intended for the use of the person or entity to which it is addressed, and may contain information that is privileged and confidential. You, the recipient, are obligated to maintain it in a safe, secure and confidential manner. Unauthorized redisclosure or failure to maintain confidentiality may subject you to federal and state penalties. If you are not the intended recipient, please immediately notify us by return email, and delete this message from your computer.
----------------------------------------------------------

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Mark McCormack

Bufferpool parms for TEMP/DSNDB07 DB
(in response to john matthews)
John,

We also have TEMP and DSNDB07 in its own bufferpool. For many of our
subsystems we have thresholds:
SEQ 80%, VDW 70%, DW 80%
rationale: If a unit of work ends before write from bufferpool to
tablespace, then writes are cancelled. Delaying start of writes leads to
fewer writes (with the workload on these subsystems).

Our subsystem with the heaviest sort load has many more buffers and these
thresholds:
SEQ 97%, VDW 20%, DW 70%
rationale: There is so much activity here that lots of writes occur to the
DSNDB07 tablespaces. The lower thresholds allow write activity to start
earlier, so the bufferpool is less likely to be clogged with write-pending
pages.

I can make no guarantee that this is the best way to do things, but iIt is
what we are doing.

Mark

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Francis C - CNF Leblanc

Re: Bufferpool parms for TEMP/DSNDB07 DB
(in response to Mark McCormack)
Very interesting discussion. On a related topic, I was wondering
whether other sites created a small number of large sortwk datasets, or
a large number of small sortwk datasets and their reasons for doing so.

Happy Holidays to all!

Fritz

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Mark McCormack
Sent: Friday, December 17, 2004 1:05 PM
To: [login to unmask email]
Subject: Bufferpool parms for TEMP/DSNDB07 DB

John,

We also have TEMP and DSNDB07 in its own bufferpool. For many of our
subsystems we have thresholds:
SEQ 80%, VDW 70%, DW 80%
rationale: If a unit of work ends before write from bufferpool to
tablespace, then writes are cancelled. Delaying start of writes leads
to fewer writes (with the workload on these subsystems).

Our subsystem with the heaviest sort load has many more buffers and
these
thresholds:
SEQ 97%, VDW 20%, DW 70%
rationale: There is so much activity here that lots of writes occur to
the
DSNDB07 tablespaces. The lower thresholds allow write activity to start
earlier, so the bufferpool is less likely to be clogged with
write-pending pages.

I can make no guarantee that this is the best way to do things, but iIt
is what we are doing.

Mark

------------------------------------------------------------------------
---------
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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Cathy Taddei

Re: Bufferpool parms for TEMP/DSNDB07 DB
(in response to Francis C - CNF Leblanc)
Referring to the most excellent Chuck Hoover paper I mentioned earlier,
"Understanding the DB2 for z/OS Sort", here are a couple of pointers he
gives pertinent to your question:

. Consider using more than the default of five tablespaces for 4K pages
. Use large equal-sized primary allocations for the DSNDB07 tablespaces
and consider not using secondary extents.

He has many other suggestions as well. It is not an easy read, but well
worth it.
http://shareweb.share.org/proceedings/SH98/pprbyses/pprbyses83.html

Merry Christmas!
Cathy Taddei

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Leblanc, Francis C - CNF
Sent: Friday, December 17, 2004 1:27 PM
To: [login to unmask email]
Subject: Re: Bufferpool parms for TEMP/DSNDB07 DB

Very interesting discussion. On a related topic, I was wondering
whether other sites created a small number of large sortwk datasets, or
a large number of small sortwk datasets and their reasons for doing so.

Happy Holidays to all!

Fritz

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Mark McCormack
Sent: Friday, December 17, 2004 1:05 PM
To: [login to unmask email]
Subject: Bufferpool parms for TEMP/DSNDB07 DB

John,

We also have TEMP and DSNDB07 in its own bufferpool. For many of our
subsystems we have thresholds:
SEQ 80%, VDW 70%, DW 80%
rationale: If a unit of work ends before write from bufferpool to
tablespace, then writes are cancelled. Delaying start of writes leads
to fewer writes (with the workload on these subsystems).

Our subsystem with the heaviest sort load has many more buffers and
these
thresholds:
SEQ 97%, VDW 20%, DW 70%
rationale: There is so much activity here that lots of writes occur to
the
DSNDB07 tablespaces. The lower thresholds allow write activity to start
earlier, so the bufferpool is less likely to be clogged with
write-pending pages.

I can make no guarantee that this is the best way to do things, but iIt
is what we are doing.

Mark

------------------------------------------------------------------------
---------
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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. 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


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

This email is confidential and may be legally privileged.

It is intended solely for the addressee. Access to this email by anyone else, unless expressly approved by the sender or an authorized addressee, is unauthorized.

If you are not the intended recipient, any disclosure, copying, distribution or any action omitted or taken in reliance on it, is prohibited and may be unlawful. If you believe that you have received this email in error, please contact the sender, delete this e-mail and destroy all copies.

======

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Isaac Yassin

Re: Bufferpool parms for TEMP/DSNDB07 DB
(in response to Cathy Taddei)
Hi,

I look after sort activity in the "heavy" hours to decide upon TS usage.
Without SP (Stored Procedure) usage I can manage (most of the times) with 10-20 TS in DSNDB07, each about 200-300 Cyls.
The VBP is ~10,000 pages (for starters), VDWQT 45 DWQT 65 VPSEQT 95. With high number of sorting SP it is better to have more TS ( I
once had ~150 TS due to high SP usage).
On old machines (pre z/OS) there is sometimes an option for HP usage as it can help with eliminating the need to do disk access for
reading back the externalized pages.
Secondary extents usage depends on whether you want to handle the growth manually :-( or getting a call about an abending job :-(
(I prefer the call - it helps to catch wrong SQL ).

Knowing about big sorts in advance is a bless but many times you know about it too late.



Isaac Yassin
DBMS & IT Consulting
IBM Certified Solution Expert
DB2 V7 for OS/390 & z/OS
IBM Certified Database Administrator
DB2 UDB V8.1 for z/OS

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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

Venkat Srinivasan

Re: Bufferpool parms for TEMP/DSNDB07 DB
(in response to Isaac Yassin)
Avram
Almost all usage of temp table is in such a way that the usage will be
within the span of a thread. Is it not so?.

Second, you should almost never allocate more buffers than the SORT data
set space. why would you want to do it?. I am assuming (Something like a 2
million work file buffers and a 10 track work file dataset?.?..)

You want to balance the choice of write threshold. A large write threshold
is fantastic as long as it doesn't result in shortage of write engines or
any sign that may be construed as a resource shortage at any time. You
probably penalize the large sorters and let the small sorters use the
buffer pool effectively. It probably resulted in Data manager threshold
because your sort buffers were smaller. I think frequent checkpoint is
likely to cause more disruption than reaching deferred write queue limit.

A value of (VDWQT) 30 - 50 % is a good starting point.
Many small work datasets is better than a large dataset. You can also
create custom volumes in ESS with smaller cylinders and not worry about
placing other datasets on that volume and thereby skewing the I/O. (A
strategy commonly adopted for PAGE datasets). You may also benefit from
PAV and dynamic alias management in WLM.

If you spend too much time reading from work datasets, then large
hiperpool will help. Now anyway, almost no one is using hiperpools.
Regards,
Venkat

---------------------------------------------------------------------------------
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". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. 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