Extract and insert tables (or portions of them) in DB2 – similar to BCP for Sybase and SQL Server

You’ll need to be in the DB2 Command Line for this. Also, the version of your DB2 client should match the version of the DB or be higher than the DB version (i.e. no using a version 7 client to go after a version 8 DB).

To get data out of a db2 table:

db2 "export to [file_name] of [file_type] select * from [table]"

[file_name] is a fully qualified name of the ouput file
[file_type] is either del (delimited) or ixf (db2 native format).
[table] is a fully qualified table, nickname, or view

For example:

db2 "export to /home/user/people.ixf of ixf select * from db2dba.people"

To import data into a db2 table:

db2 "import from [file_name] of [file_type] commitcount [value] insert into [table]"

For example:

db2 "import from /home/user/people.ixf of ixf commitcount 10000 insert into db2dba.people"

commitcount is optional but should be used to avoid filling up the transaction log. I generally use 10k to 20k.

Notice on the export command you use SQL syntax to get the data. You could use a where clause. You can also select and insert specific columns (Strongly recommended).

The import command allows you to insert into, replace into (replaces existing data), and create into (creates a new table).

You may wish to consult the DB2 Command Reference manual for more information

Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?
Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

Leave a Reply