DB2 LOAD相關

fjzcau發表於2015-03-28
load 對原資料按照DB2物理儲存方式進行格式化,並將格式化的資料頁直接寫入到資料庫,
記錄的日誌很少,不檢查約束,不觸發觸發器,適合大量資料匯入。
匯入前,目標表必須已存在。

登錄檔變數 DB2_LOAD_COPY_NO_OVERRIDE 
db2set DB2_LOAD_COPY_NO_OVERRIDE='COPY YES TO /backup'
db2set DB2_LOAD_COPY_NO_OVERRIDE=NONRECOVERABLE 

load的動作選項
1) insert
追加資料
2) replace
先刪除表資料,再插入資料
3) terminate
終止load狀態
4) restart
用於重啟被中斷的load命令,restart會使用之前load時產生的臨時檔案,並從最近的一點開始載入。
載入成功,臨時檔案自動刪除。預設下臨時檔案是在當前路徑下產生,
可透過 TEMPFILES PATH 選項指定存放臨時檔案的目錄。

load 的階段:
1)裝載 :若目標不包含唯一性索引,則只進行裝載過程。
     將源資料檔案解析成資料物理儲存格式,直接裝到頁中,不透過DB2引擎。
     收集索引鍵和統計資訊,當資料不符合定義時,無效資料轉儲到dumpfile。
     modified by dumpfile指定轉儲檔名和路徑。check約束和參考完整性約束。
2)構建索引
3)刪除重複值
若表上有主鍵或唯一性索引,則刪除重複值。唯一性約束。
4)索引複製
若指定了 allow read access 和 use tablespace 選項,
則會將索引資料從系統臨時表空間中複製到索引表空間中。

-- load 各種狀態
Normal:正常狀態
Load in Progress:
     正在進行 LOAD 。
Load Pending:
     Load 過程發生意外中斷,需用 LOAD TERMINATE 、 LOAD RESTART 或者 LOAD REPLACE 來解除該狀態 。
Read Access Only:
     LOAD時指定 ALLOW READ ACCESS(只讀)。
Set Integrity Pending:
     表上存在約束,Load 時表會處於該狀態,必須透過 Set Integrity 解除該狀態。
Reorg Pending:
     如果使用 Alter Table 語句,會將表置於該狀態,處於這種狀態的表無法訪問,必須做離線REORG來解除該狀態。
Unavailable:
     Load 時指定 NonRecoverable,當前滾到 LOAD 操作結束後的某個點,表就處於該狀態。只能重建該表。
Not Load Restartable:
     表處於不完整匯入狀態,不允許使用 LOAD RESTART 繼續執行,只能 LOAD TERMINATE 、 LOAD REPLACE 來解除。
     出現該狀態的兩種情況:當 LOAD 失敗,之後又前滾到 LOAD 之後的某個時刻;表處於 LOAD in Progress 或 Load Pending 時進行了線上備份。
Unknown:
     表處於未知狀態,Load Query 無法得到表狀態資訊。客戶端版本與資料庫伺服器版本不同時出現該狀態。

LOAD FROM SALES.DEL OF DEL
  MODIFIED BY COLDEL| CHARDEL*
  SAVECOUNT 100
  MESSAGES MSGS.TXT
  TEMPFILES PATH D:\TEMP
  INSERT INTO SALES FOR EXCEPTION SALES_EXCEPTION
  STATISTICS YES AND INDEXES ALL
  COPY NO
  CPU_PARALLELISM 4
  DISK_PARALLELISM 3
  INDEXING MODE INCREMENTAL

--load 實驗
db2 "create table t1(empno int not null primary key , name char(10), seqno int not null ) "
db2 "create table t1_exp like t1"
db2 "alter table t1_exp add column ts timestamp add column msg clob(32K)"

cat t1.del
10,"wang qi",1
20,"zhang san",
30,"xu xin",3
40,"aaaa",x
50,"bbbb",6
50,"ccccc",7
80,"li si",8

db2 "load from t1.del of del
   modified by dumpfile=/data/imp_data/t1.dmp
   messages t1.msg
   insert into t1
   for exception t1_exp
"
--查詢表的狀態
db2 "load query table t1"

db2 "create table t2_exp like t1"
db2 "alter table t2_exp add column ts timestamp add column msg clob(32K)"

db2 "load from t2.del of del
   modified by dumpfile=/data/imp_data/t2.dmp
   messages t2.msg
   replace into t2
   for exception t2_exp
"

-- copy no (load 預設選項)load後表空間處於 backup pending 狀態(表無法更新,所以生產千萬不要用該選項),
需要一次表空間備份或全備份,否則出了問題無法恢復。
db2 "create tablespace ts1"
db2 "create table t3 (id int, name char(20) ) in ts1"
db2 update db cfg for testdb using logarchmeth1 "disk :/home/db2inst1/archivelog/testdb"
db2 backup db testdb to /data/backup/testdb compress
more t3.del
1,'aaa'
2,'bbb'
3,'ccc'
db2 "load from t3.del of del insert into t3"
db2 "select * from t3"
db2 "update t3 set name='newccc' where id=3"
db2 list tablespaces show detail
db2 backup db testdb to /data/backup/testdb compress

--copy yes , load後會自動進行備份。load 時間會長點。
more t3.del
1,'aaa'
2,'bbb'
3,'ccc'
db2 "load from t3.del of del insert into t3 copy yes to /data/backup/testdb "
ls -l /data/backup/testdb
db2 restore db testdb taken at 20131014210541
db2 rollforward db testdb to end of logs and stop
db2 connect to testdb
db2 "select * from t3"

--nonrecoverable 表不可恢復,load後不備份,也不阻止查詢和修改。load 後需要進行全備份。
以後要前滾到 nonrecoverable load選項之後的某個時間點,這個表不可恢復,所有與該表有關的日誌被忽略,只能重建該表。
more t3.del
1,'aaa'
2,'bbb'
3,'ccc'
db2 "load from t3.del of del insert into t3 nonrecoverable"
db2 "select * from t3"
db2 list tablespaces show detail
db2 restore db testdb taken at   20131014210541
db2 rollforward db testdb to end of logs and stop
db2 connect to testdb
db2 "select * from t3"

--set integrity pending 完整性檢查 SQL0668N
db2  "create table t2 (col1 char(10), col2 char(10) ) "
db2 "alter table t2 add constraint check1 check(col2 in ('A','B','C') )"
cat t2.del
"AAA","A"
"BBB","B"
"CCC","CCC"
"DDD","BBB"
db2 "load from t2.del of del insert into t2"
db2 "select * from t2"   查詢表時發現:SQL0668N,set integrity pending
db2 set integrity for db2inst1.t2 immediate checked
db2 "create table t2_exp like t2"
db2 "set integrity for db2inst1.t2 immediate checked for exception in t2 use t2_exp"
db2 "select * from t2_exp"


--檢查表的主外來鍵約束、檢查約束,處於 set integrity pending 等
db2 "select tabname,status,access_mode, substr(const_checked,1,1) as fk_checked, substr(const_checked,2,1) as cc_checked
from syscat.tables
where status='C'
"

--對主表做完整性檢查,依賴表會處於 check pending
create table a (x int not null primary key)
create table b (x int not null primary key)
create table c (x int not null primary key)
create table d (x int not null primary key)
create table e (x int not null primary key)
alter table a  add foreign key(x) references b
alter table b  add foreign key(x) references c
alter table c  add foreign key(x) references d
alter table d  add foreign key(x) references e

load from /dev/null of ixf replace into c; -- c 表處於 check pending status
set integrity for c immediate checked; -- 對 c 表做完整性檢查,c 表的依賴表會處於 check pending
select tabname from syscat.tables where status='C' --檢視處於 check pending 的表,發現 a 和 b 表都處於 check pending

--將表 t1 置於 set integrity pending 狀態、不允許訪問,同時將其依賴表置於 set integrity pending 狀態
db2 "set integrity for t1 no access cascade immediate"

--不對 manger 表的外來鍵約束和 employee 的檢查約束做檢查,並表脫離set integrity pending狀態
db2 " set integrity for manager foreign key, employee check immediate unchecked

--表已有資料且存在違背約束的資料,透過set integrity 將約束檢查關閉,待建立約束完後再進行資料完整性檢查
db2 "create table emp1(empno int not null primary key, empname char(20), deptno int)"
db2 "create table dept1( deptno int not null primary key, deptname char(20))"
db2 "insert into emp1 values(111,'zhangming',1)"
db2 "insert into emp1 values(222,'zhangming22',2)"
db2 "insert into dept1 values(1,'dev')"
db2 "create table emp1_exp like emp1"
db2 "set integrity for emp1 off"
db2 "alter table emp1 add foreign key (deptno) references dept1(deptno) on delete cascade"
db2 "set integrity for emp1 immediate checked for exception in emp1 use emp1_exp"


--出現 load pending 怎麼辦
db2 "create bufferpool bp8k size automatic pagesize 8k"
db2 "create tablespace ts2 pagesize 8k managed by database 
 using  ( file '/data/i1_testdb/tbs_data/ts2' 256 ) bufferpool bp8k
"
db2 "create table t2 (id int ,name char(50) , desc char(50) ) in ts2"

cat sp_insert.sql
create procedure sp_insert (in count int)
language sql
begin
  declare i integer default 0;
  while i   do
    insert into t2 values(i,'abcdefghixxxxxxx'|| char(i),'bbbbbbbbccccccccccc'|| char(i) );
    set i=i+1;
  end while;
end
@

db2 -td@ -f sp_insert.sql
db2 "call sp_insert(7000)"
db2 "export to t2.del of del select * from t2"
db2 "drop tablespace ts2"
db2 "create tablespace ts2 pagesize 8k managed by database using (file '/data/i1_testdb/tbs_data/ts2' 200) bufferpool bp8k"
db2 "create table t2 (id int, name char(50),desc char(50) ) in ts2 "
db2 "load from t2.del of del insert into t2"
db2 load query table t2
db2 "select * from t2"
db2 "load from /dev/null of del terminate into t2"
db2 "select * from t2"
db2 "select substr(tabschema,1,10) as tabschema,substr(tabname,1,20) as tabname
from sysibmadm.admintabinfo
where load_status='PENDING'
"  

--資料分隔符不是逗號/雙引號 怎麼辦
cat t1.del
10;'wang qi';1
20;'zhang san';2

db2 truncate table t2 immediate
db2 "create table t2 (id int, name char(20),id2 int)"
db2 "import from t1.del of del modified by coldel; chardel'' insert into t2"

--分隔符的限制
使用者要確保資料中不能包含分隔符
分隔符不能是換行符、回車符、0x00或空格
在DBCS環境下(如中文系統),不能用“|”符號作為分隔符
如果 DEL 檔案透過某個特殊字元分隔,而import、load、export 命令無法透過鍵盤敲入,可轉換為十六進位制,如0x7c代表“|”

對於decimal值,預設的匯出格式是在前面有+號,如果位數不夠定義的長度,則用0補齊。

db2 "create table t2(c1 char(10),c2 decimal (7,2))"
db2 "insert into t2 values('aaa',23.4)"
db2 "insert into t2 values('bbb',1.2)"
db2 "export to t2.del of del select * from t2"
cat t2.del
"aaa       ",+00023.40
"bbb       ",+00001.20

用decplusblank去掉+號(+號轉換為空格),striplzeros去掉前面的0
db2 "export to t2_1.del of del modified by decplusblank striplzeros select * from t2"
cat t2_1.del
"aaa       ", 23.40
"bbb       ", 1.20

日期格式轉換
db2 "create table t3 (c1 char(10),c2 timestamp)"
db2 "insert into t3 values('aaa',current timestamp)"
db2 "export to t3.del of del modified by timestampformat=\"yyyy/mm/dd hh:mm:ss.uuuuuu\"  select * from t3"
cat t3.del
"aaa       ","2013/10/16 18:48:23.036031"

-------------------------------------------------------
DEL  method  P  選項
ASC method  L  選項
IXF   method  N,P 選項

--檔案中的列比匯入的表的欄位多 怎麼辦
db2 "create table t4(c1 char(10),c2 int)"
cat t4.del
"aaaa","bbbb",3,"adbc",3
"bbbb","bbbb",4,"abfc",4
"cccc","bdd",5,"abcd",5
db2 "import from t4.del of del method P(1,3) insert into t4"

--檔案中的列比匯入的表的欄位少 怎麼辦
db2 "create table t4 (c1 char(10),c2 int, c3 int)"
cat  t4.del
"aaaa",3
"bbbb",4
"cccc",5
db2 "import from t4.del of del method P(1,2,999) insert into t4"
檔案中不存在999列,所以插入空值
db2 "select * from t4"

C1         C2          C3        
---------- ----------- -----------
aaaa                 3           -
bbbb                 4           -
cccc                 5           -

  3 record(s) selected.


--sequence資料 怎麼辦
selecct max(id) as counter from tabname
alter sequence seq_name restart with counter+1

db2 "create sequence seq1 as integer start with 1 increment by 1 minvalue 1 maxvalue 99999999 cycle cache 10"
cat seq.del
1,"AAAA"
2,"BBBB"
3,"CCCC"
db2 "create table t2 (id int ,name char(20))"
db2 "load from seq.del of del insert into t2 copy yes to /data/backup/testdb"
db2 "select * from t2"
db2 "select max(id) maxid from t2"
db2 "alter sequence seq1 restart with 4"
db2 "insert into t2 values(nextval for seq1,'dddd')"

--匯入identity 資料 怎麼辦
1)identity 修飾符
identityoverride 使用資料檔案的值,該修飾符只適用於generated always,且在load中適用
identityignore   忽略資料檔案的值
identitymissing  資料檔案不含自增值
欄位:generated always as identity 或 generated by default as identity
2)generated修飾符
generatedoverride
generatedignore
generatedmissing
欄位:generated always as

cat  t6.del
3,"shrek"

db2 "create table t6 (custno smallint not null generated always  as identity(start with 500,increment by 1), custname varchar(20)) "
--
db2 "load from t6.del of del modified by identityoverride  messages t6.msg insert into t6 copy yes to /data/backup/testdb "
--
db2 "select * from t6"

CUSTNO CUSTNAME           
------ --------------------
     3 shrek 

db2 "load from t6.del of del modified by identityignore  messages t6.msg insert into t6 copy yes to /data/backup/testdb "
db2 "select * from t6"

CUSTNO   CUSTNAME           
------        --------------------
     3             shrek              
   500          shrek

db2 "alter table t6 alter custno restart with 550"
db2 "insert into t6(custname) values('hello')"
db2 "select * from t6 order by 1"

CUSTNO CUSTNAME           
------ --------------------
     3   shrek              
   500 shrek                        
   550 hello              

--
cat t7.del
MinWei
db2 "create table t7(custno int not null generated always as identity (start with 500,increment by 1), custname varchar(20))"
db2 "load from t7.del of del modified by identitymissing messages t7.msg insert into t7 copy yes to /data/backup/testdb "

--
cat t10.del             
"aaaaaaa",102000.00
"bbbbbbb",1302200.12
"ccccccc",123456.32
"ddddddd",-1234567890.234
"eeeeeee",123456789.234

db2 "create table t10 (name varchar(20) not null,salary decimal(9,2),bonus decimal(9,2) generated always as (salary/100) )"

db2 "load from t10.del of del modified by generatedmissing insert into t10 copy yes  to /data/backup/testdb"

SQL0406N  A numeric value in the UPDATE or INSERT statement is not within the
range of its target column.  SQLSTATE=22003

SQL3185W  The previous error occurred while processing data from row "F0-4" of
the input file.

db2 "select * from t10" 

NAME                 SALARY      BONUS     
-------------------- ----------- -----------
aaaaaaa                102000.00     1020.00
bbbbbbb               1302200.12    13022.00
ccccccc                123456.32     1234.56

--匯入資料有換號符 怎麼辦
db2 "create table test1 (c1 char(20))"

--兩條記錄
cat test.del
"a  
b    "
"c   
d    "

db2 "load from test.del of del insert into test1 copy yes to /data/backup/testdb "

db2 "select * from  test1"   

C1                 
--------------------
"a                 
b    "             
"c                 
d    "             

  4 record(s) selected.

--delprioritychar 雙引號之間不管有沒有換行符都視作一條記錄
db2 "load from test.del of del modified by delprioritychar  insert into test1 copy yes to /data/backup/testdb "

db2 "select  '*' || c1 || '*' as c1 from test1" 

C1                   
----------------------
*a  
b              *
*c   
d             *

  2 record(s) selected.

db2 "export to test2.del of del select replace(c1,chr(10),'') from test1"
SQL3105N  The Export utility has finished exporting "2" rows.

cat test2.del
"a   b              "
"c    d             "

預設的import/load優先順序從高到低:行分隔符、字元分隔符、列分隔符
行優先順序最高,換行符是預設的行分隔符,有換行,load就認為是新的記錄。



--遷移出現亂碼 怎麼辦

對於IXF格式,一般不會出現編碼問題,因為自動編碼轉換。
對於DEL格式,資料的編碼不同,遇到漢字亂碼。

--load
預設情況,
load假定資料檔案為資料庫codepage編碼,並將轉換為資料庫的編碼來匯入。
所以資料檔案的編碼與資料庫的編碼不同時,匯入要加資料檔案的編碼
引數 modified by codepage=

--import
預設情況,import認為輸入檔案的資料是用當前系統的內碼表編碼
(透過db2set db2codepage=設定,如1386,1208)
若資料檔案編碼不是當前系統內碼表編碼,則修改。

db2 get db cfg | grep -i code      
Database code page                                  = 1208
Database code set                                      = UTF-8
Database country/region code                 = 86

--
1)準備資料
db2 "create table t1(id int,name varchar(20))"
db2 "insert into t1 values(100,'許明文' )"
db2 "select * from t1"
2)以不同編碼匯出
db2 "export to t1 of del select * from t1"
db2 "export to t1_1208 of del modified by codepage=1208 select * from t1"
db2 "export to t1_1386 of del modified by codepage=1386 select * from t1"
db2 "export to t1_819 of del modified by codepage=819 select * from t1"
3)load 匯入測試 透過 modified by codepage 修改為資料檔案的編碼即可
db2 delete from t1
db2 "load from t1_819 of del modified by codepage=819 insert into t1"     --不支援中文
db2 "load from t1_1208 of del modified by codepage=1208 insert into t1"  --正常匯入
db2 "load from t1_1386 of del modified by codepage=1386 insert into t1"  --正常匯入
db2 "load from t1_1386 of del insert into t1"                                              --編碼不對
4)import 匯入 db2codepage一定要設定為資料檔案的編碼
db2set db2codepage=1386
db2 terminate
db2 connect to testdb
db2 "import from t1_1386 of del insert into t1"  --正常匯入
db2 "import from t1_1208 of del insert into t1"  --亂碼




--表遷移其他表空間
1) db2 create tablespace ts3
2)db2 "create table t4 (c1 char(100), c2 int ) in ts3"
3)db2 "select substr(TABNAME,1,20) as tabname,
  substr(TABSCHEMA,1,20) as tabschema,TBSPACEID,
  substr(TBSPACE,1,20) as tbspace 
from syscat.tables
where tabname='T4'
"
TABNAME              TABSCHEMA            TBSPACEID TBSPACE            
-------------------- -------------------- --------- --------------------
T4                   DB2INST1                    12 TS3    

4)db2 create bufferpool bp32k size automatic pagesize 32k
5)db2 create tablespace tbs32k pagesize 32k bufferpool bp32k
6)db2 "call sysproc.admin_move_table('DB2INST1','T4','TBS32K','TBS32K','TBS32K','','','','','','MOVE') "

  Result set 1
  --------------

  KEY                              VALUE                                                                                                                          
  -------------------------------- --------------------------------------------------------------------------------------------------------------------------------
  AUTHID                           DB2INST1                                                                                                                        
  CLEANUP_END                      2013-10-17-23.03.54.353431                                                                                                      
  CLEANUP_START                    2013-10-17-23.03.54.215935                                                                                                      
  COPY_END                         2013-10-17-23.03.53.472986                                                                                                      
  COPY_OPTS                        ARRAY_INSERT                                                                                                                    
  COPY_START                       2013-10-17-23.03.53.349348                                                                                                      
  COPY_TOTAL_ROWS                  2                                                                                                                               
  INDEXNAME                                                                                                                                                        
  INDEXSCHEMA                                                                                                                                                      
  INIT_END                         2013-10-17-23.03.53.263126                                                                                                      
  INIT_START                       2013-10-17-23.03.51.852221                                                                                                      
  REPLAY_END                       2013-10-17-23.03.54.043163                                                                                                      
  REPLAY_START                     2013-10-17-23.03.53.473723                                                                                                      
  REPLAY_TOTAL_ROWS                0                                                                                                                               
  REPLAY_TOTAL_TIME                0                                                                                                                               
  STATUS                           COMPLETE                                                                                                                        
  SWAP_END                         2013-10-17-23.03.54.158329                                                                                                      
  SWAP_RETRIES                     0                                                                                                                               
  SWAP_START                       2013-10-17-23.03.54.076932                                                                                                      
  VERSION                          09.07.0000                                                                                                                      

  20 record(s) selected.

  Return Status = 0
7)
db2 "select substr(TABNAME,1,20) as tabname,
  substr(TABSCHEMA,1,20) as tabschema,TBSPACEID,
  substr(TBSPACE,1,20) as tbspace 
from syscat.tables
where tabname='T4'
"

TABNAME              TABSCHEMA            TBSPACEID TBSPACE            
-------------------- -------------------- --------- --------------------
T4                   DB2INST1                    12 TS3    

--表空間查詢
db2 "select TBSPACEID, substr(TBSPACE,1,20) as tbspace, PAGESIZE , EXTENTSIZE
from syscat.tablespaces
order by TBSPACEID
"
TBSPACEID   TBSPACE              PAGESIZE    EXTENTSIZE
----------- -------------------- ----------- -----------
          0 SYSCATSPACE                 4096           4
          1 TEMPSPACE1                  4096          32
          2 USERSPACE1                  4096          32
          3 TBS_DATA                   32768          32
          4 TBS_TEMP                   32768          32
          5 TBS_USER_TEMP              32768          32
          6 TBS_INDEX                  32768          32
          7 TBS_DATA2                   4096          32
          8 SYSTOOLSPACE                4096           4
          9 SYSTOOLSTMPSPACE            4096           4
         10 TS1                         4096          32
         11 TS2                         4096          32
         12 TS3                         4096          32
         13 TBS32K                     32768          32

  14 record(s) selected.









來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22661144/viewspace-1477200/,如需轉載,請註明出處,否則將追究法律責任。

相關文章