Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    23

    Unanswered: copying subset of tables from one database to another

    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

  2. #2
    Join Date
    Jul 2001
    Location
    Bangalore, India
    Posts
    28
    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

  3. #3
    Join Date
    Jan 2004
    Posts
    23
    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
    _____________________

    Originally posted by Ravi
    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

  4. #4
    Join Date
    Jan 2004
    Posts
    23

    Problem with specifying table creator

    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

  5. #5
    Join Date
    Jul 2001
    Location
    Bangalore, India
    Posts
    28
    '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

  6. #6
    Join Date
    Jan 2004
    Posts
    23

    Any way to select a Schema

    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.

  7. #7
    Join Date
    Jul 2001
    Location
    Bangalore, India
    Posts
    28
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •