Analysis of fault cases caused by DB2 database optimization

As the banking industry every year to carry out year-end accounts, in order to ensure the smooth completion of the final accounts on the night, in the year-end accounts will have a routine database optimization operations. At present, the rapid development of business, data accumulation faster. Previous years use of routine database optimization is not entirely fit for the new situation but this year's optimization also took the previous years of operation steps: The day to complete the optimization of the need to manually kill the optimization process and force the database process. Finally executes the DB2 "SELECT distinct status from syscat.tables" Query database system table check that all tables are in a normal state, as expected all table states are normal, work

Went away. Who knows the night batch due to the lack of access to the table eventually led to batch failure, resulting in serious production accidents. For this I have been brooding all day ~

So what is the reason for the bulk failure? So why have you been following the same program in the past few years and have no problem with this year's optimization? With this Problem, I rushed to the company early in Children's Day. This problem was reproduced in the test environment. Studies have shown that reorg is actually staged, and that if you force the force to fall during the index recreate phase, it can cause the rebuild index to fail. The reason for this is that because of the small amount of data, once the index rebuild fails, once a business accesses the table, he first adds a Z lock to the table and then rebuilds the index, allowing normal business access after the rebuild is complete. So this is not a problem when the amount of data is small, the only symptom is that the application is slow on the first visit. But in the case of massive data, there is obviously a major problem, as we optimized the table with about 3 billion data, and it is too empirical to take force when we rebuild the index. Because when we visit the table where the index failed to rebuild, the operation that he did to rebuild the index again may take several hours, obviously exceeding the time limit of the batch Maintenance window, resulting in an accident.

So in order to let the vast number of Bo friends no longer make similar to me a low-level error so that this blog, but also exposed the author's empirical thinking seriously, not fine. So let's take a thorough look at what DB2 do when doing reorg operations!

According to the IBM official website, we are issuing reorg command DB2 will experience a sort, build, Replace idxrecreate 4 stages.

1, the build phase, using the free space in the table space (or the specified table space) temporarily generate a table for the reorganization of the table, even if the interruption does not affect the existing table or index;

2, replace phase, that is, replacement of the original table stage can not be considered manual interruption, even if the interrupt command (CTRL + C) At this stage is also equivalent to the completion of this step, the index did not hit before the break, it is clear to tell the user that the index was not created.

3, Idxrecreate stage, can be manual, while explicitly told the user index has not been created.

We can monitor the reorg progress through the following means:

DB2 get snapshot to tables on Cbusdb|grep-ip tabname

Db2pd-d cbusdb-utilities

OVER ~ ~

Another small problem that you have recently encountered: A method for improving data insert performance

1. Remove Index.

2. Remove Constraints.

3. Include multiple lines in the INSERT statement.

4. Adopt Append Mode

5. Block the log operation of the table.

6. Use parallel write operation.

7. Adopt a strict isolation level