本文是针对一个项目中DB Script分为多个*.sql文件,为了方便部署,则需要制作一个sh脚本文件,批量执行sql文件。
示例:
DBScript 目录包括4个文件
createDB.sh
createTables.sql
createIndex.sql
createViews.sql
createDB.sh内容如下:
echo "**************************************************"
echo "Create XXX DB"
echo "**************************************************"
db2 force application all
db2 drop db xxx
db2 create database xxx using codeset utf-8 territory us collate using system
db2 connect to xxx
db2 create schema xxx
db2 connect reset
db2 force application all
db2stop
db2start
echo "**************************************************"
echo "Create xxx Tables"
echo "**************************************************"
db2 -tvf ./createTables.sql
echo "**************************************************"
echo "Create xxx indexs"
echo "**************************************************"
db2 -tvf ./createIndex.sql
echo "**************************************************"
echo "Create xxx Views"
echo "**************************************************"
db2 -tvf ./createViews.sql
echo "**************************************************"
echo "Completed."
echo "**************************************************"
createTables.sql
CONNECT TO xxx;
------------------------------------------------
-- DROP Tables
------------------------------------------------
DROP TABLE xxx.USER;
DROP TABLE xxx.ROLE;
DROP TABLE xxx.DEPARTMENT;
------------------------------------------------
-- Table USER: User information table
------------------------------------------------
CREATE TABLE xxx.USER (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE NO CACHE),
LOGINID VARCHAR(40) NOT NULL,
PASSWORD VARCHAR(256) NOT NULL,
ROLEID INTEGER NOT NULL,
DEPTID INTEGER NOT NULL, --FK DEPARTMENT
NAME VARCHAR(40) NOT NULL,
EMAIL VARCHAR(40),
PHONE VARCHAR(30),
ADDRESS VARCHAR(200),
COMPANY VARCHAR(100),
REPOSITORY VARCHAR(5) NOT NULL, -- DB OR LDAP
DELETED CHARACTER(1) NOT NULL DEFAULT 'N', --Y N
CREATETIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATETIME TIMESTAMP
) DATA CAPTURE NONE;
ALTER TABLE xxx.USER ADD CONSTRAINT PK_USER PRIMARY KEY (ID);
ALTER TABLE xxx.USER ADD CONSTRAINT CHECK_USER_REPOSITORY CHECK (REPOSITORY IN ('DB','LDAP'));
ALTER TABLE xx.USER ADD CONSTRAINT CHECK_USER_DELETED CHECK (DELETED IN ('Y','N'));
别外两个SQL文件内容省略,都是标准和DDL语句.
将上面所有文件上传到Linux同一个目录下,给createDB.sh执行的权限
切换用户到db2inst1 su - db2inst1
进行sql脚本所在目录,执行 ./createDB.sh
即可。
在这里有一个需要注意的问题,就是sql文件的格式.
需要将sql文件由doc格式转换成unix格式.
操作方法:
1 进入SQL脚本所在目录
2 执行 dos2unix *