Export , Import & Load utility in DB2 : Part – 3

LOAD Operation :: The LOAD utility write pages of data directly to the database instead of using SQL statements & does not record changes in any log files. This means that LOAD operation is faster than IMPORT transaction. However if LOAD transaction failes , the data cant be reloaded by performing a roll- forward recovery operation. To compensate for this , we have to create a backup copy of all data that is loaded so that it can be reloaded if necessary.

Syntax ::
LOAD <CLIENT> FROM [Filename of [DEL | ASC | IXF] | Pipename | Device| CursorName of Cursor , ...]
<LOBS FROM [LOBpath , ..]>
<MODIFIED BY [Modifier , ..]>
<Method>
<SAVECOUNT [SaveCount]>
<ROWCOUNT [RowCount]>
<WARNINGCOUNT [WarningCount]>

eg:
(i) LOAD from Cust.ixf of ixf messages load_msgs.txt insert into customers 



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;.

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

DB2

How to select last line in a table without CURSOR in DB2

Cursors will be used in DB2 SQL PL stored procedures to perform a complex logic on a row-by-row basis. There are four basic SQL PL statements for working with cursors:
– the DECLARE CURSOR statement to define a cursor
– the OPEN statement to adjust the cursor
– the FETCH statement to retrieve the next row of the cursor
– the CLOSE statement to deactivate the cursor
we can find out last row using cursor with some logic but we can do it without cursor also by using following logic…
db2 “select * from schema_name.table_name order by column_name desc fetch first 1 rows only “

 
 

njoy the simplicity…….
©Victimizeit

DBMS/SQL

1)  OReilly.SQL.Cookbook download
2)  OReilly.Oracle.PL.SQL.Programming.4th.Edition download
3)  Oracle PLSQL Best Practices  download
4)  Good sql_for_beginners  download
5)  DB2-733-V9 download
6)  DB2 – Quick Beginings for Unix download
7)  IBM Informix Guide to SQL Tutorial  download
8)  OReilly-Oracle_Language_Pocket_Reference  download
9)  Mcgraw Hill – Database Management Systems. 2nd Ed.  download
10)  Oracle_9i_SQL_Reference  download
11)   Beginning.DB2.from.Novice.to.Professional. download
12)   IBM.Press.DB2.9.for.Linux.UNIX.and.Windows.6th.Edition.(731)download
13)   SQL Getting Started. download
14)   DB2CookBook download
15)   Understanding DB2 – Learning Visually with Examples download  ( thanks Rohit )