DB2 v12: DSNDB07 DGTT and Sort Work Bufferpools

DJ Jones

DB2 v12: DSNDB07 DGTT and Sort Work Bufferpools

Our Declared Global Temporary Table and Sort Work tablespaces in DSNDB07 share one common 4K Bufferpool and one common 32K Bufferpool. Once a month, we run relatively heavy reporting via a combination of  Stored Procedures and DB2 Connect packages by way of a client reporting tool (Crystal).

We do not have visibility via a bufferpool  management tool to see if this is adversely affecting bufferpool performance, so, as a general best practice, is it a good rule of thumb to have separate bufferpools assigned to Declared Global Temporary Table tablepaces.and Sort Work tablespaces?

That is, is it common practice to have distinct bufferpools?

 

 

 

 

 

 

 

Edited By:
DJ Jones[Organization Members] @ Feb 09, 2020 - 02:08 PM (America/Eastern)
DJ Jones[Organization Members] @ Feb 09, 2020 - 02:18 PM (America/Eastern)

Michael Hannan

RE: DB2 v12: DSNDB07 DGTT and Sort Work Bufferpools
(in response to DJ Jones)

DJ,

I would look at doing something if you have a problem. What the most common practice is, is less relevant. Some sites don't care if their DGTTs are performing great or not.

There are DB2 display commands for Bufferpools with object detail that you can capture before and after some set of work. That data should give you a clue if the I/Os rate for DGTTs is too high. You would hope most of access would stay in BP memory.

Note that DGTTs accessed via indexes are not sequential but not all sort work processing is sequential also. Lookups to workfile for IN subquery via sparse index is one example of non sequential sortwork access.

If your DGTTs are performing well enough, don't worry. It has to be having a cost to be worth addressing. There could be other areas where poor performance costs much more.

Every system has lots of poor table designs, poor SQLs, poor indexing, poor subsystem setup BPs etc. No site is perfect. Address the areas that are hurting something. So much non optimal stuff doesn't matter.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

DJ Jones

RE: DB2 v12: DSNDB07 DGTT and Sort Work Bufferpools
(in response to Michael Hannan)

Thanks for the reply.

Regarding Bufferpool detail, looking at the amount of random and sequential IO, sync vs. asynch IO, is really not that helpful. What concerns me is the possibility of page thrashing, and I don't want to request an increase in VPSIZE or the assignment of pages to other 4K or 32K bufffpools, without some sort of notion it will lower costs.  Our reporting process is costly, and it is possible pages are being paged out and paged in, due to contention between SORTs and DGTT inserts and cursor fetches, regardless of the access paths. 

Since the use of DSNDB07 for DGTTs rather than the Temp Database is a relatively new development, having scoured the IBM Release Docs and the web and finding nothing, I was simply wondering if people are using separate bufferpools.

Michael Hannan

RE: DB2 v12: DSNDB07 DGTT and Sort Work Bufferpools
(in response to DJ Jones)

In Reply to DJ Jones:

Thanks for the reply.

Regarding Bufferpool detail, looking at the amount of random and sequential IO, sync vs. asynch IO, is really not that helpful. What concerns me is the possibility of page thrashing, and I don't want to request an increase in VPSIZE or the assignment of pages to other 4K or 32K bufffpools, without some sort of notion it will lower costs.  Our reporting process is costly, and it is possible pages are being paged out and paged in, due to contention between SORTs and DGTT inserts and cursor fetches, regardless of the access paths. 

Since the use of DSNDB07 for DGTTs rather than the Temp Database is a relatively new development, having scoured the IBM Release Docs and the web and finding nothing, I was simply wondering if people are using separate bufferpools.

DJ,

It sounds like you don't like my approach, yet I was trying to be sensible. You just want solutions. So I might be better off to cease here.

The amount of I/O for your DGTT storage objects certainly does matter. If it is very low then your BP is probably working very well. Saving I/O is what a BP is for. I did not mean to suggest to compare the Sync I/Os to Async I/Os. The total I/Os is more important. 

There was just a comment that BPs have threshold settings that sometimes vary depending on proportion of Random to Sequential page processing. That typically causes the Sortwork BP to have settings favoring largely sequential processing. Therefore some might separate DGTTs since their access is potentially having a different portion of random access. However I don't care if the DGTTs are getting very low total I/O. Small DGTTs might incur zero Sync I/O (and close to zero Async) if BP pages stay in the BP. Large DGTTs might have I/Os. Might depend if DGTTs are being kept over many Commits or just one UOW.

"Paging" of BP memory is another topic, unless you really mean the BP normal operations. BPs need to be backed by enough real storage to prevent paging of the virtual memory.

Computing is full of "solutions, looking for a problem"!

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Andy Smith

RE: DB2 v12: DSNDB07 DGTT and Sort Work Bufferpools
(in response to DJ Jones)

DJ, if you were interested in analysing your overall performance of sort work files (spannable and non-spannable) rather than just from a BP perspective, then I recommend you try and find a copy of the presentation delivered by Adrian Collett at IDUG EMEA Brussels (2016) - "DB2 Workfiles Explained".  There are some very interesting measures explained in there.

Philip Sevetson

DB2 v12: DSNDB07 DGTT and Sort Work Bufferpools
(in response to Andy Smith)
That presentation is on the IDUG website!

https://www.idug.org/p/cm/ld/fid=144&tid=172&fileid=6807


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Andy Smith <[login to unmask email]>
Sent: Tuesday, February 11, 2020 8:18 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 v12: DSNDB07 DGTT and Sort Work Bufferpools


DJ, if you were interested in analysing your overall performance of sort work files (spannable and non-spannable) rather than just from a BP perspective, then I recommend you try and find a copy of the presentation delivered by Adrian Collett at IDUG EMEA Brussels (2016) - "DB2 Workfiles Explained". There are some very interesting measures explained in there.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Michael Hannan

RE: DB2 v12: DSNDB07 DGTT and Sort Work Bufferpools
(in response to Philip Sevetson)

Phil,

That link does not seem to include the actual presentation. Did any find where it is?

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Philip Sevetson

DB2 v12: DSNDB07 DGTT and Sort Work Bufferpools
(in response to Michael Hannan)
Michael,

I’ve sent an inquiry to the website managers about it. I thought it was because I wasn’t a premium member, or some such.

-phil


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Michael Hannan <[login to unmask email]>
Sent: Tuesday, February 11, 2020 10:02 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 v12: DSNDB07 DGTT and Sort Work Bufferpools


Phil,

That link does not seem to include the actual presentation. Did any find where it is?



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Philip Sevetson

DB2 v12: DSNDB07 DGTT and Sort Work Bufferpools
(in response to Philip Sevetson)
Michael,

Following up – I can’t see your email. Would you please ping me at my work address (below)?

-phil


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Sevetson, Phil <[login to unmask email]>
Sent: Thursday, February 13, 2020 8:46 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 v12: DSNDB07 DGTT and Sort Work Bufferpools

Michael,

I’ve sent an inquiry to the website managers about it. I thought it was because I wasn’t a premium member, or some such.

-phil


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
212-857-1688 w
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: Michael Hannan <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Tuesday, February 11, 2020 10:02 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: DB2 v12: DSNDB07 DGTT and Sort Work Bufferpools


Phil,

That link does not seem to include the actual presentation. Did any find where it is?



Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)