Recreate Views in Dependency Order

[esmith2112]

I had hoped to use "db2look" to recreate the infrastructure for my database (10.5 on Linux) on a different server (DB2 on Cloud). I am having difficult getting to generate the DDL in correct dependency order for views and procedures. There is a -ct option which generates the objects according to creation time, but that fails for views that were dropped and re-created after their initial creation. I would like to have a clean build, especially since I'm going to have repeat this process several times.

I tried my hand at a recursive query using SYSCAT.VIEWS and ySYSCAT.VIEWDEP trying to make a hierarchical parent/child tree, but thus far have not succeeded. Has anyone else come up with a methodology? I saw that there zOS Db2 Admin tool that looks like it might handle this, but I couldn't find anything in the way of LUW databases.

Thanks for your help,

Evan

[Jeremy Rickard]

A recursive query is definitely an option.

Another thing you could try is to run the DDL repeatedly in a new database, until all objects are created. Then you could re-extract the DDL using db2look with the -ct option, hopefully then getting the objects in the desired order. Worth a go at least.

Jeremy