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

IMPORT UTILITY To  import data into database

IMPORT from [filename] of [asc|del|wsf|ixf]
<lobs from [lobpath,...]>
<modified by [modifier,..]>
<method>
<commitcount[CommitCount]>
<restartcount[RestartCount]>
<messages[MsgFileName]>
[CRETAE | INSERT | INSERT_UPDATE | UPDATE|REPLCAE]
INTO [TABLENAME] <([ColumnName,....])>
IN [TSNAME] <INDEX IN [TSNAME]> <LONG IN [TSNAME]>>



CREATE :-  if this option is used , table is first created in the database before any data is imported. This option can only be used with IXF files.
INSERT:-  new data is added to the table without altering any of the existing data
INSERT_UPDATE:- new data is either added to the table or existing records are updated
REPLACE :- All existing data is deleted from the table before the new data is added.The table needs to be exist already
REPLACE_CREATE :- the table is first created if it does not already exist. Otherwise existing data is deleted from the table before new data is added.This option can only be used with IXF files. Also this option cant be used if the target table is a parent table that is referenced by foreign key.

eg: (i) import from cust.ixf of ixf insert into customers
(ii) import from cust.ixf of ixf lobs from c:\lobs modified by lobsinfile replace into customers
(iii)The following example shows how to import the table MOVIETABLE from the input file delfile1, which has data in the DEL format:

db2 import from delfile1 of del
modified by dldel|
insert into movietable (actorname,description,url_making_of,
url_movie) datalink specification (dl_url_default_prefix
"http://narang"), (dl_url_replace_prefix "http://bomdel"
dl_url_suffix ".mpeg")

Notes:

  1.  The table has four columns:

       actorname              VARCHAR(n)
       description            VARCHAR(m)
       url_making_of          DATALINK (with LINKTYPE URL)
       url_movie              DATALINK (with LINKTYPE URL)

 2.   The DATALINK data in the input file has the vertical bar (|) character as the sub-field delimiter.
 3.   If any column value for url_making_of does not have the prefix character sequence, “http://narang&#8221; is used.
 4.   Each non-NULL column value for url_movie will get “http://bomdel&#8221; as its prefix. Existing values are replaced.
 5.   Each non-NULL column value for url_movie will get “.mpeg” appended to the path. For example, if a column value of url_movie is “http://server1/x/y/z&#8221;, it will be stored as “http://bomdel/x/y/z.mpeg&#8221;; if the value is “/x/y/z”, it will be stored as “http://bomdel/x/y/z.mpeg&#8221;.

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: