Kdb_Database_Link 使用介绍

2022-01-16 03:48:45

kdb_database_link 是 KINGBASE 为了兼容oracle 语法而开发的跨数据库访问扩展，可用于访问KINGBASE , Postgresql , Oracle 。以下分别介绍访问KINGBASE 与Oracle 的配置过程。

一、配置ODBC

1、安装

[root@dbhost03 etc]# yum install unixODBC.x86_64

[kingbase@dbhost03 kbdata]$ isql --version
unixODBC 2.3.1

[kingbase@dbhost03 kbdata]$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /home/kingbase/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

注意：如果设置了LD_LIBRARY_PATH环境变量，则实际生效的是/usr/local/etc/odbcinst.ini ，否则可能是 /etc/odbcinst.ini

2、配置

根据odbcinst -j 显示的路径，配置 odbcinst.ini 和 odbc.ini 文件。

odbcinst.ini 文件内容如下：

[root@dbhost03 etc]# cat /usr/local/etc/odbcinst.ini 
# Example driver definitions

# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib/psqlodbcw.so
Setup           = /usr/lib/libodbcpsqlS.so
Driver64        = /usr/lib64/psqlodbcw.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1


# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description     = ODBC for MySQL
Driver          = /usr/lib/libmyodbc5.so
Setup           = /usr/lib/libodbcmyS.so
Driver64        = /usr/lib64/libmyodbc5.so
Setup64         = /usr/lib64/libodbcmyS.so
FileUsage       = 1

[KingbaseES V8R6 ODBC Driver]
Description     = ODBC for KingbaseES
Driver          = /opt/Kingbase/ES/V8/Interface/odbc/kdbodbcw.so
Debug           = 1
CommLog         = 1

odbc.ini 文件内容如下（可选配置，主要用于isql 验证）：

[v8r6]
Description=KingbaseES
Driver=KingbaseES V8R6 ODBC Driver
Host=localhost
Database=test
Username=system
Password=p123
Port=54321

3、isql 验证 (可选)

必须配置了 odbc.ini ，才能进行此步骤

[kingbase@dbhost03 ~]$ isql v8r6
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

4、验证驱动

验证kingbase自带的驱动 kdbodbcw.so，确保没有"not found"

ldd /opt/Kingbase/ES/V8/Interface/odbc/kdbodbcw.so

二、连接kingbase

1、修改 shared_preload_libraries 参数

需将kdb_database_link 加入 shared_preload_libraries 中

2、创建扩展

kdb_database_link 实际是在kingbase_fdw上层进行了包装，需要kingbase_fdw扩展。

create extension kdb_database_link
create extension kingbase_fdw

kdb_database_link 扩展创建后，会生成 $KINGBASE_DATA/sys_database_link.conf文件，类似于oracle 的tnsnames.ora 文件，用于配置服务名，在创建database link时使用。模板内容如下：

#[oradb]
#dbtype=Oracle
#dbname=orcl
#DriverName="Oracle12C ODBC Driver"
#host=127.0.0.1
#port=1521

[svr42]
dbtype=Kingbase
dbname=test
DriverName="KingbaseES V8R6 ODBC Driver"
host=192.168.237.42
port=54321

#[PostgreSQL]
#dbtype=Postgres
#dbname=postgres
#DriverName="PostgreSQL ODBC Driver"
#host=127.0.0.1
#port=5432

3、创建数据链

create public database link to_svr42 connect to ‘user_remote‘ identified by ‘user_remote‘ using 
( DriverName = ‘KingbaseES V8R6 ODBC Driver‘ , Host = ‘192.168.237.42‘ , Port = 54321 , Dbname = ‘test‘ , Dbtype = ‘kingbase‘)

或者修改sys_database_link.conf，配置正确的连接串。

create public database link to_svr42 connect to ‘user_remote‘ identified by ‘user_remote‘ using ‘svr42‘

数据链创建完后，会在pg_foreign_server 和 pg_user_mappings 增加相关条目

test=# select * from pg_user_mappings;                                                                  
 umid  | srvid |           srvname           | umuser |  usename   |                umoptions                
-------+-------+-----------------------------+--------+------------+-----------------------------------------
 16535 | 16534 | dblink_server_to_svr42_2200 |      0 | public     | {user=user_remote,password=user_remote}
(2 rows)

test=# select * from pg_foreign_server;                                                                 
  oid  |           srvname           | srvowner | srvfdw | srvtype | srvversion |                srvacl                 |                  srvoptions                  
-------+-----------------------------+----------+--------+---------+------------+---------------------------------------+----------------------------------------------
 16534 | dblink_server_to_svr42_2200 |       10 |  16495 |         |            |                                       | {dbname=test,host=192.168.237.42,port=54321}

4、验证数据链

test=# select * from user_remote.t1@to_svr42;
 ID |   NAME    
----+-----------
  1 | a        
  2 | b

5、删除数据链

drop database link public.to_svr42;

三、连接oracle

1、创建扩展

如果要通过database link 连接访问oracle数据库，除了kdb_database_link外，还需要 oracle_fdw 插件。

create extension oracle_fdw;
create extension kdb_database_link；

2、配置sys_database_link.conf

[oradb]
dbtype=Oracle
dbname=orcl
DriverName="Oracle ODBC Driver"
host=192.168.237.41
port=1521

3、配置odbc

配置过程见：https://www.cnblogs.com/kingbase/p/14921071.html

4、创建database link

create public database link to_ora41 connect to ‘user01‘ identified by ‘user01‘ using ‘oradb‘

数据链创建后，可以在字典表sys_database_link 与 pg_foreign_server 看到相关信息。

5、验证数据访问