SEO

Hello All;
Assuming a PMR is in order for this, but wanted to run by the group to
see if anyone had similar experience. We just upgraded a DB2 V8 AIX
(DPF) environment to FP10 from FP6 and have a problem with an
information-integrator-generated nickname to an Oracle table:
* Dropped nickname (which existed and had been used frequently
pre-upgrade)
* Tried to recreate with same name but pointing to a different remote
(Oracle) server
* Create failed with:
[IBM][CLI Driver][DB2/6000] SQL0601N The name of the object to be
created is identical to the existing name "FDR.CGY_REL_DSPL_DTL" of
type "TABLE". SQLSTATE=42710
* Even though the nickname was dropped, a (primary key) constraint is
visible in SYSCAT.TABCONST for the nickname. Am assuming that's why DB2
thinks the table is still around.
* We can't drop the constraint because the table no longer exists so
ALTER TABLE fails
* Tried to create a table with the same name and were successful.
ALTERed the table to try to drop the constraint, but DB2 said there was
no primary key.

Why was the constraint not dropped along with the nickname? How can we
clean up the catalog to reflect reality? Any help appreciated.

Pete H

peteh wrote:
> Hello All;
> Assuming a PMR is in order for this, but wanted to run by the group to
> see if anyone had similar experience. We just upgraded a DB2 V8 AIX
> (DPF) environment to FP10 from FP6 and have a problem with an
> information-integrator-generated nickname to an Oracle table:
> * Dropped nickname (which existed and had been used frequently
> pre-upgrade)
> * Tried to recreate with same name but pointing to a different remote
> (Oracle) server
> * Create failed with:
> [IBM][CLI Driver][DB2/6000] SQL0601N The name of the object to be
> created is identical to the existing name "FDR.CGY_REL_DSPL_DTL" of
> type "TABLE". SQLSTATE=42710
> * Even though the nickname was dropped, a (primary key) constraint is
> visible in SYSCAT.TABCONST for the nickname. Am assuming that's why DB2
> thinks the table is still around.
> * We can't drop the constraint because the table no longer exists so
> ALTER TABLE fails
> * Tried to create a table with the same name and were successful.
> ALTERed the table to try to drop the constraint, but DB2 said there was
> no primary key.
>
> Why was the constraint not dropped along with the nickname? How can we
> clean up the catalog to reflect reality? Any help appreciated.
>
> Pete H
>
PMR is the way to go. From what you describe you can't fix under your
own steam.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Did u try db2dart/inspect and drop the invalid/corrupted index using
db2dart.

Just in case as suggested by serge wait for their reply

regards
Shashi Mannepalli

Hi All;
Just to close the loop on this, we opened a PMR for this problem. In
FP10, any nickname built using 8.1 when dropped will leave this hanging
primary key constraint. Turns out it is fixed in FP11 (which is not an
option for us at this time). The work-around for FP10 is to create a
second nickname and an alias using the original name. This will meet
our needs for now.

Pete H