|
Problem IC64026
|
Status: Closed |
THE STORAGE MANAGEMENT IN THE CONTROL CENTER MAY SHOW INCORRECT DATA SKEW FOR MULTI PARTITIONED ENVIRONMENTS |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| 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.
----------------------------------------------------------------
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. |
| Problem Summary: |
****************************************************************
* 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 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
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
|
| Solution |
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. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.10.2009
25.02.2010
25.02.2010 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
