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.