達夢dmfldr載入大欄位

dmfldr載入大欄位
1.外部資料

[dmdba@shard1 ~]$ vi t1.txt
1,aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

2.建立控制檔案

[dmdba@shard1 ~]$ vi t1.ctl
load data
infile '/home/dmdba/t1.txt'
into table test_lob
fields ','

3.建立表

SQL> create table test_lob(id int,name clob);
executed successfully
used time: 51.594(ms). Execute id is 7552.

[dmdba@shard1 ~]$ dmfldr sysdba/xxzx7817600 control=\'\/home\/dmdba\/t1.ctl\' 
dmfldr V7.1.6.46-Build(2018.02.08-89107)ENT 
dmfldr: 
Copyright (c) 2011, 2015, Dameng.  All rights reserved.
Control file：
Loaded rows:All
Rows per commit to server: 50000
 Rows to skip: 0
 Errors count allowed: 100
 Whether to load direct: Yes
 Whether insert self-increase col: No
 Whether data has sort by gather index: No
 Character sets:GBK
Data file counts: 1
/home/dmdba/t1.txt
Error file :fldr.bad
Dest table :TEST_LOB
Column Name                                                                                                                      Packed data type     End
ID                                                                                                                               CHARACTER            ,
NAME                                                                                                                             CHARACTER            ,
row buffer number is 4
task thread number is 4
not set lob dir
0 rows committed.
Dest table :TEST_LOB
0 Rows loaded success
Due to data format error, 0 rows abandon
Due to data error, 1 rows not loaded
Skip logic record counts: 0
Read logic record counts: 1
Refuse logic record counts: 1
The total time used: 38.056(ms)

上面顯示拒絕載入了一行記錄也就是說資料沒有載入成功。
在載入大欄位時需要指定direct=false選項

[dmdba@shard1 ~]$ dmfldr sysdba/xxzx7817600 control=\'\/home\/dmdba\/t1.ctl\' direct=false
dmfldr V7.1.6.46-Build(2018.02.08-89107)ENT 
dmfldr: 
Copyright (c) 2011, 2015, Dameng.  All rights reserved.
Control file：
Loaded rows:All
Rows per commit to server: 50000
 Rows to skip: 0
 Errors count allowed: 100
 Whether to load direct: Yes
 Whether insert self-increase col: No
 Whether data has sort by gather index: No
 Character sets:GBK
Data file counts: 1
/home/dmdba/t1.txt
Error file :fldr.bad
Dest table :TEST_LOB
Column Name                                                                                                                      Packed data type     End
ID                                                                                                                               CHARACTER            ,
NAME                                                                                                                             CHARACTER            ,
1 rows processed.
Dest table :TEST_LOB
1 Rows loaded success
Due to data format error, 0 rows abandon
Due to data error, 0 rows not loaded
Skip logic record counts: 0
Read logic record counts: 1
Refuse logic record counts: 0
The total time used: 229.173(ms)