db2跨数据库访问表

来源：互联网发布：大数据平台功能设计编辑：程序博客网时间：2024/06/03 01:25

--首先要打开数据的联邦功能命令如下
db2 update dbm cfg using Federated YES

--首先需要在host1上把host2节点和remote_db库编目进来
db2 catalog tcpip node node_22 remote 172.16.30.22 server 50000
db2 catalog db crmdb as crmdb2 at node node_22
db2 TERMINATE --(必须使用该命令否则可能导致catalog更新不能生效)

--然后在host1上测试到host2的连通性
db2 connect to crmdb2 user sino_cc using sino123
db2 connect reset

--修改host1和host2的实例配置参数，需要重起实例
db2 update dbm cfg using FED_NOAUTH yes
db2stop
db2start

--然后连接到host1机器上的local_db数据库中
db2 connect to aaicdb14

create wrapper "DRDA" LIBRARY 'db2drda.dll' OPTIONS( ADD DB2_FENCED 'N');

create server remoteserver_22 TYPE DB2/UDB VERSION '8.2' WRAPPER DRDA AUTHORIZATION "sino_cc" PASSWORD "sino_cc/sino123" OPTIONS( DBNAME 'crmdb2', PASSWORD 'Y');
CREATE USER MAPPING FOR "sino_cc" SERVER remoteserver_22 OPTIONS ( ADD REMOTE_AUTHID 'sino_cc', ADD REMOTE_PASSWORD 'sino_cc/sino123');
CREATE NICKNAME crmPrplback FOR remoteserver_22.sino_cc.prplback;

select * from db2inst1.crmPrplback
db2 connect reset

--select * from prplback where registno='1111111111111' ;