We have a problem with one specific procedure which often gets invalidated for unknown reasons. If the procedure is called from ISQL the message reported is "Procedure 'xxxx' is no longer valid".

When this status is reported, we usually call "ALTER PROCEDURE xxxx RECOMPILE", then everything is fine for some time.

I can't see things in this procedure which we don't do the same way in other procedures, which do not give us these troubles. Things we are using only occasionally include:

• The code selects a remote view into a local temporary table
• In the result set of the procedure one column is declared using the %TYPE clause

So the procedure looks like this:

CREATE PROCEDURE "MyUser"."PRC_MyProcedure"
(IN MyParameter integer)
RESULT
(
  ResultCode INTEGER,
  MyCategory MyUser.LocalTable.MyCategory%TYPE
)
BEGIN
  DECLARE LOCAL TEMPORARY TABLE MyResult
  ( 
    ResultCode INTEGER,
    MyCategory MyUser.LocalTable.MyCategory%TYPE
  );

  INSERT INTO MyResult(ResultCode, MyCategory)
    SELECT RemoteResult, RemoteCategory FROM MyUser.RemoteView;

  SELECT * FROM MyResult;
END

The first question is, what can get such a procedure into an invalid status?

The second question is, how can we detect that this problem has happened (beside calling the procedure end encountering an error)?

For views we use a statement like

  SELECT U.user_name + '.' + T.table_name cName
    FROM sysobject O, systab T, sysuser U 
   WHERE T.object_id = O.object_id 
     AND U.user_id = T.creator  
     AND O.status = 2      /* status=2 - Invalid */   
     AND O.object_type = 2 /* views */

to detect invalid views for recompilation, but the procedure in question does have status 1 in the SYSOBJECT table, like all other procedures, even when the error message is shown.

Is this intended? Are there any alternatives?