Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2003
    Posts
    63

    Unanswered: DB2Move import issue

    Hi
    I am using DB2 V8.1 on solaris.

    I want to move schema from one databse to another databse under diff server


    Here is what i did.

    1. on source system ( to get the struc of all the schema objects)
    db2look -d sample -e -z db2inst1 -x -o sch_struc_db2inst1.out -u db2inst1

    2. On target ( to create the struct of schema)
    db2 -tvf c:\sch_struc_db2inst1.out

    3. on source ( to get the tables data )
    Db2move sample export -sn db2inst1

    4. on target ( to import the table data )
    DB2move sample import -io replace

    in step 4 it has imported the tables which doesnt have any dependent tables
    but for the master table it has not imported the data giving error :

    SQL3201N The specified table cannot be replaced because another table is
    dependent on it.

    what i am missing in step 4?

    will appreciate quick response
    thx
    Mohd

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It's rather in step 2 - you would want to create RI constraints after importing data, not before.

  3. #3
    Join Date
    Jul 2003
    Posts
    63
    Thx for the quick reply,

    But i have created the whole schema struc from the output got by db2look comand.

    Is there any option in db2look to avoid getting referential constraints while getting whole schema and also to get the constraints alone aftewards to create it.?

    Is it the correct way i am following to move schema from one database to another or is there any other way we can do it.

    Thx
    Mohd

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The Foreign Keys are all in a seperate section of the db2look output, which you should be able to cut and paste into a separate script and run after the load.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jul 2003
    Posts
    63
    Thx Marcus,

    I got it.

    Is there any easy way out as i have to do it very frequently.
    something like in oravcle we give constraints=no to avoid constraints.

    Thx
    Mohd

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    These two queries should produce scripts to drop and re-create foreign keys.
    Code:
    SELECT 
    	'ALTER TABLE schema.' || SUBSTR(TABNAME,1,50), 
    	' DROP FOREIGN KEY ' || CONSTNAME || ';' 
    FROM 
    	SYSCAT.REFERENCES 
    where 
    	tabschema = 'schema'
    ;
    
    SELECT 
    	'ALTER TABLE schema.' || SUBSTR(TABNAME,1,50), 
    	'ADD CONSTRAINT ' || CONSTNAME, 
    	'FOREIGN KEY (' || substr(FK_COLNAMES,1,50) || ')', 
    	'REFERENCES schema.' || SUBSTR(REFTABNAME,1,17),  
    	' ON DELETE ' || 
    		case deleterule
    		  when 'A' then 'NO ACTION' 
    		  when 'C' then 'CASCADE' 
    		  when 'N' then 'SET NULL' 
    		  when 'R' then 'RESTRICT'
    		end, 
    	' ON UPDATE ' || 
    		case updaterule
    		  when 'A' then 'NO ACTION' 
    		  when 'R' then 'RESTRICT'
    		end 
    	FROM 
    		SYSCAT.REFERENCES 
    	where 
    		tabschema = 'schema' 
    ;

Posting Permissions

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