Analysis of 20 headache problems in DB2 Performance Tuning

This article mainly tells you about the 20 frequently-encountered problems in DB2 Performance Tuning. If you are curious about the 20 frequently-encountered problems in DB2 performance tuning, the following articles will unveil its secrets. The following is a detailed description of the article. I hope you can learn from it.

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.

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 the DB2 performance optimization 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: because a table has too much data, it means that the performance tuning of DB2 is reduced, 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.

13. I can determine whether the access path is good only by explaining

Actual: EXPLAIN does not display the order of the queried blocks to be executed, does not tell you the first or second-stage predicates, and does not tell you how long a block will be executed. Basically, EXPLAIN only exports some data to a table, and then carries out more explanations based on other information. There are some tools to help with this process (such as Visual Explain), but if all the facts are not considered, this method will only bring disadvantages.

14. Do not make the EDM pool too large to avoid Paging

Actually: the EDM pool usually improves performance through paging (here paging refers to extended storage, rather than disks) instead of becoming smaller and continues to rebuild the internal structure due to page replacement and other factors.

15. expansion will not be related to anything else

Actual: When did it start? In the future, if the world is full of SAN or ESS, it will be similar. The impact of expansion has become small because of the new disk cache controller, but there are still some additional checks and processing to manage them.

16. Relationship division is not used in DB2

Actually: The Relationship division has been used in many systems in the past and can be effectively implemented by database designers and program developers. In the current business intelligence (BI) and market systems, it can be used in each individual program several times.

17. bind all packages to two plans: one batch processing and one online

Actually: this is a bad statement when introducing the DB2 package. There are many reasons to say that this understanding is wrong.

18. Unauthorized reading is not good.

Actually: unauthorized reading is not a four-character word, but it is a very good performance tuning enhancement for DB2 and can be used in more places than often understood.

19. There will be no lock problems without timeout or deadlock

Reality: in fact, there is no problem that does not mean that there is no DB2 Performance Tuning problem to be concerned about. Frequent locking is not considered a problem, because the focus is mainly on the adjustment measurement of the response (counting the number of deadlocks or Timeouts), rather than the subsequent adjustment (Monitoring lock wait time ).

20. ESA data compression is always good.

Reality: When compression can be used in many places, it may cause problems. In each case, you must determine whether to use it before compression. This is not optional, but must be used or not at the high level.