0

I exported an entire DB2-Luw DB's DDLs with db2look in order to prepare for a Postgres migration, however db2look seems to write the DDLs of the view in an incorrect order, so that some vies might reference other views which are defined later in the script. To give an idea of the scenario I'm facing: here's an example:

CREATE VIEW view1 AS
SELECT column1, column2 FROM table9;

CREATE VIEW view3 AS
SELECT column3, column4 FROM view2;

CREATE VIEW view2 AS
SELECT column5, column6 FROM table6;

As you can see view2 is needed by view3 but view3 gest created before view2, therefore view3 cannot be created. Obviously the situation is much more complicated than that, as I'm dealing with roughly 500 views that need to be created (I already created all the tables) and a manual reorder of the SQL script seems like a nightmare. I have two question:

  1. What's the ordering criteria in which db2look exports the DDLs?
  2. Can I do something to tell db2look to change this criteria to keep in consideration the hierarchy of the views?

Thank you very much

1
  • I've experienced this my self. I ended up using SYSCAT.TABDEP etc (all relevant *DEP tables), to build a graph in python and sort it with toposort. Then I parsed the catalog and created ddl for the objects in the correct order. If views are your only problem the view definition can be found in SYSCAT.VIEWS.TEXT. It would be nice if db2look did this for us Commented Aug 23, 2023 at 8:15

1 Answer 1

0

You may try the -ct parameter of the db2look utility.

-ct
Generates DDL statements by object creation time. The object DDL statements might not be displayed in correct dependency order.

This may help, but note the last statement cited. There is an ability to create objects in Db2 referring non-existing objects depending on the auto_reval configuration parameter setting.
So, if someone created objects in your Db2 database in a wrong order, then this doesn't help...

1
  • Thank you Mark, in the meantime I solved simply by running the DDL file multiple times until these errors disappeared. Kind of a sketchy solution but it worked.
    – Nic Can
    Commented Jul 13, 2023 at 10:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.