0

I am in process of migration MaxDB database to DB2. I need to recreate all views, but their definitions contain references to other views and have the same create date and time, so I'm unable to create them in correct order.

Does the DB2 support somehow the CREATE FORCE VIEW statement?

How can I recreate the views in correct order (without creating SQL parser - because I have just String definition of views from Data Dictionary from MAXDB)?

SELECT for MaxDB:

select vd.*, t.createdate, t.createtime from viewdefs vd 
join tables t on vd.viewname = t.tablename and vd.owner = t.owner
order by t.createdate, t.createtime

MaxDB Data Dictionary

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Michal Joštiak
  • 161
  • 9

1 Answers1

3

You don't indicate what DB2 platform you're using. DB2 for LUW has the database configuration parameter auto_reval, which, when set to deferred (default) or deferred_force, allows you to create dependent objects in any order. With deferred_force in effect objects (including views) that cannot be validated at creation time because of missing dependencies will be created "with error" and revalidated when they are first used in a SQL statement.

You can also explicitly revalidate all invalid objects after you create them by calling the system stored procedure SYSPROC.ADMIN_REVALIDATE_DB_OBJECTS().

mustaccio
  • 18,234
  • 16
  • 48
  • 57