• Recreate Views in Dependency Order

    From esmith2112@21:1/5 to All on Tue Apr 30 14:03:19 2019
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jeremy Rickard@21:1/5 to All on Sat Jun 1 15:37:59 2019
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)