| 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 IC82042 | Status: Closed |
WHEN OPTIMIZER PLAN USES GROUP-BY MQT FOR AN INSERT STATEMENT, OPTIMIZER (I.E. NON-GROUP-BY MQT) MAY NOT BE CONSIDERED. | |
| product: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
| Problem description: | |
The symptom is when optimizer plan uses Group-By MQT for an INSERT statement, optimizer (i.e. non-Group-By MQT) may not be considered. The optimizer MQT includes replicated MQT. This happens even if DB2_EXTENDED_OPTIMIZATION=FORCE_REPLICATED_MQT_MERGE is set in v9.7 | |
| Problem Summary: | |
****************************************************************
* USERS AFFECTED: *
* User prior to DB2 V9.7 FP6 *
****************************************************************
* 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: *
* Move to DB2 V 9.7 FP6 *
**************************************************************** | |
| Local Fix: | |
No | |
| available fix packs: | |
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows | |
| Solution | |
| Workaround | |
not known / see Local fix | |
| BUG-Tracking | |
forerunner : APAR is sysrouted TO one or more of the following: IC82503 IC84543 follow-up : | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 15.03.2012 06.06.2012 06.06.2012 |
| Problem solved at the following versions (IBM BugInfos) | |
9.7.FP6 | |
| Problem solved according to the fixlist(s) of the following version(s) | |
| 9.7.0.6 |
|