db2look from a DB2 10.5 to a 9.7 database: how to successfully apply the SQL
Norberto 2000000SR8 Visits (2080)
Recently I needed to restore a backup taken on DB2 10.5 into a server where I was not able to install that same version (due to OS pre-reqs not met). As I already had DB2 v9.7 there - and I was not in a position where I could have the OS upgraded - I had to use db2move instead.
I then ran db2move <dbname> export on the source server, added the dir to a .tar file, gzipped it and transferred (I was lucky it took just few gigabytes, and the network was reasonably fast). I also took a db2look -d <dbname> -a -l -e -x -c > db2look.sql. After that, it occurred to me that the new clauses in many SQL commands such as CREATE TABLE and so on would bug me while creating the database objects. Well, I was right...
After creating the target empty database, I issued the first try of db2 -stvf db2look.sql. As I used -s, DB2 stopped execution as the first error occurred. DB2 complained about the following command:
As I know this is a new feature introduced in v10.1, I just commented that command in the file. I then dropped the database, recreated it and reissued the command (after connecting to it, of course). Then I got another error on the following command:
I then noticed that DATA TAG clause had to be omitted too, so I removed all occurrences of it. (Did it manually as there were just a few.)
Running it (the saved SQL file) again over a new empty database, I kept getting different errors, and acting on all them - by removing SQL clauses that were not known by v9.7. The below is a list of clauses I had to adjust (you might find more than I had, but I'm sure most of them are here):
The lines above are vi commands to search and replace text (replace what's inside the first slash pair by what's in the second slash pair).
After changing your db2look.sql file following these instructions, you should be good to run it smoothly on your DB2 9.7 database.