Several basic strategies for DB2 database optimization

1, the subsequent use of the table to establish the index (note before inserting data to establish or after inserting but to Runstats):

Description: Before inserting the data in the table, the index is also updated, so that a large log space is required, so the speed is slower, can be inserted in a way that does not log, when the data is set and indexed, the log statistics for the table are not updated, so the execution plan is poor, Index statistics are updated after runstats on tabble asiainfo.aaaa and indexes all, so that the execution plan takes into account the use of indexes and is therefore fast.

2, the larger table is built on the multi-node table space, at the same time to build a good index:

Note: The existing DB2 Data Warehouse uses 2 CPU,4G memory per node, and the Dim table Space plan is the table space for the dimension table, so it is a single node. When using tables in this table space, you can use up to 2 cpu,4g memory, plus the other table space to use these two CPUs and 4G of memory, so the resources are relatively limited. It is recommended that the larger table not be placed in this table space, but set up the partitioning key, placed in the Multi-node table space, so that when retrieving the table 32 nodes at the same time to retrieve, the final summary to 0 nodes on display, the speed of course will be very fast. In addition, although the 32-node parallelism is good, it will be faster if you build a good index.

3. Insert the table in a way that is not logged:

Note: The database in order to ensure consistency and resilience of data, insert, UPDATE or delete data to log, so that when the failure can be rolled back, but if more concurrent or very large operation, will result in a scramble for the log situation, resulting in very slow operation. If you use a log-less way to insert, update, or delete operations, the log is used very little, but if the operation fails to be able to rollback, so the consistency is not guaranteed, this table can only delete the rebuild!!!!

4, the table to establish a table-level lock, reduce the number of locks used:

Description: The maximum number of locks in the database is limited, and each lock has to occupy a certain amount of memory, so if the number of locks is very large, the use of more memory, resulting in resource tension.

5, the creation of temporary tables as far as possible only to insert the data used, do not plug in the data not used:

Description: Many parts of the program in order to improve speed, the data used is inserted into a temporary table, but a very large number of unused data is inserted, which causes the temporary table to be very big, so as much as possible to insert the used data into the temporary table, and to use the index as much as possible to greatly improve the speed.

6, about the use of the left side of the experience:

In the condition of on, try to write only the associated condition and the left associated table as a limit, while the limit on the primary table is not written here. If it is written in it, not only is it very slow, but it can be a baffling result.