DB2 - Problembeschreibung
Problem IC62643 | Status: Geschlossen |
DB2 DESIGN ADVISOR MAY ERRONEOUSLY RECOMMEND A GENERATED COLUMN IDENTICAL TO A BASE COLUMN DURING MDC RECOMMENDATIONS. | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
The DB2 Design Advisor (db2advis) may recommend a base table or MQT (BT) with a generated column whose expression is exactly identical to one of the BT's columns. This can occur if the following conditions are satisfied: - MDC recommendations are requested, i.e., the db2advis -m command line parameter's value contains C, e.g., -m C - The BT column data-type is either of CHAR, VARCHAR, GRAPHIC or VARGRAPHIC - The BT column statistics imply the column can benefit from being created as a dimension of an MDC table, i.e., column storage as an MDC dimension does not exceed maximum allowable inflation for a BT as an MDC table The following is an example recommendation that exhibits this issue: ... -- LIST OF MODIFIED CREATE-TABLE STATEMENTS WITH RECOMMENDED PARTITIONING KEYS AND TABLESPACES AND/OR RECOMMENDED MULTI-DIMENSIONAL CLUSTERINGS -- =========================== -- table["TPCD "."ORDERS"], Added table size ....MB -- CREATE TABLE "TPCD "."ORDERS" ( "O_ORDERKEY" INTEGER NOT NULL , -- "O_CUSTKEY" INTEGER NOT NULL , -- "O_ORDERSTATUS" VARCHAR(100) NOT NULL , -- "O_TOTALPRICE" DOUBLE NOT NULL , -- "O_ORDERDATE" DATE NOT NULL , -- "O_ORDERPRIORITY" CHAR(15) NOT NULL , -- "O_CLERK" CHAR(15) NOT NULL , -- "O_SHIPPRIORITY" INTEGER NOT NULL , -- "O_COMMENT" VARCHAR(79) NOT NULL , -- MDC............... VARCHAR(100) GENERATED ALWAYS AS ( (O_ORDERSTATUS) ) ) -- IN "TPCD_MDC" -- ORGANIZE BY ( -- MDC............... ) -- ; -- COMMIT WORK ; ... | |
Problem-Zusammenfassung: | |
DB2 DESIGN ADVISOR MAY ERRONEOUSLY RECOMMEND A GENERATED COLUMN IDENTICAL TO A BASE COLUMN DURING MDC RECOMMENDATIONS. | |
Local-Fix: | |
Manually alter the recommended table DDL by removing the generated column definition and changing the name of the column in the ORGANIZE BY clause. For the above example recommendation, the DDL should be altered as follows: CREATE TABLE "TPCD "."ORDERS" ( "O_ORDERKEY" INTEGER NOT NULL , "O_CUSTKEY" INTEGER NOT NULL , "O_ORDERSTATUS" VARCHAR(100) NOT NULL , "O_TOTALPRICE" DOUBLE NOT NULL , "O_ORDERDATE" DATE NOT NULL , "O_ORDERPRIORITY" CHAR(15) NOT NULL , "O_CLERK" CHAR(15) NOT NULL , "O_SHIPPRIORITY" INTEGER NOT NULL , "O_COMMENT" VARCHAR(79) NOT NULL ) IN "TPCD_MDC" ORGANIZE BY ( O_ORDERSTATUS ) ; | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows | |
Lösung | |
First fixed in DB2 UDB Version 9.7, FixPak 1 | |
Workaround | |
Manually alter the recommended table DDL by removing the generated column definition and changing the name of the column in the ORGANIZE BY clause. For the above example recommendation, the DDL should be altered as follows: CREATE TABLE "TPCD "."ORDERS" ( "O_ORDERKEY" INTEGER NOT NULL , "O_CUSTKEY" INTEGER NOT NULL , "O_ORDERSTATUS" VARCHAR(100) NOT NULL , "O_TOTALPRICE" DOUBLE NOT NULL , "O_ORDERDATE" DATE NOT NULL , "O_ORDERPRIORITY" CHAR(15) NOT NULL , "O_CLERK" CHAR(15) NOT NULL , "O_SHIPPRIORITY" INTEGER NOT NULL , "O_COMMENT" VARCHAR(79) NOT NULL ) IN "TPCD_MDC" ORGANIZE BY ( O_ORDERSTATUS ) ; | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 20.08.2009 28.12.2009 28.12.2009 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7. | |
Problem behoben lt. FixList in der Version | |
9.7.0.1 |