Rational Method for ing DB2 data tables to tablespaces (1)

Database operation efficiency is the key to the database. How to improve efficiency has become a problem that every DBA is thinking about. tablespace settings are undoubtedly the key. The following describes how to map a data table to a tablespace.

In DB2 databases, data is stored at the table, tablespace, and database levels. Table space settings are directly related to database performance. Therefore, whether the data table is deployed properly in the tablespace directly affects the database operation efficiency.

In DB2 databases, data is stored at the table, tablespace, and database levels. Table space settings are directly related to database performance. Therefore, whether the data table is deployed properly in the tablespace directly affects the database operation efficiency. In my opinion, to properly map a data table to a tablespace, follow the following rules at least.

Rule 1: Map tablespaces Based on the table's partition rate.

In a database system, the table partition rate is different. Based on incomplete predictions, only about 40% to 50% tables in a database have a high partition rate. Other data tables are hard to use. For example, in a database system designed for ERP, data tables such as purchase orders and sales orders have a high renewal rate. However, data tables such as cost adjustment orders are rarely used several times a year. Therefore, in database deployment, the database administrator must first classify the tables in the database according to the actual situation of enterprise applications. The frequently used table is then placed in a tablespace, or several different tablespaces are placed according to the module. They are then stored on hard disks with high performance or in hard disks with low activity. In this case, the I/O contention will be much less, thus improving the database performance.

Rule 2: plan the table based on the degree of use of the field.

In addition, the Access Frequency of different tables varies greatly. Even in the same table, the Access frequency of its fields varies greatly. For example, in some personnel management system databases, some companies will store copies or photos of employee ID cards in the database. These images must be stored in the database using the LOB data type. Because of the large data type, both query and database backup will have a negative impact. For example, when you query employee information, if you do not add the field name to the SELECT statement and use the full query method, the query speed will be very slow. As the number of images increases, the speed decreases exponentially. However, users do not need to access copies or photos of these employees' ID cards. Therefore, it is a waste to display the information that is not found during the query, and it also affects the database performance.

Therefore, when designing a table, if some columns are not frequently used, in addition, the data types of these columns are big data types or many of them are NULL values (the efficiency of the database when processing NULL values is much slower than that of other columns ), in this case, it is best to separate these columns from other columns and store them in different tables. Since their access frequency is different, the tablespace can be mapped according to the first rule above to improve the database access performance.