DB2Move import issue

mohd · #1 (**permalink**) 02-16-05, 11:27

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

n_i · #2 (**permalink**) 02-16-05, 11:42

It's rather in step 2 - you would want to create RI constraints after importing data, not before.

mohd · #3 (**permalink**) 02-16-05, 12:02

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

Marcus_A · #4 (**permalink**) 02-16-05, 12:09

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.

mohd · #5 (**permalink**) 02-16-05, 12:29

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

n_i · #6 (**permalink**) 02-16-05, 14:38

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' 
;

Internet Services

Web Development

Digital Marketing

Consumer Tech