DB2自动DETACH分区，并归档数据至历史表（AIX）

#!/bin/ksh
# db2detach_archive.sh
# Execute on DB2 server to automatically DETACH partitions and archive to corresponding monthly EV_COMBINED_MM tables

DBNAME="central"
USER="db2inst1"
PASSWD="mics@db2"

# Set up logging - using the specified directory
LOG_DIR="/db2fs/log"
LOG_FILE="${LOG_DIR}/db2detach_archive_$(date +%Y%m%d_%H%M%S).log"

# Function to log messages with timestamp
log_message() {
timestamp=$(date '+%Y-%m-%d %H:%M:%S')
echo "$timestamp - $1"
echo "$timestamp - $1" >> "$LOG_FILE"
}

# Start logging
log_message "Starting DB2 partition archiving process"

# Connect to database
db2 connect to $DBNAME user $USER using $PASSWD >> "$LOG_FILE" 2>&1
if [ $? -ne 0 ]; then
log_message "ERROR: Failed to connect to database $DBNAME"
exit 1
fi

# Get partition names from 3 months ago
log_message "Retrieving partition list from SYSCAT.DATAPARTITIONS"
partitions=$(db2 -x "SELECT DATAPARTITIONNAME
FROM SYSCAT.DATAPARTITIONS
WHERE DATAPARTITIONNAME <> 'PART0'
AND TABNAME = 'EV_COMBINED'
AND CARD > 0
AND SUBSTR(HIGHVALUE,2,10) < ADD_MONTHS(CURRENT DATE, -2)
ORDER BY DATAPARTITIONNAME")

log_message "Found partitions to process: $partitions"

# Check if any partitions were found
if [ -z "$partitions" ]; then
log_message "No partitions found for archiving"
db2 connect reset >> "$LOG_FILE" 2>&1
exit 0
fi

# Loop through each partition
for part in $partitions; do
log_message "Processing partition: $part"

# Example: P202506 -> 202506
ym=${part#P} # Remove prefix P

# Use awk for string extraction in AIX-compatible way
year=$(echo "$ym" | awk '{print substr($0, 1, 4)}')
month=$(echo "$ym" | awk '{print substr($0, 5, 2)}')

src_table="EV_COMBINED_TEMP_${ym}"
archive_table="EV_COMBINED_${month}" # Archive table by month

log_message "DETACH partition $part to table $src_table ..."
db2 "ALTER TABLE EV_COMBINED DETACH PARTITION $part INTO $src_table" >> "$LOG_FILE" 2>&1
if [ $? -ne 0 ]; then
log_message "ERROR: DETACH failed, skipping $part"
continue
fi

log_message "EXPORT data to file ${src_table}.del ..."
db2 "export to ${src_table}.del of del messages ${src_table}.log select * from $src_table" >> "$LOG_FILE" 2>&1
if [ $? -ne 0 ]; then
log_message "ERROR: EXPORT failed, skipping $part"
continue
fi

log_message "LOAD data to archive table $archive_table ..."
db2 "load from ${src_table}.del of del messages ${archive_table}.log insert into $archive_table nonrecoverable" >> "$LOG_FILE" 2>&1
if [ $? -ne 0 ]; then
log_message "ERROR: LOAD failed, keeping temporary table $src_table for manual inspection"
continue
fi

log_message "LOAD successful, DROP temporary table $src_table ..."
db2 "drop table $src_table" >> "$LOG_FILE" 2>&1
if [ $? -ne 0 ]; then
log_message "ERROR: DROP temporary table failed, please manually handle $src_table"
continue
fi

log_message "Completed archiving partition $part → $archive_table"
done

# Disconnect from database
db2 connect reset >> "$LOG_FILE" 2>&1

log_message "DB2 partition archiving process completed"
log_message "Log file saved at: $LOG_FILE"