Analysis of the DB2 Performance Tuning aspects of 12 Problems

Source: Internet
Author: User

The following articles mainly describe the 12 questions about DB2 Performance Tuning. If you encounter similar problems in actual operations, however, you do not know how to solve the problem correctly, so the following articles must be good teachers and friends for you.

1. Logical design should always be fully mapped to Physical Design

Actual: the physical design in the DB2 database design should be as close as possible to the logical structure, but physical design changes made for performance cannot be ignored because they do not come from the logical design.

2. Put everything in a buffer pool (BP0) for DB2 Management

Actually: As described in the DB2 manual and elsewhere, you have to (10000 4 k pages or less) with very limited memory ), you don't have time to manage it, and you didn't consider performance. It is recommended that you do not place anything except the DB2 catalog and Directory into BP0.

3. DSNDB07 is 100% ordered

Actually: DSNDB07 is never in the 100% order, because there is a random activity on the page in the working file. The activity may be as high as 45%, but usually ranges from 3% to 10%.

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

Actually: This is always a problem. If the table is always read and has very few updates, this will reduce the CPU load, but in other cases, this is the worst, even if the table is compressed. It should be placed at the end only when it is updated frequently, but not usually.

5. The program should be encoded in a way that follows the logical process.

Actual: pseudocode or a logical process diagram does not need to consider the encoding method related to DB2 performance tuning. This is very dramatic in the OLTP transaction code.

6. Most processes are not performed in SQL.

Reality: in fact, the opposite is often true. SQL is a rich language that can process most processes. In fact, the biggest difficulty is that SQL is often used as an I/O processor rather than a collection processor.

7. The code and reference table should be used together with the referential integrity (RI) declared by DB2.

Actually, RI should not be used as a shortcut for editing validity. This is usually something else, but should be used in real parent-child relationship.

8. A table can contain at most one or two indexes.

Actual: The table should have multiple indexes according to performance requirements.

9. Non-partitioned indexes (NPI) should not be used, especially in large tables.

Actually: this is related to countless problems, which can be overcome in general, but NPI is necessary for proper access and performance.

10. Large tables should be split

Actually: A table with too much data means performance degradation, which is a legacy concern. This understanding has been eliminated when more than 6 billion rows of data exist in some tables.

11. DB2 is good by default.

Actually: the default version is generally not the best. They are changed based on different versions. For example, bind the CURRENTDATA parameter.

12. Do not use Negation in SQL WHERE predicates.

Actually: Another rule is not clearly explained. Only when the predicate is a negative, the SQL access path may use an unnecessary tablespace scan. However, in most other cases, redundant filtering should be completed in the DB2 engine, which is better. The above content is an introduction to the 20 difficult questions about the comprehensive analysis of DB2 Performance Tuning. I hope you will gain some benefits.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.