# 配置日志文件大小（4KB页）
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGFILSIZ 16384;

# 配置主日志文件数量
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGPRIMARY 10;

# 配置辅助日志文件数量
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGSECOND 20;

# 配置日志缓冲大小（4KB页）
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGBUFSZ 1024;

# 配置日志组数量
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGGROUPS 2;

# 启用无限日志
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGSECOND -1;

# 配置日志路径
UPDATE DATABASE CONFIGURATION FOR FGEDB USING NEWLOGPATH /db2log/fgedb;

# 查看配置生效情况
GET DATABASE CONFIGURATION FOR FGEDB;

# 重启数据库使配置生效
db2 force applications all
db2stop
db2start

# 配置日志路径
UPDATE DATABASE CONFIGURATION FOR FGEDB USING NEWLOGPATH /db2log/fgedb;

# 配置镜像日志路径
UPDATE DATABASE CONFIGURATION FOR FGEDB USING MIRRORLOGPATH /db2logmirror/fgedb;

# 启用日志镜像
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGARCHMETH1 DISK:/db2archive/fgedb;
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGARCHMETH2 OFF;

# 启用归档日志模式
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGARCHMETH1 DISK:/db2archive/fgedb;

# 配置归档到TSM
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGARCHMETH1 TSM;

# 配置归档到VENDOR
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGARCHMETH1 VENDOR:/vendor/lib/vendor.so;

# 配置归档压缩
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGARCHCOMPR1 ON;
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGARCHCOMPR2 OFF;

# 配置归档选项
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGARCHOPT1 ‘COMPRESS=YES’;

# 查看归档日志路径
LIST HISTORY ARCHIVE ALL FOR FGEDB;

# 配置日志保留策略
UPDATE DATABASE CONFIGURATION FOR FGEDB USING REC_HIS_RETENTN 90;

# 手动归档当前日志
ARCHIVE LOG FOR DATABASE FGEDB;

# 查看备份历史
LIST HISTORY BACKUP ALL FOR FGEDB;

# 查看归档历史
LIST HISTORY ARCHIVE ALL FOR FGEDB;

# 清理历史记录
PRUNE HISTORY 20260101 FOR FGEDB;
PRUNE HISTORY 20260101 AND DELETE FOR FGEDB;

# 清理归档日志
PRUNE LOGFILE PRIOR TO S0000010.LOG FOR FGEDB;

# 创建归档日志管理脚本
#!/bin/bash
# manage_archive_logs.sh

ARCHIVE_DIR=”/db2archive/fgedb”
RETENTION_DAYS=30
DATE=$(date +%Y%m%d)
LOG_FILE=”/db2logs/archive_log_${DATE}.log”

echo “=== 开始管理归档日志: $(date) ===” >> ${LOG_FILE}

db2 connect to FGEDB >> ${LOG_FILE} 2>&1

# 清理30天前的归档日志
find ${ARCHIVE_DIR} -name “*.LOG” -mtime +${RETENTION_DAYS} -delete >> ${LOG_FILE} 2>&1

# 清理历史记录
db2 “PRUNE HISTORY $(date -d “${RETENTION_DAYS} days ago” +%Y%m%d) FOR FGEDB” >> ${LOG_FILE} 2>&1

db2 connect reset >> ${LOG_FILE} 2>&1

echo “=== 归档日志管理完成: $(date) ===” >> ${LOG_FILE}

# crontab配置
# 0 3 * * * /db2scripts/manage_archive_logs.sh >> /db2logs/manage_archive_cron.log 2>&1

# 查看日志快照
SELECT
TOTAL_LOG_USED,
TOTAL_LOG_AVAILABLE,
SEC_LOG_USED_TOP,
APPL_ID_HOLDING_OLDEST_XACT,
LOG_READS,
LOG_WRITES
FROM SYSIBMADM.SNAPDB;

# 查看日志使用率
SELECT
ROUND(TOTAL_LOG_USED * 100.0 / (TOTAL_LOG_USED + TOTAL_LOG_AVAILABLE), 2) AS LOG_UTILIZATION_PERCENT
FROM SYSIBMADM.SNAPDB;

# 查看持有最旧事务的应用
SELECT
AGENT_ID,
SUBSTR(APPL_NAME, 1, 30) AS APPL_NAME,
SUBSTR(PRIMARY_AUTH_ID, 1, 20) AS AUTH_ID,
UOW_START_TIME,
UOW_LOG_SPACE_USED
FROM SYSIBMADM.APPLICATIONS
WHERE AGENT_ID = (SELECT APPL_ID_HOLDING_OLDEST_XACT FROM SYSIBMADM.SNAPDB);

# 查看日志文件状态
SELECT
SUBSTR(MEMBER, 1, 5) AS MEMBER,
SUBSTR(LOG_FILE, 1, 30) AS LOG_FILE,
LOG_FILE_STATE,
LOG_FILE_SIZE,
FIRST_LSO,
LAST_LSO
FROM TABLE(SNAP_GET_DB(”, -1)) AS DB,
TABLE(SNAP_GET_LOG(”, -1)) AS LOG;

# 查看数据库管理器日志
db2diag -time 1h

# 查看通知日志
db2 list history since $(date -d ‘1 hour ago’ +%Y%m%d%H%M%S) for FGEDB

# 2. 归档当前日志
ARCHIVE LOG FOR DATABASE FGEDB;

# 3. 找出持有旧事务的应用并断开
FORCE APPLICATION (12345);

# 4. 增大日志文件大小
UPDATE DATABASE CONFIGURATION FOR FGEDB USING LOGFILSIZ 32768;

# 日志切换失败
# 检查日志路径权限
ls -ld /db2log/fgedb
chown -R db2inst1:db2iadm1 /db2log

# 检查磁盘空间
df -h /db2log

# 归档失败
# 检查归档路径
ls -ld /db2archive/fgedb
chown -R db2inst1:db2iadm1 /db2archive

# 检查磁盘空间
df -h /db2archive

# 手动归档
ARCHIVE LOG FOR DATABASE FGEDB;

# 日志损坏
# 1. 从归档恢复
# 2. 从备份恢复数据库
RESTORE DATABASE FGEDB FROM /db2backup TAKEN AT 20260401000000;
ROLLFORWARD DATABASE FGEDB TO END OF LOGS AND COMPLETE;