Analysis of the key role of clustered target tables in DB2

Guidance:InDB2 databaseThere is a special table calledAggregation target table. This aggregation target table is similar to the view and is read-only and is used for statistics on the basic table. However, it is essentially different, that is, over time, the system will append rows to the target clustering table. That is to say, the data in the table actually exists in the Database, rather than a virtual table as you see it. It can be seen that the clustered target table can play a similar role in the view, but it may ensure data stability.

1. Understand the CD table in the DB2 database.

Before analyzing the role of a specific target table on the DB2 database, I think we should first understand the CD table. This is also a unique identifier in DB2 databases. The clustered target table is created based on the CD table. In DB2 databases, data replication functions similar to other databases can be implemented. That is, data can be copied and synchronized between the source database and the reservation integrator to generate database copies. Sometimes in the reservation integrator, you may need to track the changes to the data source table. In this case, you need to use this CD table. DB2 official documentation defines CD tables as follows: each user table registered for the capture and replication of changes on the DB2 database server has a corresponding change data table. Simply put, this CD table is also called a change data table. It is used to help the database administrator track the changed data (including update, delete, insert, and so on) in the source table ). An application reads the rows submitted in the change data table and copies them to any target table mapped to the registered table. For administrators who are new to the DB2 database, they only need to know that the CD table (data update table) is used to analyze and track user changes in the source table.

Ii. Type and Difference Analysis of the clustered target table.

In the DB2 database, there are two main types of clustering tables: Basic clustering tables and change clustering tables. Both tables are clustered tables, but they are quite different. As a database administrator, you must understand the differences between them and select an appropriate clustering target table based on your needs. Simply put, the main difference between the two target tables lies in the different statistical source tables. The data in the source table is different, and the results of the two clustering tables are quite different.

The basic aggregation table counts the content in the basic table of the database. The database uses a basic clustered table to track the data status of a specific table on a regular basis. For example, there is a customer information table that records all current customer information of the enterprise, including new customers and customers who have terminated the transaction. The sum function or avg function of the clustered target table can be used to track and analyze the data in this table. If you need to know the actual number of valid customers for each month in a year, you can calculate the average number of rows in the source table by month (using the avg function) then, the results are stored in the basic clustering table. Although the data in the basic clustered table comes from the source table, it is actually a record in the database. Therefore, the previous calculation will not affect the original statistical results as the content in the source table changes. This feature is the biggest difference between a clustered target table and a view. The statistical results of the previous months will not change with the data in the source table. In this way, you can count and analyze the changes in the number of users in each month.

However, this basic clustered table has a major defect, that is, it cannot reflect the data changes in the source table. For example, in March July, the company added five new customers, but also terminated five new customers. In this case, the number of valid customers in July is the same as that in July. However, the actual situation is that, although the final result is the same, the customer details have changed. Since basic clustered tables only perform simple sun and avg statistics on the source table, these changes cannot be tracked. This is the feature of the basic clustered table and one of its biggest defects.

In order to make up for the defect of the basic clustered table, the concept of changing the clustered table is introduced in the DB2 database. The biggest difference between changing a clustered table and a basic clustered table is that the source table they collect is different. The source table counted by the basic aggregation table is a basic data table, such as the customer information table. The source table used to change the statistics of the clustered table uses the content in the CD (change data table) Table mentioned above, rather than the content in the source table. Because the change data table records the changes to a source table, this change clustered table can reflect the specific changes to the data in the source table. For example, in the above example, the user not only satisfies and counts the number of valid customers in the current month, but also needs to know the number of customers and the number of customers who book each month. For enterprises, it is costly to get a new customer. From a certain point of view, enterprises prefer to retain existing customers without any efforts to develop new customers. That is to say, if an enterprise is constantly losing old customers while adding new customers, This is a failed business method for the enterprise. To this end, many enterprises are pursuing the number of customers, and may pay more attention to the loss of old customers. I have already met many enterprise users who are very concerned with the proportion of the number of lost customers and new customers in the current month. If the number of new customers is greater than the number of lost customers, the sales staff may not receive the rewards they deserve, but may also be punished. From this perspective, this requirement is more important than counting the number of valid customers. To achieve this, you need to change the clustering table. Changing the clustering table will track the data changes in the customer information table based on the CD table corresponding to the customer information table. If the number of new customers in the current month's customer information table is counted (the table has an insert operation) and the number of old customers lost (the table has a delete operation ). By using this method, we can collect statistics on the changes of new and old customers, so as to provide data support for enterprise decision-making.

3. The basic clustering table and the change clustering table are mutually dependent.

From the above analysis, we can see that although the two types of clustering target tables are based on different basic tables, their results are also quite different, but they are not opposite. According to user requirements, the two types of clustered target tables are mutually dependent. If an enterprise may need to count the changes in the number of valid customers per month and the changes of new and old customers, these two basic target tables need to be used at the same time.

In fact, I sometimes prefer to consider the differences between the two tables from another perspective. For a basic clustered table, its relationship with the source table is relatively close, that is, the source table-Basic clustered table, with no intermediary in the middle. For a clustered table, the relationship between the source table and the modified clustered table is far, and a CD Data Change table is inserted in the intermediary. Their relationship becomes source table-CD Data Change table-change clustering table. It is because the CD Data Change table plays a filtering role in the middle that it leads to the result difference between changing the clustering table and the basic clustering table. Since the CD Data Change table saves the changes of the data in the source table, the change of the clustered table can statistically analyze the changes of the data in the source table. Therefore, we can regard the basic clustered table as a two-tier structure, and change the clustered table as a three-tier structure. This makes it easier to understand the differences between the two types of aggregation targets. In actual work, it is easier to choose. When to use the appropriate target clustering table. However, I like to use some software packages at the same time. Finally, let the user select which target table to use.

Through the detailed analysis above, we should have understood the key role of the clustered target table in DB2. This article is suitable for beginners and I hope everyone can learn from it.