db2安装

####创建实例####

用户列表

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