Leverage MySQL skills to learn DB2 Express, Part 4: DB2 versus MySQL data movement

Document options Document options requiring JavaScript are not displayed Discuss Learn and share! Exchange know-how with your peers -- try our new Pass It Along beta app Rate this page Help us improve this content

Level: Introductory

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

12 Oct 2006

If you're a DBA with responsibilities across both MySQL and IBM® DB2® Express-C, you need to understand how the capabilities of these two database servers compare. This article is the fourth in a series of articles designed to assist DBAs coming from a MySQL background learn how to administer DB2 Express. In this article, learn how data movement techniques compare in these two products. Techniques discussed include Import, Export, Load, and other utilities specifically for data movement.

Introduction and background

On July 28, 2006, IBM announced the next DB2 for Linux®, UNIX®, and Windows® version, a hybrid data server designed to meet today's rigorous business demand. With this version comes the first industrial-strength data server that supports native XML storage. Other features such as compression, security, and autonomic capabilities are also enhanced.

Find quick reviews and in-depth discussions for features supported by DB2 Express-C 9 on the developerWorks Information Management resource page for DB2 for Linux, UNIX, and Windows. Such features include PureXML™, which ensures enterprises the ability to harness the XML capability in a native way. With the DB2 Express-C 9 PureXML feature, there is no longer the need to store documents in large objects (LOBs) or shred them into objects. Though highly desirable for small and medium enterprises, IBM DB2 Express-C does not come with all available features in the higher editions. DB2 Express-C does not include the following features that are found in other editions of DB2 9:

• Replication (Homogeneous Replication is supported, though)
• Database partitioning feature
• Connection Concentrator
• DB2 Geodetic Extender
• Query Patroller
• Net Search Extender
• Compression
• Label Base Access Control - LBAC

Again, bear in mind that IBM DB2 Express-C 9 is "Free to develop," "free to deploy," and "Free to distribute."

Going from DB2 Express-C 8 to DB2 Express-C 9, there are many enhancements that are beyond the scope of this article. Data movement, however, remains largely the same in terms of features and functionalities. This article provides an overview and how-to for IBM DB2 Express-C 9.1's data movement. The artcicle focuses mainly on their capabilities with some mention of MySQL's data movement capabilities. If you are already familiar with MySQL's data movement, you can read right into how IBM DB2 Express-C 9.1's data movement is carried out in the "MySQL data movement" section.

Back to top

Topics covered - Overview

This article first introduces the data movement in MySQL. Subsequently, the article discusses IBM DB2 Express-C's data movement, which can be divided into the following topics.

• Import - Import utility and DB2 Load
• Export - Export utility
• Other tools available

Back to top

MySQL data movement

There are primarily two utilities provided by MySQL for data movement. They are mysqldump and mysqlhotcopy. Though most widely used for backup and recovery, these utilites can be used to make a copy of the existing tables or the entire database, thus enabling the movement of data (usually small and medium size database) from one database to another within the same server or even different servers. Choosing between mysqldump and mysqlhotcopy will depend on the size of databases to be copied, cost of setting up, locking mechansim, restore options, type of tables, and so on. Table 1 compares these two utilities provided by MySQL.

Though there are many ways to make a copy of databases, or part of a database, by methods such as O/S level copying, select ... into outfile, mysqlsnapshot, and hot backup for InnoDB, this article only focuses on mysqldump and mysqlhotcopy. The comparisons between various methods mentioned, with their pros and cons, is out of the scope of this article.

					
mysqldump --opt db_name > backup-file.sql
 

					
mysql db_name < backup-file.sql
 

Some of the important flags supported by mysqldump are listed as follows in Listing 3. For a complete list, issue the command mysqldump -?.
Listing 3. mysqldump options

					
  -A, --all-databases Dump all the databases. This will be same as --databases
                      with all databases selected.
  --add-drop-database Add a 'DROP DATABASE' before each create.
  --add-drop-table    Add a 'drop table' before each create.
  --add-locks         Add locks around insert statements.
  --allow-keywords    Allow creation of column names that are keywords.
  --character-sets-dir=name
                      Directory where character sets are.
  -c, --complete-insert
                      Use complete insert statements.
  -C, --compress      Use compression in server/client protocol.
  --create-options    Include all MySQL specific create options.
  -B, --databases     To dump several databases. Note the difference in usage;
                      In this case no tables are given. All name arguments are
                      regarded as databasenames. 'USE db_name;' will be
                      included in the output.
                      Set the default character set.
  --delayed-insert    Insert rows with INSERT DELAYED;
  --delete-master-logs
                      Delete logs on master after backup. This automatically
                      enables --master-data.
  -e, --extended-insert
                      Allows utilization of the new, much faster INSERT syntax.
  --fields-terminated-by=name
                      Fields in the textfile are terminated by ...
  --fields-enclosed-by=name
                      Fields in the importfile are enclosed by ...
  --fields-optionally-enclosed-by=name
                      Fields in the i.file are opt. enclosed by ...
  --fields-escaped-by=name
                      Fields in the i.file are escaped by ...
  --order-by-primary  Sorts each table's rows by primary key, or first unique
                     key, if such a key exists.  Useful when dumping a MyISAM
                     table to be loaded into an InnoDB table, but will make
                     the dump itself take considerably longer.
  --single-transaction
                     Creates a consistent snapshot by dumping all tables in a
                     single transaction. Works ONLY for tables stored in
                     storage engines which support multiversioning (currently
                     only InnoDB does); the dump is NOT guaranteed to be
                     consistent for other storage engines. Option
                     automatically turns off --lock-tables.
   -T, --tab=name      Creates tab separated textfile for each table to given
                     path. (creates .sql and .txt files). NOTE: This only
                     works if mysqldump is run on the same machine as the
                     mysqld daemon.
   --tables            Overrides option --databases (-B).
   --triggers          Dump triggers for each dumped table
   -X, --xml           Dump a database as well formed XML.
 

Though mysqldump comes handy for backing up small or medium size tables and databases, the closest kin to this utility with greater horsepower is mysqlhotcopy. Note however, that mysqlhotcopy may not enjoy all flexibilities available in mysqldump. One that comes to mind is that it can be run only on the same machine where the database directories are located.

The remainder of the article is devoted to how IBM DB2 Express-C data movement is carried out.

Back to top

DB2 Import

What is DB2 Import? Contrary to MySQL's mysqldump and mysqlhotcopy, which have dual usage -- backup and recovery and data movement. DB2 Import (db2import) is an utility provided by IBM DB2 Express-C 9.1 (and all other flavors of DB2 data server) to import data from files of various formats into either tables or updateable views. Unlike MySQL, for backup and recovery, DB2 comes with a backup and recovery mechanism using db2backup and db2recover. The article "DB2 versus MySQL backup and recovery" (developerworks, June 2006) gives an overview of MySQL and IBM DB2 Express-C 9.1 backup and recovery comparisons.

There are few key characteristics with DB2 Import. In order to use DB2 Import, you must acquire the proper authority and privileges without which you will not be able to perform an import without error. The authorities and privileges required to import files into databases are listed in the following table:

Similar to most administrative tasks, DB2 Import can be administered from command prompt CLP and DB2 Control Center. Examine the import options and how they can be carried out in slightly more detail here.

DB2 Import provides a flexible way to import files from external applications, the same other sources such as other flavors of databases available in the market today. What's more about IBM DB2 Express-C is the native capability of XML store (instead of XML-enabled) and the import support that comes with it. In IBM DB2 Express-C, you can import XML documents into databases. DB2 also stores XML in parsed hierarchical format natively in the XQuery Data Model (XDM). During import, you can opt to validate XML documents to be imported. Error results for bad data during XML import if validate is enabled.

The syntax for DB2 Import can be obtained from command CLP, db2 ? import:

					
 IMPORT FROM filename OF {IXF | ASC | DEL | WSF}
[LOBS FROM lob-path [ {,lob-path} ... ] ]
[XML FROM xml-path [ {,xml-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} ... ] )}]
[XMLPARSE {STRIP | PRESERVE} WHITESPACE]
[XMLVALIDATE USING {XDS [DEFAULT schema-sqlid]
[IGNORE (schema-sqlid [ {,schema-sqlid} ... ])]
[MAP ((schema-sqlid,schema-sqlid) [ {(schema-sqlid,schema-sqlid)} ... ])] |
SCHEMA schema-sqlid | SCHEMALOCATION HINTS }]
[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]]}
  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, ...)}
 

Other than the options available, it's good to also note that DB2 Import supports four file formats:

• DEL: Delimited ASCII, for data exchange, among a wide variety of database managers and file managers. This common approach to storing data uses special character delimiters to separate column values. The default delimiter is a comma.
• ASC: Non-delimited ASCII, for importing or loading data from other applications that create flat text files with aligned column data.
• PC/IXF: PC version of the Integrated Exchange Format (IXF), the preferred method for data exchange within the database manager. PC/IXF is a structured description of a database table that contains an external representation of the internal table.
• WSF: Work-sheet format, for data exchange with products such as Lotus 1-2-3 and Symphony.

Let's now move on to see a few DB2 Import samples using command CLP. Later, you can see how DB2 Control Center can carry out the similar import tasks. To try the sample import, you can download IBM DB2 Express-C 9. Once you install it, run the command db2 db2sampl -sql -xml -schema 'db2admin'. (Make sure you enable the database to be UTF-8 or else you will get an SQL 1239N error.)

A few import samples this article includes are:

• Importing XML data (with or without validation)
• Importing a pipe (|) delimited file with insert mode

In order to import XML documents, there is often a need to ensure that the XML documents given are clean. Ill-formatted XML documents without validation causes problems during import. A good practice is to always register the schemas and, during import time, choose the right schema to validate against. The article samples show you both importing with and without using validation. To import XML documents is simple. Follow the few steps below:

1. Make sure you already created a table with XML column.
2. Use the following command in Listing 5 to import XML documents without validation. Note that it's always wise to include a message in any import. Should any failure occur, you can debug from the message file. This is an easier way to enable you to rectify the problems quicker.
   
   Listing 5. DB2 Import of XML document using command CLP
   

   IMPORT FROM "D:\XMLPoT\labdoc\scripts\data\import.del" OF DEL XML FROM "D:\XMLPoT\labdoc\scripts\data" METHOD P (1) MESSAGES "D:\db2in\xmlemp1.log" INSERT INTO DB2ADMIN.XMLEMP (EMP);

   
   where D:\XMLPoT\labdoc\scripts\data\import.del contains rows of pointers to the actual documents.
   
   Sample import.del content is as follows:
   
   Listing 6. Sample content for import.del
   

   "<XDS FIL='emp.001.xml' />" "<XDS FIL='emp.002.xml' />" "<XDS FIL='emp.003.xml' />" "<XDS FIL='emp.004.xml' />" "<XDS FIL='emp.005.xml' />" "<XDS FIL='emp.006.xml' />" "<XDS FIL='emp.007.xml' />"

   
   With a successful load, you should seeing something similar to the following message in the message file:
   
   Listing 7. Successful import
   

   SQL3109N The utility is beginning to load data from file "D:\XMLPoT\labdoc\scripts\data\import.del". SQL3110N The utility has completed processing. "42" rows were read from the input file. SQL3221W ...Begin COMMIT WORK. Input Record Count = "42". SQL3222W ...COMMIT of any database changes was successful. SQL3149N "42" rows were processed from the input file. "42" rows were successfully inserted into the table. "0" rows were rejected.

   
   
3. For most cases, when data is received from external systems, it's best to validate the XML import. In oder to do this, register the XML schema using the db2 register xmlschema command to register into the XML schema repository (XSR), and complete this process by issuing db2 complete xmlschema . You can view the list of registered schema from the system catalogue, SYSCAT.XSROBJECTS. Once registered, the command to import XML documents includes the XMLVALIDATE keyword.
   
   Listing 8.DB2 Import of XML document using command CLP -- with validation
   

   IMPORT FROM "D:\XMLPoT\labdoc\scripts\data\import.del" OF DEL XML FROM "D:\XMLPoT\labdoc\scripts\data" METHOD P (1) XMLVALIDATE USING SCHEMA DB2ADMIN.XMLEMP MESSAGES "D:\db2in\xmlemp2.log" INSERT INTO DB2ADMIN.XMLEMP (EMP);

   
   
4. With validation as a safeguard and to keep data clean, any unconformity will be returned as error in the message file.
   
   Listing 9. DB2 Import with XML validate error
   

   SQL3109N The utility is beginning to load data from file "D:\XMLPoT\labdoc\scripts\data\import.del". SQL3148W A row from the input file was not inserted into the table. SQLCODE "-16196" was returned. SQL16196N XML document contains an element "deptno" that is not correctly specified. Reason code = "37" SQLSTATE=2200M SQL3185W The previous error occurred while processing data from row "42" of the input file. SQL3110N The utility has completed processing. "42" rows were read from the input file. SQL3221W ...Begin COMMIT WORK. Input Record Count = "42". SQL3222W ...COMMIT of any database changes was successful. SQL3149N "42" rows were processed from the input file. "41" rows were successfully inserted into the table. "1" rows were rejected.

   
   
5. In order to carry the similar task, XML documents importing with validation in DB2 Control Center, there are only a few steps to follow, of which most are intuitive and easy to follow with online help available in case you are stuck. To invoke DB2 Import, start up DB2 Control Center, navigate to the target table, and right-click to choose import, as shown in Figure 1:
   
   Figure 1. Invoking DB2 Import from DB2 Control Center
   
   
   
6. Specify import file, import mode (INSERT, INSERT_UPDATE, REPLACE), and message file. A message tells you what goes wrong during import.
   
   Figure 2. DB2 Import - Specifying import file, mode, and message file
   
   
   
7. Click on DEL OPTIONS to get to the XML schema validation checkbox. Turn it on. Click on OPTIONS to pick the right XML schema to validate against. This is option two, Validate all documents using a specific XML schema, which is the most commonly used XML validation method. In this case, choose XMLEMP, and click OK.
   
   Figure 3. DB2 Import - Choose XML validation
   
   
   
8. In this step, choose the location for XML documents to be imported. Note that the DB2 Import utility supports the import of LOB objects as well. You can also control the identity and generated columns by setting either default, ignore, or missing.
   
   Figure 4. DB2 Import - Choose XML documents location
   
   
   
9. Finally, you can schedule the import of XML documents to be either immediate or repeated.
   
   Figure 5. DB2 Import - Scheduling for import
   
   
   

As you have seen earlier with the import of XML documents using the DB2 Import utility, importing a delimited file can be accomplished pretty much the same way using command prompt CLP. For example, the command below imports a pipe delimited file into the table employee:

					
IMPORT FROM "D:\db2out\employee.dat" OF DEL MODIFIED BY COLDEL| 
      METHOD P (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14) 
         MESSAGES "D:\db2out\employee2.log" 
         INSERT INTO DB2ADMIN.EMPLOYEE 
(EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, 
SEX, BIRTHDATE, SALARY, BONUS, COMM);
  

DB2 Import utility supports two table locking modes -- the offline and online modes. In offline mode (ALLOW NO ACCESS), there is no access allowed on the table being imported. Offline mode is the default behavior, as an exclusive lock is acquired at the beginning of an import. Online mode, however, allows users to access the data while import is going on. This mode allows commit at certain intervals to avoid deadlock. LOB objects can be imported just as normal data. For separately stored LOB objects in multiple files, LOBSINFILE file type modifier is required. DB2 import can import not only into table, but also hierarchy, nickname, or view.

Since various file formats are supported, together with the great flexibility provided by DB2 Import, it's not unusual to find a long list of file type modifiers for this utility. A complete list of the modifiers can be found in DB2 9.1 Data Movement Utilities Guide and Reference. Some of the commonly used modifiers are codepage, dateformat, timestampformat, reclen, and coldel. Though DB2 Import is flexible to use, what it lacks is the speed as compared to DB2 Load. For larger tables, DB2 Load is a preferable choice. Note, however, that DB2 Load, though fast, doesn't allow loading into hierarchies and nicknames. However, DB2 Import comes with its share of limitations.

The complete IBM DB2 Express-C import restrictions include:

• Referenced primary key by a foreign key in a dependent table cannot be replaced; only append is allowed.
• Importing data into a system table or a table with a structured type column is not allowed.
• You cannot import data into declared temporary tables.
• Views cannot be created through the import utility; only table can.
• Referential constraints and foreign key definitions are not preserved when creating tables from PC/IXF files.
• The maximum statement size of 2MB for import generated statements might be exceeded.
• You cannot recreate tables containing XML columns.

Finally, IBM DB2 Express-C ships with API with sample codes to allow users to code their own import. Sample codes are shipped with IBM DB2 Express-C install.

Back to top

DB2 Load

DB2 Load is another means to load huge quantities of data from flat files, tapes, or named pipes into DB2 tables. The DB2 Load utility, however, does not allow creation of tables on the fly during load time. In other words, tables and their indexes need to exist prior to the load.

There are four phases to DB2 Load:

• Load: During this phase, data is written to the table, and once data is loaded into the table, the index keys and table statistics are collected, if necessary. Save points (points of consistency) to ensure recovery are established at intervals specified through the SAVECOUNT option in the LOAD command. Messages are generated, indicating how many input rows were successfully loaded at the time of the save point. If a failure occurs, you can restart the load operation; the RESTART option automatically restarts the load operation from the last successful consistency point, or you can use the TERMINATE option that rolls back the failed load operation.
  
  
• Build: This is the phase to build indexes. During the build phase, indexes are produced based on the index keys collected during the load phase. The index keys are sorted during the load phase, and index statistics are collected (if STATISTICS USE PROFILE option was specified, and profile indicates collecting index stats). The statistics are similar to those collected through the RUNSTATS command. If a failure occurs during the build phase, the RESTART option automatically restarts the load operation at the appropriate point.
  
  
• Delete: This delete phase remove rows in the table that caused a unique key violation. Usually, unique key violations are placed into the exception table (if one was specified), and messages about rejected rows are written to the message file specified by the MESSAGE option. To ensure the successful completion of the load process, you have to review these messages to resolve any problems. This often requires you to insert corrected rows into the table from where you left off. Do not attempt to delete or modify any temporary files created by the load utility. Some temporary files are critical to the delete phase. Similar to the build phase, should a failure occur during the delete phase, the RESTART option automatically restarts the load operation at the appropriate point.
  
  
• Index copy: During this phase, the index data is copied from a system temporary table space to the original table space. This will only occur if a system temporary table space was specified for index creation during a load operation with the READ ACCESS option specified.

There are four formats supported by DB2 Load:

• ASC (non-delimited ASCII format)
• DEL (delimited ASCII format)
• IXF (integrated exchange format, PC version), exported from the same or from another DB2 table
• User defined CURSOR (a cursor declared against a SELECT or VALUES statement). Typically this requires user to write scripts.

Similar to DB2 Import, DB2 Load requires certain authorities and privileges to be granted. You need SYSADM or DBADM authority or at least a LOAD authority with the relevant privileges, as shown in the following table:

The load utility can be invoked through the command line processor (CLP), the Load wizard in the Control Center, or an application programming interface (API), db2Load. To begin the demonstration of DB2 Load capabilities, let's start with Command CLP. The complete command syntax for DB2 Load can be obtained by typing db2 ? load. Listing 11, below, shows the complete DB2 Load syntax. Though it works for partitioned environment, we will still focus on single partition environment, as IBM DB2 Express-C does not come bundled with the Data Partitioning Feature (DPF).

					
      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} ... ] )]
      [FOR EXCEPTION table-name [NOUNIQUEEXC NORANGEEXC]]
      [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}]
      [SET INTEGRITY 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
  

Let's continue the discussion on practically carrying out a DB2 Load by examining how DB2 Load can be invoked. And again, there are three ways to invoke DB2 Load -- through the command line processor (CLP), the Load wizard in the Control Center, or an application programming interface (API), db2Load. For example, to load a delimited file, the following command (in its simpler form) can be issued:

					
LOAD FROM "D:\db2out\employee.dat" OF DEL METHOD P (1, 2, 3, 4, 5, 6, 7, 
8, 9, 10, 11, 12, 13, 14) 
MESSAGES "D:\db2load\employee.log" 
INSERT INTO DB2ADMIN.EMPLOYEE (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT,
                               PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, 
                               SALARY, BONUS, COMM) 
COPY NO INDEXING 
MODE AUTOSELECT;
 

Though widely perceived as flexible and powerful, the sheer options available to choose from in command prompt syntax can be a daunting task. The other way to invoke load is to use the DB2 Control Center, which comes with online help to guide you appropriately in wizard-driven manner. DB2 Control Center affords an easy, guided way to easily walk you through a successful load, even if you are not that familiar. In the following steps, load a delimited file into table, employee, to get a feel of how the load can be accomplished in DB2 Control Center.

1. Once you invoke DB2 Load by choosing the table employee to choose the Load, you will be presented with the screen depicted in Figure 6.
   
   Note that there are eight steps in the wizards. You can, however, opt to choose the defaults for all by clicking next for all of them. Each step provides different options, of which some are required depending on your load requirements. In this simple demostration, for the first step, take the default Append data to a table. In this mode, you can allow users to access data while loading is in progress. Click Next to continue.
   
   Figure 6. DB2 Load - Step 1. Append or replace
   
   
   
2. The second step brings you to file format choosing (default is DEL). In this step, choose where the input file and message files are located -- either locally, where the DB2 Load is invoked, or remotely. You can also specify the total rows to be processed. Click Next to continue
   
   Figure 7. DB2 Load - Step 2. Specify the file locations
   
   
   
3. In this step, LOB objects' locations can be specified. Identity and generated column behavior can be specified. Also, you can choose what columns to include in the load. Take the default, and click Next.
   
   Figure 8. DB2 Load - Step 3. Specify the file locations
   
   
   
4. There are three options to index creation -- incremental, rebuild, or letting DB2 Load decide which is the best way to build index. There are certain levels of application control and, finally, DB2 Load has the intelligence to maximize the performance after the load.
   
   Figure 9. DB2 Load - Step 4. Index, application, and performance options
   
   
   
5. During DB2 Load, a system may crash. To enable you to recover from system crash, DB2 Load provides you with a crash recovery option, allowung you to specify the consistent point. Since DB2 Load logs minimal during transactions, forward recovery may be desirable. In this step, you can choose recoverable, where you can save a copy of backup image, or unrecoverable, to disallow recovery should failure occur.
   
   Figure 10. DB2 Load - Step 5. Recovery options
   
   
   
6. It is wise to let DB2 configuration advisor pick the right performance parameters if you are unsure. Some other parameters you can set here are the location of the exception table, exception dump files to contain rejected rows, and to suppress any warning received.
   
   Figure 11. DB2 Load - Step 6. More options
   
   
   

Steps 7 and 8 allow you to schedule (as shown earlier during DB2 Import). And the last step is a summary showing you the options you have chosen.

Last, but not least, some of the limitations of DB2 Load that you may want to take note of include:

• Loading data into nicknames, hierarchies, typed tables, declared temporary tables, data into tables containing XML columns, or tables with structured type columns is not supported.
• The original data in the table is lost if an error occurs during a LOAD REPLACE operation. The safeguard is to retain a copy of the input data to allow the load operation to be restarted in the event of failure.
• Triggers are not activated on newly loaded rows. Business rules associated with triggers are not enforced by the load utility.

Finally, to sum up the load utility, it's good to note its differences with its predecessor. The following table lists some of the differences between DB2 Import and Load:

Back to top

DB2 Export

In order to use DB2 Export to spit out data, you first must have SYSADM or DBADM authority or CONTROL or SELECT privilege for each table participating in the export operation. DB2 Export supports a similar set of file formats as DB2 Import , as mentioned earlier; they are DEL,PC/IXF, and WSF. As mentioned earlier as well, the complete syntax for a command can be obtained in Command CLP. The complete syntax for DB2 Export can be obtained by issuing the command db2 ? export. Listing 13 lists the complete DB2 Export syntax:

					
EXPORT TO filename OF {IXF | DEL | WSF}
[LOBS TO lob-path [ {,lob-path} ... ] ][LOBFILE lob-file [ {,lob-file} ... ] ]
[XML TO xml-path [ {,xml-path} ... ] ][XMLFILE filename [ {,filename} ... ] ]
[MODIFIED BY {filetype-mod ...}][XMLSAVESCHEMA]
[METHOD N ( column-name [ {,column-name} ... ] )] [MESSAGES message-file]
{select-statement | XQUERY xquery-statement |
HIERARCHY {STARTING sub-table-name |
(sub-table-name [{, sub-table-name} ...])} [WHERE ...] }
filetype-mod:
NODOUBLEDEL, LOBSINFILE, CHARDELx, COLDELx, DLDELx, DECPLUSBLANK
DECPTx, DATESISO, 1, 2, 3, 4, CODEPAGE=x, STRIPLZEROS, NOCHARDEL

DB2 Export utility can be invoked through the command line processor (CLP), the Load wizard in the Control Center, or an application programming interface (API). To examine, let's look at a few examples for DB2 Export invoked using Command CLP, which illustrate some of the export capabilities:

• First, let's examine what a typical export of delimited file looks like. The command in Listing 14 shows how to export the table, employee. Though by default, the delimiter, comma (,) is being used, there are no problems if you use other supported delimiters as well.
  
  Listing 14. DB2 Export to a delimited file
  

  EXPORT TO "D:\db2exp\employee.dat" OF DEL MESSAGES "D:\db2exp\employee.log" SELECT * FROM DB2ADMIN.EMPLOYEE;

  
  
• The second example shows how to export LOB objects into two directories. This prevents the first directory's overflow and the spill to be contained in a second directory by placing each LOB in one file.
  
  Listing 15. DB2 Export - Exporting LOB objects to multiple directories
  

  EXPORT TO "D:\db2exp\Lobs1\exportLoad.dat" OF DEL LOBS TO "D:\db2exp\Lobs1", "D:\db2exp\Lobs2" LOBFILE "Lob1", "Lob2" MODIFIED BY LOBSINFILE LOBSINSEPFILES MESSAGES "D:\db2exp\exportLoad.log" SELECT * FROM DB2ADMIN.EMP_PHOTO;

  
  
• As in the last example, let's take a look at how XML documents can be exported. The following command exports the XEmployee table. Each XML document is placed in a separate file with XEmployee being the base name. Using the following command, XEmployee.del, contains a list of documents, such as <XDS FIL='XEmployee.001.xml' />, while the actual document that contains data, such as XEmployee.001.xml, is being exported to the directory specified by the XML TO option. What is not seen in the following example is the ability to save a copy of the XML schema using XMLSAVESCHEMA option.
  
  Listing 16. DB2 Export - Exporting XML documents to separate files
  

  EXPORT TO "D:\db2XML\XEmployee.del" OF DEL XML TO "D:\db2XML\data" XMLFILE "XEmployee" MODIFIED BY XMLINSEPFILES MESSAGES "D:\db2XML\XEmployee.log" SELECT * FROM "ALLAN WH THAM".XEMPLOYEE;

  
  

To illustrate the use of DB2 Control Center for export purpose, follow through one sample of exporting to XML documents (the last example in Command CLP export).

1. Right-click on the table that needs to be exported, and choose Export. In the first step of DB2 Export, you are presented with the output and message file options.
   
   Figure 12. DB2 Export - Target locations
   
   
   
2. In this Columns tab, specify the actual path for XML documents to be exported. Also specify the base name, which is the name used in XEmployee.del. Note the the checkbox Place each XML document in a separate file represents the option XMLINSEPFILES. This option allows the XML document to be stored separately.
   
   Figure 13. DB2 Export - More options for XML export
   
   
   
3. The last tab allows you to run the export immediately or schedule it to run either once or repeatedly.

Finally, some of the points to remember for DB2 are:

• You can use supported delimiters other than the default comma delimiter.
• You can store XML documents in one file, unless the XMLINSEPFILES file type modifier is set, which will store the output separately in each file.
• Before export, ensure that the table you want to export is not locked in any manner.
• If you are moving data between databases, use PC/IXF format to do the import.
• Alias for table is allowed in export statement.
• You do not have to export all columns; only columns that interest you alone can be exported.
• For LOBs and XML, overflow path can be specified.

Back to top

Other tools

Other than the usual DB2 Import, Load, and Export, there are other utilities/tools provided by DB2 to ease the data movement. Some are built in features such as db2move and DB2 Replication (homogenous replication using SQL-Rep). For high speed and heterogeneous replication, IBM WebSphere Replication Server can be purchased separately. For high load and unload, customers have a choice of purchasing IBM DB2 High Performance Unload for Multiplatform. IBM DB2 High Performance Unload for Multiplatform is a high-speed unload tool for Linux, UNIX, and Windows. 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. For a customer who desires to migrate from MySQL to DB2 Express-C, the IBM DB2 Migration Toolkit (MTK) is provided as well as a free tool.

The remaining sections examine db2move, a built-in feature for cross-platform data movement utility. Subsequently, look at some key features provided by IBM DB2 Migration Toolkit (MTK) for MySQL to IBM DB2 Express-C migration.

db2move

Suitable as a utility to move tables from one database to the other, db2move has its advantages of choosing between import or export. It exports tables to PC/IXF format before they are imported or loaded back into databases that can be of the same machine or across platform. As hardware architecture between different platforms may vary, which results in backup and recovery, DB2 may not be compatible from the target to the source. For example, AIX, HPUX, Sun Solaris, and Linux on PowerPC all have big endianess, while Linux on IA-64, Linux on AMD64 and Intel® EM64T, and 32-bit Linux on x86 have small endianess. Due to this, db2move is a utility suitable to move data across these platforms. The other advantage for db2move is the ability to create a duplicate based on schema owner. The option -co allows duplicate schema to be created.

To use db2move is easy. It works in conjunction with DB2 Import, Export, and Load. The syntax itself isn't complicated, though some options are specific to either Import, Load, Export, or Copy. The syntax can be seen as follows:

					
   Usage: "db2move <dbname> <action> [options]"

  <dbname> is the name of the database.

  <action> is one of:
     EXPORT - Export all tables that meet the filtering criteria in [options]
              (If no [options] specified, export all tables).
              Internal staging information is stored in file 'db2move.lst'.
              Messages are stored in 'EXPORT.msg'.

     IMPORT - Import all tables listed in the staging file 'db2move.lst'.
              Messages are stored in 'IMPORT.msg'..
              Use the -io option for IMPORT specific actions.

     LOAD   - Load all tables listed in the staging file 'db2move.lst'.
              Messages are stored in 'LOAD.msg'.
              Use the -lo option for LOAD specific actions.

     COPY   - Duplicate a schema(s) into a target database.
              Use the -sn option to specify one or more schemas.
              See the -co option for COPY specific options.
              Use the -tn or -tf option to filter tables in LOAD_ONLY mode.
              Messages are stored in 'COPY.<timestamp>.msg'
              Error only messages in 'COPY.<timestamp>.err'
              Load messages in 'LOADTABLE.<timestamp>.msg'
              List of tables that failed Load in 'LOADTABLE.<timestamp>.err' 

    Some of the options available:
	      
     -tc <value>  Table Definers. Filter on     EXPORT
              SYSCAT.TABLES.DEFINER

     -tn <value>  Table Names. Filter on        EXPORT
              SYSCAT.TABLES.TABNAME             COPY

     -sn <value>  Schema Names. Filter on       EXPORT,
                   SYSCAT.TABLES.TABSCHEMA      COPY

     -ts <value>  Tablespace Names. Filter on   EXPORT
                  SYSCAT.TABLES.TBSPACE

     -tf <file>   Fully qualified list of       EXPORT
                   table names in <file>        COPY

     -aw          Allow warnings                EXPORT

     -io <value>  IMPORT specific actions.      IMPORT
                 Default is REPLACE_CREATE
                 (see docs for limitations
                 of Import create function)

     -lo <value>  LOAD specific options.        LOAD
                  Default is INSERT

     -l <path>    Path to lobfiles.             EXPORT,
                   Default is cwd               IMPORT,
                                                LOAD

     -u <value>   Userid to connect to db.      ALL
                  Default is current user

     -p <value>   Password to connect to db.    ALL

     -co <opt>    COPY specific options.        COPY

A few practical examples may better illustrate its usage:

• In its simplest form, to export all tables from database sample:
  
  Listing 18. DB2Move - Export all tables in Sample
  

  db2move sample export

  
  
• Exporting some tables using wildcard:
  
  Listing 19. DB2Move - Export tables using wildcard
  

  db2move sample export -tn emp*

  
  
• Loading all tables into sample database. Make sure the tables are already pre-created:
  
  Listing 20. DB2Move - Load all tables into Sample database
  

  db2move sample load -l d:\db2exp

  
  
• Finally, to make a copy of schema allan in sample database and duplicate it in sample1 database. The data from schema allan is duplicated to allan1. This command also copies schema allan into allan1 from the source database sample to the target database sample1. You have another -- copy only the DDL (DDL_ONLY) and table space mapping (TABLESPACE_MAP) for the target database. The COPY schema operation uses the COPYSCHEMA_<timestamp>.MSG message file, and the COPYSCHEMA_<timestamp>.err error file can be found on the working directory.
  
  Listing 21. DB2Move - Move using COPY option
  

  db2move sample COPY -sn allan -co TARGET_DB sample1 USER awt USING password SCHEMA_MAP ((allan,allan1))

  
  

Some limitations using db2move with COPY option to copy a schema. It will copy all allowable schema objects with the exception of the following types:

• Table hierarchy
• Staging tables (not supported by the load utility in multiple partition database environments)
• Jars (Java™ routine archives)
• Nicknames
• Packages
• View hierarchies
• Object privileges (All new objects are created with default authorizations)
• Statistics (New objects do not contain statistics information)
• Index extensions (user-defined structured type related)
• User-defined structured types and their transform functions

IBM DB2 Migration Toolkit (MTK)

The IBM Migration Toolkit, though not specificly created for MySQL to DB2 migration, can be used to migrate data from MySQL to DB2.

The latest update of the DB2 Migration Toolkit MySQL 2.0.1.1 is available for download.

A list of features supported in this release and its documentation can be found in the README of the product. Release 2.0.1.1 of the IBM DB2 Migration Toolkit includes the following new and changed features:

• Support for migrations from MySQL 4 and MySQL 5 to:
  • Informix Dynamic Server V9 and higher
  • DB2 LUW V8.2 and higher
  • DB2 iSeries V5 and higher

IBM DB2 Migration Toolkit 2.0.1.1 is of alpha quality and supports the migration of DDL constructs, such as tables, views, synonyms, and constraints.

For DB2 Migration Toolkit conversions from MySQL 4 and MySQL 5, the toolkit has the following limitations:

• The IMPORT option in the specify source tab is not supported
• The Trigger and Procedures/Functions are not supported
• MySQL data type properties are not supported
• Only the JDBC connection to the MySQL database is supported
• ENUM and SET data types are not supported
• Special registers are not supported (for example, CURRENT_TIMESTAMP)
• MySQL stores the current time stamp value in the time stamp data type by default; DB2 stores null values in the time stamp data type by default
• The ON-UPDATE clause is not supported as a default value
• On a UNIX system, deploying the LOB date using the LOAD option is not supported. Instead, use the IMPORT option.
• Extraction of views is not supported
• The AUTOINCREMENT attribute is not supported

Back to top

Conclusion

This article gave a general overview of MySQL data movement options with a table drawn to compare with MySQL's utilities mysqldump and mysqlhotcopy. After a brief discussion regarding MySQL data movement, the article introduced IBM DB2 Express-C 9.1's capability to accomplish data movement. Utilities such as DB2 Import, Load, and Export were examined in details. Finally, some other tools, both built-in and separate purchase, were discussed to give more insight to what is available and what viable solutions could be used should database/schema duplication, high speed loading/unloading is required, or even when the entire MySQL to DB2 migration is desired.

With such introductory, MySQL DBAs should be able to decide the options available when it comes to IBM DB2 Express-C data movement administration.

Back to top

Disclaimer

This article is written to the best of our knowledge. Should you find any discrepancy, please feel free to contact us.

Resources

• "Optimize the user interface of DB2 CommonStore for Lotus Domino" (developerWorks, November 2005): Learn how to customize user interface for IBM CommonStore for Lotus Domino.
  
  
• "Explore the IBM Mail Management and Compliance Solution" (developerWorks, September 2005): Explore the value proposition of the IBM mail archiving solution, the extensibility of the infrastructure to support records management/compliance, and how IBM weaves together content management, storage management, and records management into a powerful enterprise solution.
  
  
• MySQL to DB2 UDB Conversion Guide (IBM Redbook): Find best practices in migration strategy and planning, migration tools, and practical migration examples in this step-by-step guide for installing and using the IBM DB2 Migration Toolkit.
  
  
• Porting to DB2 UDB technical resources and roadmap: Find the information you need to port an application and its data from other database management systems to DB2 UDB.
  
  
• DB2 Migrate Now!: Choose the correct Migration Toolkit for your source and target combination.
  
  
• developerWorks resource page for DB2 for Linux, UNIX, and Windows: Read articles and tutorials and connect to other resources to expand your DB2 skills.
  
  
• DB2 Express-C: Learn about the no-charge version of DB2 Express Edition for the community.
  
  
• developerWorks Information Management zone: Learn more about DB2. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

• Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.
  
  
• Download the DB2 Developer Workbench.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

• Participate in the discussion forum.
  
  
• Participate in developerWorks blogs and get involved in the developerWorks community.

About the author

		Allan Tham works as a DB2 Content Manager Technical Presales Support for Business Partners. He helps business partners solve a wide range of technical issues. Allan is certified for administration of DB2 Content Management. Prior to joining IBM, Allan worked in an end-user environment, where he was an Oracle DBA for three years.

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top