DB2 LUW: Import & Export example

Little background on data movement utilities:

Three major data movement utilities are available in DB2 LUW:

  • Export
  • Import
  • Load

These data movement utilities support various file formats:

  • Non-delimited or fixed length ASCII (ASC)
  • Delimited ASCII (DEL)
  • PC Version of the Integrated Exchange Format (PC/IXF)
  • Worksheet Format (WSF)
  • Cursor

ASC:
This file type contains ASCII data in fixed length to align with column data.
Each ASC file is a stream of ASCII characters consisting of data values ordered by row and column. Rows are separated by row delimiters, which are assumed to be newline characters.

DEL:
Most common file format used by a variety of database managers for data exchange
It contains ASCII data and uses special character delimiters to separate column values.
Rows in the data stream are separated by a newline character as the row delimiter.

PC/IXF:
This is a structured description of a database table. We will see this with example in the coming slides.
This file format can be used not only to import data but also to create a table that does not already exist in the target database.

WSF:
Data stored in this format can be interpreted in worksheets. It can be used for export and import only.

Cursor:
A cursor is declared with a query. It can only be used as the input of a load operation. We will see this with example in the coming slides.

The below steps explain the process of Import and Export in DEL format:

Step1: Create a database, table and data file

-bash-4.1$ db2 “CREATE DATABASE testdb”
DB20000I The CREATE DATABASE command completed successfully.

-bash-4.1$ db2 “CONNECT TO testdb”

Database Connection Information

Database server = DB2/LINUXX8664 10.5.7
SQL authorization ID = DB2INST1
Local database alias = TESTDB

-bash-4.1$ db2 “CREATE TABLE customer(cid INTEGER, cname VARCHAR(20), cage SMALLINT, cgender CHAR(1), caddress VARCHAR(50))”
DB20000I The SQL command completed successfully.

-bash-4.1$ cat customer.del (Note: extension of file can be anything!)

501|Ravi Nandigam|32|M|India
502|David Oklay|44|M|Ireland
503|Elmer Elandy|35|M|Canada
504|Prasanna A|24|F|India
505|Susan Mathew|56|F|USA
506|Michael Tango|30|M|Nigeria
507|Betty Lee|28|F|USA
508|Jennifer Lewis|12|F|UK
509|Sarah Thompson|89|F|UK
510|Nitin Joseph|22|M|India

Note: here column delimiter is ‘|’. You have to specify the same with option “coldel” while importing

Step2: Import above 10 rows in customer table

-bash-4.1$ db2 “IMPORT FROM /mnt/db2home/scripts/customer.del of DEL MODIFIED BY coldel| MESSAGES /mnt/db2home/scripts/customer_imp.msg INSERT INTO
db2inst1.customer”

Number of rows read = 10
Number of rows skipped = 0
Number of rows inserted = 10
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 10

-bash-4.1$ db2 “select count(*) from db2inst1.customer”

1
———–
10

1 record(s) selected.

Note: If any error/warning during process, it will write in customer_imp.msg file

Step3: Export customer table in DEL format

-bash-4.1$ db2 “connect to testdb”

Database Connection Information

Database server = DB2/LINUXX8664 10.5.7
SQL authorization ID = DB2INST1
Local database alias = TESTDB

-bash-4.1$ db2 “insert into db2inst1.customer values (511, ‘Andrew Roy’, 31, ‘M’, ‘Canada’)”
DB20000I The SQL command completed successfully.

-bash-4.1$ db2 “select count(*) from db2inst1.customer”

1
———–
11

1 record(s) selected.
-bash-4.1$ db2 “export to /mnt/db2home/scripts/customer_11.del OF DEL MODIFIED BY coldel| MESSAGES /mnt/db2home/scripts/customer_11.msg SELECT * FROM
db2inst1.customer”

Number of rows exported: 11

-bash-4.1$ cat /mnt/db2home/scripts/customer_11.del
501|”Ravi Nandigam”|32|”M”|”India”
502|”David Oklay”|44|”M”|”Ireland”
503|”Elmer Elandy”|35|”M”|”Canada”
504|”Prasanna A”|24|”F”|”India”
505|”Susan Mathew”|56|”F”|”USA”
506|”Michael Tango”|30|”M”|”Nigeria”
507|”Betty Lee”|28|”F”|”USA”
508|”Jennifer Lewis”|12|”F”|”UK”
509|”Sarah Thompson”|89|”F”|”UK”
510|”Nitin Joseph”|22|”M”|”India”
511|”Andrew Roy”|31|”M”|”Canada”

Note: OF DEL tell the export utility to export data in Delimited ASCII format. OF IXF tells to export the data in binary format

Feel free to use comments section for any queries/suggestions.

Cheers!
Ravi

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: