|
Problem IC68365
|
Status: Closed |
DB2EXMIG MAY NOT MIGRATE THE ADVISE_INDEX TABLE PROPERLY |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| Problem description: |
db2exmig may not migrate the ADVISE_INDEX table properly.
This can be determined 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 result-set, the table has not been migrated properly.
Another symptom of improper migration is the 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: *
* After migration to 9.5 *
****************************************************************
* PROBLEM DESCRIPTION: *
* db2exmig may not migrate the ADVISE_INDEX table properly *
* when upgrading to DB2 Version 9.5. *
****************************************************************
* RECOMMENDATION: *
* Install Fixpack 6 *
**************************************************************** |
| 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.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
DB2 9.5 FP6 |
| Workaround |
not known / see Local fix |
| BUG-Tracking |
forerunner : APAR is sysrouted TO one or more of the following: IC68415 IC68416
follow-up : |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 03.05.2010
22.06.2010
22.06.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
