Export , Import & Load utility in DB2 : Part -1

Types of External file formats 
Db2 Supports 4 different types of External Files ::

–>Delimited ASCII(DEL)
–>NON Delimited ASCII
–>PC Integrated Exchange Format(IXF) {**Binary Format**}
–>Worksheet Format(WSF) {for exporting a table to Lotus 1-2-3 & Symphony Spreadsheet)

Storing data in External Files ::(3 methods )
1)Name Method : its is used when you export data from DB2 UDB database.its is used when exporting data between DB2 product in IXF format oir when you export data to Lotus products in WSF format.Similarly its used when importing data from Db2 & Lotus products to Db2

2)Location Method : Used with Non Delimited ASC files. Db2 wont support exporting data in this format. So you can only use when importing data.

3)Position method : Its used when importing or exporting files in IXF or DEL format.You an use this method when transferring data between DB2 products in IXF. ALSo you can use it for transferring data between DB2 UDB & other databases that reqire DEL format.

EXPORT UTILITY :: It enables you to Extract data form tables & views in Db2 database in format that can be used by other databse or applications.The files formats are , DEL , IXF, WSF.

==>To use Export Utility u should have SYSADM or DBADM authority or CONTROL or SELECT priveleges on each relevant table

syntax–>>
Export to [filename] of [del|wsf|ixf]
<LOBS TO [LOBPath,...]>
<MODIFIED BY [Modifier , ...]>
<METHOD N ([ColumnName, ...])>
<MESSAGES[MsgFileName]>
[SELECT statement ]

for Eg::
(i) db2 export to myfile.ixf of ixf messages msgs.txt select * from staff     {The following example shows how to export information from the STAFF table in the SAMPLE database to the file myfile.ixf. The output will be in IXF format.}

(ii) db2 export to awards.ixf of ixf messages msgs.txt select * from staff where dept = 20    {The following example shows how to export the information about employees in Department 20 from the STAFF table in the SAMPLE database. The output will be in IXF format and will go into the awards.ixf file.}

(iii)db2 export to myfile.del of del lobs to mylobs/lobfile lobs1, lobs2 modified by lobsinfile select * from emp_photo {The following example shows how to export LOBs to a DEL file: }

(iv) export to products.txt of del select * from products

(v) export to products.txt of del lobs to C:\lobs modified by lobsinfile select * from products

Advertisements

About victimizeit
This is Atul.. working for IBM as a DataStage Developer. I may not be an expert on any particular DataStgae technology, but I'm sure I do know a few things about DB2, AIX, Unix, Windows, and DataBase. In this blog, I'll give out some tips on these subjects. If you find them useful, great, I'll be happy. Thanks for stopping by !!

What is your opinion ?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: