| Latest versions | fixlist |
| 11.1.0.7 |
 |
| 10.5.0.9 |
|
| 10.1.0.6 |
|
| 9.8.0.5 |
|
| 9.7.0.11 |
|
| 9.5.0.10 |
|
| 9.1.0.12 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
DB2 - Problem description
| Problem IC68416 | Status: Closed |
DB2EXMIG MAY NOT MIGRATE THE ADVISE_INDEX TABLE PROPERLY | |
| product: | |
DB2 FOR LUW / DB2FORLUW / 980 - 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: *
* na *
****************************************************************
* 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". *
****************************************************************
* RECOMMENDATION: *
* 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; *
**************************************************************** | |
| 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; | |
| Solution | |
| Workaround | |
not known / see Local fix | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 04.05.2010 04.08.2011 04.08.2011 |
| Problem solved at the following versions (IBM BugInfos) | |
| Problem solved according to the fixlist(s) of the following version(s) | |