DB2 FOR LUW / DB2FORLUW / 970 - DB2

db2exmig may not migrate the ADVISE_INDEX table properly. 
 
This can be verified by executing the following query on a 
database with the suspect improperly migrated ADVISE_INDEX 
table: 
SELECT C.NAME, C.TBNAME, C.TBCREATOR 
FROM SYSIBM.SYSCOLUMNS AS C 
WHERE C.TBNAME = 'ADVISE_INDEX' 
 
AND C.TBCREATOR = UPPER('<schema>') 
 
AND C.NAME IN ('NLEAF', 'SEQUENTIAL_PAGES') 
 
AND C.DEFAULT IS NULL; 
 
If either the NLEAF or SEQUENTIAL_PAGES columns are in the 
output resultset, the table has not been migrated properly. 
 
Another symptom of improper migration would be an unexpected 
termination of the DB2 Design Advisor (db2advis) with a return 
code of -407 and a corresponding entry in the db2diag.log that 
looks something like: 
 
YYYY-MM-dd-hh.mm.SS.ssssssssss ssssssssssss       LEVEL: Error 
PID     : nnnnnnn              TID  : n           PROC : 
db2advis 
INSTANCE: xxxxxxx              NODE : nnn 
APPID   : *xxxxx.xxxxxxx.nnnnnnnnnnnn 
EDUID   : n 
DATA #1 : String, 51 bytes 
***   Error at line nnnn, in file db2advindex.SQC 
DATA #2 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes 
 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -407   sqlerrml: 33 
 sqlerrmc: TBSPACEID=n, TABLEID=nn, COLNO=18 
 sqlerrp : SQLDFMT2 
 sqlerrd : (1) 0xhhhhhhhh      (2) 0xhhhhhhhh      (3) 
0xhhhhhhhh 
           (4) 0xhhhhhhhh      (5) 0xhhhhhhhh      (6) 
0xhhhhhhhh 
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6) 
           (7)      (8)      (9)      (10)        (11) 
 sqlstate: 23502 
 
The key problem identifiers in the db2diag.log entry are: 
"sqlcode: -407" and "COLNO=18".

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description.                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 3 or later.              * 
****************************************************************

Manually ALTER the ADVISE_INDEX table to account for the default 
values of the NLEAF and SEQUENTIAL_PAGES columns, i.e., 
ALTER TABLE <schema>.ADVISE_INDEX ALTER COLUMN NLEAF SET DEFAULT 
0 ALTER COLUMN SEQUENTIAL_PAGES SET DEFAULT 0;

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

First fixed in DB2 Version 9.7 Fix Pack 3.

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

04.05.2010
28.09.2010
28.09.2010

9.7.FP3