DB2 관리를 위한 import & export 정리

DB2 관리를 위한 import & export 정리

DB2도 공부해야 하네요...

이제는 진짜 이것저것 가리는게 없는듯 합니다.

원문 : http://m.blog.daum.net/initdb/31

===================================================

DB2 Table Export & Import

방법	설명
del 형식	csv 형태의 텍스트 파일로 export
ixf  형식	oracle exp 와 유사, 테이블 생성과 insert 작업을 동시에 지정

 

[테스트용 테이블 생성]

----------------------------------------------------------------------------------------------

$ db2 connect to sample                                             <----- sample 데이터베이스 접속

   Database Connection Information

 Database server        = DB2/AIX64 10.1.2
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

$db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.1.2

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 =>

db2 => create table tb_reorg ( a int, b char(10))                    <----- 테스트용 tb_reog 테이블 생성
DB20000I  The SQL command completed successfully.

db2 => insert into tb_reorg values (1, 'aaa')                         <----- tb_reorg 테이블에 데이터 INSERT
DB20000I  The SQL command completed successfully.
db2 => insert into tb_reorg values (2, 'bbb')
DB20000I  The SQL command completed successfully.
db2 => insert into tb_reorg values (3,'ccc')
DB20000I  The SQL command completed successfully.
db2 => insert into tb_reorg values (4,'ddd')
DB20000I  The SQL command completed successfully.
db2 => insert into tb_reorg values (5,'eee')
DB20000I  The SQL command completed successfully.
db2 => select * from tb_reorg                                              <----- tb_reorg 테이블의 데이터 검색

A           B
----------- ----------
          1 aaa
          2 bbb
          3 ccc
          4 ddd
          5 eee

  5 record(s) selected.

----------------------------------------------------------------------------------------------

 

[ del 형식 export / import ]

 

db2 => select * from tb_reorg     

A           B
----------- ----------
          1 aaa
          2 bbb
          3 ccc
          4 ddd
          5 eee

  5 record(s) selected.

db2 => export to tb_reorg_del.dmp of del select * from tb_reorg          <----- del 형식으로 tb_reorg 테이블에 대한 export 수행
SQL3104N  The Export utility is beginning to export data to file
"tb_reorg_del.dmp".

SQL3105N  The Export utility has finished exporting "5" rows.

Number of rows exported: 5

db2 => !file tb_reorg_del.dmp
tb_reorg_del.dmp: ascii text
db2 => delete from tb_reorg                                                          <-----  데이터 import를 위해, tb_reorg 테이블의 데이터 삭제
DB20000I  The SQL command completed successfully.
db2 => select * from tb_reorg

A           B
----------- ----------

  0 record(s) selected.

db2 => import from tb_reorg_del.dmp of del insert into tb_reorg          <----- del 형식으로 tb_reorg 테이블에 대한 import 수행
SQL3109N  The utility is beginning to load data from file "tb_reorg_del.dmp".

SQL3110N  The utility has completed processing.  "5" rows were read from the
input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "5".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "5" rows were processed from the input file.  "5" rows were
successfully inserted into the table.  "0" rows were rejected.

Number of rows read         = 5
Number of rows skipped      = 0
Number of rows inserted     = 5
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 5

db2 => select * from tb_reorg

A           B
----------- ----------
          1 aaa
          2 bbb
          3 ccc
          4 ddd
          5 eee

  5 record(s) selected.

db2 =>

 

 

[ ixf 형식 export / import ]

 

db2 => select * from tb_reorg

A           B
----------- ----------
          1 aaa
          2 bbb
          3 ccc
          4 ddd
          5 eee

  5 record(s) selected.

db2 => export to tb_reorg_ixf.dmp of ixf select * from tb_reorg         <-----  ixf 형식으로 tb_reorg 테이블에 대한 export 수행

SQL3104N  The Export utility is beginning to export data to file
"tb_reorg_ixf.dmp".

SQL3105N  The Export utility has finished exporting "5" rows.

Number of rows exported: 5

db2 => !file tb_reorg_ixf.dmp
tb_reorg_ixf.dmp: ascii text

db2 => drop table tb_reorg
DB20000I  The SQL command completed successfully.
db2 => import from tb_reorg_ixf.dmp of ixf create into tb_reorg         <----- ixf 형식으로 tb_reorg 테이블에 대한 import 수행
SQL3150N  The H record in the PC/IXF file has product "DB2    02.00", date
"20130509", and time "172936".

SQL3153N  The T record in the PC/IXF file has name "tb_reorg_ixf.dmp",
qualifier "", and source "            ".

SQL3109N  The utility is beginning to load data from file "tb_reorg_ixf.dmp".

SQL3110N  The utility has completed processing.  "5" rows were read from the
input file.

SQL3221W  ...Begin COMMIT WORK. Input Record Count = "5".

SQL3222W  ...COMMIT of any database changes was successful.

SQL3149N  "5" rows were processed from the input file.  "5" rows were
successfully inserted into the table.  "0" rows were rejected.

Number of rows read         = 5
Number of rows skipped      = 0
Number of rows inserted     = 5
Number of rows updated      = 0
Number of rows rejected     = 0
Number of rows committed    = 5

db2 => select * from tb_reorg

A           B
----------- ----------
          1 aaa
          2 bbb
          3 ccc
          4 ddd
          5 eee

  5 record(s) selected.

[ 테이블 정보 백업 ]

$ db2look -d sample -e -t "tb_reorg" -nofed -x > tb_reorg_look.out
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- The db2look utility will consider only the specified tables
-- Creating DDL for table(s)

$ cat tb_reorg_look.out
-- This CLP file was created using DB2LOOK Version "10.1"
-- Timestamp: Thu May  9 17:34:38 GMT+09:00 2013
-- Database Name: SAMPLE
-- Database Manager Version: DB2/AIX64 Version 10.1.2
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY

CONNECT TO SAMPLE;

------------------------------------------------
-- DDL Statements for Table "DB2INST1"."TB_REORG"
------------------------------------------------

CREATE TABLE "DB2INST1"."TB_REORG"  (
                  "A" INTEGER ,
                  "B" CHAR(10) )
                 IN "IBMDB2SAMPLEREL" ;

 

 

 

COMMIT WORK;

CONNECT RESET;

TERMINATE;