Shulou(Shulou.com)06/01 Report--

Today, accompanied the newcomer DB2 DBA online, he asked me a few questions, I summed up, although very simple, but it seems that I also encountered many years ago, confused.

I added columns to several large tables with tens of millions of rows in the database and did the offline reorg operation. A few minutes later, I didn't finish it. I began to ask me how I think it has run to where now and how long it will take to finish it.

For the first question, it's easier to use db2pd to view the output every 5 seconds.

Db2pd-d DBNAME-reorg-rep 5

It has to be mentioned here that the offline reorg of DB2 is divided into several steps. From the output above, you can see the two steps of Build,IdxRecreat.

In fact, there may be more steps, depending on whether the commands and tables used by reorg have cluster index. Here is a detailed explanation.

There are four phases in a classic or offline table reorganization:

(1) SORT: If an index is specified with the REORG TABLE command, or if a clustering index is defined on the table, the rows of the table are first sorted according to that index. If the INDEXSCAN option is specified, an index scan is used to sort the table, otherwise, a table scan sort is used. This phase only applies to a clustering REORG. Space reclaiming reorganizations begin at the build phase.

(2) BUILD: In this phase, a reorganized copy of the entire table is build, either in the table space that the table being reorganized resides, or in a temporary table space specified with the REORG command.

(3) REPLACE: In this phase, the original table object is replaced by either copying back from the temporary table space, or by pointing to the newly built object within the table space of the table being reorganized.

(4) RECREATE ALL INDEXES: All indexes defined on the table are recreated

Estimate the running time, it is best to refer to the time of the previous REORG

Select START_TIME,END_TIME from sysibmadm.db_history where OPERATION='G' and OPERATIONTYPE='F' and TABNAME='XXXXXXXXX'

Every time a change to the database is discussed in the group, it is necessary to estimate the time required for the change. For ordinary SQL and DDL, the time consumption is very small. If several large tables are involved in reorg & runstats operations, it is often estimated that there is a big difference between the actual and estimated results without doing a survey beforehand. It is possible that things will be finished by challenge. This is thankless. The key is that I didn't do it carefully.

After Reorg, do runstats and continue to observe db2pd-d DBNAME-runstats with db2pd

By the way, I used several kinds of databases. In terms of monitoring, db2pd is my favorite. It is easy to use. There are a lot of things that can be monitored now. I do not like to use SQL to do monitoring. I really do not have time to write SQL at the customer site, especially when the problem is in a hurry.

The new version of DB2 has added more content that can be monitored with db2pd. I hope db2pd can more and more consider the actual needs and add more monitoring parameters.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.