Tuesday, June 25, 2013

15 DB2 Export/Import/load


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"




  • 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"



  • 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'”
--- 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




  1. a given article is very interesting and very useful for my admin thank you very much and sorry for my permission to share the article here

    Obat penyakit maag kronis
    Obat alami pengapuran tulang
    Cara mengatasi badan yang berlemak

  2. a pride for me to be able to discuss on a quality website because I just learned to make an article on
    cara menggugurkan kandungan

  3. Great Explanation and more useful for anyone.Thanks for sharing...
    oracle course in chennai

  4. Great Article. Thank you for sharing! Really an awesome post for every one.

    IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

    Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
    Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

  5. You have worked nicely with your insights that make our work easy. The information you have provided is really factual and significant for us. Keep sharing these types of articles, Thank you.china export data

  6. I know this is one of the most meaningful information for me. And I'm animated reading your article. But should remark on some general things, the website style is perfect; the articles are great. Thanks for the ton of tangible and attainable help. Shipping Container

  7. Determine in case you and your own circle of relatives have the choice and monetary functionality to come to be joint cruise deliver (or megayacht) proprietors.Shipping from China to Usa



SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates