10 DB2 Export/Import/load

Export

DB2 export utility can export the table data to 4 different file format:

• DEL : delimited ASCII format
• WSF: work sheet format
• IXF: Integration Exchange Format, PC version. Binary format

Below example is to export the file as binary format.

 db2 export to /tmp/department.ixf of ixf messages /tmp/export_department.log "select * from department"

The SQL statement must be in quote, otherwise, it will get the “SQL3022N  An SQL error "-104" occurred”

Below example is to export the file as DEL format , by default it is separate by common separate .

db2 export to /tmp/department.del of del messages /tmp/export_del_department.log "select * from department"

Import

• Only the binary format of the data ( IXF) can use CREATE INTO  and REPLACE_CREATE to create the table during the import time. However, if the sourcing table has index , the index may create fail because the import would try to create it with the same name as source table.
• REPLACE: this would delete all existing data before insert it.

 

Below example is to import the IXF data and create the table.

 db2 import from /tmp/department.ixf of ixf messages /tmp/import_ixf_department.log "create into department2"

The data looks good but index does not get created and the error is showing in the message file.

Below example is to import the del data with REPLACE

  db2 import from /tmp/department.del of del messages /tmp/import_del_department.log "replace into department2"

Load

• Another utility for loading data is using load command or db2load API.
• The load utility is faster than the import utility because it writes formatted pages directly to the database not running the individual insert as import .
• The load operation maintains:
• unique constrains.
• Range constrains for partition tables
• generated columns
• LBAC security rules.
• For other constrains, the table is placed in the SET INTERGRITY PENDING at beginning of the load. After the load, the SET INTERGRITY must be used to take the table out of pending state.
• If the db2 server and the load directory is not on the same host, add the client keyword after the load : ex  db2 load client ….

 

Below example is to load IXF data and replace existing data.

db2 load from /tmp/department.ixf of ixf messages /tmp/load_ixf_department.log replace into department2

Below example is to load IXF data and insert into the table.

db2 load from /tmp/department.ixf of ixf messages /tmp/load_ixf_department.log insert  into department2

Below example is to load DEL data and insert into the table.

 db2 load from /tmp/department.del of del messages /tmp/load_del_department.log insert  into department2

Another character is the load can replace existing data even there is a dependence for other table. Import can not replace the existing data if other tables has dependence on it.

-- Below would work

db2 load from /tmp/department.ixf of ixf messages /tmp/load_ixf_department.log replace into department

db2 “select tabname,status from syscat.tables where tabname='DEPARTMENT' and tabschema='DB2INST1'”

db2 “SET INTEGRITY  for DEPARTMENT,EMPLOYEE IMMEDIATE CHECKED”

--- Below would fail becuase department table has index, and constraint that is refence by other table

db2 import from /tmp/department.del of del messages /tmp/import_del_department.log "replace into department"

The load log shows the index would be rebuild after the load .

If the table reach an unstable state due to incomplete loading. We can use below statement to take out the unstable state.

db2 load client from /dev/null of ixf messages /tmp/xxx terminate into department2

 

Reference

• http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.cmd.doc%2Fdoc%2Fr0008305.html
• http://search400.techtarget.com/tip/Resolving-a-Load-Pending-state-on-a-tablespace
• http://www.ibm.com/developerworks/data/library/techarticle/0202kline/0202kline.html