shell中执行db2数据库命令_DB2命令 （shell脚本自动执行）

//deploy

INSERT INTO GDAPP.USER(ID,NAME,AGE) VALUES('001','JMY','13');//执行插入数据操作

ALTER TABLE GDAPP.USER ADD COLUMN DELETE_DATE TIMESTAMP;

ALTER TABLE GDAPP.USER ADD COLUMN ACTIVE VARCHAR(1024);

ALTER TABLE GDAPP.USER ADD COLUMN ATTACHMENT BLOB(10485760);//新增列，附件10M

//rollback

DELETE FROM GDAPP.USER WHERE ID='001';//删除

ALTER TABLE GDAPP.USER DROP COLUMN DELETE_DATE ;

ALTER TABLE GDAPP.USER DROP COLUMN ACTIVE;

ALTER TABLE GDAPP.USER DROP COLUMN ATTACHMENT;//删除列

//validate

SELECT '1.OK.NEW DATA HAS BEEN ADDED INTO GDAPP.USER TABLE.'

FROM sysibm.sysdummy1

WHERE EXISTS(SELECT * FROM GDAPP.USER WHERE ID='001')

UNION

SELECT '1.ERROR.NEW DATA HAS NOT BEEN ADDED INTO GDAPP.USER TABLE.'

FROM sysibm.sysdummy1

WHERE NOT EXISTS(SELECT * FROM GDAPP.USER WHERE ID='001');//插入操作验证

SELECT '1.OK.ATTACHMENT COLUMN HAS BEEN ADDED INTO GDAPP.USER TABLE.'

FROM sysibm.sysdummy1

WHERE EXISTS(select 1 from syscat.columns where colname='ATTACHMENT' and tabname='USER' and tabschema='GDAPP')

UNION

SELECT '1.ERROR.ATTACHMENT COLUMN HAS NOT BEEN ADDED INTO GDAPP.USER TABLE.'

FROM sysibm.sysdummy1

WHERE NOT EXISTS(select 1 from syscat.columns where colname='ATTACHMENT' and tabname='USER' and tabschema='GDAPP')

//插入列操作验证

//rollback_validate

SELECT '1.OK.NEW DATA HAS BEEN DELETED FROM GDAPP.USER TABLE.'

FROM sysibm.sysdummy1

WHERE NOT EXISTS(SELECT * FROM GDAPP.USER WHERE ID='001')

UNION

SELECT '2.ERROR.NEW DATA HAS NOT BEEN DELETED FROM GDAPP.USER TABLE.'

FROM sysibm.sysdummy1

WHERE EXISTS(SELECT * FROM GDAPP.USER WHERE ID='001');//插入操作回滚验证

SELECT '1.OK.ATTACHMENT COLUMN HAS BEEN DELETED FROM GDAPP.USER TABLE.'

FROM sysibm.sysdummy1

WHERE NOT EXISTS(select 1 from syscat.columns where colname='ATTACHMENT' and tabname='USER' and tabschema='GDAPP')

UNION

SELECT '1.ERROR.ATTACHMENT COLUMN HAS NOT BEEN DELETED FROM GDAPP.USER TABLE.'

FROM sysibm.sysdummy1

WHERE EXISTS(select 1 from syscat.columns where colname='ATTACHMENT' and tabname='USER' and tabschema='GDAPP')

//插入列回滚操作验证

shell脚本自动执行(发布)sql pref下

#！/bin/bash

#install_01

db2 'connect to gddb user "jmy" using "123456"'

curDir=$(cd $(dirname $0)/..; pwd)

echo "current dir >>>>" ${curDir}

cd ${curDir}/sql/

db2 -tvf sql_deploy.sql

db2 reorg table GDAPP.USER

#init_02

echo "init finished"

#checking_03

db2 'connect to gddb user "jmy" using "123456"'

curDir=$(cd $(dirname $0)/..; pwd)

echo "current dir >>>>" ${curDir}

cd ${curDir}/sql/

db2 -tvf sql_validate.sql

shell脚本自动执行(回滚)sql pref下

#！/bin/bash

#install_11

db2 'connect to gddb user "jmy" using "123456"'

curDir=$(cd $(dirname $0)/..; pwd)

echo "current dir >>>>" ${curDir}

cd ${curDir}/sql/

db2 -tvf sql_rollback.sql

db2 reorg table GDAPP.USER

#init_12

echo "init finished"

#checking_13

db2 'connect to gddb user "jmy" using "123456"'

curDir=$(cd $(dirname $0)/..; pwd)

echo "current dir >>>>" ${curDir}

cd ${curDir}/sql/

db2 -tvf sql_rollback_validate.sql

自动执行sh脚本(发布)

#！/bin/sh

#install

ROOT_HOME=$(cd $(dirname $0)/..; pwd)

outlog=$ROOT_HOME/log/install.log

echo "sh $ROOT_HOME/normal/install.sh" >> ${outlog}

sh $ROOT_HOME/GDDB01/perf/01_install.sh |tee -a ${outlog}

#init

ROOT_HOME=$(cd $(dirname $0)/..; pwd)

outlog=$ROOT_HOME/log/init.log

echo "sh $ROOT_HOME/normal/init.sh" >> ${outlog}

sh $ROOT_HOME/GDDB01/perf/02_init.sh |tee -a ${outlog}

#check

ROOT_HOME=$(cd $(dirname $0)/..; pwd)

outlog=$ROOT_HOME/log/checking.log

echo "sh $ROOT_HOME/normal/checking.sh" >> ${outlog}

sh $ROOT_HOME/GDDB01/perf/03_check.sh |tee -a ${outlog}

自动执行sh脚本(回滚)

#！/bin/sh

#install

ROOT_HOME=$(cd $(dirname $0)/..; pwd)

outlog=$ROOT_HOME/log/rollback_install.log

echo "sh $ROOT_HOME/normal/rollback_install.sh" >> ${outlog}

sh $ROOT_HOME/GDDB01/perf/11_install.sh |tee -a ${outlog}

#init

ROOT_HOME=$(cd $(dirname $0)/..; pwd)

outlog=$ROOT_HOME/log/rollback_init.log

echo "sh $ROOT_HOME/normal/rollback_init.sh" >> ${outlog}

sh $ROOT_HOME/GDDB01/perf/12_init.sh |tee -a ${outlog}

#check

ROOT_HOME=$(cd $(dirname $0)/..; pwd)

outlog=$ROOT_HOME/log/rollback_checking.log

echo "sh $ROOT_HOME/normal/rollback_checking.sh" >> ${outlog}

sh $ROOT_HOME/GDDB01/perf/13_check.sh |tee -a ${outlog}