|
Problem IT01141
|
Status: Closed |
REORGCHK COMMAND AND REORGCHK_TB_STATS PROCEDURE MAY NOT RECOMMEND REORG FOR PARTITIONS IN A PARTITIONED TABLE. |
| product: |
DB2 FOR LUW / DB2FORLUW / A50 - DB2 |
| Problem description: |
When a partitioned table uses less than or equal to (NPARTITIONS
* 1 extent size) of pages, no table reorganization is
recommended. However, for each partition, (FPAGES <= 1 extent
size) should be applied. Currently (FPAGES <= NPARTITIONS * 1
extent size) is applied for each partition and it can cause the
situation that REORG is not recommended even though FPAGES > 1
extent size.
Here is the snippet from the reorgchk output. REORG column shows
'---' for each partition(PART0-9), but it should be '-**'.
$ db2 reorgchk on table $USER.t38801
Doing RUNSTATS ....
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
SCHEMA.NAME CARD OV NP FP ACTBLK
TSIZE F1 F2 F3 REORG
----------------------------------------------------------------
------------------
Table: DB2INST1.T38801 3335 0 1033 2011 -
2477905 0 30 51 -**
Table: DB2INST1.T38801
Data Partition: PART0 335 0 67 201 -
248905 0 33 34 ---
Table: DB2INST1.T38801
Data Partition: PART1 330 0 66 201 -
245190 0 33 34 ---
Table: DB2INST1.T38801
Data Partition: PART2 335 0 134 201 -
248905 0 33 69 ---
Table: DB2INST1.T38801
Data Partition: PART3 335 0 104 201 -
248905 0 33 53 ---
Table: DB2INST1.T38801
Data Partition: PART4 330 0 66 201 -
245190 0 33 34 ---
Table: DB2INST1.T38801
Data Partition: PART5 335 0 134 201 -
248905 0 33 69 ---
Table: DB2INST1.T38801
Data Partition: PART6 335 0 134 201 -
248905 0 33 69 ---
Table: DB2INST1.T38801
Data Partition: PART7 330 0 132 201 -
245190 0 33 68 ---
Table: DB2INST1.T38801
Data Partition: PART8 335 0 129 201 -
248905 0 33 66 ---
Table: DB2INST1.T38801
Data Partition: PART9 335 0 67 201 -
248905 0 33 34 ---
Table: DB2INST1.T38801
Data Partition: PART10 0 0 0 1 -
0 0 0 0 ---
Here is the output from the reorgchk_tb_stats output. REORG
column shows '--*' for each partition, but it should be '-**'.
$ db2 "call sysproc.reorgchk_tb_stats('T','DB2INST1.T38801')"
Result set 1
--------------
TABLE_SCHEMA TABLE_NAME DATAPARTITIONNAME CARD OVERFLOW
NPAGES FPAGES ACTIVE_BLOCKS TSIZE F1 F2 F3 REORG
------------ ------------ ----------------- ------- ----------
-------- -------- ------------- -------- ---- ---- ---- -----
DB2INST1 T38801 3335 0
1033 2011 -1 2477905 0 30 51 -**
DB2INST1 T38801 PART0 335 0
67 201 -1 248905 0 33 34 --*
DB2INST1 T38801 PART1 330 0
66 201 -1 245190 0 33 34 --*
DB2INST1 T38801 PART2 335 0
134 201 -1 248905 0 33 69 --*
DB2INST1 T38801 PART3 335 0
104 201 -1 248905 0 33 53 --*
DB2INST1 T38801 PART4 330 0
66 201 -1 245190 0 33 34 --*
DB2INST1 T38801 PART5 335 0
134 201 -1 248905 0 33 69 --*
DB2INST1 T38801 PART6 335 0
134 201 -1 248905 0 33 69 --*
DB2INST1 T38801 PART7 330 0
132 201 -1 245190 0 33 68 --*
DB2INST1 T38801 PART8 335 0
129 201 -1 248905 0 33 66 --*
DB2INST1 T38801 PART9 335 0
67 201 -1 248905 0 33 34 --*
DB2INST1 T38801 PART10 -1 -1
-1 -1 -1 -1 -1 -1 -1 --- |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users using reorgchk command *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Cancun Release 10.5.0.4 (also known as Fix *
* Pack 4) or higher. *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
|
| Solution |
Fixed in DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 18.04.2014
22.10.2014
22.10.2014 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.5.0.4
|
 |
