# 不同环境使用不同服务器
# 开发环境
DEV_DB2_HOST=dev-db2.example.com
DEV_DB2_PORT=50000
DEV_DB2_DB=FGEDB_DEV

# 测试环境
TEST_DB2_HOST=test-db2.example.com
TEST_DB2_PORT=50000
TEST_DB2_DB=FGEDB_TEST

# 预生产环境
STAGE_DB2_HOST=stage-db2.example.com
STAGE_DB2_PORT=50000
STAGE_DB2_DB=FGEDB_STAGE

# 生产环境
PROD_DB2_HOST=prod-db2.example.com
PROD_DB2_PORT=50000
PROD_DB2_DB=FGEDB_PROD

# 连接脚本
#!/bin/bash
# connect_db2.sh

ENV=$1

case $ENV in
dev)
DB_HOST=$DEV_DB2_HOST
DB_PORT=$DEV_DB2_PORT
DB_NAME=$DEV_DB2_DB
;;
test)
DB_HOST=$TEST_DB2_HOST
DB_PORT=$TEST_DB2_PORT
DB_NAME=$TEST_DB2_DB
;;
stage)
DB_HOST=$STAGE_DB2_HOST
DB_PORT=$STAGE_DB2_PORT
DB_NAME=$STAGE_DB2_DB
;;
prod)
DB_HOST=$PROD_DB2_HOST
DB_PORT=$PROD_DB2_PORT
DB_NAME=$PROD_DB2_DB
;;
*)
echo “Usage: $0 {dev|test|stage|prod}”
exit 1
;;
esac

db2 connect to $DB_NAME host $DB_HOST port $DB_PORT user db2inst1 using password

# 同一实例不同数据库
# 开发环境
CREATE DATABASE FGEDB_DEV;

# 测试环境
CREATE DATABASE FGEDB_TEST;

# 预生产环境
CREATE DATABASE FGEDB_STAGE;

# 生产环境
CREATE DATABASE FGEDB_PROD;

# 不同Schema隔离
# 开发环境
CREATE SCHEMA DEV;

# 测试环境
CREATE SCHEMA TEST;

# 预生产环境
CREATE SCHEMA STAGE;

# 生产环境
CREATE SCHEMA PROD;

# 创建环境标识表
CREATE TABLE ENVIRONMENT_INFO (
ENV_NAME VARCHAR(20) NOT NULL,
ENV_TYPE VARCHAR(20) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
DESCRIPTION VARCHAR(200)
);

INSERT INTO ENVIRONMENT_INFO VALUES
(‘DEV’, ‘DEVELOPMENT’, CURRENT TIMESTAMP, ‘Development Environment’),
(‘TEST’, ‘TESTING’, CURRENT TIMESTAMP, ‘Testing Environment’),
(‘STAGE’, ‘STAGING’, CURRENT TIMESTAMP, ‘Staging Environment’),
(‘PROD’, ‘PRODUCTION’, CURRENT TIMESTAMP, ‘Production Environment’);

# 环境配置文件
# dev.conf
DB_NAME=FGEDB_DEV
DB_HOST=dev-db2.example.com
DB_PORT=50000
DB_USER=dev_user
SORTHEAP=4096
LOCKLIST=2048
MAXLOCKS=10

# test.conf
DB_NAME=FGEDB_TEST
DB_HOST=test-db2.example.com
DB_PORT=50000
DB_USER=test_user
SORTHEAP=8192
LOCKLIST=4096
MAXLOCKS=20

# prod.conf
DB_NAME=FGEDB_PROD
DB_HOST=prod-db2.example.com
DB_PORT=50000
DB_USER=prod_user
SORTHEAP=16384
LOCKLIST=8192
MAXLOCKS=30

# 应用配置脚本
#!/bin/bash
# apply_config.sh

ENV=$1
CONFIG_FILE=”/db2/config/${ENV}.conf”

if [ ! -f “$CONFIG_FILE” ]; then
echo “Config file not found: $CONFIG_FILE”
exit 1
fi

source $CONFIG_FILE

db2 connect to $DB_NAME

db2 “UPDATE DATABASE CONFIGURATION USING SORTHEAP $SORTHEAP IMMEDIATE”
db2 “UPDATE DATABASE CONFIGURATION USING LOCKLIST $LOCKLIST IMMEDIATE”
db2 “UPDATE DATABASE CONFIGURATION USING MAXLOCKS $MAXLOCKS IMMEDIATE”

db2 connect reset

echo “Configuration applied for $ENV environment”

# 使用Git管理配置
# 初始化Git仓库
cd /db2/config
git init
git add .
git commit -m “Initial commit”

# 创建分支
git checkout -b dev
git checkout -b test
git checkout -b prod

# 修改配置
vim dev.conf
git add dev.conf
git commit -m “Update dev config”

# 合并配置
git checkout prod
git merge test

# 创建标签
git tag -a v1.0 -m “Release version 1.0”

# 查看历史
git log –oneline

# 回滚配置
git checkout v0.9

# 使用配置模板
# db.conf.template
DB_NAME=${DB_NAME}
DB_HOST=${DB_HOST}
DB_PORT=${DB_PORT}
DB_USER=${DB_USER}
SORTHEAP=${SORTHEAP}
LOCKLIST=${LOCKLIST}
MAXLOCKS=${MAXLOCKS}

# 生成配置文件
envsubst < db.conf.template > dev.conf

# 从开发环境迁移到测试环境
# 1. 导出开发环境数据
db2 connect to FGEDB_DEV
db2 export to /backup/dev_orders.ixf of ixf select * from orders
db2 export to /backup/dev_customer.ixf of ixf select * from customer
db2 connect reset

# 2. 导入到测试环境
db2 connect to FGEDB_TEST
db2 import from /backup/dev_orders.ixf of ixf insert into orders
db2 import from /backup/dev_customer.ixf of ixf insert into customer
db2 connect reset

# 3. 使用db2move批量迁移
db2move FGEDB_DEV export
db2move FGEDB_TEST import

# 使用脚本自动化迁移
#!/bin/bash
# migrate_env.sh

SOURCE_ENV=$1
TARGET_ENV=$2

SOURCE_DB=”FGEDB_${SOURCE_ENV^^}”
TARGET_DB=”FGEDB_${TARGET_ENV^^}”

echo “Migrating from $SOURCE_DB to $TARGET_DB”

# 导出源环境数据
db2 connect to $SOURCE_DB
db2move $SOURCE_DB export
db2 connect reset

# 导入到目标环境
db2 connect to $TARGET_DB
db2move $TARGET_DB import
db2 connect reset

echo “Migration completed”

# 导出配置
db2 connect to FGEDB_DEV
db2 “export to /backup/dev_config.del of del
select * from syscat.tables where tabschema=’FGEDB'”

# 导入配置
db2 connect to FGEDB_TEST
db2 “import from /backup/dev_config.del of del insert into config_table”

# 比较环境差异
#!/bin/bash
# compare_env.sh

ENV1=$1
ENV2=$2

DB1=”FGEDB_${ENV1^^}”
DB2=”FGEDB_${ENV2^^}”

# 比较表结构
db2 connect to $DB1
db2 “export to /tmp/${ENV1}_tables.del of del
select tabname from syscat.tables where tabschema=’FGEDB'”
db2 connect reset

db2 connect to $DB2
db2 “export to /tmp/${ENV2}_tables.del of del
select tabname from syscat.tables where tabschema=’FGEDB'”
db2 connect reset

# 找出差异
diff /tmp/${ENV1}_tables.del /tmp/${ENV2}_tables.del

# 同步配置
#!/bin/bash
# sync_config.sh

SOURCE_ENV=$1
TARGET_ENV=$2

SOURCE_DB=”FGEDB_${SOURCE_ENV^^}”
TARGET_DB=”FGEDB_${TARGET_ENV^^}”

# 导出源环境配置
db2 connect to $SOURCE_DB
db2 “export to /tmp/config.del of del
select * from sysibm.sysdbauth”
db2 connect reset

# 导入到目标环境
db2 connect to $TARGET_DB
db2 “import from /tmp/config.del of del insert into sysibm.sysdbauth”
db2 connect reset