In situations in which you have very high insert rates, you may want to use append processing. When the append processing is turned on, DB2 uses an alternate insert algorithm that simplifies add data to the end of a partition or a table space. Append processing can be turned on for DB2 V7 or DB2 V8 by setting the table space options PCTFREE 0 FREEPAGE 0 MEMBER cluster. Make sure that APARS PQ86037 and PQ87381 are applied.
Note: In DB2 9, turn on append processing by using the APPEND YES option of the CREATE TABLE statement. When the append processing is turned on, DB2 does not respect the cluster during inserts and simply puts all new data at the end of the table space.
If you have a single index for read access, append processing may mean more random reads. This activity may require more frequent REORGs to keep the data organized for read access. Also, if you are partitioning, and the partitioning key is not the read index, then you still have random reads during the insert to the non-partitioned index. Ensure that you have adequate free space to avoid index page splits.
You can also use append processing to store historical or seldom read audit information. In these cases, perform your partitioning based upon an ascending value (for example, a date) and have all new data go to the end of the last partition. In this situation, all table space maintenance, such as copies and REORGs, are against the last partition. All other data is static and does not require maintenance. You may need to create a secondary index, or each read query will have to be for a range of values within the ascending key domain.
|
Copyright © 2014 CA Technologies.
All rights reserved.
|
|