之前我们整理了DM DCP 的相关内容：http://www.cndba.cn/dave/article/108716http://www.cndba.cn/dave/article/108716

达梦 DM8 DCP 备考笔记(1) — 理论考试 说明
https://www.cndba.cn/dave/article/108708
达梦 DM8 DCP 备考笔记(2) — 分区表操作
https://www.cndba.cn/dave/article/108710
达梦 DM8 DCP 备考笔记(3) — 外部表操作
https://www.cndba.cn/dave/article/108711
达梦 DM8 DCP 备考笔记(4) — 同义词操作
https://www.cndba.cn/dave/article/108712
达梦 DM8 DCP 备考笔记(5) — 物化视图操作
https://www.cndba.cn/dave/article/108713
达梦 DM8 DCP 备考笔记(6) — 序列操作
https://www.cndba.cn/dave/article/108714
达梦 DM8 DCP 备考笔记(7) — 审计操作
https://www.cndba.cn/dave/article/108715

http://www.cndba.cn/dave/article/108716

本篇我们来看下达梦数据库中的快速加载的操作。 相关理论说明参考《DM8_dmfldr使用手册》，达梦用了一个单独的手册来介绍dmfldr（DM Fast Loader）。

我之前博客对Fast Loader 的说明：http://www.cndba.cn/dave/article/108716

DM7 达梦 数据库 数据快速加载(dmfldr) 使用说明
https://www.cndba.cn/dave/article/3705

1 案例1：普通表操作

编辑数据文件：

[dave@www.cndba.cn data]$ pwd
/dm/dmdbms/data
[dave@www.cndba.cn data]$ cat cndba.txt
1,dave,0556
2,hefei,0551
3,anqing,0556

[dave@www.cndba.cn data]$

编写控制文件：http://www.cndba.cn/dave/article/108716http://www.cndba.cn/dave/article/108716

[dave@www.cndba.cn data]$ cat cndba.ctl
LOAD DATA
INFILE '/dm/dmdbms/data/cndba.txt'
BADFILE '/dm/dmdbms/data/cndba.bad'
INTO TABLE cndba.ustc
FIELDS ','
[dave@www.cndba.cn data]$

创建表

SQL> create table cndba.ustc(id int, name varchar(20), code int);
executed successfully
used time: 4.636(ms). Execute id is 56405.
SQL>

快速加载数据

[dave@www.cndba.cn ~]$ dmfldr sysdba/dameng123 control=/'/dm/dmdbms/data/cndba.ctl/'
dmfldr V8
Control file:
……
Data error
3 rows committed

Dest table :CNDBA.USTC
load success.
3 rows loaded success.
1 rows not loaded due to data format error.
0 rows not loaded due to data error.

Skip logic record counts: 0
Read logic record counts: 4
Refuse logic record counts: 0

8.419(ms) time used.

[dave@www.cndba.cn ~]$


SQL> select * from ustc;

LINEID     ID          NAME   CODE
---------- ----------- ------ -----------
1          1           dave   556
2          2           hefei  551
3          3           anqing 556

used time: 0.453(ms). Execute id is 56407.
SQL>

2 案例2：大字段导出

创建表并插入测试数据：http://www.cndba.cn/dave/article/108716http://www.cndba.cn/dave/article/108716

SQL> CREATE TABLE HEFEI(C1 INT,C2 BLOB,C3 CLOB);
executed successfully
used time: 4.594(ms). Execute id is 56408.
SQL> INSERT INTO HEFEI VALUES(1,0XAB121032DE,'https://www.cndba.cn/');
affect rows 1

used time: 0.576(ms). Execute id is 56409.
SQL> INSERT INTO HEFEI VALUES(2,0XAB121032DE,'https://www.cndba.cn/');
affect rows 1

used time: 0.442(ms). Execute id is 56410.
SQL> COMMIT;
executed successfully
used time: 2.173(ms). Execute id is 56411.
SQL>

创建控制文件：

[dave@www.cndba.cn data]$ pwd
/dm/dmdbms/data
[dave@www.cndba.cn data]$ cp cndba.ctl hefei.ctl
[dave@www.cndba.cn data]$ vim hefei.ctl
[dave@www.cndba.cn data]$ cat hefei.ctl
LOAD DATA
INFILE '/dm/dmdbms/data/hefei.txt'
BADFILE '/dm/dmdbms/data/heifei.bad'
INTO TABLE cndba.hefei
FIELDS '|'
(
C1,
C2,
C3
)
[dave@www.cndba.cn data]$

导出大字段：

[dave@www.cndba.cn data]$ dmfldr sysdba/dameng123 control=/'/dm/dmdbms/data/hefei.ctl/' lob_directory=/'/dm/dmdbms/data/' mode=/'out/'
dmfldr V8
2 rows is load out
export success.
2 rows exported success.
121.486(ms) time used.

[dave@www.cndba.cn data]$ ls
cndba.bad  cndba.ctl  cndba.txt  DCP  dmfldr.lob  fldr.log  hefei.ctl  hefei.txt  t11.ctl  t11.txt  t1.ctl  t1.txt

[dave@www.cndba.cn data]$ cat hefei.txt
1|dmfldr.lob:0:5|dmfldr.lob:5:21
2|dmfldr.lob:26:5|dmfldr.lob:31:21
[dave@www.cndba.cn data]$

3 案例3：大字段导入

先将上节创建的表清空，然后将导出文件导入：

SQL> select * from hefei;

LINEID     C1          C2           C3
---------- ----------- ------------ ---------------------
1          1           0xAB121032DE https://www.cndba.cn/
2          2           0xAB121032DE https://www.cndba.cn/

used time: 0.464(ms). Execute id is 56412.
SQL> truncate table hefei;
executed successfully
used time: 3.890(ms). Execute id is 56413.
SQL> select * from hefei;
no rows

used time: 0.390(ms). Execute id is 56414.
SQL>

导入：

[dave@www.cndba.cn data]$ dmfldr sysdba/dameng123 control=/'/dm/dmdbms/data/hefei.ctl/' lob_directory=/'/dm/dmdbms/data/' mode=/'in/'

……

row buffer number is: 2
task thread number is: 2

2 rows committed

Dest table :CNDBA.HEFEI
load success.
2 rows loaded success.
0 rows not loaded due to data format error.
0 rows not loaded due to data error.

Skip logic record counts: 0
Read logic record counts: 2
Refuse logic record counts: 0

5.463(ms) time used.

[dave@www.cndba.cn data]$


SQL> select * from hefei;

LINEID     C1          C2           C3
---------- ----------- ------------ ---------------------
1          1           0xAB121032DE https://www.cndba.cn/
2          2           0xAB121032DE https://www.cndba.cn/

used time: 0.261(ms). Execute id is 56415.
SQL>

版权声明：本文为博主原创文章，未经博主允许不得转载。