Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Top 20 DB2 performance myths

The top 20 DB2 performance myths debunked.

This is an excerpt from Richard A. Yevich & Susan Lawson's "DB2 High Performance Design and Tuning." You can purchase the book here. More details about each of these myths can be found throughout the book.

1. Logical design should always map exactly to the physical.

Reality: Physical design should remain as close as possible to the logical structure, but changes in the physical design mandated by performance should never be ignored just because they didn't come from the logical.

2. Put everything in one buffer pool (BP0) and let DB2 manage it.

Reality: While this is stated in the DB2 manuals and other places, you want to do this only if your memory is limited (10,000 4K pages or less), you do not have the time to manage it, and you are also not at all concerned with performance. It is better stated this way: never put anything but the DB2 catalog and directory into BP0.

3. DSNDB07 is always 100% sequential.

Reality: Almost never is DSNDB07 100% sequential, as there is random activity to the pages in the work files. Random activity can be as much as 45%, but normally it is in the range of 3% to 10%.

4. VARCHARs should always be placed at the end of the row.

Reality: It is the word always that is the real problem. If a table is almost always read and very seldom updated, then yes, this would reduce CPU, but in almost all other cases it is the worst thing to do, even if the table is compressed. It should be at the end only if it is heavily updated, but this is often not the case.

5. Applications should be coded to follow the logical process.

Reality: Pseudo-code or a logical process diagram never takes into account coding methods for performance. This is most dramatic in OLTP transaction coding.

6. Most processes cannot be performed in SQL.

Reality: Actually, the reverse is generally true. SQL is a very rich language that can perform most processes. The real difficulty is that SQL is often approached as an I/O handler instead of a set processor.

7. Code and reference tables should be used with DB2 declared referential integrity (RI).

Reality: RI should not be used as a shortcut for edit validations, which generally belong elsewhere, but should be used for true parent-child relationships.

8. Tables should have only one or two indexes at most.

Reality: Tables should have as many indexes as required to provide the performance necessary.

9. Nonpartitioning indexes (NPIs) should not be used, especially on large tables.

Reality: There are "numerous problems involved," which can generally be overcome, but NPIs are quite necessary for proper access and performance.

10. Large tables should be split.

Reality: This is a legacy fear that too much data in a table means performance degradation. With several tables of over 6 billion rows in production, this myth has been laid to rest.

11. DB2 defaults are okay.

Reality: Defaults are generally never okay, since they can and do change over versions and releases. Consider, for example, the bind parameter CURRENTDATA.

12. Never use negatives in SQL WHERE predicates.

Reality: Another one of those generic rules that was never fully stated correctly. If and only if the only predicate is a negative, then the SQL access path could be using an unnecessary table space scan. But in almost all other cases, additional filtering would be done inside the DB2 engine, which is good.

13. I can rely on just EXPLAIN to determine whether the access path is good.

Reality: EXPLAIN does not show the order in which the query blocks are executed, tells you nothing about stage 1 or stage 2 predicates, and never tells how often a block is executed. Basically, EXPLAIN just dumps some data in a table that takes a lot of additional explaining through competent analysis in conjunction with other information. There are some tools to help with this process (such as Visual Explain), but there also are some that can do more harm than good if all the facts are not considered.

14. Do not make the EDM pool so large that it pages.

Reality: The EDM pool generally gives much better performance through paging (referring to paging to expanded storage, not disk) rather than being too small and having to constantly rebuild internal structures due to page stealing and other factors.

15. Extents do not matter anymore.

Reality: Since when? At some point in the future, when the world is fully SAN and ESS, then maybe. The impact of extents has been lessened quite a bit with the new disk cache controllers, but there is still some additional checking and processing required to manage them.

16. Relational division has no use in DB2.

Reality: Relational division has been used in many systems in the past and can be effectively implemented by both database designers and application developers. In the current world of business intelligence (BI) and marketing systems, it probably is used in every single application, several times.

17. Bind all packages into two plans: one batch and one online.

Reality: This was an unfortunate statement made at the introduction of packages in DB2. There are many reasons that this generic approach is wrong, besides just being generic.

18. Uncommitted read is a dirty word.

Reality: Uncommitted read is not a four-letter word but a very nice performance enhancement that can be used in far more places than often realized.

19. There are no locking problems since there are no timeouts and deadlocks.

Reality: The fact that a particular problem does not occur does not mean that there is not a performance issue to be investigated. Often locking is not perceived as a problem due to focusing on reactive tuning measures (counting the number of deadlocks or timeouts), not on proactive tuning (monitoring lock wait time).

20. ESA data compression is always good.

Reality: While compression can provide benefits in many areas, there are a few situations where it can hurt. Each scenario must be evaluated to determine if compression is appropriate. This is not an option that should be mandated or rejected at a high level.

21. There are only 20 myths about DB2 performance.

Reality: There are hundreds.

Dig Deeper on DB2 UDB (universal databases)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.