DB2 FOR LUW / DB2FORLUW / 970 - DB2

With the number_compat database configuration parameter set to 
ON, creation of a refresh immediate MQT that 
references a distinct type as a group by column may succeed 
unexpectedly, while subsequent updates on the base table of the 
MQT may result in SQL0901N. SQL20058N rc 6 should have been 
returned during table creation. 
 
  CREATE DISTINCT TYPE MONEY AS DECIMAL(8,2) WITH COMPARISONS; 
  CREATE FUNCTION COUNT(MONEY) RETURNS INTEGER SOURCE 
COUNT(DECIMAL()); 
  CREATE FUNCTION SUM(MONEY) RETURNS MONEY SOURCE 
SUM(DECIMAL()); 
 
  create table t1(m1 money, m2 money); 
 
  create table t2(m1, sum_m2, count_m2, count_all) as 
   (select m1, sum(m2), count(m2), count(*) from t1 where m2 >= 
money(100.00) 
    group by m1) 
   data initially deferred refresh immediate disable query 
optimization;

**************************************************************** 
* USERS AFFECTED:                                              * 
* User using NUMBER/ORA compatibility feature                  * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* With the number_compat database configuration parameter set  * 
* to ON, creation of a refresh immediate MQT that references a * 
* distinct type as a group by column may succeed unexpectedly, * 
* while subsequent updates on the base table of the MQT may    * 
* result in SQL0901N. SQL20058N rc 6 should have been returned * 
* during table creation.                                       * 
*                                                              * 
* CREATE DISTINCT TYPE MONEY AS DECIMAL(8,2) WITH COMPARISONS; * 
* CREATE FUNCTION COUNT(MONEY) RETURNS INTEGER SOURCE          * 
* COUNT(DECIMAL());                                            * 
* CREATE FUNCTION SUM(MONEY) RETURNS MONEY SOURCE              * 
* SUM(DECIMAL());                                              * 
*                                                              * 
* create table t1(m1 money, m2 money);                         * 
*                                                              * 
* create table t2(m1, sum_m2, count_m2, count_all) as          * 
*  (select m1, sum(m2), count(m2), count(*) from t1 where m2   * 
* >= money(100.00)                                             * 
*   group by m1)                                               * 
*  data initially deferred refresh immediate disable query     * 
* optimization;                                                * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 9.7 Fix Pack 4 or above.                      * 
****************************************************************

Drop the MQT

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 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

The fix to properly return SQL20058N first appeared in DB2 9.7 
Fix Pack 4.

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

02.03.2011
28.04.2011
28.04.2011

9.7.,
9.7.FP4