| Latest versions | fixlist |
| 11.1.0.7 |
 |
| 10.5.0.9 |
|
| 10.1.0.6 |
|
| 9.8.0.5 |
|
| 9.7.0.11 |
|
| 9.5.0.10 |
|
| 9.1.0.12 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
DB2 - Problem description
| Problem IC84272 | Status: Closed |
CALL TO SYSPROC.REORGCHK_IX_STATS('T','ALL') MAY PRODUCE INCORRECT RESULTS | |
| product: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
| Problem description: | |
The sysproc.REORGCHK_IX_STATS procedure returns two different
outputs when its runs against a single table and
against all tables. The results returned when run against all
tables is not correct.
Sample output
CALL sysproc.REORGCHK_IX_STATS('T','VENTAS.VENTA')
TABLE_SCHEMA TABLE_NAME INDEX_SCHEMA INDEX_NAME
INDCARD NLEAF NUM_EMPTY_LEAFS NLEVELS
NUMRIDS_DELETED FULLKEYCARD LEAF_RECSIZE
NONLEAF_RECSIZE LEAF_PAGE_OVERHEAD NONLEAF_PAGE_OVERHEAD
F4 F5 F6 F7 F8 REORG
VENTAS VENTA VENTAS IX_VENTA_02
2036380584 908722 9 3 22113685 7341552 11
11 3206 3206 100 68 0 1 0 -----
VENTAS VENTA VENTAS IX_VTA_ARTICULO
2036381326 675996 0 3 1205830 243038 5 5
4452 4452 33 95 0 0 0 *----
VENTAS VENTA VENTAS IX_VENTA_03
2036381326 689965 9 3 684651 98844 14 14
2936 2936 98 89 0 0 0 -----
VENTAS VENTA VENTAS IX_VTA_ARTCT
2036381326 684180 0 3 1836419 1418327 15 15
2936 2936 11 89 0 0 0 *----
VENTAS VENTA VENTAS IX_VENTA_01
2036380584 853148 27 4 24976958 7341552 11
11 3206 3206 100 73 224 1 0 --*--
VENTAS VENTA VENTAS PK_VENTA
2036380584 1485009 966 3 40876480 2036380584 8
8 3534 3534 100 89 0 1 0 -----
CALL sysproc.REORGCHK_IX_STATS('T','ALL')
TABLE_SCHEMA TABLE_NAME INDEX_SCHEMA INDEX_NAME
INDCARD NLEAF NUM_EMPTY_LEAFS NLEVELS
NUMRIDS_DELETED FULLKEYCARD LEAF_RECSIZE
NONLEAF_RECSIZE LEAF_PAGE_OVERHEAD NONLEAF_PAGE_OVERHEAD
F4 F5 F6 F7 F8 REORG
VENTAS VENTA VENTAS IX_VENTA_02
2036380584 908722 9 3 0 7341552 -1 -1 -1
-1 100 -1 -1 0 0 -----
VENTAS VENTA VENTAS IX_VTA_ARTICULO
2036381326 675996 0 3 0 243038 -1 -1 -1
-1 33 -1 -1 0 0 -----
VENTAS VENTA VENTAS IX_VENTA_03
2036381326 689965 9 3 0 98844 -1 -1 -1
-1 98 -1 -1 0 0 -----
VENTAS VENTA VENTAS IX_VTA_ARTCT
2036381326 684180 0 3 0 1418327 -1 -1 -1
-1 11 -1 -1 0 0 -----
VENTAS VENTA VENTAS IX_VENTA_01
2036380584 853148 27 4 0 7341552 -1 -1
-1 -1 100 -1 -1 0 0 -----
VENTAS VENTA VENTAS PK_VENTA
2036380584 1485009 966 3 0 2036380584 -1 -1
-1 -1 100 -1 -1 0 0 -----
From the above output we see the diffrences in columns
NUMRIDS_DELETED,LEAF_RECSIZE, NONLEAF_RECSIZE,
LEAF_PAGE_OVERHEAD, NONLEAF_PAGE_OVERHEAD, F4, F5, F6, F7, F8,
REORG. | |
| Problem Summary: | |
****************************************************************
* USERS AFFECTED: *
* Users prior to DB2 V10 FP1 *
****************************************************************
* PROBLEM DESCRIPTION: *
* CALL TO SYSPROC.REORGCHK_IX_STATS('T','ALL') MAY PRODUCE *
* INCORRECT RESULTS *
* *
* The sysproc.REORGCHK_IX_STATS procedure returns two *
* different *
* outputs when its runs against a single table and *
* *
* against all tables. The results returned when run against *
* all *
* tables is not correct. *
* *
* *
* *
* Sample output *
* *
* *
* *
* CALL sysproc.REORGCHK_IX_STATS('T','VENTAS.VENTA') *
* *
* *
* *
* *
* *
* TABLE_SCHEMA TABLE_NAME INDEX_SCHEMA INDEX_NAME *
* *
* INDCARD NLEAF NUM_EMPTY_LEAFS NLEVELS *
* *
* NUMRIDS_DELETED FULLKEYCARD LEAF_RECSIZE *
* *
* NONLEAF_RECSIZE LEAF_PAGE_OVERHEAD *
* NONLEAF_PAGE_OVERHEAD *
* F4 F5 F6 F7 F8 REORG *
* *
* VENTAS VENTA VENTAS IX_VENTA_02 *
* *
* 2036380584 908722 9 3 22113685 7341552 11 *
* *
* 11 3206 3206 100 68 0 1 0 ----- *
* *
* VENTAS VENTA VENTAS IX_VTA_ARTICULO *
* *
* 2036381326 675996 0 3 1205830 243038 5 *
* 5 *
* 4452 4452 33 95 0 0 0 *---- *
* *
* VENTAS VENTA VENTAS IX_VENTA_03 *
* *
* 2036381326 689965 9 3 684651 98844 14 *
* 14 *
* 2936 2936 98 89 0 0 0 ----- *
* *
* VENTAS VENTA VENTAS IX_VTA_ARTCT *
* *
* 2036381326 684180 0 3 1836419 1418327 15 *
* 15 *
* 2936 2936 11 89 0 0 0 *---- *
* *
* VENTAS VENTA VENTAS IX_VENTA_01 *
* *
* 2036380584 853148 27 4 24976958 7341552 11 *
* *
* 11 3206 3206 100 73 224 1 0 --*-- *
* *
* VENTAS VENTA VENTAS PK_VENTA *
* *
* 2036380584 1485009 966 3 40876480 2036380584 *
* 8 *
* 8 3534 3534 100 89 0 1 0 ----- *
* *
* *
* *
* CALL sysproc.REORGCHK_IX_STATS('T','ALL') *
* *
* *
* *
* *
* *
* TABLE_SCHEMA TABLE_NAME INDEX_SCHEMA INDEX_NAME *
* *
* INDCARD NLEAF NUM_EMPTY_LEAFS NLEVELS *
* *
* NUMRIDS_DELETED FULLKEYCARD LEAF_RECSIZE *
* *
* NONLEAF_RECSIZE LEAF_PAGE_OVERHEAD *
* NONLEAF_PAGE_OVERHEAD *
* F4 F5 F6 F7 F8 REORG *
* *
* *
* *
* VENTAS VENTA VENTAS IX_VENTA_02 *
* *
* 2036380584 908722 9 3 0 7341552 -1 -1 *
* -1 *
* -1 100 -1 -1 0 0 ----- *
* *
* VENTAS VENTA VENTAS IX_VTA_ARTICULO *
* *
* 2036381326 675996 0 3 0 243038 -1 -1 *
* -1 *
* -1 33 -1 -1 0 0 ----- *
* *
* VENTAS VENTA VENTAS IX_VENTA_03 *
* *
* 2036381326 689965 9 3 0 98844 -1 -1 *
* -1 *
* -1 98 -1 -1 0 0 ----- *
* *
* VENTAS VENTA VENTAS IX_VTA_ARTCT *
* *
* 2036381326 684180 0 3 0 1418327 -1 -1 *
* -1 *
* -1 11 -1 -1 0 0 ----- *
* *
* VENTAS VENTA VENTAS IX_VENTA_01 *
* *
* 2036380584 853148 27 4 0 7341552 -1 -1 *
* *
* -1 -1 100 -1 -1 0 0 ----- *
* *
* VENTAS VENTA VENTAS PK_VENTA *
* *
* 2036380584 1485009 966 3 0 2036380584 -1 *
* -1 *
* -1 -1 100 -1 -1 0 0 ----- *
* *
* *
* *
* From the above output we see the diffrences in columns *
* *
* NUMRIDS_DELETED,LEAF_RECSIZE, NONLEAF_RECSIZE, *
* *
* LEAF_PAGE_OVERHEAD, NONLEAF_PAGE_OVERHEAD, F4, F5, F6, F7, *
* F8, *
* REORG. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 V10 FP1 *
**************************************************************** | |
| Local Fix: | |
| available fix packs: | |
DB2 Version 10.1 Fix Pack 1 for Linux, UNIX, and Windows | |
| Solution | |
| Workaround | |
not known / see Local fix | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 13.06.2012 21.11.2012 21.11.2012 |
| Problem solved at the following versions (IBM BugInfos) | |
| Problem solved according to the fixlist(s) of the following version(s) | |
| 10.1.0.1 |
|
| 10.5.0.1 |
|