Informix external table

基本上跟Oracle external table類似概念, 不過缺點就是

優點

ü   定義存在DB, 只在存取的時候撈檔案

ü   不須對table做checkpoint 檢查點

ü   可以跨資料庫搬移

缺點

ü   不支援Triggers

ü   對於file system有存取權限的人都看的到檔案內容

ü   DR架構內無法複製到異地

以下為轉載使用方法:  http://www.inturi.net/coranto/viewnews.cgi?id=EklyVykuVFrtWZuKkZ

Informix: Example for loading/unloading data to/from external table to database table -- Posted by inturi on Friday, October 8 2010
> create table t1 (c1 int, c2 int); 

Table created. 

-- Create external table with same schema as t1 
> create external table t1_ext sameas t1 using (datafiles("DISK:/tmp/t1.unl"), rejectfile "/tmp/t1.rej" ); 

Table created. 

> 

> insert into t1 values(1,1); 

1 row(s) inserted. 

> insert into t1 values(2,2); 

1 row(s) inserted. 

> insert into t1 values(3,3); 

1 row(s) inserted. 

> insert into t1 values(4,4); 

1 row(s) inserted. 

> insert into t1 values(5,5); 

1 row(s) inserted. 

-- Unload data to external table t1_ext. Data stored in /tmp/t1.unl 
> insert into t1_ext select * from t1; 

5 row(s) inserted. 

> select * from t1_ext; 


c1 c2 

1 1 
2 2 
3 3 
4 4 
5 5 

5 row(s) retrieved. 

> select * from t1_ext where c1 <3 class="apple-converted-space" span=""> 


c1 c2 

1 1 
2 2 

-- Now load data from external table into database table t1. 
> insert into t1 select * from t1_ext; 

5 row(s) inserted. 

> select * from t1; 


c1 c2 

1 1 
2 2 
3 3 
4 4 
5 5 
1 1 
2 2 
3 3 
4 4 
5 5 

10 row(s) retrieved. 




$ cat /tmp/t1.unl 
1|1| 
2|2| 
3|3| 
4|4| 
5|5| 

External table example with multiple devices: 


> create external table t1_extv2 sameas t1 using (datafiles("DISK:/tmp/t1.unl", "DISK:/tmp/t1_2.unl") , rejectfile "/tmp/t1.rej"); 

Table created. 

> insert into t1_extv2 select * from t1; 

10 row(s) inserted. 

> select * from t1_extv2; 


c1 c2 

1 1 
2 2 
3 3 
4 4 
5 5 
1 1 
2 2 
3 3 
4 4 
5 5 

10 row(s) retrieved.

informix unload to table 的80個字元限制問題

使用以下方法去產生export table語法
As informix User:
$dbaccess db_name gen_unload

gen_unload.sql:
OUTPUT TO "unload_stores.sql" WITHOUT HEADINGS

SELECT 'UNLOAD TO "' || trim(tabname) ||'.txt" select * from ' || trim(tabname) ||';' from systables where tabid>99 and tabtype='T'

and tabname not like '%cdr%'

會遇到表格名太長的問題,而造成換行，如果沒連在同一行，再大量產生匯出的表格備份就會失敗。

例如:

看來informix dbaccess 有寬度限制80個字元好像是無正解.

解法1:

在IIUG download "sqlcmd" 可以解此問題. ( ftp://ftp.iiug.org/pub/informix/pub/sqlcmd-87.02.tgz )

此工具在Linux若有裝cc, 編譯很簡單.

./configure

make

測試:

$cat G1.sql

SELECT 'UNLOAD TO "' || trim(tabname) ||'.txt" select * from ' || trim(tabname) ||';' from systables where tabid>99 and tabname not like '%cdr%'

$./sqlcmd -d db_name -f G1.sql -x >unload.sql

$cat unload.sql

UNLOAD TO "customer.txt" select * from customer;

UNLOAD TO "orders.txt" select * from orders;

UNLOAD TO "items.txt" select * from items;

UNLOAD TO "stock.txt" select * from stock;

UNLOAD TO "manufact.txt" select * from manufact;

UNLOAD TO "state.txt" select * from state;

UNLOAD TO "syscolatt.txt" select * from syscolatt;

UNLOAD TO "table_1234567890123456789.txt" select * from table_1234567890123456789;

解法2:

先用unload.sql 產出 ==>"customer.txt" select * from customer;

再用SED 指令 把"customer.txt" select * from customer; 轉換成UNLOAD TO "customer.txt" select * from customer;

語法如下:

cat unload_stores.sql | sed '/^[<b><tab>]*$/d'|sed -n 's/"/UNLOAD TO "/p' >

  unload_${db}.sql

語法說明

sed '/^[<b><tab>]*$/d'     #是把空白的行去掉

sed -n 's/"/UNLOAD TO "txt\//p'        #是把 " 轉成 UNLOAD TO "


--
Anyway 以上兩個方法可以擇一使用 , 就可以把表格備份成flat file囉!
方法如下:



export DB_LOCALE=en_us.8859-1
export CLIENT_LOCALE=en_us.8859-1
export LANG=C
today=`date +%Y%m%d`
db=db_name

dbaccess ${db} unload_stores.sql