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 <db>; 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;
db2look -d <db> -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.