# PureScale架构组件
# – CF (Cluster Facility)
# – Member (数据库成员)
# – GPFS (共享文件系统)

# 查看集群成员
SELECT
MEMBER,
HOST_NAME,
STATE
FROM SYSIBMADM.DB2_MEMBER;

# 查看CF状态
SELECT
CF_ID,
HOST_NAME,
STATE
FROM SYSIBMADM.DB2_CF;

# 查看集群状态
db2pd -pureScale -status

# 成员管理
# 添加成员
db2iupdt -add -m db2member3 db2inst1

# 删除成员
db2iupdt -drop -m db2member3 db2inst1

# 查看成员配置
db2pd -pureScale -members

# 配置客户端负载均衡
# JDBC连接字符串
jdbc:db2://host1:50000,host2:50000,host3:50000/FGEDB:enableClientAffinitiesList=1;

# 配置WLM
CREATE SERVICE CLASS OLTP_CLASS
SOFT CPU SHARES 100
SOFT MEMORY SHARES 100;

CREATE SERVICE CLASS BATCH_CLASS
SOFT CPU SHARES 50
SOFT MEMORY SHARES 50;

# 创建工作负载
CREATE WORKLOAD OLTP_WORKLOAD
APPLNAME(‘OLTP_APP’)
SERVICE CLASS OLTP_CLASS;

CREATE WORKLOAD BATCH_WORKLOAD
APPLNAME(‘BATCH_APP’)
SERVICE CLASS BATCH_CLASS;

# 监控成员负载
SELECT
MEMBER,
TOTAL_CPU_TIME,
TOTAL_WAIT_TIME,
TOTAL_SORTS
FROM SYSIBMADM.APPLICATIONS
ORDER BY MEMBER;

# 按业务分库
# 订单库
CREATE DATABASE ORDERDB;

# 用户库
CREATE DATABASE USERDB;

# 商品库
CREATE DATABASE PRODUCTDB;

# 按时间分表
CREATE TABLE ORDERS_202601 (
ORDER_ID VARCHAR(32) NOT NULL,
CUSTOMER_ID VARCHAR(32),
TOTAL_AMOUNT DECIMAL(18, 2),
CREATE_TIME TIMESTAMP,
CONSTRAINT PK_ORDERS_202601 PRIMARY KEY (ORDER_ID)
);

CREATE TABLE ORDERS_202602 (
ORDER_ID VARCHAR(32) NOT NULL,
CUSTOMER_ID VARCHAR(32),
TOTAL_AMOUNT DECIMAL(18, 2),
CREATE_TIME TIMESTAMP,
CONSTRAINT PK_ORDERS_202602 PRIMARY KEY (ORDER_ID)
);

# 使用视图统一访问
CREATE VIEW V_ORDERS AS
SELECT * FROM ORDERS_202601
UNION ALL
SELECT * FROM ORDERS_202602
UNION ALL
SELECT * FROM ORDERS_202603;

# 创建哈希分片表
CREATE TABLE ORDERS_HASH (
ORDER_ID VARCHAR(32) NOT NULL,
CUSTOMER_ID VARCHAR(32),
TOTAL_AMOUNT DECIMAL(18, 2),
CREATE_TIME TIMESTAMP,
CONSTRAINT PK_ORDERS_HASH PRIMARY KEY (ORDER_ID)
) DISTRIBUTE BY HASH(ORDER_ID);

# 查看分片信息
SELECT
TABSCHEMA,
TABNAME,
DISTRIBUTION
FROM SYSCAT.TABLES
WHERE TABNAME = ‘ORDERS_HASH’;

# 查询特定分片数据
SELECT * FROM ORDERS_HASH
WHERE ORDER_ID = ‘O001’;

# 配置联邦数据库
UPDATE DATABASE MANAGER CONFIGURATION USING FEDERATED YES;
db2stop
db2start

# 创建包装器
CREATE WRAPPER DRDA;

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

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

# 创建昵称
CREATE NICKNAME REMOTE_ORDERS FOR ORDERDB_SERVER.ORDERS;

# 联邦查询
SELECT * FROM REMOTE_ORDERS
WHERE CREATE_TIME > ‘2026-04-01’;

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

# 提交阶段
COMMIT TRANSACTION ‘tx001’;

# 回滚阶段
ROLLBACK TRANSACTION ‘tx001’;

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

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

INSERT INTO REMOTE_ORDERS VALUES (‘O002’, ‘C002’, 2000.00);
END;