Automated Re-creation of Select-Star Views

If a view is created by using a 'SELECT *' statement, the view is not updated when a new column is added to the base table. An example:

create table dm_tab(N integer, M integer) organize by row;

insert into dm_tab(N, M) values (1, 2);

create view dm_vw1 as select * from dm_tab;

commit;

alter table dm_tab add column K integer default 3;

commit;

-- The new column K is not part of the view:

select * from dm_vw1;

N M

- -

1 2

This means that we need to re-create our views if an underlying table has changed. Because we have more than 30,000 views along with table alterations on a regular basis in our software release life cycle, we ask you to introduce a feature to automate the re-creation of the affected views.

The feature could be implemented in DB2 via an additional procedure similar to the ADMIN_REVALIDATE_DB_OBJECTS procedure. Alternatively it could be embedded in SQL via a 1st level construct of the language like for example a new optional clause in the CREATE VIEW statement.

Please notice that the feature which we are asking for would correspond to the standard behavior of Oracle databases.

Needed By

Quarter

Post comment

Guest

Reply
| Apr 20, 2023

Have you considered using "create alias" in this case?

0 reply Hide replies

By clicking the "Post Comment" or "Submit Idea" button, you are agreeing to the IBM Ideas Portal Terms of Use.
Do not place IBM confidential, company confidential, or personal information into any field.

Please enter your email address

RELATED IDEAS

Automated Re-creation of Select-Star Views