IBM Support

IC77931: ORPHAN ROWS MAY BE FOUND IN SYSIBM.SYSINVALIDOBJECTS CATALOG TABLE (OR SYSCAT.INVALIDOBJECTS CATALOG VIEW)

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error 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';
    

Local fix

  • Contact DB2 support for the catalog clean-up tool
    

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.                                           *
    ****************************************************************
    

Problem conclusion

  • Fixed in v97fp6.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC77931

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-08-04

  • Closed date

    2012-06-04

  • Last modified date

    2012-06-04

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC77931

Modified date: 04 June 2012