db2建立类似oracle的dblink
db2 catalog tcpip node rmt_node remote 127.0.0.1 server 50000;
--db2 catalog database rmt_db as rmt_db_as at node rmt_node;
db2 terminate;
测试连通性
--db2 connect to rmt_db_as user db2inst1 using db2inst1
--db2 connect reset
--db2 uncatalog db dbAlisName
--db2 uncatalog node nodeName
--db2 list db directory
--db2 list node directory
--DROP USER MAPPING FOR authorization_name SERVER server_name
--DROP NICKNAME nickname
--DROP WRAPPER wrapper_name
--DROP SERVER server_name[@more@]
设置允许联邦
db2 update dbm cfg using FED_NOAUTH yes
db2stop
db2start
db2 connect to localdb user localuser using localpwd;
--这一步很重要,下面的命令开始一定不要加结尾分号
db2;
create wrapper drda
用node建server,关键点是不同服务器要用对方实例用户
create server linkRmtServer type db2/LINUX version 10.1.3 wrapper drda authorization "db2inst1" password "db2inst1" OPTIONS(node 'rmt_node',dbname 'rmt_db',PASSWORD 'Y')
或者用db别名建server
--create server linkRmtServer type db2/LINUX version 10.1.3 wrapper drda authorization "db2inst1" password "db2inst1" OPTIONS(dbname 'rmt_db_as',PASSWORD 'Y')
create user mapping for "localuser" server linkRmtServer options (add remote_authid 'db2inst1' ,add remote_password 'db2inst1')
注意,这里建的user mapping要用应用系统连接数据库的用户
如果同义词连不上远程表,或者create报错,就用刚刚建user mapping的用户重新连接再来建nickname
create nickname <nickname2rmt> for linkRmtServer.<schema>.<tablename>