Basic DB2 Buffering and Memory Guidelines

One of the most important areas for tuning DB2 subsystem performance is memory usage. DB2 for z/OS uses memory for buffer pools, the EDM pool, RID pool and sort pools to cache data and structures in memory. The better memory is allocated to these structures, the better DB2 will perform.

When allocating DB2 buffer pools, keep these rules of thumb in mind:

Don't allocate everything to a single buffer pool (e.g., BP0); use a multiple buffer pool strategy.

Explicitly specify a buffer pool for every table space and index.

Isolate the DB2 Catalog in BP0; put user and application DB2 objects into other buffer pools.

Consider separating indexes from table spaces with each in their own dedicated buffer pools.

Consider isolating heavily hit data into its own buffer pool to better control performance.

Consider isolating sorts into a single buffer pool and tuning for mostly sequential access (e.g. BP7).

Consider separating DB2 objects into separate buffer pools that have been configured for sequential verses random access.

Forget about trying to follow a cookie-cutter approach to buffer pool management. Every shop must create and optimize a buffer pool strategy for its own data and application mix. DB2 offers the following buffer pool tuning "knobs" that can be used to configure virutal buffer pools to the type of processing they support:

DWQT –this value is the deferred write threshold; it is expressed as a percentage of the virtual buffer pool that might be occupied by unavailable pages. When this threshold is reached DB2 will start to schedule write I/Os to externalize data. The default is 50%, which is likely to be too high for most shops.

VDWQT – this value is the vertical deferred write threshold; it is basically the same as DWQT, but for individual data sets. The default is 10%, which once again is quite likely to be too high for many shops.

VPSEQT – this value is the sequential steal threshold; it is a expressed as a percentage of the virtual buffer pool that can be occupied by sequentially accessed pages. Tune buffer pools for sequential access such as scans and sorting by modifying VPSEQT to a larger value. The default is 80%.

VPPSEQT – this value is the sequential steal threshold for parallel operations; the default value is 50%.

VPXPSEQT – this value is assisting parallel sequential threshold; it is basically the VPPSEQT for opertaions from another DB2 subsystem in the data sharing group.

These parameters can be changed using the ALTER BUFFERPOOL command. Additionally, hiperpools can be created to back up DB2 virtual buffer pools with additional memory. DB2 provides several tuning knobs for hiperpools, too, including HPSIZE to adjust the size of hiperpools and HPSEQT to adjust the hiperpool sequential steal threshold.

With the advent of DB2 V8, we will have more memory at our disposal for DB2's use. This next version of DB2 will be able to surmount the limitation of 2GB real storage that was imposed due to S/390's 31-bit addressing. Theoretically, with 64-bit addressing DB2 could have up to 16 exabytes of virtual storage addressability to be used by a single DB2 address space. Now there is some room for growth!

In addition to buffer pools, DB2 uses memory for the EDM pool. The EDM pool is used for caching internal structures used by DB2 programs. This includes DBDs, SKCTs, CTs, SKPTs, and PTs. It also includes the authorization cache for plans and packages, as well as the cache for dynamic SQL mini-plans. As a general rule of thumb, shoot for an 80 percent hit rate with the EDM pool; this means that only one out every five times should a structure need to be loaded from disk into the EDM pool.

Finally, remember that buffer and EDM pool tuning are in-depth subjects that cannot be adequately covered in a high-level tip such as this. So, study those IBM DB2 manuals - and learn by doing. Additionally, there is much more to proper DB2 system performance tuning than memory tuning. Other system elements requiring attention include allied agent setup (CICS, TSO, etc.), network configuration, locking, logging, and Parallel Sysplex configuration and management for DB2 data-sharing shops.

Stages 3 and 4

All good DB2 developers and DBAs know about Stage 1 and Stage 2 predicates, right? But have you ever heard of Stage 3 and Stage 4 predicates? Well, you’re about to!

First of all, let’s do a quick review to catch those readers who don’t know what Stage 1 and 2 are. You may have heard about sargable and nonsargable, and if so, Stage 1 is sargable and Stage 2 is nonsargable. If not, don’t worry about those terms, they are obsolete.

A predicate that can be evaluated in the Data Manager (DM) component of DB2, that is at the earliest stage of query execution, is called a Stage 1 predcicate. Stage 2 predicates need to be passed up to the Relational Data System (RDS) to process. So Stage 1 predicates are more efficient than Stage 2 predicates because the Data Manager component of DB2 is at a level closer to the data than the Relational Data System. Stage 1 predicates, being evaluated earlier in the data retrieval process, avoid the overhead of passing data from component to component of DB2. For this reason, developers are encourage to use Stage 1 predicates rather than Stage 2 predicates to optimize performance.

What makes a predicate Stage 2 instead of Stage 1? Well, it is all in the type of predicate you code and how you write your SQL. There is a list of Stage 1 and Stage 2 predicates in Chapter 12 of the DB2 Performance and Tuning manual. (The same chart also tells you whether a predicates is indexable or not.) Whenever you move from one release of DB2 to another one of the first things you should do is consult this manual to see if any predicates have changed from Stage 2 to Stage 1… and you should make sure all of your developers have a copy of that chart taped to their cubicle wall!

OK, so what is all of this about Stage 3 and Stage 4, then? Well, it is a way of thinking about some bad SQL practices. Instead of coding a SQL predicate some programmers choose to bring all (or most) of the data into their program and then filter it using IF-THEN or CASE statements. You can think of these as Stage 3 predicates because it is one more place that the data must be passed to before it can be determined whether the data is needed.

Stage 4? That is when you use a black box (see the link for an explanation if you don't know what a black box is)... Instead of filtering the data in the DM or the RDS or even in your program, you have to work with another program altogether – the black box – to return the right data.

So just remember 1… 2… 3… 4… and that is the order of efficiency for those types of predicates. 1 is better than 2 is better than 3 is better than 4…

Today's Modern Mainframe

IBM mainframes process two thirds of the world’s business information every day, and have
been doing so for decades, because of the many strengths provided by the mainframe, such
as high availability, good redundancy, high performance I/O capabilities, and much more.

But have you ever been challenged by an anti-mainframer by a statement like "Oh, aren't mainframes dead?" - or - "Why would anyone still use a mainframe?" Have you wanted a ready response that is easily digestible and not overly techie? Well, IBM has a new Redguide book titled The IBM Mainframe Today: System z Strengths and Values that might be of help.

This publication describes the business drivers, their impact on IT, and how the System z platform can be a major player for the business solution by providing flexibility, responsiveness to customers and market needs, as well as cost effectiveness. It also discusses the mainframe hardware, software, and environment, with an emphasis on the features and capabilities that make the System z mainframe a good choice for businesses.

Also, you may have noticed the new term: Redguide. Similar to an IBM Redbook, an IBM Redguide is not quite as technical as a Redbook, but more in-depth than a brochure or other pieces of "sales" collateral.

So, if you are a mainframer like me, you'll want to download and read The IBM Mainframe Today: System z Strengths and Value. After all, the price is right (free). And you'll want to keep an eye out for additional Redguides from IBM. Susan Visser recently blogged about the first 15 Redguides here.

Cost vs. Advantage of Moving From IMS DB to DB2

As my regular readers know, every now and then I like to share Q+A exchanges I've had with folks. Today, the question I was asked is as follows:

My customer is wondering about the possible advantages of converting his IMS DB/DC system to IMS DC/DB2. The application currently performs well with an internal response time of less than .5 seconds on average.

Even with an arrival rate of 425 full-function transactions per second, the queue count rarely goes above 10. This system typically peaks at 12.5 million transactions per twelve-hour day against HDAM and HIDAM databases totaling close to 1 terabyte. The application itself is currently a bit over seven million lines of code.

Can you comment on the relative cost vs. advantage of moving an existing application from IMS DB to DB2 along with relative CPU capacity requirements?

And here is my short response:

Well, the main advantages of converting from IMS/DB to DB2 would be to gain better support for ad hoc queries, standard SQL (instead of non-standard DL/1) for writing queries and a deeper pool of talent to support the DB2 environment (there are more DB2 folks out there than IMS folks now-a-days).

The benefit of sticking with IMS is the good performance you currently enjoy as well as no need to convert the database structures or the 7 million lines of application code. Converting database structures is not horribly difficult, but there are some gotchas that can arise. The bigger problem is converting all of those DL/1 calls to appropriate SQL. This will not be a simple 1 to 1 conversion and it will very likely be quite time-consuming.

I guess it boils down to this: How happy are you with the current application, are you able to support it properly and how many other IMS/DB databases do you support? If this is the last IMS/DB database and you are looking to rid yourself of the IMS license, then it might make sense to convert. But you should do a project plan and cost/benefit analysis before making your final decision (conversion can be very costly). If you have a lot of other IMS/DB databases, then it probably doesn't make a lot of sense to convert to DB2 unless you cannot support the needs of your end users (management, ad hoc support, etc.) using IMS.

In terms of CPU requirements, DB2 will consume more CPU than IMS. DB2 optimizes queries internally whereas IMS programmers construct access paths to data. This additional requirement will cause DB2 to consume more CPU. But, of course, that additional CPU brings with it the enormous benefit of database optimization and better ad hoc query support.

You might also want to take a look at a product like DL/2. I have never used it so I cannot recommend for or against its functionality, but it looks like it might save you some work.

A Short Introduction to Lock Avoidance

Lock avoidance is a mechanism employed by DB2 for z/OS to access data without locking while still maintaining data integrity. It prohibits access to uncommitted data and serializes access to pages. Lock avoidance improves performance by reducing the overall volume of lock requests. After all, let’s face it, the most efficient lock is the one never taken.

Of course, even if it is not taking a lock, DB2 must still maintain the integrity of its data. Instead of taking a lock, DB2 uses a latch. To take advantage of Lock Avoidance, the SQL statement must be Read Only and the plan must be bound with Isolation Level Cursor Stability (CS) and CURRENTDATA(NO).

In general, DB2 avoids locking data pages if it can determine that the data to be accessed is committed and that no semantics are violated by not acquiring the lock. DB2 avoids locks by examining the log to verify the committed state of the data.

When determining if lock avoidance techniques will be practical, DB2 first scans the page to be accessed to determine whether any rows qualify. If none qualify, a lock is not required.

For each data page to be accessed, the RBA of the last page update (stored in the data page header) is compared with the log RBA for the oldest active unit of recovery. This RBA is called the Commit Log Sequence Number, or CLSN. If the CLSN is greater than the last page update RBA, the data on the page has been committed and the page lock can be avoided.

Additionally, a bit is stored in the record header for each row on the page. The bit is called the Possibly UNCommitted, or PUNC, bit. The PUNC bit indicates whether update activity has been performed on the row. For each qualifying row on the page, the PUNC bit is checked to see whether it is off. This indicates that the row has not been updated since the last time the bit was turned off. Therefore, locking can be avoided. (Note that there is no external method for DBAs to use to determine whether a row’s PUNC bit is on or off.)

If neither CLSN nor PUNC bit testing indicates that a lock can be avoided, DB2 acquires the requisite lock.

In addition to enhancing performance, lock avoidance improves data availability. Data that without lock avoidance would have been considered locked, and therefore unavailable, can now be accessible.

Lock avoidance is used only for data pages. Further, DB2 Catalog and DB2 Directory access does not use lock avoidance techniques. You can avoid locks under the following circumstances:

• For any pages accessed by read-only or ambiguous queries bound with ISOLATION(CS) and CURRENTDATA NO
  
• For any unqualified rows accessed by queries bound with ISOLATION(CS) or ISOLATION(RS)
  
• When DB2 system-managed referential integrity checks for dependent rows caused by either the primary key being modified, or the parent row being deleted and the DELETE RESTRICT rule is in effect
  
• For both COPY and RUNSTATS when SHRLEVEL(CHANGE) is specified
  

To determine the impact of lock avoidance on your system, you can review DB2 trace records. IFCIDs 218 and 223 provide CLSN information, and IFCIDs 226 and 227 provide 'wait for page latch' information.

Avoiding locks can improve the performance of your queries and programs that satisfy the preceding requirements. To encourage DB2 to avoid locks, BIND your plans and packages specifying ISOLATION(CS) and CURRENTDATA NO. Furthermore, avoid ambiguous cursors by specifying FOR READ ONLY for all cursors that are not used for updating.