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
Very nice and informative blog.
ReplyDeletemicrosoft office free download
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
ReplyDeleteObat penyakit maag kronis
Obat alami pengapuran tulang
Cara mengatasi badan yang berlemak
a pride for me to be able to discuss on a quality website because I just learned to make an article on
ReplyDeletecara menggugurkan kandungan
What a nice information to sharing the post. I really enjoyed the post..
ReplyDeletePLC Training in Chennai | PLC Training Institute in Chennai | PLC Training Center in Chennai | PLC SCADA Training in Chennai | Inplant Training in Chennai | PLC Training in Kerala
Good Post..Thanks for sharing such a wonderful article..
ReplyDeletePLC Training in Chennai | PLC Training Institute in Chennai | PLC Training Center in Chennai | PLC SCADA Training in Chennai | PLC SCADA DCS Training in Chennai | Best PLC Training in Chennai | Best PLC Training Institute in Chennai | PLC Training Centre in Chennai | Automation Training in Chennai | Automation Training Institute in Chennai
Great Explanation and more useful for anyone.Thanks for sharing...
ReplyDeleteoracle course in chennai
I really enjoyed your blog Thanks for sharing such an informative post.
ReplyDeleteclipping path
clipping path service
background removal
raster to vector
Shark Navigator Pro Review
DeleteUpright vacuum
ash vacuum cleaner review
Superb blog! I loved this blog. This content was very valuable for freshers and definitely, it will use in my future. Keep sharing...
ReplyDeleteOracle DBA Training in Chennai
oracle dba training institutes in chennai
Job Openings in Chennai
Linux Training in Chennai
Power BI Training in Chennai
Oracle Training in Chennai
Unix Training in Chennai
Social Media Marketing Courses in Chennai
Tableau Training in Chennai
Oracle DBA Training in Tambaram
Great Article. Thank you for sharing! Really an awesome post for every one.
ReplyDeleteIEEE 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
Mua vé máy bay tại Aivivu, tham khảo
ReplyDeletekinh nghiệm mua vé máy bay đi Mỹ giá rẻ
ve may bay tet pacific airlines
vé máy bay giá rẻ đi San Francisco
mua vé máy bay đi Pháp giá rẻ
vé máy bay đi anh bao nhiêu tiền
giá vé máy bay đi Los Angesles
combo hà nội đà nẵng 3 ngày 2 đêm
combo tour nha trang
rớt visa trung quốc
cách ly khách sạn trọn gói