|
Problem IT00380
|
Status: Closed |
SQL1476N WITH ERROR "-668" RETURNED WHEN SET INTEGRITY FOR AN MQT OF "COMPRESS YES" AND "REFRESH IMMEDIATE" |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
The scenario is like below,
1) create a base table, then create an MQT with "COMPRESS YES"
and "REFRESH IMMEDIATE"
2) load data into the base table, and run SET INTEGRITY for the
base table
3) for the MQT, activate NOT LOGGED INITIALLY and run SET
INTEGRITY ... IMMEDIATE CHECKED in one UOW, it returns SQL1476N
with error "-668".
-> db2 +c -tvf alterMQT.sql
ALTER TABLE TEST.MYMQT activate NOT LOGGED INITIALLY
DB20000I The SQL command completed successfully.
SET INTEGRITY FOR TEST.MYMQT IMMEDIATE CHECKED
DB21034E The command was processed as an SQL statement because
it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1476N The current transaction was rolled back because of
error "-668".
SQLSTATE=40506
Internally, during SET INTEGRITY for the MQT, Automatic
Dictionary Creation(ADC) is kicked off to build the compression
dictionary when there is enough data in the MQT, however ADC
fails with error "-668" and it lead to the failure of SET
INTEGRITY. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* all *
****************************************************************
* PROBLEM DESCRIPTION: *
* The scenario is like below, *
* 1) create a base table, then create an MQT with "COMPRESS *
* YES" *
* and "REFRESH IMMEDIATE" *
* 2) load data into the base table, and run SET INTEGRITY for *
* the *
* base table *
* 3) for the MQT, activate NOT LOGGED INITIALLY and run SET *
* INTEGRITY ... IMMEDIATE CHECKED in one UOW, it returns *
* SQL1476N *
* with error "-668". *
* *
* -> db2 +c -tvf alterMQT.sql *
* ALTER TABLE TEST.MYMQT activate NOT LOGGED INITIALLY *
* DB20000I The SQL command completed successfully. *
* *
* SET INTEGRITY FOR TEST.MYMQT IMMEDIATE CHECKED *
* DB21034E The command was processed as an SQL statement *
* because *
* it was not a *
* valid Command Line Processor command. During SQL processing *
* it *
* returned: *
* SQL1476N The current transaction was rolled back because of *
* error "-668". *
* SQLSTATE=40506 *
* *
* Internally, during SET INTEGRITY for the MQT, Automatic *
* Dictionary Creation(ADC) is kicked off to build the *
* compression *
* dictionary when there is enough data in the MQT, however ADC *
* fails with error "-668" and it lead to the failure of SET *
* INTEGRITY. *
****************************************************************
* RECOMMENDATION: *
* Use MQT refresh method REFRESH DEFERRED instead of REFRESH *
* IMMEDIATE, then follow REFRESH statement in separate. *
* Or, disable compression when create MQT table, then enable *
* compression after SET INTEGRITY. *
**************************************************************** |
| Local Fix: |
Use MQT refresh method REFRESH DEFERRED instead of REFRESH
IMMEDIATE, then follow REFRESH statement in separate.
Or, disable compression when create MQT table, then enable
compression after SET INTEGRITY. |
| available fix packs: |
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 18.03.2014
02.06.2014
02.06.2014 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.4
|
 |
