# 主库配置
UPDATE DATABASE CONFIGURATION FOR FGEDB USING
HADR_LOCAL_HOST 192.168.1.10
HADR_LOCAL_SVC 55001
HADR_REMOTE_HOST 192.168.1.11
HADR_REMOTE_SVC 55002
HADR_REMOTE_INST db2inst1
HADR_TIMEOUT 120
HADR_SYNCMODE SYNC
HADR_PEER_WINDOW 120;

# 启动HADR主库
START HADR ON DATABASE FGEDB AS PRIMARY;

# 查看HADR状态
GET SNAPSHOT FOR HADR ON FGEDB;

# 备库配置
UPDATE DATABASE CONFIGURATION FOR FGEDB USING
HADR_LOCAL_HOST 192.168.1.11
HADR_LOCAL_SVC 55002
HADR_REMOTE_HOST 192.168.1.10
HADR_REMOTE_SVC 55001
HADR_REMOTE_INST db2inst1
HADR_TIMEOUT 120
HADR_SYNCMODE SYNC
HADR_PEER_WINDOW 120;

# 启动HADR备库
START HADR ON DATABASE FGEDB AS STANDBY;

# 查看HADR状态
GET SNAPSHOT FOR HADR ON FGEDB;

# 检查HADR连接状态
SELECT
HADR_ROLE,
HADR_STATE,
HADR_SYNCMODE,
HADR_TIMEOUT
FROM SYSIBMADM.SNAPHADR;

# Java应用示例
public class DB2DataSource {
private DataSource writeDataSource;
private DataSource readDataSource;

public Connection getConnection(boolean readOnly) throws SQLException {
if (readOnly) {
return readDataSource.getConnection();
} else {
return writeDataSource.getConnection();
}
}
}

# 使用示例
DB2DataSource ds = new DB2DataSource();

// 写操作使用主库
Connection writeConn = ds.getConnection(false);
PreparedStatement stmt = writeConn.prepareStatement(“INSERT INTO ORDERS …”);
stmt.executeUpdate();

// 读操作使用备库
Connection readConn = ds.getConnection(true);
PreparedStatement stmt = readConn.prepareStatement(“SELECT * FROM ORDERS …”);
ResultSet rs = stmt.executeQuery();

# 使用连接池配置读写分离
# 主库连接池
db.primary.url=jdbc:db2://192.168.1.10:50000/FGEDB
db.primary.username=write_user
db.primary.password=password

# 备库连接池
db.secondary.url=jdbc:db2://192.168.1.11:50000/FGEDB
db.secondary.username=read_user
db.secondary.password=password

# 使用代理实现自动路由
CREATE ROUTINE SCHEMA READONLY AUTHORIZATION read_user;

# 创建只读视图
CREATE VIEW V_ORDERS_READONLY AS
SELECT * FROM ORDERS;

# 授权只读用户
GRANT SELECT ON V_ORDERS_READONLY TO read_user;

# JDBC连接字符串配置负载均衡
jdbc:db2://192.168.1.10:50000,192.168.1.11:50000/FGEDB:enableClientAffinitiesList=1;clientRerouteAlternateServerName=192.168.1.11;clientRerouteAlternatePortNumber=50000;

# 配置客户端路由
UPDATE ALTERNATE SERVER FOR DATABASE FGEDB
USING HOST 192.168.1.11 PORT 50000;

# 查看客户端路由配置
SELECT
SERVER_NAME,
HOST_NAME,
PORT_NUMBER
FROM SYSIBMADM.SERVERS;

# 配置工作负载管理
CREATE SERVICE CLASS READ_CLASS
SOFT CPU SHARES 50
SOFT MEMORY SHARES 50;

CREATE SERVICE CLASS WRITE_CLASS
SOFT CPU SHARES 100
SOFT MEMORY SHARES 100;

# 创建工作负载
CREATE WORKLOAD READ_WORKLOAD
APPLNAME(‘READ_APP’)
SERVICE CLASS READ_CLASS;

CREATE WORKLOAD WRITE_WORKLOAD
APPLNAME(‘WRITE_APP’)
SERVICE CLASS WRITE_CLASS;

# 监控工作负载
SELECT
WORKLOAD_NAME,
SERVICE_CLASS_NAME,
TOTAL_CPU_TIME,
TOTAL_WAIT_TIME
FROM SYSIBMADM.WORKLOAD_OCCURRENCE_METRICS;