How does the table change when the database is collocated in DB2?

The following articles mainly introduce the coexistence of DB2 databases and describe the configuration of tables for the occurrence of the coexistence of DB2 databases. The following is a detailed description of the main content of this article, I hope you will have a better understanding of it after browsing.

DB2 database Synchronization

Collocation is used to place rows in different tables in the same database partition that contain relevant data. The parallel table enables DB2 to use the connection policy more effectively.

You may find that as a response to a specific query, two or more tables provide data frequently. In this case, you want the data in such a table to be as close as possible. In an environment where a database is physically divided into two or more database partitions, there must be a way to keep the table shards as close as possible. The function to complete this process is called table concatenation.

When accessing multiple tables used for connection or subquery, DB2 (R) Universal Database (DB2 UDB) can identify whether the data to be connected is located in the same database partition. Therefore, DB2 can execute connections or subqueries on the database partition where data is stored without moving data between database partitions. This ability to locally execute connections or subqueries has significant performance advantages.

To enable the coexistence of DB2 databases, tables must:

In the same database partition group, and this database partition group cannot be in the redistribution period. (During the redistribution period, tables in the database partition group may use different partition mappings-they are not collocated .)

The partition key contains the same number of columns.

The corresponding column of the partition key is partition compatible.

If a table is in a single-partition database partition group and the Partition Block is defined on the same partition where the other table is located, the Union can also occur.

Summary:

Data types that can be hashed, such as int, char, and varchar, are compatible with partitions. data types such as LOB and LF cannot be hashed.

For the DB2 database, for example, I have two tables A and B, where A contains column c1 int, c2 char, and B contains c1 varchar and c2 int.

Then Table A is partitioned by c1, and table B is partitioned by c2. That is to say, when you insert A row of data to Table. c1 is hashed, and then the number of partitions is modeled to insert new data into the corresponding partition. For Table B, the new data is for table B. c2 hash with the same algorithm.

In this way, when querying select * from c1, c2 where. c1 = B. in c2, because. c1 and B. c2 has the same data type and uses the same hash algorithm. If. c1 and B. c2 is equal, so they must be in the same partition. In this case, when performing the join operation, you do not need to move data in different intervals. Instead, you only need to return data to coord partition after the join operation in this partition.

The above content is an introduction to the parallel configuration of the DB2 database. I hope you will have some gains.