|
Problem IC68415
|
Status: Closed |
DB2EXMIG MAY NOT MIGRATE THE ADVISE_INDEX TABLE PROPERLY |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
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". |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* See Error Description. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 3 or later. *
**************************************************************** |
| Local Fix: |
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; |
| available fix packs: |
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
|
| Solution |
First fixed in DB2 Version 9.7 Fix Pack 3. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 04.05.2010
28.09.2010
28.09.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP3
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.3
|
 |
| 9.7.0.3
|
|
