Using db2look to mimic a database? I'm in the process of migrating some databases from iso8859-1 to utf-8. One of the databases contain 1000+ tables, loads of procedures, functions, triggers, constraints etc. I would like to automate the process as much as possible since this is going to happen several times, and for several instances of the system. Ideally I would like to hand over the work to my friends Jenkins and Ansible ;-)

My plan was to generate the ddl with db2look, but it fails to generate the "objects" in correct order. I tried running both with and without the -ct flag (fails for different reasons). For example:

connect to &lt;db&gt;;
create table t1 (x int not null);
create unique index t1pk on t1 (x);
alter table t1 add constraint t1pk primary key (x);
connect reset;


running 

db2look -d &lt;db&gt; -e -td @ -ct


generates the wrong order for the index and the constraint. 

CREATE TABLE "DB2INST1"."T1"  (
              "X" INTEGER NOT NULL )   
             IN "USERSPACE1"  
             ORGANIZE BY ROW@ 

ALTER TABLE "DB2INST1"."T1" 
    ADD CONSTRAINT "T1PK" PRIMARY KEY
            ("X")@



CREATE UNIQUE INDEX "DB2INST1"."T1PK" ON "DB2INST1"."T1" 
            ("X" ASC)
            COMPRESS NO 
            INCLUDE NULL KEYS ALLOW REVERSE SCANS@


Removing -ct works in this trivial example, but fails in the actual database due to other dependencies.

I imagine migrating to UTF-8 is a quite common task so I'm curios on what people have done. The two possible solutions I see is to:

a) write a parser that inspects the catalog for database
   objects and sort them topologically using dependency tables.

b) write a parser that reads the output from db2look, identify
   each object and sort them topologically using dependency tables.


Clearly there are drawbacks with both of these, am I overlooking some trivial way to migrate the databases?

EDIT: An additional observation is that as long as an index does not contain any extra attributes compared to the implicitly created index through a unique / primary key constraint a QL0605W warning is raised. If on the other hand additional attributes are specified a SQL0601N error is raised. Example:

CREATE UNIQUE INDEX X1 ON T1 (C1, C2) 
COMPRESS NO 
INCLUDE NULL KEYS 
DISALLOW REVERSE SCANS


generates a warning SQL0605W if a similar index is created via primary key statement.

On the other hand an index like:

CREATE UNIQUE INDEX X2 ON T2 (C1, C2) 
INCLUDE (C3) 
CLUSTER 
COMPRESS NO 
INCLUDE NULL KEYS 
ALLOW REVERSE SCANS


generates an error SQL0601N. I assume this is due to the INCLUDE clause, but perhaps the CLUSTER clause will cause this behavior as well.
Few years ago we migrated our DB2 from 9.7 to 10.5. We also moved the database to new hardware and implemented data compression. Because of this we decided to create the database from scratch and export and import the data. 

We used db2look and db2move to do the job. However, there was no way to get the DDL created by db2look in the correct order. We had to split the generated script into different parts for creating tables, creating triggers, creating indexes, etc. 

Finally we end up with the following steps: 


export the existing data
create the new database
create bufferpools and tablespaces
create tables
create primary keys
create indexes
create views
load data
run reorgs and runstats
create stored procedures, user defined functions and triggers


I hope it helps, even it is not the answer you may have expected.