0

I'm migrating a database from DB2 10.1 for Windows x86_64 to DB2 10.1 for Linux x86_64 - this is a combination of operating systems and machine types that have incompatible backup file formats, which means I can't just do a backup and restore.

Instead, I'm using db2move to backup the database from Windows and restore it on Linux. However, db2move does not move the materialized query tables (MQTs). Instead I need to use db2look. This poses the challenge of finding a generic method to handle the process. Right now to dump the DDLs for the materialized queries I have to run the following commands:

db2 connect to MYDATABASE
db2 -x "select cast(tabschema || '.' || tabname as varchar(80)) as tablename from syscat.tables where type='S'"

This returns a list of MQTs such as:

MYSCHEMA.TABLE1
MYSCHEMA.TABLE2
MYOTHERSCHEMA.TABLE3

I can then take all those values and feed them into a db2look to generate the DDLs for each table and send the output to mqts.sql.

db2look -d MYDATABASE -e -t MYSCHEMA.TABLE1 MYSCHEMA.TABLE2 MYOTHERSCHEMA.TABLE3 -o mqts.sql

Then I copy the file mqts.sql to the target computer, which I've previously restored all the non-MQTs, and run the following command to restore the MQTs:

db2 -tvf mqts.sql

Is this the standard way to migrate a MQT? There has got to be a simpler way that I'm missing here.

Pridkett
  • 4,883
  • 4
  • 30
  • 47

2 Answers2

2

db2move is mainly to migrate data, and things related to that data, for example the DDL of each table, etc. db2move does not even migrate the relation between tables, so you have to recreated them with the ddl.

Taking the previous thing into account, an MQT is just a DDL, it does not have any data. The tool to deal with DDLs is db2look, and it has so many options to extract exactly what you want.

The process you indicated is a normal process to extract that DDL. However, I have seen more difficult processes than yours, dealing with DDLs and with db2more/db2look; yours is "simple".

Another option is to use Data Studio, however you cannot script that.

AngocA
  • 7,655
  • 6
  • 39
  • 55
0

I believe what you are doing is right because MQTs do not have data of their own and are populated from the base tables. So the process should be to migrate data into the base tables which the MQT is referring and then simple create/refresh the MQTs.

Saurabh Agrawal
  • 1,355
  • 3
  • 17
  • 33