达梦数据库-达梦数据库DM中link访问达梦数据库DM-记录总结

三种方式：MAL方式、DPI 方式、ODBC方式。
LINK分为私用和公用：
私用：某个用户使用。
公用：所有用户都可以使用。

示例步骤

示例需求

从本地环境192.168.118.217:5237库中创建link访问远程192.168.118.218:5237库中TEST(HUN_admin2026)用户TEST模式表t1；

环境准备

本地环境DM v8 03134284368-20260323-318812-20149 Pack66 + Kylin 10 + x86_64
远程环境DM v8 03134284368-20260323-318812-20149 Pack66 + Kylin 10 + x86_64
远程环境192.168.118.218:5237 DM库上提前准备
create tablespace TEST datafile ‘TEST01.DBF’ size 1024 autoextend on next 128, ‘TEST02.DBF’ size 1024 autoextend on next 128 CACHE = NORMAL;
create user TEST identified by “HUN_admin2026” default tablespace “TEST” default index tablespace “TEST”;
grant “PUBLIC”,“RESOURCE”,“SOI”,“SVI”,“VTI” to TEST;
create table test.t1(id int,info varchar2(10));
insert into test.t1 values(1,‘test1’);
commit;
select * from test.t1;

本地环境192.168.118.217:5237 DM库上提前准备
create tablespace TEST datafile ‘TEST01.DBF’ size 1024 autoextend on next 128, ‘TEST02.DBF’ size 1024 autoextend on next 128 CACHE = NORMAL;
create user TEST identified by “HUN_admin2026” default tablespace “TEST” default index tablespace “TEST”;
grant “PUBLIC”,“RESOURCE”,“SOI”,“SVI”,“VTI” to TEST;

1DPI方式-直接创建语句使用

–本地环境TEST用户登录192.168.118.217:5237 DM库DPI方式创建link远程访问
create or replace link DBLINKTEST connect ‘DPI’ with “TEST” identified by “HUN_admin2026” using ‘192.168.118.218:5237’;
select * from test.t1@DBLINKTEST ;
insert into test.t1@DBLINKTEST values(2,‘test2’);
commit;
select * from test.t1@DBLINKTEST;
update test.t1@DBLINKTEST set info=‘test1sss’ where id=1;
commit;
select * from test.t1@DBLINKTEST;
delete from test.t1@DBLINKTEST where id=2;
commit;
select * from test.t1@DBLINKTEST;
–删除link
drop link DBLINKTEST;

2ODBC方式

2.1编译安装ODBC

–下载unixODBC-2.3.0.tar.gz并解压编译安装
[root@localhost unixODBC-2.3.0]# tar -xvzf unixODBC-2.3.0.tar.gz
cd unixODBC-2.3.0/
./configure
make
make install
–查看odbc版本
odbc_config --version
odbcinst -j

2.2ODBC配置与使用

[root@loaclhost unixODBC-2.3.0]# vi /usr/local/etc/odbcinst.ini
[DM8 ODBC DRIVER]
Description = ODBC DRIVER FOR DM8
DRIVER = /opt/dmdbms/bin/libdodbc.so
注：/opt/dmdbms/bin/libdodbc.so为达梦数据库软件安装目录
[root@loaclhost unixODBC-2.3.0]# vi /usr/local/etc/odbc.ini
[dm8]
Description = DM ODBC DSND
Driver = DM8 ODBC DRIVER
SERVER = 192.168.118.218
UID = TEST
PWD = HUN_admin2026
TCP_PORT = 5237
[root@loaclhost unixODBC-2.3.0]# su - dmdba
[dmdba@loaclhost ~]$ cat /usr/local/etc/odbcinst.ini
[dmdba@loaclhost ~]$ cat /usr/local/etc/odbc.ini
[dmdba@loaclhost ~]$ isql dm8

2.3DM数据库中使用访问远程DM库

–TEST用户登录192.168.118.217:5237 DM数据库中创建link并使用
create or replace link ODBCLINKTEST connect ‘ODBC’ with “TEST” identified by “HUN_admin2026” using ‘dm8’;
select * from test.t1@ODBCLINKTEST;

注意问题：
这里一直报错：第1行附近出现错误[-6033]:DBLINK连接丢失。
查询数据库日志也没有任何报错。
解决问题：
把/usr/local/etc/odbcinst.ini和/usr/local/etc/odbc.ini 拷贝到/etc/目录下即可。
[root@localhost unixODBC-2.3.0]# cp /usr/local/etc/odbcinst.ini /etc/
[root@localhost unixODBC-2.3.0]# cp /usr/local/etc/odbc.ini /etc/
–然后登录数据库使用
create or replace link ODBCLINKTEST connect ‘ODBC’ with “TEST” identified by “HUN_admin2026” using ‘dm8’;
select * from test.t1@ODBCLINKTEST;
insert into test.t1@ODBCLINKTEST values(2,‘test2’);
commit;
select * from test.t1@ODBCLINKTEST;
update test.t1@ODBCLINKTEST set info=‘test1-odbc’ where id=1;
commit;
select * from test.t1@ODBCLINKTEST;
delete from test.t1@ODBCLINKTEST where id=2;
commit;
select * from test.t1@ODBCLINKTEST;
drop link DBLINKTEST;

3MAL系统方式

3.1MAL系统配置

本地和远程环境都配置dm.ini文件MAL_INI=1
sp_set_para_value(1,‘MAL_INI’,1);–动态参数
本地和远程环境都配置dmmal.ini文件
[MAL_INST1]
MAL_INST_NAME =DMSERVER #本地端实例名;
MAL_HOST = 192.168.118.217 #内网IP;
MAL_PORT = 5537 # MAL系统端口号;
MAL_INST_HOST = 192.168.118.217 #外网IP,对外提供服务IP;
MAL_INST_PORT = 5237 #数据库端口号;
[MAL_INST2]
MAL_INST_NAME =DMSERVERS
MAL_HOST = 192.168.118.218
MAL_PORT = 5537
MAL_INST_HOST = 192.168.118.218
MAL_INST_PORT = 5237
####3.2TEST用户登录本地环境192.168.118.217:5237数据库中创建link并使用

或
create or replace link LINK_MAL connect ‘DAMENG’ with “TEST” identified by “HUN_admin2026” using ‘192.168.118.218/5237’;
select * from test.t1@LINK_MAL;
insert into test.t1@LINK_MAL values(2,‘test2’);
commit;
select * from test.t1@LINK_MAL;
update test.t1@LINK_MAL set info=‘test1-mal’ where id=1;
commit;
select * from test.t1@LINK_MAL;
delete from test.t1@LINK_MAL where id=2;
commit;
select * from test.t1@LINK_MAL;
drop link LINK_MAL;