DB2 Universal Database versus Oracle data movement

A comparative guide

If you've had experience with Oracle and now are starting to learn DB2® Universal Database™, this article will help you leverage your previous experience and put you on the fast track for learning how to move data from one machine to another, or from one platform to another.

Share:

Allan W. Tham (thamawh@my.ibm.com), DB2 Presales Technical Specialist, ASEAN Techline, IBM Malaysia

Allan W. ThamAllan Tham works in DB2 Content Manager presales support for IBM Business Partners. He helps business partners solve a wide range of technical problems. He is certified for DB2 Content Management administration. Prior to joining IBM, Allan worked in an end-user environment, where he was an Oracle DBA for 3 years.



05 May 2005

Also available in Chinese

Introduction

Data movement between two databases is a common daily DBA task. Most mature databases provide a means to move data from one database to another, either within the same platform or machine, or to other platforms or machines.

If you've experienced with Oracle and are now are beginning to learn DB2, this article will ease your learning process. The intention is to put Oracle DBAs into the DB2 Universal Database (DB2 UDB) fast track while keeping the differences between the two in mind. We'll take a look at the tools and commands you need to perform data movement, and compare and contrast the two database systems. We will also be looking at some scenarios for data movement.

Note that this article does not cover replication mechanisms for data movement such as Oracle Advanced Replication and WebSphere Information Integrator Q-Replication. Nor will we consider connectivity or data movement by using custom coding through JDBC, PERL DBI, and ODBC, as these interfaces are not meant for movement of large quantities of data.


Topics

This article highlights the data movement utilities provided with both databases together with their features and functions. Also, we'll consider the pros and cons of each utility or method. We will be considering the following topics. As we consider these topics, we'll look at some scenarios in order to better understand the features and capabilities of each utility.

  • Import
  • Export
  • Tool comparisons
  • Quick benchmarking
  • Other tools available
  • Data movement scenarios

Data movement requirements

On a typical day, a DBA might be involved in administrative tasks such as moving data files to a bigger file system on the production machine, creating a subset of production data for testing purposes, or duplicating the entire database for development use. With a duplicate set of data on a development machine, developers can test and trash the data without worrying about the consequences. Whenever a new set of data is required, a simple restore will bring the data back to its original state for another cycle of testing.

In an environment where terabytes(TB) of data are involved, especially in a warehouse environment, duplicating an entire database for testing purposes is not viable. In this case, you might want a subset of data with referential integrity intact. Also, you might want to deploy proper multiple data sets in test machines for developers who are working on application modules. When testing is completed, there are times when data must be moved to production.

Another reason for data movement would be the requirement to migrate your database to a new platform for various reasons.

To summarize, here are the most common scenarios for data movement:

  • Moving data files to another filesystem when the current one is full
  • Moving entire database or database subsets for testing/development purposes
  • Migrating databases from one platform to another.

The DBA generally chooses a method for data movement based on the size of database, the window of maintenance, and system resources, such as network/IO, CPU/memory, and so on. In the following sections we'll look at the various methods for data movement.


Import utilities

We will first examine the utilities provided by Oracle for data import. Oracle provides the following utilities to import data:

  • Oracle - imp
  • Oracle - SQL Loader

Oracle - imp

The Oracle import utility, imp, has been around for quite a while. The imp utility is used to import small and medium size databases, for example between 100 MB to 10 GB. To hasten the import process, you can employ multiple imp jobs at one time. In order to use imp, first you have to run either the catalog.sql script or the catexp.sql script residing in the /rdbms directory.

The privilege CREATE SESSION, which is included in the role CONNECT, is required in order to use import.

There are two import schema modes involved:

  • Import into same user schema -
    To import objects into the same user schema, both system and object privileges need to be granted to the user, especially the RESOURCE role.
  • Import into other user schema -
    Database objects can be exported by one user and imported by a different user. If importing into another schema, the IMP_FULL_DATABASE role must be enabled.

The syntax for import is shown in Listing 1. For a complete explanation of all of the parameters, please refer to Oracle Utility Guide or simply issue imp help=y from a command prompt.

Listing 1. Syntax for Oracle imp
imp keyword=value [,keyword=value,...]  where keywords are

   USERID   username/password         BUFFER   size of data buffer        
   FILE     input files (EXPDAT.DMP)  SHOW     list file contents (N)  
   IGNORE   ignore create errors (N)  GRANTS   import grants (Y)
   INDEXES  import indexes (Y)        ROWS     import data rows (Y)       
   LOG      log file of screen output FULL     import entire file (N)  
   FROMUSER list of owner usernames   TOUSER   list of usernames
   TABLES   list of table names       RECORDLENGTH length of IO record 
   INCTYPE  incremental import type   COMMIT   commit array insert (N)    
   PARFILE      parameter filename    CONSTRAINTS  import constraints (Y)

Alternatively, you can obtain online help with the command, imp help=y.

There are three ways to invoke the imp utility:

  • Command line
    For example: imp system/password fromuser=scott touser=scott file=acct_pay.dmp
    Figure 1. Invoking the Oracle import utility using the command line
    Invoking the Oracle import utility using the command line
  • Command line with parfile option
    For example: imp system/manager parfile=Acct_Pay.txt
    Figure 2. Invoking the Oracle import utility using the command line with parfile parameter
    Invoking the Oracle import utility using the command line with parfile parameter
  • Command line interactively
    Figure 3. Invoking the Oracle import utility interactively
    Invoking the Oracle import utility interactively

There are 4 different modes of import. Below you see some examples of the use of the import utility. As we begin to examine these examples, you will see a number of important considerations. For example, to minimize errors, it's best is to use CONSTRAINTS=N to disable referential integrity. At the end of import, constraints will be enabled. Also, triggers should be disabled as well. Normally the DBA executes a script to generate the triggers after import completes.

Only the export dump generated by the Oracle exp utility can be used. Higher versions of imp can read the export dump of the same or lower versions. However, errosr will occur when trying to import a dump file from a lower version of the export utility.

Here are examples of the 4 modes of import:

  • Full import - FULL=Y.
    With the parameter FULL=Y, all objects previously exported using FULL=Y will be imported. There are steps to be taken care of in order to make sure a full import can be carried successfully. Refer to the scenarios section for more details.
  • User level import - FromUser and Touser
    For example, to import all objects from user schema scott, issue the command: imp system/password fromuser=scott Touser=allan file=Acct_Pay.dmp.
    Figure 4. Importing using Fromuser and Touser parameters
    Importing using Fromuser and Touser parameters
  • Table level import - Tables = (*)
    • Importing using Table = (*). For example, to import all the tables in schema Scott, issue the command: imp system/password fromuser=scott touser=allan file=Acct_Pay.dmp tables=(*).
      Figure 5. Importing All tables
      Importing All tables
    • Importing using Tables = (A, B, C). For example, imp system/password fromuser=scott touser=allan file=Acct_Pay.dmp tables=(BONUS,EMP) constraints=N.
      Figure 6. Importing selected tables
      Importing selected tables
    • Importing partitioned tables, Tables = (T:p1, T:p2) - Eg. imp scott/tiger file=Scottt.dmp tables=(empp:p1, empp:p2)
      Figure 7. Importing partitioned tables
      Importing partitioned tables
  • Tablespace level import - Transport_Tablepspace = Y and Tablespaces=(A,B,C) and Datafiles=xxx. Refer to the scenarios section for more details on tablespace level import for Oracle.

Oracle - SQL Loader

SQL Loader is a utility provided by Oracle to load external files into Oracle databases. SQL Loader is considered more powerful and flexible than imp. SQL Loader can filter data to be loaded and at the same time allows the Oracle SQL functions to massage the data to be loaded. Besides variable, fixed length and stream data, it can load object-oriented data, LOB data, and collections.

There are two modes of loading: conventional path and direct path. Starting with Oracle 9i, the fastest method is to use external tables (with parallelism and direct insert). With an external table, you can issue a SQL SELECT statement to load the contents of a file.

The syntax for Oracle SQL Loader is shown in Listing 2. Note that you can obtain the complete syntax by issuing the sqlldr command at a command prompt.

Listing 2. Syntax for SQL Loader
SQLLDR keyword=value [,keyword=value,...]  where keywords are

       userid     -- ORACLE username/password       control    -- Control file name
       log        -- Log file name                  bad        -- Bad file name
       data       -- Data file name                 discard    -- Discard file name
       discardmax -- Number of discards to allow          (Default all)
       skip       -- Number of logical records to skip    (Default 0)
       load       -- Number of logical records to load    (Default all)
       errors     -- Number of errors to allow            (Default 50)

There is only one way to invoke SQL Loader, and that is by using the command line. In order to use SQL Loader, you will need a control file. The control file is the "brain" of the SQL Loader, providing the locations of the files, formats, insert targets, and so on. A control file is a case insensitve, format free text file with particular paramters defined.

The listing below show some examples of the use of SQL Loader.

  • Loading fixed length records

    Issue the command, sqlldr userid=scott/tiger log=course.log control=course.ctl.

    Listing 3. Sample fixed length data, Course.dat
    CS3121Theory of Computation I  F Vari Hall*****
    CS3122Theory of Computation II W Norman Bethune
    CS4101Computer Robotics ****** W Stong College*
    CS4102Computer Graphics ****** W Earth Science*
    CS4120Advanced Relational **** F Winter College
    Listing 4. Course.ctl
    LOAD DATA
    INFILE 'course.dat' "fix 49"
    BADFILE 'course.bad'
    Insert
    INTO TABLE Course
    (courseid       position    (1:6) char,  coursetitle    position    (7:30) char,
     term           position    (32:32) char, location       position    (34:47) char)
  • Loading variable length records

    Issue the command, sqlldr scott/tiger control=emp.ctl, log=emp.log .

    Listing 5. Sample variable length data, Emp.dat
    1234,    "Allan", "IT Specialist", 2345,  "April-15-2002",  15000, 3000, 88
    2345, Lily Ng, "HR", 7766, Jan-12-2000, 9000, 2000, 55
    3456,   "Odelia", "Fun", 8899, "June-11-2001", 8000,1000, 77
    4567, "Titus", Fun, 6655,"Aug-03-2005", 5000,200, 76
    5678, "Timothy", Sales, 7788, Aug-99-2005", 4000, 2000, 11

    Listing 6. Emp.ctl
    LOAD DATA
    INFILE 'emp.dat' -- emp.dat is the input file
    BADFILE 'emp.bad' -- bad file
    DISCARDFILE 'emp.dsc' -- discard file
    DISCARDMAX 2 -- Max discards allowed before load terminates
    APPEND
    INTO TABLE emp
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
    	-- terminated by comma. Allow double quote
    (empno, ename, job, mgr, hiredate date(30) "Month-DD-YYYY", sal, comm, deptno)
  • Loading into multiple tables

    Issue the command, sqlldr scott/tiger control=mul.ctl, log=multables.log

    Listing 7. Sample variable length data, EmpOrig.dat
    7369  SMITH  CLERK     7902 17-DEC-80 800  20             
    7499  ALLEN  SALESMAN  7698 20-FEB-81 1600 300  30               
    7521  WARD   SALESMAN  7698 22-FEB-81 1250 500  30               
    7566  JONES  MANAGER   7839 02-APR-81 2975 20               
    7654  MARTIN SALESMAN  7698 28-SEP-81 1250 1400 30

    Listing 8. Mul.ctl
    LOAD DATA
    INFILE emporig.dat
    BADFILE multables.bad
    DISCARDFILE multables.dsc
    APPEND INTO TABLE emp1 when empno<>'7788'
    FIELDS TERMINATED BY X'2c' OPTIONALLY ENCLOSED BY '"' TRAILING
        NULLCOLS
          (empno POSITION(1:4)  INTEGER EXTERNAL,
           ename POSITION(7:12) INTEGER EXTERNAL)
           INTO TABLE emp2 when job = 'CLERK'
           FIELDS TERMINATED BY X'2c' OPTIONALLY ENCLOSED BY '"' TRAILING
           NULLCOLS
          (empno POSITION(1:4)  INTEGER EXTERNAL,
            job   POSITION(14:22) INTEGER EXTERNAL)
  • Loading into partitioned tables

    Issue the command, sqlldr scott/tiger control=partition.ctl log=partition.log.

    Listing 9. Sample Emp_orig.dat
    7369, SMITH,CLERK,7902,17-DEC-80,800,20               
    7499, ALLEN,SALESMAN,7698, 20-FEB-81,1600,300,30               
    7521, WARD,SALESMAN,7698, 22-FEB-81,1250,500,30               
    7566, JONES,MANAGER,7839, 02-APR-81,2975,20               
    7654, MARTIN,SALESMAN,7698, 28-SEP-81,1250,1400,30

    Listing 10. Partition.ctl
    LOAD DATA
    INFILE 'emp_orig.dat'
    BADFILE 'emp_orig.bad'
    DISCARDFILE 'emp_orig.dsc'
      INSERT
        INTO TABLE empp PARTITION (sal_p2) -- salary with values less than 2000
        FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
        (empno, ename, job, mgr, hiredate, sal)
  • Loading LOB data

    Issue the command, sqlldr scott/tiger control=clob.ctl log=clob.log

    Listing 11. Mybmp.lst
    d:\mybmp\datamovement\impCmdline.png
    d:\mybmp\datamovement\imphelp.png
    d:\mybmp\datamovement\impIteractive.png
    d:\mybmp\datamovement\impParfile.png
    Listing 12. Clob.ctl
    LOAD DATA
    INFILE mybmp.lst
    INTO TABLE MYBMP
    (filename CHAR(200),
    bmp  LOBFILE(filename) TERMINATED BY EOF)
  • Loading via external table

    Create first the load directory using create directory load_dir as 'd:/tmp' and then run the command Sqlplus scott/tiger @external.sql.

    Listing 13. Sample Emp_Orig.dat
    7369, SMITH,CLERK,7902,17-DEC-80,800,20               
    7499, ALLEN,SALESMAN,7698, 20-FEB-81,1600,300,30               
    7521, WARD,SALESMAN,7698, 22-FEB-81,1250,500,30
    Listing 14. External.sql
    create table empp (EMPNO    NUMBER(4), ENAME    VARCHAR2(10), JOB      VARCHAR2(9),
                       MGR      NUMBER(4), HIREDATE DATE, SAL      NUMBER(7,2),
       		    COMM     NUMBER(7,2), DEPTNO   NUMBER(2))
     organization external( type oracle_loader
    default directory load_dir
    access parameters ( records delimited by newline
                        fields terminated by ','
                		missing field values are null 
    		        (empno, ename, job, mgr, hiredate char date_format 
    			 date mask "dd-mon-yyyy", sal, comm, deptno ))
    location ('emp_orig.dat'))  reject limit 5;

DB2 - import

DB2 Universal Database provide a similar utility for importing data, the IMPORT utility. There are four file formats supported for import and export. The format chosen usually reflects the source it comes from or the target tools to be used. Usually the extension of files such as .ixf, .del or .asc reveal the content format. For example, employee.ixf will contain uneditable DB2 UDB interchange format. Import has the ability to traverse the hierachy of typed tables in ixf format.

The four supported file formats are:

  • Delimited ASCII files - DEL

    In this format, delimiters can be either character delimited, column delimited or row delimited. The default delimiter for character delimited format is double quote ("), and the default for column delimited is comma (,). Note that the default delimiters can be changed. For row delimited, the hex code for UNIX® is X'0A' and Windows® is X'0D0A'

  • Non-delimited ASCII files - ASC

    Non-delimited ASCII files are those with fixed length fields. It is a sequential rows of records delimited by rows. Usually, this format is used for data exchange with external applications that comes with aligned columns.

  • Integrated Exchange Format files - IXF

    This is the preferred method for data exchange between DB2 UDB databases. IXF files contain structured descriptions of the database tables and indexes. Note, this format normally is not editable using a text editor.

  • Worksheet Format files - WSF

    This format is used for data interchange between Lotus 1-2-3 and Symphony products.

In order to use DB2 import, you must have the proper authorizations and privileges. You will either need to have sysadm or dbadm authority level, or else have privileges such as CREATETAB, CONTROL, SELECT and INSERT for the participating objects, depending on the options chosen.

The syntax for DB2 UDB import is very straightforward. Refer to the syntax below. To get the syntax online, enter db2 ? import at a DB2 command line.

Listing 15. DB2 UDB import syntax
IMPORT FROM filename OF {IXF | ASC | DEL | WSF}
   [LOBS FROM lob-path [ {,lob-path} ... ] ] [MODIFIED BY filetype-mod ...]
   [METHOD {L ( col-start col-end [ {,col-start col-end} ... ] )
    [NULL INDICATORS (col-position [ {,col-position} ... ] )] | 
    N ( col-name [ {,col-name} ... ] ) |
    P ( col-position  [ {,col-position} ... ] )}]
    [ALLOW {NO | WRITE} ACCESS]
    [COMMITCOUNT {n | AUTOMATIC}] [{RESTARTCOUNT | SKIPCOUNT} n]
    [ROWCOUNT n] [WARNINGCOUNT n] [NOTIMEOUT] [MESSAGES message-file]
    {{INSERT | INSERT_UPDATE | REPLACE | REPLACE_CREATE}
    INTO {table-name [( insert-column , ... )] | hierarchy-description}
    | CREATE INTO {table-name [( insert-column , ... )] |
    hierarchy-description {AS ROOT TABLE | UNDER sub-table-name}
    [IN tablespace-name [INDEX IN tablespace-name] [LONG IN tablespace-name]]}
    [datalink-specification]
    filetype-mod:
        COMPOUND=x, INDEXSCHEMA=schema, FORCEIN, INDEXIXF, IMPLIEDDECIMAL,
        NOCHECKLENGTHS, NOEOFCHAR, NULLINDCHAR, RECLEN=x, STRIPTBLANKS,
        STRIPTNULLS, NO_TYPE_ID, NODOUBLEDEL, LOBSINFILE, USEDEFAULTS,
        CHARDELx, COLDELx, DLDELx, DECPLUSBLANK, DECPTx, DATESISO,
        DELPRIORITYCHAR, IDENTITYMISSING, IDENTITYIGNORE,
        GENERATEDMISSING, GENERATEDIGNORE, DATEFORMAT=x, TIMEFORMAT=x,
        TIMESTAMPFORMAT=x, KEEPBLANKS, CODEPAGE=x, NOROWWARNINGS,
        NOCHARDEL, USEGRAPHICCODEPAGE
      hierarchy-description:
        {ALL TABLES | (sub-table-name [(insert-column, ...)], ...)} [IN]
         HIERARCHY {STARTING sub-table-name | (sub-table-name, ...)}
      datalink-specification:
        ([DL_LINKTYPE URL][{DL_URL_REPLACE_PREFIX prefix |
        DL_URL_DEFAULT_PREFIX prefix}] [DL_URL_SUFFIX suffix], ...)

There are three ways to invoke DB2 UDB import, as follows:

  • Control Centre - GUI approach

    The Control Centre is an interactive, easy-to-use approach, with wizards provided to guide you step by step. Here are some examples of using the Control Centre to perform imports, using the SAMPLE database. If don't already have the SAMPLE database created, go to a command line and enter db2sampl to create it.

    Example 1 - Importing a comma delimited files, employee.del. into the existing employee table.

    Listing 16 shows a few rows and from the employee.del file.

    Listing 16. Sample content for employee.del
    "000010","CHRISTINE","I","HAAS","A00","3978",19650101,"PRES    ",18,"F",19330824, ...
    "000020","MICHAEL","L","THOMPSON","B01","3476",19731010,"MANAGER ",18,"M",19480202, ...
    "000030","SALLY","A","KWAN","C01","4738",19750405,"MANAGER ",20,"F",19410511, ...
    "000050","JOHN","B","GEYER","E01","6789",19490817,"MANAGER ",16,"M",19250915, ...
    "000060","IRVING","F","STERN","D11","6423",19730914,"MANAGER ",16,"M",19450707, ...

    In this example, navigate using Control Centre. Go to Start -> Programs -> IBM DB2 -> General Administration Tools -> Control Centre. Select the Sample database, click on Tables, right click on the Employee table and select Import.

    Figure 8. Invoking import of a table using Control Centre
    invoking import of a table using Control Centre

    Note that since this is delimited format (DEL), choosing this option will allow you to select more options for finer control over the import. We will not go into the nitty gritty of all these options. In this case, we specify the Employee.del file to be imported as well as the log file to write to. Note also, we choose the INSERT mode. You can have either INSERT, INSERT_UPDATE or REPLACE. For INSERT, import will just append to the existing table without disturbing the rest of the data. For INSERT_UPDATE, you must have at least a primary key. New records will be inserted and existing records will be updated. For REPLACE, the data in the table will be deleted first before insert takes place.

    Figure 9. Import - the file tab
    Import - the file tab

    You will then receive a message stating successful import.

    Example 2 - In this example, we are given a delimited file with the last two columns omitted. We will import some columns with INSERT-REPLACE option. The format of the file is asterisk, '*' delimited.

    Listing 17 shows sample content from Employee.del.

    Listing 17. Sample Employee Data
     "000010"*"CHRISTINE"*"I"*"HAAS"*"A00"*"3978"*19650101*"PRES    "*18*"F"*19330824* ... 
     "000020"*"MICHAEL"*"L"*"THOMPSON"*"B01"*"3476"*19731010*"MANAGER "*18*"M"*19480202* ... 
     "000030"*"SALLY"*"A"*"KWAN"*"C01"*"4738"*19750405*"MANAGER "*20*"F"*19410511* ... 
     "000050"*"JOHN"*"B"*"GEYER"*"E01"*"6789"*19490817*"MANAGER "*16*"M"*19250915* ... 
     "000060"*"IRVING"*"F"*"STERN"*"D11"*"6423"*19730914*"MANAGER "*16*"M"*19450707* ...

    From the Control Centre, repeat the same steps you followed in Example 1. However, you need to perform some extra steps. Choose the DEL option button.

    Choose Column Delimiter (COLDEL) and choose asterisk. '*'. Note other values listed in the dropdown list. These are the allowed column delimiters. Take the defaults for the rest. Click OK.

    Figure 10. Choosing the delimiter
    Choosing the delimiter

    Now, go to the column tab and click on column button. This step is to select which column to import into and which to omit.

    Figure 11. Choosing the columns to be included - part 1
    impDEL1_GUI3.jpg

    Choose all the columns except two. Note that in this case, the last two columns, BONUS and SALARY, will be omitted. Click OK.

    Figure 12. Choosing the columns to be included - part 2
    impDEL1_GUI4.jpg

    Make sure you choose INSERT_REPLACE as import mode from File tab. The prerequisite for using the INSERT_REPLACE option is that the primary key must exist. If the primary key does not exist yet, you must first issue the SQL command to create the primary key, for example: alter table employee add constraint empno primary key (pk_empno). Click OK. You will be see that the import is successful.

  • Command Line Processor (CLP) - command prompt approach

    Example 1 - As in the GUI approach example 1 above, we are importing a comma delimited files, employee.del. into the existing employee table.

    From the DB2 CLP, issue the following commands:

    Listing 18. Importing using command CLP - Example 1
    CONNECT TO SAMPLE;
    IMPORT FROM "E:\tmp1\employee.del"  
    	OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14) 
    MESSAGES "E:\tmp1\employee.log" 
    INSERT INTO ADMINISTRATOR.EMPLOYEE 
     (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, 
      SEX, BIRTHDATE, SALARY, BONUS, COMM);
    CONNECT RESET;

    Example 2 - As in the GUI approach sample 2, we are given a delimited file with the last two columns omitted. We will import some columns with INSERT-REPLACE option. The format of the file is asterisk, '*' delimited.

    Using the DB2 CLP, issue the following commands:

    Listing 19. Importing using command CLP - Example 2
    CONNECT TO SAMPLE;
    IMPORT FROM "E:\tmp1\employee.del" OF DEL MODIFIED BY COLDEL* 
       METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) 
       MESSAGES "E:\tmp1\employee.log" 
       INSERT_UPDATE INTO ADMINISTRATOR.EMPLOYEE 
         (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, 
          SEX, BIRTHDATE, SALARY);
    CONNECT RESET;
  • API - code approach
    Refer to the DB2 Information Center for API instructions and samples. DB2 ships with sample code for C, C++ and COBOL.

DB2 LOAD

Besides DB2 IMPORT, DB2 also provides a much faster loading tool, DB2 LOAD, to move massive amounts of data into a DB2 UDB database. Unlike DB2 IMPORT, which writes to the database via SQL INSERT, DB2 LOAD writes directly to the database files. Both DB2 IMPORT and LOAD support user-defined types (UDTs) and LOBs; however, DB2 LOAD can load data into a partitioned table. The primary difference and choice of usage between these tools is performance. DB2 LOAD is much faster than IMPORT.

DB2 LOAD does not provide logging for individual records. However, reloading data is supported. During the load process, the DB2 command LIST UTILITIES can be used to monitor the progress of the load. Also, DB2 LOAD employs table level locking where users will not be able to access the data during this load period.

To use DB2 LOAD, you will need authority level of SYSADM, DBADM, or SYSMAINT, or else have load authority on the database and privileges for INSERT and DELETE, depending on the operations chosen.

The DB2 LOAD syntax is shown in Listing 20. Alternatively, issue db2 ? import at a DB2 command prompt to view the syntax.

Listing 20. DB2 Load Syntax
LOAD QUERY TABLE table-name [TO local-message-file]
[NOSUMMARY | SUMMARYONLY] [SHOWDELTA]

LOAD [CLIENT] FROM file/pipe/dev/cursor_name [ {,file/pipe/dev} ... ]
OF {ASC | DEL | IXF | CURSOR}
[LOBS FROM lob-path [ {,lob-path} ... ] ]
[MODIFIED BY filetype-mod [ {filetype-mod} ... ] ]]
[METHOD {L ( col-start col-end [ {,col-start col-end} ... ] )
     [NULL INDICATORS (col-position [ {,col-position} ... ] )]
   | N ( col-name [ {,col-name} ... ] )
   | P ( col-position  [ {,col-position} ... ] )}]
[SAVECOUNT n]
[ROWCOUNT n] [WARNINGCOUNT n] [MESSAGES msg-file]
[TEMPFILES PATH pathname]
{INSERT | REPLACE | RESTART | TERMINATE}
INTO table-name [( insert-column [ {,insert-column} ... ] )]
[datalink-specification] [FOR EXCEPTION table-name]
[STATISTICS {NO | USE PROFILE}] 
[{COPY {NO  | YES { USE TSM [OPEN num-sess SESSIONS]
            | TO dir/dev [ {,dir/dev} ... ]
            | LOAD lib-name [OPEN num-sess SESSIONS]}}
            | NONRECOVERABLE} ]
[HOLD QUIESCE] [WITHOUT PROMPTING] [DATA BUFFER buffer-size]
[SORT BUFFER buffer-size] [CPU_PARALLELISM n] [DISK_PARALLELISM n]
[INDEXING MODE {AUTOSELECT | REBUILD | INCREMENTAL | DEFERRED}]
[CHECK PENDING CASCADE {DEFERRED | IMMEDIATE}]
[ALLOW NO ACCESS | ALLOW READ ACCESS [USE tblspace-name]] [LOCK WITH FORCE]
[[PARTITIONED DB CONFIG] partitioned-db-option [{partitioned-db-option}...]]
   filetype-mod:
    NOROWWARNINGS, ANYORDER, BINARYNUMERICS, CODEPAGE=x,
     DUMPFILE=x, FASTPARSE, NOHEADER, TOTALFREESPACE=x,
      INDEXFREESPACE=x, PAGEFREESPACE=x, FORCEIN, IMPLIEDDECIMAL,
      PACKEDDECIMAL, NOCHECKLENGTHS, NOEOFCHAR, NULLINDCHAR=x,
      RECLEN=x, STRIPTBLANKS, STRIPTNULLS, NODOUBLEDEL, LOBSINFILE,
      CHARDELx, COLDELx, DLDELx, DECPLUSBLANK, DECPTx, DATESISO,
      DELPRIORITYCHAR, USEDEFAULTS, DATEFORMAT=x, TIMEFORMAT=x,
      TIMESTAMPFORMAT=x, ZONEDDECIMAL, KEEPBLANKS, IDENTITYMISSING,
      IDENTITYIGNORE, IDENTITYOVERRIDE, GENERATEDMISSING,
      GENERATEDIGNORE, GENERATEDOVERRIDE, USEGRAPHICCODEPAGE
partitioned-db-option:
HOSTNAME x, FILE_TRANSFER_CMD x, PART_FILE_LOCATION x, OUTPUT_DBPARTNUMS x,
PARTITIONING_DBPARTNUMS x, MODE x, MAX_NUM_PART_AGENTS x, OMIT_HEADER,
ISOLATE_PART_ERRS x, STATUS_INTERVAL x, PORT_RANGE x, CHECK_TRUNCATION,
MAP_FILE_INPUT x, MAP_FILE_OUTPUT x, TRACE x, NEWLINE, DISTFILE x
   datalink-specification:
     ([DL_LINKTYPE URL][{DL_URL_REPLACE_PREFIX prefix |
     DL_URL_DEFAULT_PREFIX prefix}] [DL_URL_SUFFIX suffix], ...)

The DB2 LOAD operation can be broken down into four phases. These four phases happen in a sequential manner:

  1. Load phase - In this phase, data from external files are loaded into tables. Indexes will be collected and sorted. An important event during this phase is the savepoint check for the load operation. This savepoint is needed in the event the load fails, and reload needs to be performed. Savepoint allows this reloadable feature to be carried out. In this phase, invalid records will be written to a message file.
  2. Build phase - In this phase, indexes will be built. In the event of failure during this phase, a restart of the load operation will cause the build phase to start from the very beginning. During this phase, messages will be generated.
  3. Delete phase - In this phase, the constraint violation of unique and primary keys will be taken care of. Violating rows will be written into an exception table. In the event of failure during this phase, a restart of the load operation will cause the delete phase to start from the very beginning. During this phase, messages will be generated.
  4. Index copy phase - In this phase, indexes are moved from system temporary table spaces to target tablespaces. This will only happen if system temporary table spaces are specified for index creation during load. In this phase, messages will be generated.

DB2 LOAD allows data to be appended or replaced. During the load operation, an intermediate table will be created to facilitate the loading. CURSOR file type can be used instead. Also, there are many options that can be selected such as

  • WARNINGCOUNT - specifies when to stop the load. When n warnings are hit, the load operation will cease
  • CPU_PARALLELISM - specifies the degree of parallelism for CPU. The default is an intelligent value that DB2 determines
  • DISK_PARALLELISM - specifies the degree of parallelism for disks. This value determines the number of threads to spawn. The default is an intelligent value that DB2 determines
  • DATA BUFFER - specifies the number of 4K pages to use
  • TEMPFILES PATH - specifies the path to use when creating intermediate tables during load
  • FOR EXCETION - specifies the table to write to for all problematic rows
  • DUMPFILE - specifies the file name to dump the rejected rows errors to
  • NOROWWARNING - specifies no warning will be written for rejected rows

DB2 LOAD provides for some performance tuning capabilites in that total space can be specified for pages created during load operation.

  • PAGEFREESPACE - Percentage of each data page left free. Normally, leave it default for DB2 to decide.
  • INDEXFREESPACE - Percentage of each index page left free. Normally, leave it default for DB2 to decide.
  • TOTALFREESPACE - Percentage of the table size appended as free space

For all other options, refer to the DB2 UDB Data Movement Utilities Guide and Reference.

We will be looking at some examples of how DB2 LOAD can be used for day-to-day operations. Like IMPORT, there are three modes of calling DB2 LOAD, by using the interactive GUI, the command interface, and through APIs.

  • Interactive GUI -

    Example 1 - Loading a delimited file into a table.

    Note, we will be using the same employee.del that we used in the IMPORT examples as the input file, with comma delimited. Start up the Control Centre. Go to Start -> Programs-> IBM DB2 -> General Administration Tools -> Control Centre. Click on Database, and open up the Sample database. Go to Tables, right click on Employee table. Select Load.

    Figure 13. Invoking Load table using Control Centre
    loadDEL_GUI1.jpg

    Note that you can either append or replace the data. You can also opt for read access during table load when you choose to append.

    Figure 14. Choose Append or Replace
    loadDEL_GUI2.jpg

    Enter the values for input file name and message file. Note that the DEL option is left alone since the default delimiter used is the comma, the same as our input file. In the event that another delimiter is used, click on the DEL button and choose from the COLDEL drop down list the desired delimiter. Note also that your database can be either local or remote.

    Figure 15. Specifying paths and file names
    loadDEL_GUI3.jpg

    In this case, we load every column except BONUS and COMM. Note you can specify the column behaviour as well.

    Figure 16. Selecting columns to be included
    loadDEL_GUI4.jpg

    Note the options you can have during LOAD to impact performance. We will take the defaults.

    Figure 17. Performance and Statistics
    loadDEL_GUI5.jpg

    On this page, you have three options namely, crash recovery, forward, or no recovery at all during the load period. We will take the defaults.

    Figure 18. Fail and Recovery selection
    loadDEL_GUI6.jpg

    Take the defaults for the following screen. These are the advanced settings that you can use. For example, you can specify parallelism of CPU, dump file to write to, and so on.

    Figure 19. Choosing Advanced options
    loadDEL_GUI7.jpg

    Take the default for the following screen and click Finish.

    Figure 20. Scheduling or Running immediately
    loadDEL_GUI8.jpg

    You will now see the successful load message.

    Figure 21. Load success screen
    loadDEL_GUI9.jpg

    Have a look into Employee.msg. You will see total rows read, skipped, loaded, rejected, deleted and committed.

  • Command CLP

    Using the same example as we examined for the interactive GUI approach, the command to be issued is as listed below.

    Listing 21. DB2 Load using CLP command
    CONNECT TO SAMPLE;
    LOAD FROM "E:\tmp1\employee.del" OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) 
    MESSAGES "E:\tmp1\employee.msg" 
    INSERT INTO ADMINISTRATOR.EMPLOYEE 
    (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, 
    	SEX, BIRTHDATE, SALARY) 
    COPY NO INDEXING MODE AUTOSELECT;
    CONNECT RESET;
  • API - code approach

    Refer to the DB2 UDB Information Center for API instructions and sample code. DB2 ships with code samples for C, C++, and COBOL

Tool comparisons

The following table compares the differences between the four utilities: Oracle imp, Oracle SQL Loader, DB2 UDB IMPORT and DB2 UDB LOAD.

Table 1. Import Utility Comparisons
Oracle ImportOracle SQL LoaderDB2 ImportDB2 Load
Invoke via command promptInvoke via command promptInvoke via command prompt & APIsInvoke via command prompt & APIs
Only can read the dump file generated by Oracle Exp utility (See Exp/Imp compatible matrix, metalink note 132904.1)Reads from external filesReads from external files of either DEL, ASC, IXF or WSF formatsReads from external files of either DEL, ASC, IXF or WSF formats
Meant for small databases - however, with proper conditions met, transportable tablespaces can be used for large environmentCan handle larger data volumeMeant for small databasesMeant for large databases
Can be used to generate DDL scripts with INDEXFILE parameterWill not generate DDL scriptsWill not generate DDL scripts. DDL is contained in uneditable IXF format.Will not generate DDL scripts
Not very fast in performance (doesn't allow direct=Y)Faster in performanceNot very fast in performance. Using SQL INSERTVery fast in performance. Write directly into database.
Less options for inclusion or exclusion of objects to be importedSlightly more flexible in terms of objects to be importedQuite flexible in terms of options and objects importedVery flexible
Non restartableRestartable - bad records can be reloadedRestartableRestartable
Tables must pre-existTables must pre-existTables must pre-existTables must pre-exist
Import tables from the export dump files. No importing into multiple tables capabilityLoads into multiple tables concurrentlyMultiple table import from multiple sources using one pass is not supportedMultiple table import from multiple sources using one pass is not supported
Index creation can be suppressedIndex creation cannot be suppressedNo index re-generation. Existing indexes stay intactNo index re-generation. Existing indexes stay intact
Starting Oracle 8, the parameter, QUERY can be used to specify rows/columns to be importedColumns and Rows selection is possible using WHEN. However, not all operators are supported using WHEN.Where clause to filter rows and columns is a standard feature.Where clause to filter rows and columns is a standard feature.
COMMIT=Y commits after each record imported. No way to specify commit after importing 100 rows for example.Use the option ROWS=n. For example ROWS=100 commits after 100 SQL LoadUse the option COMMITCOUNT=5000 to commit after importing 5000 rowsUse SAVECOUNT option
Tables inserted will not be locked - Users can access while import takes place.Tables inserted will not be locked - Users can access while import takes place.Two modes are supported - offline mode 4 (ALLOW NO ACCESS) and online mode (ALLOW WRITE ACCESS)Table level lock. Users have full read and write access to all the tables in the table space, except for the table being loaded. For the table being loaded, the existing data in the table will be available for read access if the load is appending data to the table.
Mainly use for backward compatibilityWill be around for a while, no plan to remove itWill be around for a while, no plan to remove itWill be around for a while, no plan to remove it
Gotcha's here and there, for example FULL=Y requires precaution steps, transportable tablespaces, and so onNumerous restrictions on direct path & parallel direct path loadRestriction is that it cannot import into structure typed column (Refer to DB2 Data Movement Util for a list of restrictions.)Restriction is on the hierarchy table structure support .
No native API providedNo native API providedA rich API set is providedA rich API set is provided

Quick benchmarking

The following benchmark provides a quick view of how each tool provides performance in loading various amount of records into database. The code to generate the input files is listed below.

Listing 22. Sample Perl script to generate imp/load files
#!/usr/bin/perl -w

my ($sec,$min,$hour,$mday,$mon,$year) = localtime time;
$year+=100;		 		 		 # it starts counting at 1900
for ($i=1; $i<100000; $i++) {
$datestr = sprintf "%4d%02d%02d", $year, $mon, $mday;
print <<ENDLINE;
"$i","This is benchmarking test for DB2...", $datestr, "Oracle and DB2 data ...", 
"Allan W. Tham", "Asean techline..."
ENDLINE
}

Refer to the Table 2 to see results of performance testing for DB2 UDB IMPORT and LOAD. Note that IMPORT is significantly slower. This exercise was carried out using an IBM pSeries P615 2-WAY 1.65GHz, 4GB RAM.

Table 2. Quick benchmark comparisons
Total rowsImport time (secs)Load time (secs)
10,00082
50,000396
100,00010011
500,00053158
1,000,0001109116
5,000,0003610454
Figure 22. Quick benchmark for DB2 Import and Load
benchmark1.jpg

Data movement - Export utilities

  • Oracle - exp
  • Oracle - good old SQL Plus

Oracle - exp

The Oracle exp utility is the counterpart of the Oracle imp utility. They co-exist even with version differences; for example, export of Oracle 8i can be imported into 9i. As mentioned earlier, export writes database objects in a specific sequence into a proprietory format that only imp utility can read. The exp utility is used to export small and medium size database. The exp comes with the parameter DIRECT=Y which bypasses the evaluation buffer ensure a much faster export processs. There are numerous advantages in using Oracle exp utility such as the ones listed below

  • Logical backup - exp utility can be used to supplement the backup and recovery stragegy. For example, a good export ensure a recovery of a particular table dropped accidentally
  • Reorg - An export will eliminate the fragmentation in the database
  • Corruption check - An export will require a full table scan. Any physical or logical corruption will be detected. Physical corruption refers to the used blocks that may corrupt the data whereas logical corruption involves the data dictionary

The Oracle exp utility is exactly the opposite of imp utility. Whilst imp is like inhaling, export is considered to be the exhaling or spitting part. The ways to invoke the exp utility is the same as for invoking imp. The syntax for export are very similar. The key differences are the presence of parameters such as COMPRESS and DIRECT, and the absence of TOUSER and FROMUSER. To list the complete list, issue the command exp help=y. See the syntax below:

Listing 23. Syntax for Oracle exp
exp keyword=value [,keyword=value,...]  where keywords are

USERID      username/password            BUFFER     size of data buffer
FILE        input files (EXPDAT.DMP)     COMPRESS   import into one extent (Y)
IGNORE      ignore create errors (N)     GRANTS     import grants (Y)
INDEXES     import indexes (Y)           DIRECT     direct path (N)
ROWS        import data rows (Y)         CONSISTENT cross-table consistency(N)
LOG         log file of screen output    FULL         import entire file (N)
OWNER       list of owner usernames      TABLES       list of table names
INCTYPE     incremental import type      RECORDLENGTH length of IO record 
RECORD      track incr. export (Y)       TRIGGERS     export triggers (Y)
STATISTICS  analyze objects (ESTIMATE)   PARFILE      parameter filename
CONSTRAINTS import constraints (Y)

Let's examine the four modes of export supported by this utility:

  • Full Export - FULL=Y. With the parameter FULL=Y, all objects will be exported. Issue the command, exp system/password full=y file=scott.dmp log=scott.log direct=y .
  • Particular Owner - OWNER=XYZ. With this parameter, you can export objects pertaining to a specific user. For example, the command exp system/password owner=scott file=scott.dmp will export all objects belong to Scott.
  • Table level export - TABLES = (*). With this parameter, specify tables owned by a user schema can be exported. For example the command, exp scott/tiger file=scott.dmp tables=(emp, dept)
  • Tablespace level export - Transport_Tablepspace = Y and Tablespaces=(A,B,C). Refer to the scenario 3 in the scenarios section.

Oracle - SQLPLUS

SQL Plus is one of the quick ways to extract data from single or multiple tables. To use SQL Plus to extract data follow the steps below.

  1. SQLplus username/password@dblink
  2. Set the formatting parameters
  3. Spool <output_filename>
  4. Run the SQL statement to extract data
  5. Spool off
  6. Voila, you have your data!

DB2 - EXPORT

The DB2 UDB EXPORT utility exports data into three different formats: DEL, WSF, and IXF. There is nothing fancy about DB2 UDB export. The examples below show how to use DB2 UDB EXPORT.

  • Interactive GUI approach

    Start up the Control Centre and right click on the desired table to choose the Export option.

    Figure 23. Invoking export from DB2 UDB Control Center
    expall_1.jpg

    Choose the output format, either DEL, WSF or IXF. Enter the export file name and message file name as well. Note, there is an SQL assist in the event you want to export a subset of the columns or rows for a particular table. If you know the table columns already, you can simply enter in the text box without using the SQL Assist. There are other tabs as well such as Columns and Schedule.

    Figure 24. Choosing export format and output file names
    expall_2.jpg

    If in previous step, you chose DEL, you will not be able to add in column tab. For WSF and IXF formats, you are able to add columns. These columns specify the names to be used for the output file. In our example, we leave it at the default. You can also specify where LOB data is to be found or file names to find LOB data.

    Figure 25. Specifying columns for output files.
    expall_3.jpg

    The last tab is the schedule tab. We choose run immediate. You can always schedule the task to run later.

    Figure 26. Schedule task
    expall_4.jpg

    You will see a successful message.

  • Command CLP approach
    Listing 24. Sample DB2 export using command CLP
    CONNECT TO SAMPLE;
    EXPORT TO "E:\tmp1\employee_1.ixf" OF DEL MESSAGES "E:\tmp1\employee_1.msg" 
    SELECT EMPLOYEE.EMPNO, EMPLOYEE.FIRSTNME, EMPLOYEE.LASTNAME, 
    		EMPLOYEE.WORKDEPT, EMPLOYEE.PHONENO
           FROM ADMINISTRATOR.EMPLOYEE AS EMPLOYEE;
           CONNECT RESET;

Other tools

Notes on Oracle imp utility

Though Oracle imp utility has been around since version 5, and will still be shipped and remain an integral part of Oracle for the versions to come, it will not play as important role as before. The direction is to use a much faster solution, a true server loading mechanism namely the Data Pump.

Oracle - Data Pump (impdp/expdp)

Starting with Oracle 10g, Oracle provides a new high speed data and metadata pumping utility called Data Pump. Data Pump will maximize the throughput by achieving parallel streams during data pumping. What is new to Data Pump (impdp and expdp) is the ability to call it via custom code. The package that makes Data Pump available is DBMS_DATAPUMP. The key advtantage of Data Pump as compared to its predecessors is the speed of loading and unloading. Other then the speed (20-30 times faster than imp), the other benefit is the finer control over objects import and export. For example, Data Pump can exclude some data objects using the EXCLUDE keyword, or include using INCLUDE keyword. You can also specify whether data or metadata should be exported/imported using CONTENT keyword. The usual, APPEND, SKIP, TRUNCATE and REPLACE apply to the tables imported. Last but not least is the detailed monitoring during the load such as percentage done, work in progress and history. For a complete reference, refer to the Oracle 10g Utility Guide.

IBM DB2 High Performance Unload for Multiplatform

IBM DB2 High Performance Unload for Multiplatform is a high speed unload tool for UNIX, Windows and Linux®. This tool can be used in place of export when the data volume is huge. This tool unloads DB2 tables from either a table space or a backup copy.

IBM DB2 Test Database Generator

IBM DB2 Test Database Generator is a tool to generate test database from the database in the main production. A subset of the database can be created from the existing database or a new test database can be generated from scratch. This tool maintains the referential integrity of the database.

IBM Migration ToolKit (MTK)

MTK version 1.3 is designed to migrate data, the query and procedure language from the several source database management systems including Oracle 8i and partial support for Oracle 9i, to the following target systems:

  • IBM DB2 UDB Universal Database for Workstation platforms, Version 8.1 or later
  • IBM DB2 UDB Universal Database for i5/OS, Version 5.2 or later
  • IBM DB2 UDB Universal Database for z/OS, Version 8

MTK can be useful to migrate from an Oracle repository to a DB2 repository since its features include:

  • The migration source can be a database data source or a DDL script.
  • It converts Transact-SQL or PL/SQL object definitions (including stored procedures, user-defined functions, triggers, packages, tables, views, indexes and sequences).
  • It has a full-featured GUI interface (Toolkit) to further refine the migration and tailor options for complex databases conversions.
  • It converts Transact-SQL or PL/SQL scripts to DB2 scripts.
  • It generates and runs scripts to unload data from source and upload data to DB2.

The developerWorks DB2 library contains many references to material related to migration to DB2 topics, specifically a tutorial and technical articlse about MTK:

MTK runs on AIX®, Linux, Sun Solaris, and Windows. The only language available is English. MTK is available for a complementary download from IBM DB2 Migration Toolkit site


Scenarios

This section looks at some scenarios showing how things are accomplished in both the Oracle and DB2 UDB world. Note, presented solutions are not meant to be exhaustive.

  • Scenario 1 - Data files movement

    In Oracle, to relocate the datafiles in a tablespace (without shutting down the database) requires the following steps:

    • Alter tablespace USERS read only - Make the tablespace read only.
    • Copy the datafiles to new locations using OS level copy.
    • Alter tablespace USERS offline - Once copying is done, take the tablespace, USERS offline.
    • Alter database rename '/oldpath/old_datafile1.dbf' to '/newpath/new_datafile1.dbf' - Rename the datafiles one by one using the full old and new paths.
    • Alter tablespace USERS online - Bring the tablespce online again.
    • Alter tablespace USERS read write - Bring the tablespace out of read only mode.

    In DB2 UDB, simply issue the command db2relocatedb with the right parameters. For example db2relocatedb -f configFilename where configFilename is as you see in Listing 25:

    Listing 25. Sample config file for db2relocatedb
    DB_NAME=oldName,newName
    DB_PATH=oldPath,newPath
    INSTANCE=oldInst,newInst
    NODENUM=nodeNumber
    LOG_DIR=oldDirPath,newDirPath
    CONT_PATH=oldContPath1,newContPath1
    CONT_PATH=oldContPath2,newContPath2

    Note that, unlike Oracle, DB2 allows the renaming of database name, path, instance name, and log directory all in one command as long as it is specified in the config filen.

  • Scenario 2 - Migrating database from the same OS and DB versions

    In Oracle, if for some reason you need to move a database from the same database version within the same operating system (for example moving Oracle 9i (release 2) in Windows 2000 to another machine with the same Oracle version in Windows 2000 as well), there is actually a quick way without using the Oracle imp/exp. In this event, you use the create controlfile option. Let's assume the old database name is 'AWT' and the new one is 'AWT1'. Note these steps are similar to the case where control file is totally missing.

    • Alter database backup control file to trace - back up the control file to.
    • Shutdown immediate - shut down the database clean on the source machine.
    • Copy the dump files, init.ora, and redo to the target machine.
    • Edit the trace file so that it reads 'Create controlfile reuse set database "AWT1" resetlogs' in the first line. Edit also any structural changes for your datafiles and log files in your new environment. Save the file to createControlFile.sql for example.
    • Edit the init.ora file to reflect the new environment as well. That will include SID, including paths that were changed.
    • Start up mount and run createControlFile.sql in new machine
    • Alter database open resetlogs -- you have no choice but to reset log.
    • You may want to rename the global name as well.

    DB2 UDB is a whole lot simpler in this case. DB2 UDB provides the BACKUP and RESTORE commands. The BACKUP command backs up the database to a directory specified by the user. This backup can be copied using OS level copy. The restore will restore by overwriting the same database in the new environment or restoring to a new database name specified on the fly. Assume that 'SAMPLE' is the database in the old environment and 'SAMPLE1' is the target database in new environment. You can do the following from the command CLP. Note the following backup is an offline backup method.

    • Back up using the command db2 backup database sample to E:\SampleBKUP.
    • Copy using the OS, copying the entire folder, SampleBKUP, to the new environment
    • Restore using the command db2 restore database sample from E:\YYY\SampleBKUP (restore into existing database) OR
    • db2 restore database sample from E:\YYY\SampleBKUP into SAMPLE1 (restore into a new database).
  • Scenario 3 - Migrating a database from lower to higher version within the same OS (Oracle 8.1.7 to 9iR2 in different machines)

    The best way is to use the supported migration path by using the import and export utility. Note that the lower version to higher version migration is a supported one before attempting it. Refer to Oracle Metalink Note Id 132904.1 for compatibility. In this case, you actually have a few choices:

    • Full database export
    • Tablespace level export
    • Schema level export
    • Table level export

    We will consider using tablespace level transportable export/import. Assuming we have a tablespace USERS with schema LILY to be transported.

    Do the following on the Oracle 8.1.7 (Source)

    • Alter tablespace USERS read only
    • exp \"sys/password as sysdba\" file=trans817.dmp tablespaces=users transport_tablespace=y
    • Copy the data files belong to USERS tablespace and trans817.dmp to tape over to the new machine where Oracle 9iR2 is
    • Drop tablespace users including contents (if USERS tablespace already exists)
    • imp \"sys/password as sysdba\" file=trans817.dmp transport_tablespace=y datafiles=users01.dbf

    Note that to use transportable tablespaces, the Oracle versions have to be 8i or 9i. You can transport to higher but not lower. Refer to Metalink note 77523.1 and 291024.1 Also, you have to adhere to the following restrictions before using transportable tablespace export/import.

    • Operating need to be the same for Oracle 8i and 9i. For Oracle 10g, transportable tablespace can be used across different platforms. Also, transportable tablespace is not supported in Oracle 7
    • Same data block size and charset for source and target databases
    • Target should not have preexist tablespace name
    • Snapshot/replication, function-based indexes,Scoped REFs, domain indexes etc are not supported
    • The tablespace must be self contained

    For DB2 UDB, since the operating system is the same for source and target databases, this scenario is the same as the first scenario 2 above. Simply copy the backup folder to the new machine and restore it.

  • Scenario 4 - Migrating a database from within same DB versions to different OS (for example from Windows 2000 SP4 to AIX 5.2 ML4)

    In this scenario, you have no other choice in Oracle other than export and import. You can either do full database export, schema level or table level. Note, for transportable tablespaces across different platforms to work, you need to have at least Oracle 10g. Follow the steps below to do full export and import. Make sure the role IMP_FULL_DATABASE is enabled. To do a full database export and import, follow the steps below.

    • imp system/manager full=y constraints=n indexes=n file=full817.dmp log=full817_1.dmp
    • imp system/manager full=y constraints=y indexes=y rows=n ignore=y file=full817.dmp log=full817_2.log

    Note that you will see a whole bunch of "objects already exist" errors, since the system and sys objects already existed.

    In DB2 UDB, there are two ways to accomplish this.

    • Using Backup and Restore - backup/restore works between AIX and Solaris and HPUX
    • Using db2move utility - Moving databases in/out different platforms including Linux, Unix, Windows (LUW) and databases residing in host systems

    To use Backup & Restore option, follow the steps below

    • Issue the backup command from the source machine (for example AIX), db2 'backup database sample to "/home/db2inst1/sample"'.
    • FTP the file with long name over to the target (for example Solaris).
    • Issue the restore command, db2 'restore database sample from "/home/db2inst1"'

    To use DB2move, Db2move will first export data into ixf format. Follow the steps below:

    • Export data in DB2 UDB 8.2 in Windows by issuing the command, db2move sample export -tc administrator
    • FTP over to AIX machine.
    • Precreate tables to reside in desired table space. Note, although db2move creates the tables for you, it may not be on the desirable table space. Use the command db2look -d sample -u administrator -e -m -o sample.sql to find out the table space for the tables.
    • Issue the command in AIX, db2move sample load -lo replace. If your table is not pre-created, you will get SQL3304N The table does not exist message. (Please refer to DB2 UDB Information Centre as db2move ofters many more options than are presented here).
  • Scenario 5 Migrating a database from a lower to a higher version between different operating systems

    In Oracle, if your database is 8i or 9i, transportable tablespace cannot be used to go across platforms. In the event of 8i or 9i, the only option you have is imp/exp. However, if you are using 10g, the best way to go about this is to use transportable tablespaces.

    In DB2 UDB, this is the same as Scenario 4. You have the options of using DB2 Backup and Restore or db2move.


Conclusion

Though there are many ways to accomplish data movement within Oracle and DB2 UDB, often the factor that decides one method over the other is an economical one. The DBA's tasks will often have to conform with the constraints of the current environment. In this article we've looked at some ways data movement can be accomplised in both the Oracle and DB2 worlds. In addition, we introduced some tools for managing DB2 databases such as the IBM DB2 Test Database Generator and the high speed unload tool, IBM DB2 High Performance Unload for Multiplatform. For users who want to migrate from Oracle to DB2 UDB, IBM MTK is a complementary tool to speed up the migration.


Acknowlegdement

Special thanks to Amyris Rada, the team leader for the i2 Partner Integration Center, IBM Toronto, Canada. Amryis contributed to the IBM Migration Toolkit (MTK) portion of this article.

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=82649
ArticleTitle=DB2 Universal Database versus Oracle data movement
publish-date=05052005