How to edit a view in DB2 IBM iseries instead of deleting it and then creating it again?

How to change or edit a view in DB2 through System i Navigator? I do not know how, since it removes other views built on it.

+4
source share
1 answer

There is no way to change the view using iSeries Navigator. However, if you are open to using the SQL statement and you are on v7.1 or higher, you can use the new "CREATE OR REPLACE VIEW" command: SQL CREATE or REPLACE .

This allows you to modify existing views without requiring premature deletion of dependent objects. For example, consider a view named "VIEW2" that consumes a column named "DESC" from "VIEW1". You can add a column to "VIEW1" without affecting "VIEW2" using the following command:

CREATE OR REPLACE VIEW MYSCHEMA.VIEW1 AS SELECT DESC, NEWCOL FROM MYSCHEMA.MYTABLE; 

Similarly, you can remove a column from "VIEW1" without affecting other views. The only limitation is that you cannot delete a column that is being used by another view (or function, alias, etc.). If you try to delete a column that another view depends on, you will receive an error message.

Using the REPLACE method will also save any security permissions / restrictions.

It should be noted that a more ideal method would be to automate the creation of views / functions / procs / aliases / etc through scripts. This will allow you to sequentially and sequentially drop and recreate dependent objects. This involves some automation around database management and can be problematic if you have a very large number of dependent objects (if you have a large number of indexed views.) The biggest risk with a lot of REPLACE is that they will be applied incorrectly or not using one of the scripts, or applying them in the wrong order.

+4
source

All Articles