How to restore db2 backups cross Platform ( Windows to AIX issue shown )

           RESTORING  DB2  BACKUP FROM WINDOWS TO AIX. -STEP BY STEP

Since it is  a cross  platform  migration (Windows  to AIX  ) -a  DB2  backup  restore will not work.

By  using  DB2MOVE  AND  DB2LOOK we  can achieve  the  same  as  given   below.

I will show  you  a simple  example on how to do it step by step.

*****************************************************************
STEPS  AT  WINDOWS  MACHINE
*****************************************************************
 As  an  example  I  have taken   testdb  as  the  database  name


Steps 1.  Run the db2look command for  testdb  database to generate the DDL for the testdb database.
                              db2look -d testdb -e –l –x -o testdb_create.sql

Step2. Create a directory in which to store the logical backup of the source database testdb.
   mkdir backup

Step3. Change to the new directory, and create the logical backup using the db2move utility
cd backup

Step 4. db2move testdb  export   - here  by db2move   we  are  exporting data  of this  db.

Step 5.Transfer the backup of the database and DDL script from Source to Destination.
Transfer testdb_create.sql in ASCII mode, and all backup*.ixf files in binary mode.

******************************************************************************
STEPS  AT  AIX  OR LINUX  BOX
******************************************************************************
Step1.
Split the testdb_create.sql script into two scripts. The first is named testdb_tables.sql, and should contain all alias, table, and view creation commands.
The second script is to be named testdb_ri.sql, and picks up where the first stopped, including check and foreign key constraints and grants. Make sure that both scripts have a CONNECT command at the top, and COMMIT, CONNECT RESET, and TERMINATE commands at the end.
Edit the testdb_tables.sql and ensure that any user tablespaces path  are  according to the  filesystem.

Step 2.Create an empty   database

db2 create db testdb

Step3.  Run the testdb_tables.sql script to create aliases, tables, and views.
db2 –tvf testdb_tables.sql > testdb_tables.out

Step4. Review testdb_tables.out for errors. An easy method of doing this is to run the following command.

grep SQL0 testdb_tables.out|grep –v SQL0403W|grep –v SQL0598W|more

Step5.
Import the data to the tables (the INSERT option can be used instead of REPLACE, but REPLACE is rerunnable).
db2move testdb load -lo INSERT -l   /home/datamove  -This  is  the directory  which have all the   files collected in first step.

Step6.

Run the testdb_ri.sql script to create referential integrity constraints, etc.
db2 –tvf testdb_ri.sql > testdb_ri.out

Review testdb_ri.out for errors. An easy method of doing this is to run the following command.
grep SQL0 testdb_ri.out|grep –v SQL0403W|grep –v SQL0598W|more
Verify that the source and target databases are identical.

Regarding  tables with  identity columns  check this  link first

http://www.ibm.com/developerworks/data/library/techarticle/0205pilaka/0205pilaka2.html

Thanks

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.