1

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;

running

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.

  • I often use db2look -d -a -l -e -o -td “@" that has worked for me. – Chris Aldrich Apr 5 '17 at 12:56
  • Thanks, but that outputs objects in the wrong order for the db in question. I suspect that some of the problem is due to the db being created 2005 and that it had regular schema changes ever since. – Lennart Apr 5 '17 at 13:33
  • Does setting the AUTO_REVAL database configuration parameter to DEFERRED_FORCE solve your problem? – Ian Bjorhovde Apr 7 '17 at 22:47
  • @Ian, not really. That only helps for some dependency violations. I actually prefer running in an all or nothing fashion ( db2 +c -s ... ) so that I can safely say that everything is recreated correctly. – Lennart Apr 9 '17 at 10:14
1

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.

  • Thanks for you answer. The situation is a bit more complicated (there are for example tables that are dependent of functions and vice versa). I'll probably create a parser that sort the output from db2look in topological order using dependency tables. – Lennart Apr 12 '17 at 9:01
0

I have used following approach to copy production schema to test servers for developers. Trick is to ignore errors during individual runs and compare schema at the end to make sure everything is recreated correctly. I know it is not a clean approach. I have done this against schema with over 1000 tables, 500 functions with dependencies, constraints, etc.. You can automate the steps using shell script. It requires lot less effort compared to writing parser and testing it.

Step 1 Use script to extract schema from production server in specific order (db2look for tablespaces first, db2look for tables next, etc..). Using specific order reduces number of iterations for step 2 & 3.

Step 2 Run output from step 1 on test server

Step 3 Extract schema from test server and compare it with schema from production server

Step 4 Repeat step 2 and 3, until both schema are in synch.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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