Db2 export command example to export data to external file format (del , ixf )

Suppose you want to transfer data from a db2 database table to another db2 database or within the same database, one of the solution is to use db2 export / import command . Using db2 export / import you can move your data to excel also.

DB2 export command exports data selected by SQL statement from a table or view to an external files in the format of del, asc (ascii format), wsf, ixf etc.. del - delimited , wsf - worksheet format , ixf - Integration Exchange Format.

The DEL format uses delimiter and column separator and decimal point. The default string delimiter is double quote " and column separator is comma(,) and Decimal Point is dot(.), you can also specify your own string delimiter & column separator, decimal point. Another format supported by DB2 is   IXF (Integration Exchange Format). It is a generic relational database exchange format which supports an end-of-record delimiter . IXF architecture supports to exchange of relational database structures and data. IXF is used for transferring data across platforms. The following examples illustrates the use of Export command. You can see more about db2 import command at db2 import.

Example 1 : Export all rows in the Employee table to the file emp.ixf with message file msgs.txt for writing the messages or warnings

              db2 export to emp.ixf of ixf messages msgs.txt select * from employee

Example 2:The following command exports all rows in the Employee table to the file emp.del.

               db2 export to emp.del of del select * from employee

Example 3: The following command exports all rows in the Employee table to the file employee.del using the column delimiter semi colon(;) and string delimiter single quote (') and the decimal point comma,

              db2 export to employee.del of del modified by chardel'' coldel; decpt, select * from employee

    Output of the above is command is as follows.

'1002';'XYZ';+00050003,00;+00060000,00

'1003';'ABC';+00048750,00;+00055550,00

Some of the Parameters  applicable to DB2 Export command:

                MESSAGES message-file : Specifies the destination file to be created for writing warnings and error messages which is generated during an export operation. If the file already exists, then warnings & errors are appended. If msg file is omitted, the messages are written on the console. 

         In the Example 1, msgs.txt is created for information . Sample msgs.txt file has the messages of SQL3104N  

                 The Export utility is beginning to export data to file "emp.ixf". , SQL3105N The Export utility has finished exporting "2" rows.

METHOD N col-name : Specifies 1 or more column names to be written in the output file. If it is not specified, the column names in the table are used. This is valid only for WSF and IXF files . 

    Example : db2 export to emp.ixf of ixf method N (empcode , empname) messages msgs.txt select * from employee

MODIFIED BY filetype-mod : Specifies file type of DEL , IXF , WSF

Read More

How to insert data from external file to db2 database using db2 import

Do you have any data in excel file . Do you want to bring those data into db2 table. Suppose I am having the below data in excel file.

1001 Akash Kumar 15000.00 ak@xyz.com
1002 Micky 60000.00 micky@xyz.com
1003 Jackson 100000.00 jack@zyz.com

          How to bring these data into db2 table. Simple you can use the command db2 import. Steps to bring those data into db2 table. Two ways are there.

First one : In excel , save the data in any folder (eg. D:\db2imp) as CSV (Comma seperated value) (emp.csv) file by selecting the file type as CSV (MS-DOS )(*.csv). Now create a table or use the existing table (employee) with the field empcode, empname, salary, email. Give the following command.

D:\db2> db2 import from emp2.csv of del insert into emp (empcode,empname,salary,email)

              Now all the records from the emp.csv are inserted. In the above statement we have used the word of del which means it will seperate the data fields by a delimiter (,) . Here the default delimiter is Comma (,).

IInd way : In excel save the file as Text files by selecting file type as txt (MS-DOS) (*.txt) . Now open the file and put comma (,) between each data in each row and issue the above command.

db2 import command is used

1. To transfer data from one db2 database table to another db2 database table or within the same database . This can be done with the help of db2 export command . First export data in a specied format by putting delimiter . Then the data is imported to another table using import command.

2. To insert data from an external file with a supported file format (csv , DEL , ixf ) into a table , view.

Now let us see how to move data from one table to another table in the same database or another database in the same or any remote system. Give the following commands

D:\db2expimp> db2 export to emp.ixf of ixf messages msgs.txt select * from emp

Copy the emp.ixf file to the system where the target database lying then issue the following command.

D:\db2expimp> db2 import from emp.ixf of ixf messages msg.txt insert into emp ;

- where msgs.txt , msg,txt are message files where error / successful messages , warnings are stored.

Some more examples :

-The default delimiter for column seperation is Comma (,) . Suppose if you are using column seperator as # (eg. 1001# Akash Kumar# 15000.00# ak@xyz.com ) , then you have to use the following command .

D:\db2expimp>db2 import from emp.txt of del modified by coldel# insert into emp (empcode,empname,salary,email)

      - where coldel is used to mention the column delimiter. The default string delimiter is double quote("). You can also change the string delimiter to any charater by using modified by chardel newcharacter; 
    eg. db2 import from emp.txt of del modified by chardel'' insert into emp (empcode,empname,salary,email).

      To substitute for the period as a decimal point character, use decptx where x the character to substitute. For example (;) instead of decimal point (.) , use decpt;

            Now let us see how to insert identity column from a text file to table independent the availabilty of identity column in the text file.

stu.txt

"abc", 400

"xyz", 300

"abc1", 450

"xyz1", 200

In the above file , no rows have identity column values. But we can insert the data into tables with identity column values by the issuing the command

db2 import from stu.txt of del modified by identitymissing replace into stu

Replace identityignore by identitymissing for inserting identity column values for those rows missing identity values in the text file.

Read More