Grant/revoke and SP reoptimization - seeking information (repost)

Can granting or revoking connection privileges result in the flagging of stored procedures for reoptimization? Can anybody tell me if there is documentation on the specific auth (table and db-level) changes that will flag a stored procedure for reoptimization? (Informix 7.31, AIX)

An SP will reoptimize of one of the tables referenced in it is altered and under certain circumstances if you add or drop an index on such a table or update statistics on it. Note that there are certain conditions whichprevent the engine from detecting table dependencies (see the performance guide). You can force the reoptimization of an SP anytime you want using: UPDATE STATISTICS FOR PROCEDURE procname; Art S. Kagel Russ wrote: > > Can granting or revoking connection privileges result in the flagging of > stored procedures for reoptimization? > > Can anybody tell me if there is documentation on the specific auth (table > and db-level) changes that will flag a stored procedure for reoptimization? > > (Informix 7.31, AIX)

Thanks, but I'm interested specifically in authorization changes that would trigger reoptimization (in order to avoid such triggering when db is 'online'). This triggering first came to light about a year ago. I was granting 'select' auths to a member of our prod support team and ended up with a flood of catalog locking errors in our online apps (1,500 users, extensive use of stored procedures). The cause of the locking was that the auth change had flagged several stored procedures for reoptimization. I thought this was a little strange as I couldn't recall this having happened before and I didn't think it was a very logical way for the dbms to behave (stored procedure can be created and optimized without even checking that a table exists, yet it is flagged for reoptimization when a table auth is changed? Even if dependencies were thoroughly tracked in the dbms, we use DBA procedures so specific table auths are irrelevant). I called Informix Support about this and got someone who initially sounded like they were thinking, "Oh, is that what happens?" but who eventually said it was a feature (ex-Microsoft employee, obviously) and said if I wanted the dbms to behave any differently he'd put (bury) it somewhere in the "Enhancements Request List". The Support Line had no other helpful information to offer - Couldn't even tell me if adding somebody to a role that had access to a table would flag stored procedures for reoptimization. The goal of this line of investigation is to determine what types of authorization changes I can implement while our online systems are available and what types should be restricted to "off-hours". >Russ wrote: >> >> Can granting or revoking connection privileges result in the flagging of >> stored procedures for reoptimization? >> >> Can anybody tell me if there is documentation on the specific auth (table >> and db-level) changes that will flag a stored procedure for reoptimization? >> >> (Informix 7.31, AIX)