# 配置连接池参数
UPDATE DATABASE MANAGER CONFIGURATION USING NUM_POOLAGENTS 100;
UPDATE DATABASE MANAGER CONFIGURATION USING MAX_POOLAGENTS 200;

# 配置代理参数
UPDATE DATABASE MANAGER CONFIGURATION USING MAXAGENTS 500;
UPDATE DATABASE MANAGER CONFIGURATION USING MAX_COORDAGENTS 300;

# 配置连接超时
UPDATE DATABASE MANAGER CONFIGURATION USING CONNECT_TIMEOUT 10;

# 配置空闲超时
UPDATE DATABASE MANAGER CONFIGURATION USING IDLE_TIMEOUT 600;

# 查看连接池配置
GET DATABASE MANAGER CONFIGURATION;

# 查看连接池状态
SELECT
POOL_CONFIG,
POOL_SIZE,
POOL_FREE
FROM SYSIBMADM.SNAPDBM;

# JDBC连接池配置
# 最小连接数
minPoolSize=10

# 最大连接数
maxPoolSize=100

# 连接超时时间
connectionTimeout=30000

# 空闲超时时间
idleTimeout=600000

# 最大生命周期
maxLifetime=1800000

# 连接验证
validationQuery=SELECT 1 FROM SYSIBM.SYSDUMMY1

# 连接验证超时
validationQueryTimeout=5

# Python连接池示例
import ibm_db_dbi as dbi
from threading import Lock

class DB2ConnectionPool:
def __init__(self, max_connections=20):
self.max_connections = max_connections
self.pool = []
self.lock = Lock()

def get_connection(self):
with self.lock:
if self.pool:
return self.pool.pop()
else:
return self._create_connection()

def return_connection(self, conn):
with self.lock:
if len(self.pool) < self.max_connections: self.pool.append(conn) else: conn.close() def _create_connection(self): conn_str = "DATABASE=FGEDB;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=password;" return dbi.connect(conn_str) # Java连接池示例（HikariCP） HikariConfig config = new HikariConfig(); config.setJdbcUrl("jdbc:db2://localhost:50000/FGEDB"); config.setUsername("db2inst1"); config.setPassword("password"); config.setMaximumPoolSize(100); config.setMinimumIdle(10); config.setConnectionTimeout(30000); config.setIdleTimeout(600000); config.setMaxLifetime(1800000); HikariDataSource ds = new HikariDataSource(config);

# 查看当前连接
SELECT
AGENT_ID,
APPL_NAME,
APPL_STATUS,
CLIENT_PID,
UOW_START_TIME,
CURRENT TIMESTAMP – UOW_START_TIME AS DURATION
FROM SYSIBMADM.APPLICATIONS
ORDER BY DURATION DESC;

# 查看连接统计
SELECT
COUNT(*) AS TOTAL_CONNECTIONS,
COUNT(CASE WHEN APPL_STATUS = ‘UOWWAIT’ THEN 1 END) AS IDLE_CONNECTIONS,
COUNT(CASE WHEN APPL_STATUS = ‘UOWEXEC’ THEN 1 END) AS ACTIVE_CONNECTIONS
FROM SYSIBMADM.APPLICATIONS;

# 查看长时间运行的连接
SELECT
AGENT_ID,
APPL_NAME,
UOW_START_TIME,
CURRENT TIMESTAMP – UOW_START_TIME AS DURATION,
SUBSTR(STMT_TEXT, 1, 100) AS STMT_TEXT
FROM SYSIBMADM.APPLICATIONS
WHERE UOW_START_TIME < CURRENT TIMESTAMP - 1 HOUR ORDER BY DURATION DESC; # 查看锁等待的连接 SELECT AGENT_ID, APPL_NAME, LOCK_WAIT_START_TIME, CURRENT TIMESTAMP - LOCK_WAIT_START_TIME AS WAIT_DURATION FROM SYSIBMADM.APPLICATIONS WHERE LOCK_WAIT_START_TIME IS NOT NULL ORDER BY WAIT_DURATION DESC;

# 查看连接CPU使用
SELECT
AGENT_ID,
APPL_NAME,
TOTAL_CPU_TIME,
TOTAL_USR_CPU_TIME,
TOTAL_SYS_CPU_TIME
FROM SYSIBMADM.APPLICATIONS
ORDER BY TOTAL_CPU_TIME DESC;

# 查看连接内存使用
SELECT
AGENT_ID,
APPL_NAME,
APPL_MEMORY_USED,
AGENT_STATEMENT_HEAP_USED
FROM SYSIBMADM.APPLICATIONS
ORDER BY APPL_MEMORY_USED DESC;

# 查看连接IO使用
SELECT
AGENT_ID,
APPL_NAME,
ROWS_READ,
ROWS_WRITTEN,
POOL_DATA_L_READS,
POOL_DATA_P_READS
FROM SYSIBMADM.APPLICATIONS
ORDER BY ROWS_READ DESC;

# 查看连接执行SQL数量
SELECT
AGENT_ID,
APPL_NAME,
TOTAL_APP_COMMITS,
TOTAL_APP_ROLLBACKS,
TOTAL_SELECTS,
TOTAL_INSERTS,
TOTAL_UPDATES,
TOTAL_DELETES
FROM SYSIBMADM.APPLICATIONS
ORDER BY TOTAL_SELECTS DESC;

# 优化连接参数
# 增大最大连接数
UPDATE DATABASE MANAGER CONFIGURATION USING MAXAGENTS 1000;
UPDATE DATABASE MANAGER CONFIGURATION USING MAX_COORDAGENTS 500;

# 优化连接池
UPDATE DATABASE MANAGER CONFIGURATION USING NUM_POOLAGENTS 200;
UPDATE DATABASE MANAGER CONFIGURATION USING MAX_POOLAGENTS 500;

# 优化应用内存
UPDATE DATABASE CONFIGURATION USING APPL_MEMORY AUTOMATIC;

# 优化语句堆
UPDATE DATABASE CONFIGURATION USING STMTHEAP AUTOMATIC;

# 优化包缓存
UPDATE DATABASE CONFIGURATION USING PCKCACHESZ AUTOMATIC;

# 查看优化效果
GET DATABASE MANAGER CONFIGURATION;
GET DATABASE CONFIGURATION;

# 1. 及时关闭连接
# Java示例
Connection conn = null;
try {
conn = dataSource.getConnection();
// 执行SQL
} finally {
if (conn != null) {
conn.close();
}
}

# 2. 使用连接池
# 避免频繁创建和销毁连接

# 3. 设置合理的超时时间
# 连接超时、查询超时、空闲超时

# 4. 监控连接使用
# 定期检查连接数量和状态

# 5. 清理空闲连接
# 定期断开长时间空闲的连接

# 6. 使用连接重试机制
# 连接失败时自动重试

# 清理空闲连接脚本
#!/bin/bash
# clean_idle_connections.sh

db2 connect to FGEDB

# 查找空闲超过1小时的连接
db2 “SELECT AGENT_ID FROM SYSIBMADM.APPLICATIONS
WHERE APPL_STATUS = ‘UOWWAIT’
AND UOW_START_TIME < CURRENT TIMESTAMP - 1 HOUR" > idle_connections.txt

# 断开空闲连接
while read agent_id; do
db2 “FORCE APPLICATION ($agent_id)”
done < idle_connections.txt db2 connect reset echo "Cleaned idle connections at $(date)"