How to copy schema objects to different schema within same database.

IMPORTING  DATA  WITHIN  SAME  DATABASE  TO  DIFFERENT SCHEMA

Sometimes due to Application  restrictions ,we need to have the tables and objects  within  schema,which cannot be changed.

But  what  if  the  schema is not present  in my new database .

Here  are  simple steps  to   do the same.

I.  Ist   make  a directory  in the local system and cd  to that  directory
   Now  run this  command

db2move  dbname export -sn schemaname  ----------This  will export  all data  in ixf file  in  current directory.
This is your  data repository.

2. Next   take the  objects  creation definition as below

db2look -d dbname -z originalschema -e -x -nofed -o SOURCE1.sql

this SOURCE1.sql  will  show all  original schema tables.

Open the  file  in  a editpad  and  do  a  replace of the originalschema  to   desiredschemaname

This will  be you  new schema creation file.

3. Now run this  in the  current database connection

db2 -tvf  SOURCE1.sql

this  will create  new schema  and  will create  all  the tables of earlier  schema  in new  schema.

4. Now  from the  files  created  in  step1  take the file  db2move.lst and  do the  change  as follows.
 you need  to REPLACE  the schema name  of each table  with new schema name. Open the file in editpad  and  replace the name of oldschema to  newschema.

This  will  help in mapping the  data  insertion  through  the  ixf  files.

5. Now  run  the  following   to  import  the  data  to new schema

db2move dbname  LOAD -lo INSERT -l   /home/datamove  -This  is  the directory  which have all the  files collected in first step.

Now after completion  you will have  all the  data and tables in new schema in  same database.

This  above  method  will  work  ,even if you have some  tables or objects  to  new target  schema .
**************************************************************************
There  is another  method  ,which  can work  db2  v9.7 onwards  and  also  presume  that there is  no existing  objects  in  new target  schema.

use   SYSPROC.ADMIN_COPY_SCHEMA
Here  is  the  inbuild  procedure  to do it.

db2 "CALL SYSPROC.ADMIN_COPY_SCHEMA('SOURCE_SCHEMA', 'TARGET_SCHEMA',     
      'COPY', NULL, 'SOURCETS1 , SOURCETS2', 'TARGETTS1, TARGETTS2,     
      SYS_ANY', 'ERRORSCHEMA', 'ERRORNAME') "

******************************************************************************

Few  command references

db2 "select  SCHEMANAME from  syscat.schemata"  -  This wll give  schema list. 

db2  set current schema schemaname - It will set the current schema. 

db2 drop  schema  schemaname  RESTRICT  - This will drop the schema.