"Cannot use view 'v1' because it is invalid." error.

[jong-kil_park]

I'm going to share with you about an error below happened when querying against a view.

It happened on the IQ 16.0 SP10 PL08 and the reproduction scenario is as below.

==============================================================

T1. Creation of table & view

create table jerry(a int, b int, c int );

insert jerry values(1,1,1);commit;

create view v1

as

select * from jerry ;

T2. Alter the schema of the referenced table.

alter table jerry rename jerry22 ;  // The view becomes invalid.

T3. Run a query against the View & recreate the referenced table.

select * from v1 ;

create table jerry(a int, b int, c int );

select * from v1 ; /* the errro keeps happening */

==============================================================

It's a behavior change in IQ 15.x and IQ 16.0.

In IQ 12.7, this error doesn't happen after recreating the referenced object.

If a view is dependent on a schema object that has been modified, and the view subsequently fails to recompile, the status of that view remains INVALID

Under the situation, if a query is run against the view before the referenced object is recreated, the "Cannot use view 'v1' because it is invalid." error happens.

You can identify the invalid views by issuing a query below.

select U.user_name ||'.'|| T.table_name

from sysobject O, systab T, sysuser U

where T.object_id = O.object_id

and U.user_id = T.creator

and O.status <>1 /* Invalid */

and O.object_type = 2 /* views */;

Also, you can solve this error by recompiling the invalid views as below.

ALTER VIEW <VIEW> RECOMPILE ;

Please refer to the manual pages below explaining this behavior.

http://dcx.sap.com/index.html#sa160/en/dbadmin/sa11c60dfe-6ca7-4344-914e-ba1b735a618f.html

http://dcx.sap.com/index.html#sa160/en/dbusage/workingwdb-s-6042429.html

Jerry