# 在线重组表并重建索引
REORG TABLE ORDERS INPLACE ALLOW WRITE ACCESS INDEX IDX_ORDERS;

# 在线重组表并指定临时表空间
REORG TABLE ORDERS INPLACE ALLOW WRITE ACCESS USE TEMPSPACE1;

# 查看重组进度
db2pd -db FGEDB -reorgs

# 暂停重组
REORG TABLE ORDERS INPLACE PAUSE;

# 恢复重组
REORG TABLE ORDERS INPLACE RESUME;

# 终止重组
REORG TABLE ORDERS INPLACE STOP;

# 离线重组表
REORG TABLE ORDERS;

# 重组索引
REORG INDEXES ALL FOR TABLE ORDERS;

DBNAME=FGEDB
TABLE_NAME=$1

if [ -z “$TABLE_NAME” ]; then
echo “Usage: $0 ”
exit 1
fi

echo “Starting online reorg for table: $TABLE_NAME”
echo “Time: $(date)”

db2 connect to $DBNAME

# 检查表是否需要重组
FRAG_PERCENT=$(db2 “SELECT REORG_ROWS FROM SYSIBMADM.ADMINTABINFO WHERE TABNAME=’$TABLE_NAME'” | tail -1 | awk ‘{print $1}’)

if [ -z “$FRAG_PERCENT” ] || [ “$FRAG_PERCENT” -lt 10 ]; then
echo “Table fragmentation is low, no reorg needed”
db2 connect reset
exit 0
fi

echo “Table fragmentation: $FRAG_PERCENT%”

# 执行在线重组
db2 “REORG TABLE $TABLE_NAME INPLACE ALLOW WRITE ACCESS”

# 检查重组状态
while true; do
STATUS=$(db2pd -db $DBNAME -reorgs | grep $TABLE_NAME | awk ‘{print $3}’)

if [ “$STATUS” = “COMPLETED” ]; then
echo “Reorg completed successfully”
break
elif [ “$STATUS” = “FAILED” ]; then
echo “Reorg failed”
break
else
echo “Reorg in progress: $STATUS”
sleep 60
fi
done

# 更新统计信息
db2 “RUNSTATS ON TABLE $TABLE_NAME WITH DISTRIBUTION AND INDEXES ALL”

db2 connect reset

echo “Online reorg completed”

# 在线增量备份
BACKUP DATABASE FGEDB ONLINE INCREMENTAL TO /db2backup COMPRESS;

# 在线表空间备份
BACKUP DATABASE FGEDB TABLESPACE USERSPACE1 ONLINE TO /db2backup;

# 在线备份到多个路径
BACKUP DATABASE FGEDB ONLINE TO /db2backup1, /db2backup2 COMPRESS;

# 在线备份并包括日志
BACKUP DATABASE FGEDB ONLINE TO /db2backup INCLUDE LOGS;

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

# 查看备份状态
db2pd -db FGEDB -backup

DBNAME=FGEDB
BACKUP_DIR=/db2backup
DATE=$(date +%Y%m%d)
LOG_FILE=/db2/logs/backup_$DATE.log

echo “=== Online Backup ===” > $LOG_FILE
echo “Date: $(date)” >> $LOG_FILE

db2 connect to $DBNAME

# 检查数据库是否启用了归档日志
LOG_MODE=$(db2 “SELECT LOGARCHMETH1 FROM SYSIBMADM.DBMCFG” | tail -1 | awk ‘{print $1}’)

if [ “$LOG_MODE” = “OFF” ]; then
echo “Database is in circular logging mode, online backup not supported” >> $LOG_FILE
db2 connect reset
exit 1
fi

# 执行在线备份
echo “Starting online backup…” >> $LOG_FILE
db2 “BACKUP DATABASE $DBNAME ONLINE TO $BACKUP_DIR COMPRESS INCLUDE LOGS” >> $LOG_FILE 2>&1

if [ $? -eq 0 ]; then
echo “Backup completed successfully” >> $LOG_FILE

# 验证备份
BACKUP_FILE=$(ls -t $BACKUP_DIR/*.0 | head -1)
echo “Verifying backup: $BACKUP_FILE” >> $LOG_FILE
db2ckbkp $BACKUP_FILE >> $LOG_FILE 2>&1
else
echo “Backup failed” >> $LOG_FILE
fi

db2 connect reset

# 清理旧备份（保留7天）
find $BACKUP_DIR -name “*.0” -mtime +7 -exec rm -f {} \;

echo “Backup process completed” >> $LOG_FILE

# 在线收集表统计信息
RUNSTATS ON TABLE ORDERS ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS;

# 在线收集索引统计信息
RUNSTATS ON TABLE ORDERS FOR INDEX IDX_ORDERS;

# 查看统计信息收集进度
db2pd -db FGEDB -runstats

# 自动统计信息收集脚本
#!/bin/bash
# auto_runstats.sh

DBNAME=FGEDB

db2 connect to $DBNAME

# 查找需要更新统计信息的表
db2 “SELECT TABNAME, CARD, STATS_TIME
FROM SYSCAT.TABLES
WHERE TABSCHEMA=’FGEDB’
AND (STATS_TIME IS NULL OR STATS_TIME < CURRENT TIMESTAMP - 7 DAYS) ORDER BY TABNAME" > /tmp/runstats_list.txt

# 对每个表执行统计信息收集
while read TABNAME CARD STATS_TIME; do
echo “Updating statistics for table: $TABNAME”
db2 “RUNSTATS ON TABLE FGEDB.$TABNAME WITH DISTRIBUTION AND INDEXES ALL”
done < /tmp/runstats_list.txt db2 connect reset

# 在线修改列
ALTER TABLE ORDERS ALTER COLUMN ORDER_AMOUNT SET DATA TYPE DECIMAL(20, 2);

# 在线添加索引
CREATE INDEX IDX_ORDERS_CUSTOMER ON ORDERS(CUSTOMER_ID);

# 在线删除索引
DROP INDEX IDX_ORDERS_OLD;

# 在线添加约束
ALTER TABLE ORDERS ADD CONSTRAINT CHK_ORDER_AMOUNT CHECK (ORDER_AMOUNT > 0);

# 在线修改表空间
ALTER TABLE ORDERS MOVE TO NEWTABLESPACE;

# 在线表维护脚本
#!/bin/bash
# online_table_maintenance.sh

DBNAME=FGEDB
TABLE_NAME=$1
OPERATION=$2

db2 connect to $DBNAME

case $OPERATION in
add_column)
db2 “ALTER TABLE $TABLE_NAME ADD COLUMN NEW_COLUMN VARCHAR(100)”
;;
modify_column)
db2 “ALTER TABLE $TABLE_NAME ALTER COLUMN OLD_COLUMN SET DATA TYPE VARCHAR(200)”
;;
add_index)
db2 “CREATE INDEX IDX_${TABLE_NAME}_NEW ON $TABLE_NAME(NEW_COLUMN)”
;;
drop_index)
db2 “DROP INDEX IDX_${TABLE_NAME}_OLD”
;;
add_constraint)
db2 “ALTER TABLE $TABLE_NAME ADD CONSTRAINT CHK_NEW CHECK (NEW_COLUMN IS NOT NULL)”
;;
*)
echo “Unknown operation: $OPERATION”
;;
esac

db2 connect reset