If we have a view over another view and we drop th eparent view what happens to the other vie

Question

If we have a view over another view and we drop th eparent view what happens to the other view and if we recreate teh base view wil the second view be active again

Answer 1

From my understanding of views in DB2 (and SQL in general), they act effectively as aliases to SQL select statements. I would expect that in this situation, your child view would still exist, but querying it when the parent was deleted would result in an error.

Since the definition of the child view is stored and remains static, recreating the parent view with the same object names should result in the child view returning the expected result again.

This would be very easy for you to verify yourself, by the way. :-)

Answer 2

Probably the same thing that happens in this question.

Answer 3

Michael Sharek's response is correct; the remaining views that depended on the view that was dropped will remain invalid (VALID='N' for that row in SYSCAT.VIEWS) even after the dropped view is replaced. You will need to reissue the create statements for any view in SYSCAT.VIEWS where VALID='N', but the good news is that you can overwrite an invalid view without dropping it.

What I typically do is use EXPORT to extract a copy of the TEXT column of every view in SYSCAT.VIEWS where VALID = 'N'. Then I execute the DDL statements in that file and the invalid views are all typically replaced on the first pass. However, if you have a more sophisticated hierarchy of interdependent views, you may need to run the file a couple more times. There's no need to filter out the DDL for the views that were made valid during a previous pass; those statements will be safely rejected with a duplicate object error.