What are the misunderstandings about the performance of the DB2 database?

The following articles mainly describe the reasons for the physical design and logical structure in the DB2 database design. The main reasons include that the DB2 default is good, and do not use Negation in the SQL WHERE predicate, and determine whether the access path is a good content description based on EXPLAIN.

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.

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.

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.

Do not make the EDM pool too large to avoid Paging

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

Expansion is not 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.

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.

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.

Unauthorized reading is not good.

Actually: unauthorized reading is not a four-word but a very good performance enhancement for the DB2 database. It can be used more often than often understood.

No lock issues in the absence of timeout and deadlock

Reality: in fact, there is no problem that does not mean there is no need to pay attention to performance issues. 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 ).

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.

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.

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.

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.

Do not make the EDM pool too large to avoid Paging

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

Expansion is not 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.

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.

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.

Unauthorized reading is not good.

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

No lock issues in the absence of timeout and deadlock

Reality: in fact, there is no problem that does not mean that there is no DB2 database performance issue 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 ).

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.