In-depth analysis of DB2 Data Statistics and Analysis System Optimization

How should we optimize the DB2 data statistics and analysis system? Many people may have mentioned this issue. The following describes how to optimize the DB2 data statistics and analysis system for your reference.

Combined with the DB2 database data statistics and analysis system and the optimization methods described above, an optimization method for the DB2 database statistics and analysis system is obtained.

1) the DB2 data statistics and analysis system uses the index creation method for the tables used in the future. If indexes are created before insertion, indexes will also be updated during the data insertion process, in this case, a large log space is required, so the speed will be relatively slow. You can use the log-independent insertion method. If you create an index after data insertion, the log statistics of the table are not updated, so the execution plan is poor. indexes are not used to update the index statistics. In this way, the execution plan takes into account the use of indexes, which leads to high system efficiency.

2) the DB2 data statistics and analysis system builds large tables on multi-node tablespaces and builds indexes. Assume that each node in the db2 data warehouse uses two CPUs, 4 GB memory, and the DIM tablespace plan is to store the tablespace of the dimension table, so it is a single node. When using a table in this tablespace, only two CPUs and 4 GB memory are used at most, and other tablespaces also need to use these two CPUs and the 4 GB memory, therefore, resources are limited. The DB2 data statistics and analysis system does not place large tables in this tablespace, but creates partition keys and stores them in multi-node tablespaces, in this way, when the table is retrieved by 32 nodes at the same time, it is finally summarized to 0 nodes for display. Of course, the system performance is superior.

3) In the DB2 data statistics and analysis system, the inserted tables are inserted in a non-log manner. To ensure data consistency and rollability, the database needs to count logs when inserting, updating, or deleting data, so that logs can be rolled back when data fails, however, if the concurrency is large or the operation is very large, it will lead to competition for logs, resulting in very slow operations. If you insert, update, or delete a table without logs, the log is rarely used. However, if the operation fails, the table cannot be rolled back. Therefore, the consistency is not guaranteed and the table can only be deleted and rebuilt.

4) Create Table-level locks for tables to minimize the number of locks. Because the maximum number of database locks is limited, and each lock occupies a certain amount of memory, if there are a large number of locks, there will be more memory, resulting in resource shortage.

5) when creating a temporary table, insert only the data used, instead of the data not used. In the program, the data used is first inserted into a temporary table to improve the speed, but a lot of unused data is inserted, resulting in a large temporary table, therefore, try to insert only the data used in the temporary table and use the index as much as possible to optimize the system performance.

 

Implementation of DB2 date format change

Basic knowledge of DB2 Date and Time

Introduction to DB2 SQL Execution Plan Monitoring

Learn about DB2 snapshot monitoring

How to Implement online DB2 Import