Hi,
I find much regrettable that SYSCAT.INVALIDOBJECTS has 'F' for objectype, meaning "routine", when ADMIN_REVALIDATE_DB_OBJECTS has object_type 'Function' or 'Procedure'.
Can't you be simple and support both 'F' and 'P' so that we would logically call in scripts ADMIN_REVALIDATE_DB_OBJECTS with correct object_type, or support 'Routine' in this procedure ?
Best regards, JMB
db2 -tvf testinvalid
create table jmb (f1 int)
DB20000I The SQL command completed successfully.
create procedure pjmb
begin
delete from jmb where f1=1;--
end
DB20000I The SQL command completed successfully.
drop table jmb
DB20000I The SQL command completed successfully.
select objectname, objecttype, routinename from syscat.invalidobjects where routinename='PJMB'
OBJECTNAME OBJECTTYPE ROUTINENAME
-------------------------------------------------------------------------------------------------------------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------
SQL171024092809939 F PJMB
1 record(s) selected.
call pjmb
SQL0727N An error occurred during implicit system action type "3".
Information returned for the error includes SQLCODE "-204", SQLSTATE "42704"
and message tokens "PPSSYS.JMB". LINE NUMBER=3. SQLSTATE=56098
select objectname, objecttype, routinename from syscat.invalidobjects where routinename='PJMB'
OBJECTNAME OBJECTTYPE ROUTINENAME
-------------------------------------------------------------------------------------------------------------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------
SQL171024092809939 F PJMB
1 record(s) selected.
CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('function', 'PPSSYS', NULL)
Return Status = 0
CALL SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS('procedure', 'PPSSYS', NULL)
SQL20482N Revalidation failed for all objects that were specified to be
revalidated. One object, "PPSSYS .SQL171024092809939", could not be
revalidated because it references object "PPSSYS.JMB". SQLSTATE=429C4
select objectname, objecttype, routinename from syscat.invalidobjects where routinename='PJMB'
OBJECTNAME OBJECTTYPE ROUTINENAME
-------------------------------------------------------------------------------------------------------------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------
SQL171024092809939 F PJMB
1 record(s) selected.