Export & Load some table in DB2

How can I export / Import some table in DB2 on command prompt

C:\Program Files\IBM\SQLLIB\BIN>db2
(c) Copyright IBM Corporation 1993,2002
Command Line Processor for DB2 ADCL 9.1.0

You can issue database manager commands and SQL statements from the command
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd


For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside
interactive mode, all commands must be prefixed with ‘db2’.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to DEV_DB user bcuaix using password

Database Connection Information
Database server = DB2/AIX64 9.5.1
SQL authorization ID = BCUAIX
Local database alias = DEV_DB

db2 => EXPORT TO ‘C:\Documents and Settings\david\AR_LIFECYCLE_STATUS_TYPE.DAT’
of DEL modified by NOCHARDEL coldel| messages ‘C:\Documents and Settings\david\A
R_LIFECYCLE_STATUS_TYPE.LOG.MSG’ SELECT * FROM UAT_DM.AR_LIFECYCLE_STATUS_TYPE

Number of rows exported: 10

SQL3107W There is at least one warning message in the message file.
db2 =>
db2 => LOAD CLIENT FROM ‘C:\Documents and Settings\david\AR_LIFECYCLE_STATUS_TYP
E.DAT’ of DEL modified by coldel| INSERT INTO UAT_DM.AR_LIFECYCLE_STATUS_TYPE_TM
P NONRECOVERABLE ALLOW READ ACCESS

Agent Type Node SQL Code Result
______________________________________________________________________________
LOAD 001 +00000000 Success.
______________________________________________________________________________
LOAD 002 +00000000 Success.
______________________________________________________________________________
PARTITION 000 +00000000 Success.
______________________________________________________________________________
PRE_PARTITION 000 +00000000 Success.
______________________________________________________________________________
RESULTS: 2 of 2 LOADs completed successfully.
______________________________________________________________________________

Summary of Partitioning Agents:
Rows Read = 10
Rows Rejected = 0
Rows Partitioned = 10

Summary of LOAD Agents:
Number of rows read = 10
Number of rows skipped = 0
Number of rows loaded = 10
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 10

db2 => quit
DB20000I The QUIT command completed successfully.
C:\Program Files\IBM\SQLLIB\BIN>

Advertisements

About davidand

Hi, welcome to my blogs. My name is David Andriansyah. I am currently working as an independent Cognos Business Intelligence consultant, and I also as Freelance Web Developer. View all posts by davidand

2 responses to “Export & Load some table in DB2

  • Asif

    Hi,
    I want to load a delimited file havind hex decimal delimiter…can you help me with the command i am using…

    LOAD FROM D:\out.csv OF DEL MODIFIED BY NOCHARDEL COLDEL0xac INSERT INTO branches

    This code is not recognizing the delimiter and loading all data in a single column

Leave a Reply

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: