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