|
Problem IC77931
|
Status: Closed |
ORPHAN ROWS MAY BE FOUND IN SYSIBM.SYSINVALIDOBJECTS CATALOG TABLE (OR SYSCAT.INVALIDOBJECTS CATALOG VIEW) |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
When a database object (routine, view, trigger, user-defined
type or global variable) is invalidated and later dropped, it
may cause an orphan row to remain in the SYSIBM.SYSINVALIDOBJECT
catalog table.
To see if you have any orphan rows in SYSIBM.SYSINVALIDOBJECTS,
use the following queries:
with routines as (select routineschema, specificname,
routinemoduleid
from sysibm.sysroutines)
select objectschema, objectname, objectmoduleid from
sysibm.sysinvalidobjects
where not exists (select routineschema, specificname
from routines
where objectschema = routineschema
and objectname = specificname
and ((objectmoduleid =
routinemoduleid)
or (objectmoduleid IS NULL and
routinemoduleid IS NULL)))
and objecttype='F';
with views as (select creator, name
from sysibm.sysviews)
select objectschema, objectname from
sysibm.sysinvalidobjects
where not exists (select creator, name from views
where objectschema = creator
and objectname = name )
and objecttype='V';
with triggers as (select schema, name
from sysibm.systriggers)
select objectschema, objectname from
sysibm.sysinvalidobjects
where not exists (select schema, name from triggers
where objectschema = schema
and objectname = name )
and objecttype='B';
with udts as (select schema, name, typemoduleid
from sysibm.sysdatatypes
where metatype <> 'S')
select objectschema, objectname, objectmoduleid from
sysibm.sysinvalidobjects
where not exists (select schema, name from udts
where objectschema = schema
and objectname = name
and ((objectmoduleid = typemoduleid)
or (objectmoduleid IS NULL and
typemoduleid IS NULL)))
and objecttype='R';
with variables as (select varschema, varname, varmoduleid
from sysibm.sysvariables)
select objectschema, objectname, objectmoduleid from
sysibm.sysinvalidobjects
where not exists (select varschema, varname from
variables
where objectschema = varschema
and objectname = varname
and ((objectmoduleid = varmoduleid)
or (objectmoduleid IS NULL and
varmoduleid IS NULL)))
and objecttype='v'; |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* When a database object (routine, view, trigger, user-defined *
* *
* type or global variable) is invalidated and later dropped, *
* it *
* may cause an orphan row to remain in the *
* SYSIBM.SYSINVALIDOBJECT *
* catalog table. *
* *
* *
* *
* To see if you have any orphan rows in *
* SYSIBM.SYSINVALIDOBJECTS, *
* use the following queries: *
* *
* *
* *
* with routines as (select routineschema, specificname, *
* *
* routinemoduleid *
* *
* from sysibm.sysroutines) *
* *
* select objectschema, objectname, objectmoduleid from *
* *
* sysibm.sysinvalidobjects *
* *
* where not exists (select routineschema, *
* specificname *
* from routines *
* *
* where objectschema = *
* routineschema *
* and objectname = specificname *
* *
* and ((objectmoduleid = *
* *
* routinemoduleid) *
* *
* or (objectmoduleid IS NULL and *
* *
* routinemoduleid IS NULL))) *
* *
* and objecttype='F'; *
* *
* *
* *
* with views as (select creator, name *
* *
* from sysibm.sysviews) *
* *
* select objectschema, objectname from *
* *
* sysibm.sysinvalidobjects *
* *
* where not exists (select creator, name from views *
* *
* where objectschema = creator *
* *
* and objectname = name ) *
* *
* and objecttype='V'; *
* *
* *
* *
* with triggers as (select schema, name *
* *
* from sysibm.systriggers) *
* *
* select objectschema, objectname from *
* *
* sysibm.sysinvalidobjects *
* *
* where not exists (select schema, name from *
* triggers *
* where objectschema = schema *
* *
* and objectname = name ) *
* *
* and objecttype='B'; *
* *
* *
* *
* with udts as (select schema, name, typemoduleid *
* *
* from sysibm.sysdatatypes *
* *
* where metatype <> 'S') *
* *
* select objectschema, objectname, objectmoduleid from *
* *
* sysibm.sysinvalidobjects *
* *
* where not exists (select schema, name from udts *
* *
* where objectschema = schema *
* *
* and objectname = name *
* *
* and ((objectmoduleid = *
* typemoduleid) *
* or (objectmoduleid IS NULL and *
* *
* typemoduleid IS NULL))) *
* *
* and objecttype='R'; *
* *
* *
* *
* with variables as (select varschema, varname, *
* varmoduleid *
* from sysibm.sysvariables) *
* *
* select objectschema, objectname, objectmoduleid from *
* *
* sysibm.sysinvalidobjects *
* *
* where not exists (select varschema, varname from *
* *
* variables *
* *
* where objectschema = varschema *
* *
* and objectname = varname *
* *
* and ((objectmoduleid = *
* varmoduleid) *
* or (objectmoduleid IS NULL and *
* *
* varmoduleid IS NULL))) *
* *
* *
* *
* and objecttype='v'; *
****************************************************************
* RECOMMENDATION: *
* Upgrade to v97fp6. *
**************************************************************** |
| Local Fix: |
Contact DB2 support for the catalog clean-up tool |
| available fix packs: |
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 |
Fixed in v97fp6. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 04.08.2011
04.06.2012
04.06.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP6
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.6
|
 |
