####创建实例####
用户列表
1、创建用户组
groupadd ydxdinst
2、创建用户
useradd -d /home/ydxdinst -m -s ydxdinst
3、创建实例
cd /opt/IBM/db2/V9.7/instance
./db2icrt -u ydxdinst ydxdinst
建立数据库空间,并赋权给ydxdinst
mkdir /ydxddbspace
chown -R ydxdinst:ydxdinst /ydxddbspace/
4、用ydxdinst用户执行db2start启动实例
db2start
####数据库创建####
db2 "CREATE DATABASE ydxd AUTOMATIC STORAGE YES ON /ydxddbspace using CODESET UTF-8 TERRITORY CN COLLATE USING SYSTEM"
db2 connect to ydxd
--并行度 9;
db2 "update db cfg using DFT_DEGREE 9"
--重启ydxdinst实例使配置生效
db2 terminate
db2stop
db2start
缓冲池
db2 connect to ydxd
db2 "CREATE BUFFERPOOL BP_4K_1 IMMEDIATE SIZE 65536 PAGESIZE 4K";
db2 "CREATE BUFFERPOOL BP_4K_2 IMMEDIATE SIZE 262144 PAGESIZE 4K";
db2 "CREATE BUFFERPOOL BP_16K IMMEDIATE SIZE 262144 PAGESIZE 16K";
表空间设计
db2 "CREATE SYSTEM TEMPORARY TABLESPACE YDYXSYS_TEMP16 IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 16 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 OVERHEAD 10.5 TRANSFERRATE 0.14 BUFFERPOOL BP_16K";
db2 "CREATE SYSTEM TEMPORARY TABLESPACE YDYXSYS_TEMP4 IN DATABASE PARTITION GROUP IBMTEMPGROUP PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 OVERHEAD 10.5 TRANSFERRATE 0.14 BUFFERPOOL BP_4K_1";
db2 "CREATE USER TEMPORARY TABLESPACE YDYXUSER_TEMP4 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 OVERHEAD 10.5 TRANSFERRATE 0.14 BUFFERPOOL BP_4K_1";
db2 "CREATE LARGE TABLESPACE YDXD_MMC_DAT IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 OVERHEAD 10.5 TRANSFERRATE 0.14 BUFFERPOOL BP_4K_2";
db2 "CREATE LARGE TABLESPACE YDXD_MMC_IDX IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 OVERHEAD 10.5 TRANSFERRATE 0.14 BUFFERPOOL BP_4K_2";
db2 "CREATE LARGE TABLESPACE YDXD_MMP_DAT IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 OVERHEAD 10.5 TRANSFERRATE 0.14 BUFFERPOOL BP_4K_2";
db2 "CREATE LARGE TABLESPACE YDXD_MMP_IDX IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 OVERHEAD 10.5 TRANSFERRATE 0.14 BUFFERPOOL BP_4K_2";
db2 "CREATE LARGE TABLESPACE YDXD_LARGE_DAT IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 16 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 OVERHEAD 10.5 TRANSFERRATE 0.14 BUFFERPOOL BP_16K";
db2 "CREATE LARGE TABLESPACE YDXD_LARGE_IDX IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 16 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 OVERHEAD 10.5 TRANSFERRATE 0.14 BUFFERPOOL BP_16K";
db2 "CREATE LARGE TABLESPACE YDXD_HIS_DAT IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 OVERHEAD 10.5 TRANSFERRATE 0.14 BUFFERPOOL BP_4K_2";
db2 "CREATE LARGE TABLESPACE YDXD_HIS_IDX IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 64 PREFETCHSIZE 64 OVERHEAD 10.5 TRANSFERRATE 0.14 BUFFERPOOL BP_4K_2";
####设置服务及端口####
--设置服务端口、协议
db2 "update dbm cfg using SVCENAME DB2_ydxdinst"
db2set db2comm=tcpip
--并行性 YES
db2 "update dbm cfg using INTRA_PARALLEL yes"
--最大并行度 15
db2 "update dbm cfg using MAX_QUERYDEGREE 15"
db2set DB2COUNTRY=86
db2set DB2CODEPAGE=1208
db2set DB2TERRITORY=CN
db2set db2_parallel_io=*
####创建表结构,视图,函数####
切换到对应目录
db2 -td@ -f db2looke.sql | tee db2looke.log
db2 connect to ydxd
db2 -td@ -f VIEW.001.lob | tee view.log
db2 -td@ -f FUNCTION.001.lob | tee func.log
####初始化数据####
切换到对应目录
db2move ydxd load -lo replace
####定时任务####
将脚本相关目录放置在对应的目录下
0 4 * * * /home/ydxd/fft/fft.sh