DB2 FOR LUW / DB2FORLUW / 970 - DB2

The Storage Management tool of the Control Center may show 
incorrect data skew when analyzing storage in multi partitioned 
environments. 
 
The Storage Management tool uses data from the SYSTOOLS.STMG_* 
tables to show the data skew.  The problem lies in the builtin 
stored procedure CAPTURE_STORAGEMGMT_INFO which fills the 
SYSTOOLS.STMG_* tables with data.  The routine collects the data 
using SYSTEM table sampling mechanism and uses following formula 
to calculate the sampling size: 
 
  samplingRate = 10000.00 / (double)table_rowCount 
 
The sampling mechanism SYSTEM and the actual sampling size is 
set this way that it produces accurate data using the least 
performance impact.  However there may be cases, specially in 
multi partitioned environments, where such sampling size is too 
small producing inaccurate output. 
 
---------------------------------------------------------------- 
Example: 
A table contains 1,869,241,320 rows and is spread evenly across 
18 partitions.  The Storage Management incorrectly reports that 
data for *only one* partition 10 is being captured and that 
there is 1,550,555,952 rows. 
 
This is due to the inefficiency of the CAPTURE_STORAGEMGMT_INFO 
routine.  Since sampling size is 10000.00/1869241320 = 
0.0000053497% of the pages, it checks only 1 of every 18,692,413 
pages. Because the system in this example had 1,268,160 pages 
per partition x 18 partitions = 22,826,880 pages, this sampling 
size caused that only one page was analyzed and only one 
partition (10) was reported. 
---------------------------------------------------------------- 
 
The purpose of this APAR is to provide a way how the sampling 
size can be adjusted/overridden.  Having this option the users 
can balance performance over accuracy of the captured data.

**************************************************************** 
* USERS AFFECTED:                                              * 
* This occurs on all environments                              * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* The Storage Management tool of the Control Center may show   * 
* incorrect data skew when analyzing storage in multi          * 
* partitioned                                                  * 
* environments.                                                * 
*                                                              * 
* The Storage Management tool uses data from the               * 
* SYSTOOLS.STMG_*                                              * 
* tables to show the data skew. The problem lies in the        * 
* builtin                                                      * 
* stored procedure CAPTURE_STORAGEMGMT_INFO which fills the    * 
* SYSTOOLS.STMG_* tables with data. The routine collects the   * 
* data                                                         * 
* using SYSTEM table sampling mechanism and uses following     * 
* formula                                                      * 
* to calculate the sampling size:                              * 
*                                                              * 
* samplingRate = 10000.00 / (double)table_rowCount             * 
*                                                              * 
* The sampling mechanism SYSTEM and the actual sampling size   * 
* is                                                           * 
* set this way that it produces accurate data using the least  * 
* performance impact. However there may be cases, specially in * 
*                                                              * 
* multi partitioned environments, where such sampling size is  * 
* too                                                          * 
* small producing inaccurate output.                           * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Limit the 'sampling rate ' , used in storage managment       * 
* stored procedure , to 0.1                                    * 
****************************************************************

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Always , limit the smallest 'sampling rate' (highest can be 1) 
, used in storage managment stored procedure ,  in such a way 
that customer's will get : 
1.Good accuracy 
2.Good performance 
After testing, it was found that sampling rate of 0.1 is 
yiedling good results even on a very large databases.

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

22.10.2009
25.02.2010
25.02.2010