# 启用联邦功能
UPDATE DATABASE MANAGER CONFIGURATION USING FEDERATED YES;

# 重启实例
db2stop
db2start

# 验证联邦功能
GET DATABASE MANAGER CONFIGURATION | grep FEDERATED

# 连接到本地数据库
db2 connect to FGEDB

# 安装包装器
# DB2包装器
CREATE WRAPPER DRDA;

# Oracle包装器
CREATE WRAPPER ORACLE;

# MySQL包装器
CREATE WRAPPER MYSQL;

# ODBC包装器
CREATE WRAPPER ODBC;

# 创建远程DB2服务器
CREATE SERVER REMOTE_DB2
TYPE DB2/UDB
VERSION 12.1
WRAPPER DRDA
AUTHORIZATION “remote_user”
PASSWORD “remote_password”
OPTIONS (
ADD DBNAME ‘REMOTEDB’,
ADD NODE ‘REMOTE_NODE’
);

# 创建Oracle服务器
CREATE SERVER ORACLE_SERVER
TYPE ORACLE
VERSION 19c
WRAPPER ORACLE
AUTHORIZATION “oracle_user”
PASSWORD “oracle_password”
OPTIONS (
ADD NODE ‘oracle_tns_name’
);

# 创建MySQL服务器
CREATE SERVER MYSQL_SERVER
TYPE MYSQL
VERSION 8.0
WRAPPER MYSQL
AUTHORIZATION “mysql_user”
PASSWORD “mysql_password”
OPTIONS (
ADD HOST ‘192.168.1.100’,
ADD PORT ‘3306’,
ADD DBNAME ‘mysqldb’
);

# 查看服务器定义
SELECT
SERVERNAME,
SERVER_TYPE,
SERVER_VERSION,
WRAPPERNAME
FROM SYSCAT.SERVERS;

# 创建用户映射
CREATE USER MAPPING FOR LOCAL_USER
SERVER REMOTE_DB2
OPTIONS (
ADD REMOTE_AUTHID ‘remote_user’,
ADD REMOTE_PASSWORD ‘remote_password’
);

CREATE USER MAPPING FOR LOCAL_USER
SERVER ORACLE_SERVER
OPTIONS (
ADD REMOTE_AUTHID ‘oracle_user’,
ADD REMOTE_PASSWORD ‘oracle_password’
);

# 创建昵称（远程表的本地别名）
CREATE NICKNAME REMOTE_ORDERS FOR REMOTE_DB2.ORDERS;
CREATE NICKNAME REMOTE_CUSTOMER FOR REMOTE_DB2.CUSTOMER;

CREATE NICKNAME ORACLE_EMPLOYEES FOR ORACLE_SERVER.EMPLOYEES;
CREATE NICKNAME ORACLE_DEPARTMENTS FOR ORACLE_SERVER.DEPARTMENTS;

CREATE NICKNAME MYSQL_PRODUCTS FOR MYSQL_SERVER.PRODUCTS;

# 查看昵称定义
SELECT
NICKNAME,
SERVERNAME,
REMOTE_SCHEMA,
REMOTE_TABLE
FROM SYSCAT.NICKNAMES;

# 查看远程表结构
DESCRIBE REMOTE_ORDERS;
DESCRIBE ORACLE_EMPLOYEES;

# 查询远程DB2表
SELECT * FROM REMOTE_ORDERS
WHERE CREATE_TIME > ‘2026-04-01’;

# 联合查询本地和远程表
SELECT
o.ORDER_ID,
o.TOTAL_AMOUNT,
c.CUSTOMER_NAME
FROM ORDERS o
INNER JOIN REMOTE_CUSTOMER c ON o.CUSTOMER_ID = c.CUSTOMER_ID
WHERE o.CREATE_TIME > ‘2026-04-01’;

# 跨多个数据源查询
SELECT
o.ORDER_ID,
o.TOTAL_AMOUNT,
e.EMPLOYEE_NAME AS SALES_REP,
p.PRODUCT_NAME
FROM ORDERS o
INNER JOIN ORACLE_EMPLOYEES e ON o.SALES_REP_ID = e.EMPLOYEE_ID
INNER JOIN MYSQL_PRODUCTS p ON o.PRODUCT_ID = p.PRODUCT_ID
WHERE o.CREATE_TIME > ‘2026-04-01’;

# 联邦聚合查询
SELECT
c.COUNTRY,
COUNT(*) AS ORDER_COUNT,
SUM(o.TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM ORDERS o
INNER JOIN REMOTE_CUSTOMER c ON o.CUSTOMER_ID = c.CUSTOMER_ID
GROUP BY c.COUNTRY
ORDER BY TOTAL_AMOUNT DESC;

# 使用PASS THROUGH直接访问远程数据源
SET PASSTHRU REMOTE_DB2;

SELECT * FROM ORDERS WHERE CREATE_TIME > ‘2026-04-01’;

SET PASSTHRU RESET;

# 分布式事务示例
BEGIN ATOMIC
INSERT INTO ORDERS (ORDER_ID, CUSTOMER_ID, TOTAL_AMOUNT)
VALUES (‘O001’, ‘C001’, 1000.00);

UPDATE REMOTE_CUSTOMER
SET TOTAL_ORDERS = TOTAL_ORDERS + 1,
TOTAL_AMOUNT = TOTAL_AMOUNT + 1000.00
WHERE CUSTOMER_ID = ‘C001’;

INSERT INTO ORACLE_EMPLOYEES (EMPLOYEE_ID, SALES_COUNT)
VALUES (‘E001’, 1);
END;

# 两阶段提交
# 准备阶段
PREPARE TRANSACTION ‘tx001’;

# 提交阶段
COMMIT TRANSACTION ‘tx001’;

# 回滚阶段
ROLLBACK TRANSACTION ‘tx001’;

# 监控分布式事务
SELECT
XID,
STATUS,
PARTICIPANTS
FROM SYSIBMADM.TRANSACTIONS
WHERE XID IS NOT NULL;

# 启用查询下推
ALTER SERVER REMOTE_DB2
OPTIONS (SET PUSHDOWN ‘Y’);

ALTER SERVER ORACLE_SERVER
OPTIONS (SET PUSHDOWN ‘Y’);

# 查看查询下推设置
SELECT
SERVERNAME,
OPTION,
SETTING
FROM SYSCAT.SERVEROPTIONS
WHERE OPTION = ‘PUSHDOWN’;

# 创建索引昵称
CREATE NICKNAME REMOTE_ORDERS_IDX FOR REMOTE_DB2.IDX_ORDERS_CUSTOMER;

# 使用本地物化查询表缓存远程数据
CREATE TABLE MQT_REMOTE_ORDERS AS (
SELECT * FROM REMOTE_ORDERS
) DATA INITIALLY DEFERRED REFRESH DEFERRED;

REFRESH TABLE MQT_REMOTE_ORDERS;

# 使用本地表缓存热点远程数据
CREATE TABLE LOCAL_CUSTOMER_CACHE AS (
SELECT * FROM REMOTE_CUSTOMER
) DATA INITIALLY DEFERRED REFRESH DEFERRED;

REFRESH TABLE LOCAL_CUSTOMER_CACHE;

# 监控联邦查询性能
SELECT
SUBSTR(STMT_TEXT, 1, 100) AS STMT_TEXT,
TOTAL_EXEC_TIME,
TOTAL_USR_CPU_TIME,
TOTAL_SYS_CPU_TIME,
TOTAL_WAIT_TIME
FROM SYSIBMADM.TOP_DYNAMIC_SQL
WHERE STMT_TEXT LIKE ‘%REMOTE_%’
ORDER BY TOTAL_EXEC_TIME DESC;

# 监控远程服务器连接
SELECT
SERVERNAME,
CONNECTIONS,
TOTAL_CONNECTS,
TOTAL_CONNECT_TIME
FROM SYSCAT.SERVERS;

# 查看联邦查询执行计划
EXPLAIN PLAN FOR
SELECT * FROM ORDERS o
INNER JOIN REMOTE_CUSTOMER c ON o.CUSTOMER_ID = c.CUSTOMER_ID;

SELECT * FROM EXPLAIN_OPERATOR WHERE OPERATOR_TYPE = ‘REMOTE’;

# 监控包装器性能
SELECT
WRAPPERNAME,
SERVERS,
NICKNAMES
FROM SYSCAT.WRAPPERS;