Simplified Method to Export and Import Data from/to DB2 tables

Here is an easy way to export and import data from and into a DB2 Table.

-- EXPORT

1. Create a sql file (export.sql) with following content.

--

connect to db2Instance user username using password;

Export to /filepath/tableDataFile.dat of del modified by nochardel coldelx09

select * from schema.tableName with ur;

Connect reset;

terminate;

--

2. Execute the sql file like this:

db2 -tvf export.sql &



-- IMPORT

1. Create a sql file (import.sql) with following content.

--

CONNECT to db2Instance  user username using password;

LOAD CLIENT FROM '/filepath/tableDataFile.dat' OF DEL MODIFIED BY NOCHARDEL COLDELX09 MESSAGES '/filespath/tableDataFile.msg'

TEMPFILES PATH /tempfilespath  INSERT INTO schema.tableName NONRECOVERABLE INDEXING MODE REBUILD;

RUNSTATS ON TABLE schema.tableName WITH DISTRIBUTION AND INDEXES ALL ALLOW WRITE ACCESS;

CONNECT Reset;

TERMINATE;

--

2. Execute the sql file like this:
db2 -tvf import.sql &

How to handle huge data in and out of DB2 database?

I often come across situations where I need to export or import huge amounts of data into and out of databases. Hence I decided to collect all the information/steps regarding this, at one place. Here it is.

1. CSV files: If you want to create a sample or test data, you can use rand( ) function in EXCEL to generate random numbers. Then copy-and-paste to create huge files.

I had created a csv file with more than 4 million rows (datfile.csv, say).
SQL to be used to load this data into table say, myschema.my_temp_table, (assuming the table has already been created, to match the csv file):
--
IMPORT FROM '/home/data/datfile.csv' OF DEL MESSAGES '/tmp/messages.txt'
INSERT INTO myschema.my_temp_table ;
--

Embedd this SQL into a shell script, which looks like this:
##
db2 connect to InstanceName user username using passwd
db2 -tvf import_temp_table.sql # shown above
db2 "RUNSTATS ON TABLE myschema.my_temp_table WITH DISTRIBUTION AND INDEXES ALL ALLOW WRITE ACCESS"
db2 connect reset
db2 terminate
##

Easy? Try it.