Toad DB2 LUW doesn't show the list of invalid objects on a database from the Object Explorer | Database | Invalid Objects.
RESOLUTION:
1. Please run the following command to make sure you get data returned.
SELECT * FROM SYSCAT.INVALIDOBJECTS
2. If no data shows up, check the SYSCAT.INVALIDOBJECTS script to make sure that it has the same objects as the following correct script lists:
SET CURRENT SCHEMA = SYSIBM;
SET CURRENT PATH = SYSIBM,SYSFUN,SYSPROC,SYSIBMADM;
CREATE OR REPLACE VIEW SYSCAT.INVALIDOBJECTS
( OBJECTSCHEMA, OBJECTMODULENAME, OBJECTNAME, ROUTINENAME, OBJECTTYPE,
SQLCODE, SQLSTATE, ERRORMESSAGE, LINENUMBER, INVALIDATE_TIME,
LAST_REGEN_TIME )
AS
select
i.objectschema, m.modulename, i.objectname,
case when i.objecttype = 'F' then r.routinename else null end,
i.objecttype, i.sqlcode, i.sqlstate,
cast(case when i.sqlcode is not null then
case when i.sqlcode < 0
then substring(SYSPROC.SQLERRM (concat('SQL', substring(varchar(i.sqlcode),
2, octets)), i.sqlerrmc, x'FF', '', 1), 1, 4000, octets)
else substring(SYSPROC.SQLERRM (concat('SQL', varchar(i.sqlcode)), i.sqlerrmc,
x'FF', '', 1), 1, 4000, octets) end
else null end as varchar(4000)),
i.linenumber, i.invalidate_time, i.last_regen_time
from sysibm.sysinvalidobjects i
left outer join sysibm.sysroutines r on (i.objectname = r.specificname and
i.objectschema = r.routineschema and (i.objectmoduleid = r.routinemoduleid or
(i.objectmoduleid is NULL and r.routinemoduleid is NULL)))
left outer join sysibm.sysmodules m on i.objectmoduleid = m.moduleid
WITH NO ROW MOVEMENT;
GRANT SELECT ON SYSCAT.INVALIDOBJECTS TO PUBLIC;
SET CURRENT SCHEMA = DB2ADMIN;
If the script is different, please consult with IBM. It seems in DB2 9.7 there is a configuration parameter that is disabled when upgrading from DB2 9.5 to 9.7. The parameter is auto_reval. It must be set to IMMEDIATE, DEFERRED or DEFERRED_FORCE in order for the SYSIBM.SYSINVALIDOBJECTS table to be populated. Only objects that become invalid after that parameter is set will be populated in the catalog table.
