I have a database with a set of tables. A subset of these tables are related in the sense that some of the columns in one table are foreign keys for other tables in this subset.

What are some of the ways to copy this subset of tables from one database to another database on the same installation so the referential integrity is still maintained between them?

Thanks for your help

You can use db2look and db2move on the identified tables (your subset of tables) to replicate them on to another database with all the referential integrities.
For ex: if you have two tables T1 and T2 under the schema S1, then use the following command to generate the ddl,
db2look -d dbname1 -z S1 -e -t T1 T2 -o c:\T1_T2.ddl

then use the db2move utility to export the data of these two tables,
db2move dbname1 export -tc S1 -tn T1,T2

To replicate the structure on the second database,
db2 -tvf c:\T1_T2.ddl (to create the structure)

db2move dbname2 load (to load the data)

db2 set integrity for T2 immediate checked (to set the referential integrity on the table T2 which has the foreign key)

Hope this helps...

Ravi

Thanks very much for your reply.

db2look seems to work fine. But, even though I don't get any errors, exporting the data creates a db2move.lst file with zero size. What am I doing wrong? Here is the output:

________________
C:\db2Exercise>db2move snmasg1 export -tc MED1 -tn Procedure,Anatomic,ProcAnat

***** DB2MOVE *****

Action: EXPORT

Start time: Tue Jan 06 11:01:14 2004


Exporting tables created by: MED1;

All table names beginning with: PROCEDURE; ANATOMIC; PROCANAT;

Connecting to database SNMASG1 ... successful! Server: DB2 Common Server V7.2.0


Disconnecting from database ... successful!

End time: Tue Jan 06 11:01:14 2004


C:\db2Exercise>dir
Volume in drive C has no label.
Volume Serial Number is 70D5-7C79

Directory of C:\db2Exercise

01/06/2004 10:48a <DIR> .
01/06/2004 10:48a <DIR> ..
01/06/2004 10:49a 3,993 paprocanat.ddl
01/06/2004 11:01a 0 db2move.lst
01/06/2004 11:01a 278 EXPORT.out
3 File(s) 4,271 bytes
_____________________

It seems like the problem is in the way I am specifying the -tc option!

I have two schemas in this database, MED1 and SN1. The data is exported for all tables, successfully, for both schema if I don't specify the -tc option or specify "-tc *". But nothing gets exported if I specify any one of the schema, thus: "-tc MED1" or "-tc SN1".

Is table creator not the same as Schema? How can I find out what value to use for the -tc option?

Thanks

'CREATOR' is actually the user who has defined the table. For ex, if USER1 creates a table SCHEMA1.TABLE1, then USER1 will be the creator and SCHEMA1 will be the schema for the table TABLE1. You can find out the creator of a table from the column DEFINER in the SYSIBM.SYSTABLES table.

Ravi

Thank you very much for your help. That works!

Is there a way to limit db2move to tables in a specific schema? I have two schemas in the same database and one may need to limit to one or more schemas.

I don't see an option for that in db2move, and specifying SCHEMA.TABLENAME doesn't work.

DB2MOVE has an option to export all tables under specific schema. Try the following,

db2move sample export -sn schema1,schema2 -tn *

This exports all tables under schema SCHEMA1 and SCHEMA2

HTH

Ravi