ChannelDB2

New Release Available! Now with smaller download size!!

Download DB2 Express-C 9.7.2 (FREE Community Edition) - Select Platform:
Windows 32-bit | Linux 32-bit | Windows 64-bit | Linux 64-bit | Linux on POWER | Solaris x64 | DB2 9.5.2: Mac OS X

Videos

Blog Posts

Radim Kolar

Fast inserts with JDBC batch

More than year ago i presented simple document how to insert data into DB2 quickly using PHP and multi row insert statement. Today i will show you something for JDBC coders, similar but more flexible.


In JDBC you are free to use multirow insert with parameter markers too, but there is disadvantage because you don't know how much rows will be inserted. Most JDBC coders use different sized prepared stat

… Continue

Posted by Radim Kolar on August 22, 2010 at 9:43am

Dan Galvin

Call for Presentations for IDUG® 2011 - North America in Anaheim ends September 1st 2010

The planning committee for IDUG® 2011 - North America is currently accepting abstracts for presentations. If you have an interesting topic to share with the conference community, this is an excellent opportunity to to relate your experiences and to give others your insi

… Continue

Posted by Dan Galvin on August 23, 2010 at 2:50pm

david t

Data Replication for the IBM Smart Analytics System 7700

Someday after you get through reading the outstanding highlights in this week's announcement of the 7700, you may want to know what your replication options are. The answer is pretty much the same as it is for the 7600. I'll use this post to give you a quic
… Continue

Posted by david t on August 20, 2010 at 12:07pm

Rav Ahuja

Free Database now Slimmer!

A slimmer / lighter, option of the free database, DB2 Express-C was recently made available. This is good news for many who just want to use the core database features and not have to wait to download extra features they are not going to use. Those with not so good bandwidth are going to app… Continue

Posted by Rav Ahuja on August 6, 2010 at 2:19pm — 1 Comment

Blogs from PlanetDB2.com

Product management resources for newbies

Every few weeks someone approaches me because they are interested in learning more about software product management. Here are 3 resources for product management newbies: 1. Pragmatic Marketing – As far as I can tell, Pragmatic Marketing’s framework the industry standard for high-tech product management.  They claim to have trained over 60,000 product marketers and [...]

sql for sampling data from tables

Some time ago, I had the requirement to sample data from a table to do validation work.  Basically, we wanted to validate syncronized data with a base table, but could not afford to compare every row.  We had to come up with a way to ‘sample’ the data.  Each table we had to sample had [...]

tiếng Việt - Getting Started with DB2 Express-C 9.7


The Vietnamese translation of the Getting Started with DB2…

Peter Vanroose's presentations about DB2 for z/OS

Hi DB2 user,

Here are several Peter Vanroose's presentations about DB2 for z/OS available on Abis website:

- DB2 9 New Datatypes and SQL Functions: Blessing or Curse?
by Peter Vanroose

- Experiences with stored procedures, triggers and XML on DB2 version 8 for z/OS<br /&hellip;

Thoughts on DB2 for z/OS Buffer Pool Sizing

A couple of years ago, on my Catterall Consulting blog, I posted an entry in which I urged mainframe DB2 people to take advantage of 64-bit addressing, a capability that debuted with DB2 for z/OS Version 8 and which allowed, among other things, the allocation of very large buffer pools. Now, I'm singing the second verse of that same song (actually, about the twentieth verse -- I have to keep repeating it): IF YOU HAVE BIG MEMORY, YOU SHOULD HAVE BIG DB2 BUFFER POOLS.

Again and again I see it: a mainframe server (or logical partition thereof) that has a dozen or more gigabytes of central storage, and a production DB2 subsystem -- the only one on that z/OS instance -- that has a a buffer pool configuration with an aggregate size of a a gigabyte or less. Sometimes, pages of large and very heavily accessed tables and/or indexes are cached in a pool that has 80,000 buffers, or 40,000, or 15,000, or maybe just 10,000 buffers. The result? Way high rates of I/O activity, as in thousands of disk reads per second. That's a big drag on application performance, negatively impacting both throughput and CPU efficiency. When you have the server memory available to eliminate this undesirable situation, USE IT.

So, first things first: get a handle on your buffer pool I/O situation. You can use a DB2 monitor to do this, but my preference is to use the output of the command -DISPLAY BUFFERPOOL(ACTIVE) DETAIL. More specifically, I like to issue this command once, and then again an hour later. That way I get, in the output of the second issuance of the command, one hour's worth of buffer pool activity data. For each pool referenced in the output of the second command, I sum the numbers in five fields:
  • SYNC READ I/O (R)
  • SYNC READ I/O (S)
  • PREFETCH I/O (under SEQUENTIAL PREFETCH)
  • PREFETCH I/O (under LIST PREFETCH)
  • PREFETCH I/O (under DYNAMIC PREFETCH)
I take that total figure and divide it by the number of seconds between the first and second -DISPLAY BUFFERPOOL(ACTIVE) DETAIL commands, and voila -- I have the rate of disk read I/Os per second for each active pool. If that number is less than 100 for a buffer pool, I'm not likely to have a performance concern. If it's between 100 and 1000, I'll probably want to see about increasing the size of the pool. If it's north of 1000, I will definitely want to make that pool larger -- maybe much larger (the largest number I've seen for disk read I/Os associated with a DB2 buffer pool is a little over 8000 per second -- I'm sure there are larger numbers out there).

If you have a really high rate of disk read I/O activity for a pool and you want to make it larger, can you? That depends on whether or not the z/OS system in question has enough memory to accommodate a buffer pool size increase without negatively impacting other work on the server. How do you know that? A good way is to check the demand paging rate on the system. This figure indicates the number of times per second that a page, previously moved from central storage to auxiliary storage, is brought back into central storage to satisfy a program request. It's available by way of a z/OS monitor. The lower the demand paging rate, the less pressure there is on the system's central storage resource. What you want is for the demand paging rate to be less than 10 per second. You also want it to be more than zero, because a super-low demand paging rate means that the mainframe memory your organization has paid for is probably not being leveraged as it should be for system performance. If the demand paging rate is less than one per second, memory on your system is likely being underutilized. Enlarging one or more of your DB2 buffer pools is often a very good way to put underutilized server memory to productive use. In my experience, if there is one production DB2 subsystem on a z/OS instance, and if the size of that DB2 subsystem's buffer pool configuration is less than 10% of the amount of memory available to the z/OS instance, it's almost certain that the demand paging rate is very low. If you make a DB2 buffer pool (or pools) larger, check the demand paging rate again after the fact and make sure that it's still less than 10 per second (and note that an occasional incident such as a dump can cause the demand paging rate to briefly spike -- that's not a big deal).

Something else: if a buffer pool's I/O rate is really high (as in a few thousand per second), and the pool is pretty small (e.g., 20,000 buffers or less for a 4K pool), and the system's demand paging rate is really low, don't just dink and dunk your way to a larger buffer pool configuration. In other words, don't add 1000 buffers to a 15,000-buffer pool. Go for way bigger. Think in terms of doubling the pool's current size, or tripling it, or even quadrupling it. Later, after it's bigger (like, 80,000 buffers or more), you can think about growing it in smaller chunks, percentage-wise (e.g., maybe make it 50% larger). Whenever you take any action to enlarge a buffer pool, follow that with the -DISPLAY BUFFERPOOL commands that I mentioned previously, to gauge the effect of the size increase on the pool's disk read I/O rate. When you do that, in addition to checking on read I/Os per second, look to see if the number of of synchronous reads associated with sequential access (SYNC READ I/O(S)) went down. When a buffer pool is really undersized relative to the volume of requests for pages in objects assigned to the pool, it may be that when a set of 32 pages is brought into the pool via a prefetch read I/O, some of those pages are flushed from the pool before the requesting application process can access them. That drives the number of synchronous reads related to sequential access higher (because those flushed-out pages, when requested, will be read into memory individually). With a larger pool, page residency time increases, and it's less likely that prefetched pages will get flushed before they are accessed for the requesting application process.

Another thing: once an individual buffer pool (as opposed to the whole buffer pool configuration) gets to be pretty big (say, a gigabyte in size), before making it larger still consider the possibility of creating a new pool (maybe 25% or half the size of the big one) and moving some of the highest-activity objects from the really big pool to the new one. This is NOT a technical requirement, as a single 4K buffer pool can grow to a terabyte in size; rather, it's an opportunity to split some objects out in a way that will provide you with more granular buffer pool statistics and a chance to more finely tune the overall buffer pool configuration. Just a thought.

Finally, if your DB2 environment operates in data sharing mode on a parallel sysplex mainframe cluster, keep in mind that an increase in the size of a buffer pool (and you usually want a given pool to be the same size on all members) may make an enlargement of the associated group buffer pool highly advisable -- this mainly to ensure that the group buffer pool will have enough directory entries to prevent directory entry reclaims, which get in the way of top performance. A long time ago, when the earliest users of data sharing asked for a formula to help with group buffer pool sizing, I came up with a pretty simple one: add up the size (in MB) of the local pools, and divide that sum by three (this assumes the default ratio of five directory entries for every data entry in a group buffer pool). Fifteen years later, that simple formula still works really well. So if, in a four-way data sharing group, you want to grow BP5 to 120,000 buffers (480 MB) on each member, a good size for GBP5 would be:

(480 MB X 4 members) / 3 = 1920 MB / 3 = 640 MB

If GBP5 is currently smaller than that, take it up to 640 MB (or more) before taking BP5 to 120,000 buffers.

I hope that this information will help you to assess your buffer pool configuration from a performance perspective, and I hope that you'll grow your DB2 buffer pools to improve throughput and CPU efficiency (assuming that you have enough memory for this on your system -- and I'm sure that a lot of you do).
… Continue

FEATURED BLOG POSTs

* Which database image to use on cloud? Various options for DB2
* Top 3 ways to return TOP 10 rows by an SQL query
* DB2 on EC2 Quickstart and Production ready on the Cloud
* Free DB2 Express-C Licensing changes in V9.7
* For Databases Size Does Matter: Why DB2 pureScale matters
* Benchmark comparing free databases: MS SQL Express vs. MySQL vs. DB2 Express-C
* Using Cloud for Disaster Recovery: Cheap [HA+] DR Recovery with DB2 and Amazon's EC2: Part #1
* From Backups to Scalability in the Cloud: Learn about complimenting your database backup and scalability strategies with Cloud Computing
 
 
 

Featured Downloads

New: Download DB2 9.7.2
32-bit:Windows | Linux | MacOS Other Platforms

Download FREE Database: DB2 Express-C
FREE Book: Getting started with DB2

DB2 on the Cloud

Webcast | Tutorial | Start | Video

ChannelDB2 is a social network for the DB2 community. It features content such as DB2 related videos, podcasts, blogs, photos, resources, etc. for Linux, UNIX, Windows, System z, and System i.

Latest Activity

Leons Petrazickis left a comment for Roopa M S
22 hours ago
Leons Petrazickis left a comment for Victor Luo
on Friday
Paul Rockwood left a comment for Shahid Rashid
on Friday
Leons Petrazickis left a comment for Shahid Rashid
on Friday
Leons Petrazickis left a comment for Gary Rothermel
on Thursday
Natasha Tolub replied to Susan Visser's discussion 'Celebrate an Author' in the group DB2 in Books
There is one more talented author here at the Toronto Lab: Antonio Cangiano
on Thursday
Susan Visser added 4 discussions to the group DB2 in Books
1 more…
on Thursday
Leons Petrazickis left a comment for david bachrach
on Thursday
Leons Petrazickis left a comment for Nagarjunaraju
on Thursday
Leons Petrazickis left a comment for Jorge Luis Cabral
on Thursday
Leons Petrazickis left a comment for Luke Harmer
on Thursday
Leons Petrazickis left a comment for akshath
on Thursday
null

© 2010   Created by channeldb2.

Badges  |  Report an Issue  |  Terms of Service