Informix 數(shù)據(jù)裝卸技術(shù)

1、IBM Informix Dynamic Server (IDS) IDS 數(shù)據(jù)裝載、卸載技術(shù) CSDL 議程unload/loadunonload/onloaddbloaddbexport/dbimportHPLExternal table議程un load/loadunonload/onloadDbloaddbexport/dbimportHPLExternal tableunload/load 語法unloadloadUNLOAD TO DELIMITER delimiter SELECT Statement; 可以為全路徑或者相對路徑文件名;DELIMITER選項，定義數(shù)據(jù)列分隔符，默

2、認(rèn)為|;SELECT 項為SQL子句;LOAD FROM DELIMITER delimiter INSERT INTO Table Name | Synonym Name| View Name (column); 可以為全路徑或者相對路徑文件名;DELIMITER選項，定義數(shù)據(jù)列分隔符，默認(rèn)為|;Column 選項，默認(rèn)為數(shù)據(jù)庫表的字段的順序， 注意要與文件中數(shù)據(jù)列順序保持一致;unload/load 遷移步驟導(dǎo)出導(dǎo)入準(zhǔn)備充足的磁盤空間； 采用unload工具卸載表數(shù)據(jù)；使用dbschema 工具卸載表的數(shù)據(jù)模式，生成建表SQL語句；磁盤數(shù)據(jù)文件及數(shù)據(jù)模式SQL語句移到目的主機(jī)上； 根據(jù)需要

3、編輯生成的數(shù)據(jù)模式SQL語句，并創(chuàng)建空表； 采用load工具將的數(shù)據(jù)文件裝載至數(shù)據(jù)庫中；unload/load 操作內(nèi)容卸載內(nèi)容Unload 只卸載遷移表的數(shù)據(jù)，不包含表數(shù)據(jù)模式及索引數(shù)據(jù)。SELECT子句的靈活方式，導(dǎo)出的數(shù)據(jù)可以來自一個表或者多個表。卸載粒度unload 卸載粒度包括部分字段或全部字段，部分記錄或全表記錄，是遷移工具中粒度最小的，也是該工具靈活方便的原因之一輸出結(jié)果Unload 輸出以分隔符分隔各字段的 ASCII 文件。分隔符可在卸載命令 delimiter 選項中顯式指定導(dǎo)入數(shù)據(jù)導(dǎo)入到指定的表unload/load 鎖情況卸載表的鎖定狀態(tài)Unload ：1.在數(shù)據(jù)庫非日

4、志狀態(tài)下或臟讀隔離級別下，可卸載表數(shù)據(jù)，即使遷移表被獨占。 2.在日志數(shù)據(jù)庫以非臟讀隔離級卸載獨占的表時，返回鎖沖突錯誤。 3.對卸載表的加鎖情況：在非日志數(shù)據(jù)庫、或臟讀、或提交讀隔離級別下，對卸載表不鎖定；在日志數(shù)據(jù)庫中，以游標(biāo)穩(wěn)定讀、重復(fù)讀隔離級別卸載表，對表記錄不同數(shù)據(jù)集加共享鎖。 裝載時對表的鎖定狀況load ：1.在日志數(shù)據(jù)庫中，對裝載表加排他鎖。鎖定粒度缺省情況下為行鎖或頁鎖（建表時確定）。當(dāng)裝載數(shù)據(jù)量較大時，可顯示將鎖模式更改為表級鎖。在裝載過程中不能對表進(jìn)行訪問。 2.在非日志數(shù)據(jù)庫中，對裝載表不加鎖，對裝載成功的記錄可隨時訪問。 數(shù)據(jù)庫日志狀態(tài)Unload/load 裝卸載不

5、受數(shù)據(jù)庫日志狀態(tài)的限制。unload/load 鎖情況Load支持事務(wù)并發(fā)load數(shù)據(jù)1.通過事務(wù)控制數(shù)據(jù)的裝載過程，失敗后可以回滾本次裝載的記錄；begin work; load from insert into tablename;commit work; 2.默認(rèn)情況下，load把裝載過程分成多個事務(wù)，當(dāng)失敗時回滾最后 一個子事務(wù)，已經(jīng)導(dǎo)入的記錄不能自動回滾。T1,Begin work;Load from t_1.unl insert into t;Commit work;T2,Begin work;Load from t_2.unl insert into t;Commit work;

6、unload/load 如何提高裝載性能提高 unload/load 性能的技巧 1去掉數(shù)據(jù)庫（表）日志，提高 load 速度取掉目的表級日志 ALTER TABLE TABNAME TYPE(RAW);采用 LOAD 工具裝載增加目的表表級日志 ALTER TABLE TABNAME TYPE(STANDARD);2采用多進(jìn)程，加速 unload/load 卸裝速度將需要裝卸的多張表分配到不同的進(jìn)程中并發(fā)裝卸，可減少整體裝卸載時間； 將海量表分配到多個進(jìn)程中并發(fā)卸裝，可減少海量表的卸載時間 采用多進(jìn)程裝載海量表，需要特別注意鎖溢出與長事務(wù)的出現(xiàn)3去掉參照性約束及索引，加速 load 裝載速度

7、在裝載前先刪除表索引及參照性約束，在裝載完畢后重新創(chuàng)建。unload/load 應(yīng)用場景最小操作對象為表導(dǎo)出導(dǎo)入單個表的數(shù)據(jù)unload輸出的文件是ASICC文件可以導(dǎo)入到外部應(yīng)用進(jìn)行使用，如EXCEL重建表的模式對 dbschem 卸出的 SQL 語句進(jìn)行編輯，可改變：表的字段名、約束、權(quán)限、表名、封鎖模式、初始 EXTENTS、后續(xù) EXTENTS、索引名、分片信息等。不同數(shù)據(jù)頁重新裝載數(shù)據(jù)跨不同平臺由于采用文本文件，可以在多個不同操作系統(tǒng)平臺，不同數(shù)據(jù)庫版本之間進(jìn)行數(shù)據(jù)的遷移議程unload/loadunonload/onloaddbloaddbexport/dbimportHPLExt

8、ernal tableunonload/onload 語法unonloadonloadunonload/onload 遷移步驟導(dǎo)出導(dǎo)入準(zhǔn)備磁帶，若采用磁盤文件方式，則準(zhǔn)備充足的磁盤空間并創(chuàng)建一用于存放卸載數(shù)據(jù)的空文件。 采用onunload卸載表，結(jié)果存放在磁帶或磁盤文件中。 磁帶或磁盤文件移到目的主機(jī)上。 去掉目的數(shù)據(jù)庫的日志。 采用onload創(chuàng)建表結(jié)構(gòu)、裝載表數(shù)據(jù)及索引數(shù)據(jù)。 目的數(shù)據(jù)庫恢復(fù)日志。 unonload/onload 操作內(nèi)容卸載內(nèi)容Onunload 卸載遷移表數(shù)據(jù)模式、數(shù)據(jù)頁、索引頁信息。卸載粒度Onunload 卸載粒度為數(shù)據(jù)庫級或表級。輸出結(jié)果Ononload 輸出為二

9、進(jìn)制數(shù)據(jù)文件unonload/onload 鎖情況卸載表的鎖定狀態(tài)Unonload ：不能對加有獨占鎖的表進(jìn)行卸載，包括任意粒度的鎖定：表級鎖、行鎖、頁鎖。 卸載時對卸載表加表級共享鎖。 裝載時對表的鎖定狀況onload ：對裝載表加互斥鎖。數(shù)據(jù)庫日志狀態(tài)onload 要求數(shù)據(jù)庫必須無日志unonload/onload 應(yīng)用場景最小操作對象為表包括表的索引等輸出的文件為二進(jìn)制格式不需重建表的模式由于導(dǎo)出導(dǎo)入過程，不可以對表模式及數(shù)據(jù)進(jìn)行調(diào)整，只能適應(yīng)于表及數(shù)據(jù)的快速遷移。相同數(shù)據(jù)頁大小議程unload/loadunonload/onloaddbloaddbexport/dbimportHPLE

10、xternal tabledbload dbloadUsage: dbload -d dbname -c cfilname -l logfile -e errnum -n nnum -i inum -s -p -r | -k -X -d 數(shù)據(jù)庫名 -c 命令文件名（也叫控制文件名） -l 保存導(dǎo)入過程中失敗的記錄 -e 導(dǎo)入失敗多少條記錄后退出 -s 僅檢查語法，不真正執(zhí)行任務(wù) -n 批量提交記錄數(shù)定義 -p 失敗情況：提交或者放棄 -i 從文件第幾行開始，默認(rèn)為第一行記錄 -r 導(dǎo)入過程對表不加排他鎖 -X 識別16進(jìn)制數(shù)據(jù) -k 導(dǎo)入過程對表加排他鎖dbload命令文件 DELIMITER

11、 | nfields INSERT INTO tablename (column) values (f01,f02,) 定義輸入文件名| 分隔符號，這里為“|”，可以定義為其他的符號nfields 定義每行記錄的列數(shù)tablename 定義導(dǎo)入的目的表(column) values 為可選項 f01為文件的第一列，依次類推舉例說明導(dǎo)入說明數(shù)據(jù)庫名：demodb控制文件名：load_tab.ctl錯誤日志文件名：error.log每次提交的數(shù)據(jù)條數(shù)：100000數(shù)據(jù)文件名：tab.unl數(shù)據(jù)字段分隔符： “”每行數(shù)據(jù)的字段數(shù)據(jù): 14要插入的數(shù)據(jù)表名：tab應(yīng)用命令如下：dbload -d de

12、modb -c load_tab.ctl -l error.log -n 100000;控制文件Load_tab.ctl 內(nèi)容： -file “tab.unl delimiter | 14 ;insert into tab;dbload優(yōu)點由于有容錯能力，可以用來重復(fù)導(dǎo)入數(shù)據(jù)，已有記錄保留不變，只增加新增記錄可以指定分批提交記錄數(shù)，從而能有效處理大數(shù)據(jù)量的表的導(dǎo)入對導(dǎo)入失敗數(shù)據(jù)進(jìn)行記錄到指定文件中，可以幫助使用者快速找到有問題的記錄信息可以指定文件記錄開始行信息通過命令文件，靈活定義導(dǎo)入文件數(shù)據(jù)的格式議程unload/loadunonload/onloaddbloaddbexport/dbim

13、portHPLExternal tabledbexportdbexportdbexport -X -c -q -d -ss -o | -t -b -s -f 其中主要選項說明如下：-c 如果沒有發(fā)生致命錯誤，則輸出全部信息-d 僅輸出blob描述符，不輸出blob數(shù)據(jù)-q 在標(biāo)準(zhǔn)輸出設(shè)備上不顯示錯誤信息，警告和所生成的SQL數(shù)據(jù)定義語句database 指定卸出的數(shù)據(jù)庫名-o directory 命名存儲ASCII數(shù)據(jù)文件和模式文件的磁盤目錄-t tapedevice 指定存儲ASCII數(shù)據(jù)文件和模式文件的磁帶設(shè)備路徑-b blocksize 指定磁帶設(shè)備塊大?。▎挝唬篕B）-s tapesi

14、ze 指定磁帶所能存儲的數(shù)據(jù)總量（單位：KB）-f sql-command-file 指定存于磁盤上的模式文件dbimportdbimportdbimport實用程序的語法：-c 指示dbimport在沒有發(fā)生致命錯誤的情況下完成裝入工作。-q 指示在標(biāo)準(zhǔn)輸出設(shè)備上不顯示錯誤信息和警告。輸入文件位置選項：-i 指定存儲ASCII文件和模式文件的磁盤目錄，以便dbimport查找。-t 指定含有ASCII文件和模式文件的磁帶設(shè)備名。-b 指定磁帶設(shè)備塊大小。block以KB為單位，該值必須能為所使用的設(shè)備接受。-s 指定磁帶的最大數(shù)據(jù)存儲量。-f 指定磁盤上模式文件的路徑名，以便dbimport

15、查找模式文件，并以此為輸入建立數(shù)據(jù)庫。建庫選項：-d 指定用來存儲新建數(shù)據(jù)庫的OnLine數(shù)據(jù)庫空間(dbspace)。如果沒有使用-d選項指定dbspace，則數(shù)據(jù)庫缺省建立在根dbspace(rootdbs)中。-l 如果沒有使用buffered參數(shù)，為裝入的數(shù)據(jù)庫建立無緩沖事務(wù)日志；如果使用了buffered參數(shù)，為裝入的數(shù)據(jù)庫建立有緩沖事務(wù)日志。-l選項等價于CREATEDATABASE語句的WITH LOG字句。如果沒有使用-l選項，新建數(shù)據(jù)庫不帶日志。-ansi 為裝入的數(shù)據(jù)庫建立ANSI方式兼容的事務(wù)日志。舉例說明1、導(dǎo)出，首先創(chuàng)建導(dǎo)出一個目錄，示例中在/data/目錄下創(chuàng)建db

16、name的一個文件夾 dbexport dbname -c -ss -o /data/dbname2、導(dǎo)出完成后，在dbname目錄下生成一個dbname.exp的目錄，找到該目錄下的dbname.sql模式腳本文件，需要利用工具（如sqleditor）來驗證一下腳本的語法的正確性（導(dǎo)出的腳本可能存在語法上的錯誤），需要加以修改糾正。3、導(dǎo)入，在導(dǎo)入之前，需要按照存儲情況先劃分好DBSPACE dbimport dbname -i /data/dbname -c -l buffered -d default_dbspacename4、導(dǎo)入完成后，進(jìn)行數(shù)據(jù)庫級別的統(tǒng)計更新 update stat

17、istics ;議程unload/loadunonload/onloaddbloaddbexport/dbimportHPLExternal tableonpladm create job jobname -d devicename -D databasedb -t tabname fluJob NameDevice nameDatabase nameTable nameJob typeusage: onpladm create job -p -d -D -t -flc|Nuadp -z | -n -T -S -M -B where, -p project name -d device nam

18、e, further qualified by flags -D database name -t table name -f flags, l means load job, default will be both load and unload c means runmode DELUXE with replication, default will be EXPRESS runmode N means runmode DELUXE without replication, default will be EXPRESS runmode u means unload job a mean

19、s device is a device array, default will be file d means device is a tape p means device is a pipe command -z type of format to be used, default is Delimited type D means DELIMITED_FORMAT FI means FAST_FORMAT FA means FIXED_ASCII_FORMAT FB means FIXED_BINARY_FORMAT C means COBOL_FORMAT CB means COBO

20、L_BYTE_FORMAT -n no-conversion fast-job -T Target server, where load/unload is to be done -S Onpload server containing the onpload database -M device size, if device is a tape -B block size, if device is a tape確定表名，定義HPL卸載/裝載數(shù)據(jù)任務(wù)onpladm run job jobname -fu -l unload_logname.log Job NameJob typeLog f

21、ile 進(jìn)入指定的目錄(有足夠空間) 運行如下的命令, 進(jìn)行數(shù)據(jù)卸載usage: onpladm run job -flu -p -l -S -Z where, -p project name -f flags, l means load job u means unload job -l log , default is std-out -S Onpload server containing the onpload database -Z Write/read to/from tape until end of media卸載數(shù)據(jù)重命名表，創(chuàng)建新表 - 調(diào)整空間分配, 創(chuàng)建索引,增加約束(

22、注意:不要創(chuàng)建觸發(fā)器)rename table tabname to tabname_old;create table tabname ( colums) extent size 1280000 next size 1280000 lock mode row;Create index .其他索引及約束onpladm run job jobname fl -l load_logname.log 裝載數(shù)據(jù)Job NameJob typeLog file 進(jìn)入數(shù)據(jù)卸載所在的目錄 運行如下的命令, 進(jìn)行數(shù)據(jù)裝載usage: onpladm run job -flu -p -l -S -Z where,

23、 -p project name -f flags, l means load job u means unload job -l log , default is std-out -S Onpload server containing the onpload database -Z Write/read to/from tape until end of media后續(xù)動作將TAPEDEV數(shù)據(jù)庫參數(shù)設(shè)置為/dev/null, 執(zhí)行ontape s L 0進(jìn)行空備份.若原表上存在觸發(fā)器的表, 刪除原先舊表中的觸發(fā)器, 在新表上創(chuàng)建同樣的觸發(fā)器drop trigger create trigg

24、er .#刪除舊表打開PDQ，執(zhí)行update statistics對新表進(jìn)行統(tǒng)計更新議程unload/loadunonload/onloaddbloaddbexport/dbimportHPLExternal tableExternal tableA從導(dǎo)入數(shù)據(jù)到數(shù)據(jù)庫B多線程機(jī)制C性能優(yōu)異，對于大數(shù)據(jù)情況，導(dǎo)入性能比HPL性能高2到3倍，性能測試數(shù)據(jù)見本文檔的3節(jié)測試數(shù)據(jù)。External table語法SELECT FROM INSERT INTO SELECT FROM INSERT INTO SELECT FROM 導(dǎo)出數(shù)據(jù)select from INTO EXTERNAL USING

25、(FORMAT INFORMIX,DATAFILES (DISK:/data/latest/data.unl);External tableSQL示例 A采用sameas(根據(jù)已有表結(jié)構(gòu)),包含兩個文件情況 create external table external_tablename sameas real_tablename USING (DATAFILES (DISK:/opt/IBM/informix/demo/ex_unl1.unl,DISK:/opt/IBM/informix/demo/ex_unl2.unl), FORMAT DELIMITED, REJECTFILE /opt/IBM/informix/demo/tablename.rej, MAXERRORS 100, DELIMITER |, DELUXE -EXPRESS); External tableSQL示例 B 定義字段create external table external_tablename （a integer,