This article will answer following questions.

1. How to move DB2 database from one operating system to other?
2. How to create database for which the structure creation file (db2look) is not there?
3. How to move only few tables from one database to other?
4. What is way for O/S independent backup in db2?
5. How to change path of tablespaces while restore? (not using redirect restore)

This utility of DB2 create operating system independent backup. It will create one data file for each table you have in your database. This is easiest method for remote backup and restore of database for small size database. It is very useful for the organizations which are working on development area. The situation over here is you need to backup one database on one location and needs to deploy on other location.  Suppose you have development going on using a server which is dedicated for development team. QA team needs database which is running at client end, so as they can test issues as per client environment. Here the operating system needed is not available, so this is very useful tool provided by DB2.

We will look into one by one how to move database using this utility to external files. When this tool is used with EXPORT/IMPORT/LOAD mode, facilities the data movement in PCI/IXF files. There are many options for schema, copy schema and other. This article will touch some of them.

This article will provide commands which need to be executed for moving database with some of screen shot in this. Let’s start the activity with exporting database into ixf files for each table.

All these exercises will done using sample database, which is created after installation, using command

                                          bash-3.00$ db2sampl

                                          Creating database “SAMPLE”…

We will export this database to external files, using following commands.

                                          bash-3.00$ db2move sample export

We can see following output which is written on screen.

                                          Action:  EXPORT

                                         Start time:  Wed Feb  8 13:07:54 2012

                                        Connecting to database SAMPLE … successful!  Server : DB2 Common Server V9.7.5

                                       EXPORT:     18 rows from table “DBAV97 “.”ACT”

                                       EXPORT:      0 rows from table “DBAV97 “.”CATALOG”

                                      EXPORT:      5 rows from table “DBAV97 “.”CL_SCHED”

                                      EXPORT:      6 rows from table “DBAV97 “.”CUSTOMER”

                                     EXPORT:     14 rows from table “DBAV97 “.”DEPARTMENT”

                                     EXPORT:     42 rows from table “DBAV97 “.”EMPLOYEE”

                                    EXPORT:  10000 rows from table “DBAV97 “.”EMPMDC”

                                                           ~~~~~~~

                                     EXPORT:     35 rows from table “DBAV97 “.”STAFFG”

                                    EXPORT:      2 rows from table “DBAV97 “.”SUPPLIERS”

                                    Disconnecting from database … successful!

                                     End time:  Wed Feb  8 13:08:00 2012

Where last few lines you will come to know the export command have successfully exported all the needed tables to external files. If you want to create new database on same server or identical server you can use following command.

We will check what kind of files which got exported using this command.

                                  -rw-r–r–    1 dbav97  db2iadm        1585 Feb 08 13:08 EXPORT.out

                                 -rw-r–r–    1 dbav97  db2iadm         946 Feb 08 13:08 db2move.lst

                                 -rw-r–r–    1 dbav97  db2iadm        7208 Feb 08 13:07 tab1.ixf

                                 -rw-r–r–    1 dbav97  db2iadm         142 Feb 08 13:07 tab1.msg

Export.out file contains detail list of tables which got exported with the number of rows it has exported from this table.

                                  EXPORT:     18 rows from table “DBAV97 “.”ACT”

                                  EXPORT:      0 rows from table “DBAV97 “.”CATALOG”

                                  EXPORT:      5 rows from table “DBAV97 “.”CL_SCHED”

                                 EXPORT:      6 rows from table “DBAV97 “.”CUSTOMER”

                                 EXPORT:     14 rows from table “DBAV97 “.”DEPARTMENT”

The db2move.lst file has detail of files which are created with relation of table name.

                                  !”DBAV97 “.”ACT”!tab1.ixf!tab1.msg!

                                 !”DBAV97 “.”CATALOG”!tab2.ixf!tab2.msg!

                                 !”DBAV97 “.”CL_SCHED”!tab3.ixf!tab3.msg!

                                 !”DBAV97 “.”CUSTOMER”!tab4.ixf!tab4.msg!

                                 !”DBAV97 “.”DEPARTMENT”!tab5.ixf!tab5.msg!

The file which has created with .ixf file, is binary file. This contains the table structure and table data. These are the files where data has been exported. You must have seen these kind of files getting generated with db2 export command as well. Export support other formats as well like .del, but in db2move only PCI/IXF format are supported. The last file which is generally created is .MSG file, this contains information about the messages which are generated while export of particular table. It creates .XML file as well, which dumps the XML data for particular table.

Data exporting to external files is done over here.

If new database which needs to be created from this dump is on same or identical server, you can use import method. Create database test with create database command.

db2move test import

                                * IMPORT:  table “DBAV97 “.”STAFFG”

                                         -Rows read:         35

                                         -Inserted:          35

                                        -Rejected:           0

                                       -Committed:         35

                        IMPORT:  table “DBAV97 “.”EMPPROJACT”

                                      -Rows read:         73

                                     -Inserted:          73

                                    -Rejected:           0

                                    -Committed:         73

Sounds so simple aren’t it? You are done with your work.

How to move to other server? Other server might not have same partition (unix partitions) or drive (windows drive). You need to change the container path. Here after the export is finished. Database structure needs to be captured in a file. This can be done using db2look utility.  Following is command to execute db2look and get all structure of database. The output file generated using this looks like as follows.

                                   bash-3.00$ db2look -d sample -e -l -xd -o db2look.ddl

                                   — No userid was specified, db2look tries to use Environment   variable USER

                                   — USER is: DBAV97

                                  — Creating DDL for table(s)

                                  — Output is sent to file: db2look.ddl

                                  — Binding package automatically …

                                  — Bind is successful

                                  — Binding package automatically …

                                 — Bind is successful

                                 — This CLP file was created using DB2LOOK Version “9.7″

                                 — Timestamp: Wed Feb  8 15:59:45 IST 2012

                                 — Database Name: SAMPLE

                                 — Database Manager Version: DB2/AIX64 Version 9.7.5

                                 — Database Codepage: 1208

                                — Database Collating Sequence is: IDENTITY

                                 CONNECT TO SAMPLE;

                                ————————————

                                — DDL Statements for TABLESPACES –

                               ————————————

                             CREATE LARGE TABLESPACE “IBMDB2SAMPLEREL” IN  DATABASE PARTITION GROUP IBMDEFAULT

GROUP

         PAGESIZE 8192 MANAGED BY AUTOMATIC STORAGE

         AUTORESIZE YES

         INITIALSIZE 32 M

         MAXSIZE NONE

         EXTENTSIZE 32

         PREFETCHSIZE AUTOMATIC

—————————————-

If you want to change any container, you need to edit the db2look.ddl file. Make appropriate changes in this and execute it. You need to change the database name in db2look file. It should be the new database name.

Here it comes first time on

                                ~~~~~

                              — Database Collating Sequence is: IDENTITY

                              CONNECT TO SAMPLE;

                             ————————————

                             — DDL Statements for TABLESPACES –

                            ————————————

                                                ~~~~~

It needs to connect before buffuerpool creation as well.

This db2look execution will create a database structure. It is a database skeleton.

                               CREATE TABLE “DBAV97 “.”CL_SCHED”  ( “CLASS_CODE” CHAR(7) , “DAY” SMALLINT , “S

TARTING” TIME , “ENDING” TIME ) IN “USERSPACE1″

                                DB20000I  The SQL command completed successfully.

 

                               CREATE TABLE “DBAV97 “.”DEPARTMENT”  ( “DEPTNO” CHAR(3) NOT NULL , “DEPTNAME” V

ARCHAR(36) NOT NULL , “MGRNO” CHAR(6) , “ADMRDEPT” CHAR(3) NOT NULL , “LOCATION”

 CHAR(16) ) IN “USERSPACE1″

                                DB20000I  The SQL command completed successfully.                              

After this we will load data into this database. Using following command. DB2move with load works very fast.

                                * LOAD:  table “DBAV97 “.”SALES”

                                              -Rows read:         41

                                              -Loaded:            41

                                             -Rejected:           0

                                             -Deleted:            0

                                            -Committed:         41

                              * LOAD:  table “DBAV97 “.”STAFF”

                                          -Rows read:         35

                                          -Loaded:            35

                                         -Rejected:           0

                                         -Deleted:            0

                                        -Committed:         35

If you want to load only certain table, just remove other table name from db2move.lst file and execute simple db2move load command. It will load only tables which are included in .lst file.

Since backup can be taken from one operation system to other, so this is called as os independent backup. Very easy tool to migrate database to other server.

Database support, Database services, Oracle ADF, Mobile Application, Java Application