【IBM DB2链接】DB2使用FEDERATED 功能配置-库与库,表与表的链接

准备工作：
将 dbm cfg 参数 FEDERATED 从 NO 改为 YES
说明：federated是DB2联合数据库，您可以使用一条SQL语句查询多个数据源中的数据，这些数据源可以多种多样：即可以是多种关系数据库系统，也可以是非关系数据库系统
修改 dbm cfg 参数 FEDERATED

[db2inst1@ibmdb2 ~]$ db2 get dbm cfg|grep FEDERATED
Federated Database System Support (FEDERATED) = NO
Maximum Asynchronous TQs per query (FEDERATED_ASYNC) = 0

db2 update dbm cfg using FEDERATED YES
db2 force applications all
db2 terminate
db2stop
db2start
db2 get dbm cfg

操作日志如下：
[db2inst1@ibmdb2 ~]$ db2 update dbm cfg using FEDERATED YES
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
[db2inst1@ibmdb2 ~]$ db2 get dbm cfg|grep FEDERATED
Federated Database System Support (FEDERATED) = YES
Maximum Asynchronous TQs per query (FEDERATED_ASYNC) = 0

--如果是YES,则不需要重启，如果还是NO，则需要重启DB2

[db2inst1@ibmdb2 ~]$ db2 force applications all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[db2inst1@ibmdb2 ~]$ db2 terminate
DB20000I The TERMINATE command completed successfully.
[db2inst1@ibmdb2 ~]$ db2stop
10/18/2010 16:26:56 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
[db2inst1@ibmdb2 ~]$ db2start
10/18/2010 16:27:02 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@ibmdb2 ~]$
[db2inst1@ibmdb2 ~]$ db2 get dbm cfg|grep FEDERATED
Federated Database System Support (FEDERATED) = YES
Maximum Asynchronous TQs per query (FEDERATED_ASYNC) = 0

1、创建安装包：
db2 => connect to test1
Database Connection Information
Database server = DB2/LINUX 9.5.6
SQL authorization ID = DB2INST1
Local database alias = TEST1

//CREATE WRAPPER DRDA LIBRARY 'libdb2drda.so' OPTIONS ( ADD DB2_FENCED 'N') ;
选中DB2_FENCED,使用其默认值“N”，“DB2_FENCED”属性用来指定包装器是以受保护方式还是可信方式运行，默认值“N”表示包装器以可信方式方式运行。
注：在创建包装器可能出现“对指定的操作启用数据库实例”，原因码:”1”。设置配置FEDERATED为yes即可解决该问题，详见下面：
。
提示为对指定的操作启用数据库实例，原因：为设置配置项的FEDERATED为yes。解决方法：update dbm cfg using FEDERATED yes，设置后停止服务器，再启动服务器。

db2 =>
Database 4 entry:
Database alias = TEST2
Database name = TEST2
Local database directory = /home/db2inst1/db2inst1/NODE0000/test2
Database release level = c.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =

2、创建服务器定义

//CREATE SERVER TEST2 TYPE DB2/UDB VERSION '9.5' WRAPPER DRDA AUTHID "db2inst1" PASSWORD "db2inst1" OPTIONS( ADD DBNAME 'TEST2');

3、创建用户映射

win:
db2 => CREATE USER MAPPING FOR "ADMINISTRATOR" SERVER "TEST2" OPTIONS ( ADD REMOTE_AUTHID 'db2inst1', ADD REMOTE_PASSWORD 'db2inst1')
DB20000I The SQL command completed successfully.
//unix:
//CREATE USER MAPPING FOR DB2INST1 SERVER TEST2 OPTIONS ( ADD REMOTE_AUTHID 'db2inst1', ADD REMOTE_PASSWORD 'db2inst1') ;

4、创建相应的表
db2 => CONNECT TO TEST2 USER db2inst1 USING db2inst1
Database Connection Information
Database server = DB2/LINUX 9.5.6
SQL authorization ID = DB2INST1
Local database alias = TEST2

CREATE TABLE WORKITEM1 (ITEMNO CHAR(4) NOT NULL,ITEMNAME VARCHAR(50) NOT NULL,EMPNO CHAR(6),PRIMARY KEY (ITEMNO));
INSERT INTO WORKITEM1(ITEMNO,ITEMNAME,EMPNO) VALUES ('0001','测试程序1','000010');
INSERT INTO WORKITEM1(ITEMNO,ITEMNAME,EMPNO) VALUES ('0002','完成模块2','000020');
INSERT INTO WORKITEM1(ITEMNO,ITEMNAME,EMPNO) VALUES ('0003','完成模块3','000030');

------------------------------ Commands Entered ------------------------------
CREATE TABLE WORKITEM (
ITEMNO CHAR(4) NOT NULL,
ITEMNAME VARCHAR(50) NOT NULL,
EMPNO CHAR(6),
PRIMARY KEY (ITEMNO)
);
------------------------------------------------------------------------------
CREATE TABLE WORKITEM ( ITEMNO CHAR(4) NOT NULL, ITEMNAME VARCHAR(50) NOT NULL, EMPNO CHAR(6), PRIMARY KEY (ITEMNO) )
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
INSERT INTO WORKITEM(ITEMNO,ITEMNAME,EMPNO) VALUES ('0001','测试程序1','000010');
INSERT INTO WORKITEM(ITEMNO,ITEMNAME,EMPNO) VALUES ('0002','完成模块2','000020');
INSERT INTO WORKITEM(ITEMNO,ITEMNAME,EMPNO) VALUES ('0003','完成模块3','000030');
------------------------------------------------------------------------------
INSERT INTO WORKITEM(ITEMNO,ITEMNAME,EMPNO) VALUES ('0001','测试程序1','000010')
DB20000I The SQL command completed successfully.
INSERT INTO WORKITEM(ITEMNO,ITEMNAME,EMPNO) VALUES ('0002','完成模块2','000020')
DB20000I The SQL command completed successfully.
INSERT INTO WORKITEM(ITEMNO,ITEMNAME,EMPNO) VALUES ('0003','完成模块3','000030')
DB20000I The SQL command completed successfully.

db2 => select * from WORKITEM
ITEMNO ITEMNAME EMPNO
------ -------------------------------------------------- ------
0001 1 000010
0002 2 000020
0003 A 000030
3 record(s) selected.

5、创建昵称
CONNECT TO TEST1;
db2 => CREATE NICKNAME db2inst1.WORKITEM FOR TEST2.db2inst1.WORKITEM
DB20000I The SQL command completed successfully.
6、测试结果

测试结果如下：
db2 => SELECT * FROM WORKITEM
\
ITEMNO ITEMNAME EMPNO
------ -------------------------------------------------- ------
0001 1 000010
0002 2 000020
0003 A 000030
3 record(s) selected.

-----通过SQL创建联邦
-----创建都有对应的SQL语句：
1、创建安装包：
CREATE SERVER APPDB TYPE DB2/UDB VERSION '9.7' WRAPPER DRDA2 AUTHID "db2admin" PASSWORD "*****" OPTIONS( ADD DBNAME 'APPDB');
2、创建服务器定义
CREATE SERVER APPDB TYPE DB2/UDB VERSION '9.7' WRAPPER DRDA2 AUTHID "db2admin" PASSWORD "*****" OPTIONS( ADD DBNAME 'APPDB');
3、创建用户映射
CREATE USER MAPPING FOR ADMINISTRATOR SERVER FILEDB OPTIONS ( ADD REMOTE_AUTHID 'db2admin', ADD REMOTE_PASSWORD '*****') ;
4、创建昵称
CREATE NICKNAME DB2ADMIN.USERTBL FOR APPDB.DB2ADMIN.USERTBL;