前言：年底了，公司需要部署一套DB2環(huán)境+應(yīng)用系統(tǒng)來(lái)對(duì)客戶進(jìn)行相關(guān)的功能模塊演示操作，之前對(duì)DB2接觸不多，利用這次機(jī)會(huì)，開啟對(duì)DB2的探索學(xué)習(xí)之路。
十多年的宜黃網(wǎng)站建設(shè)經(jīng)驗(yàn)，針對(duì)設(shè)計(jì)、前端、開發(fā)、售后、文案、推廣等六對(duì)一服務(wù)，響應(yīng)快，48小時(shí)及時(shí)工作處理。成都全網(wǎng)營(yíng)銷推廣的優(yōu)勢(shì)是能夠根據(jù)用戶設(shè)備顯示端的尺寸不同，自動(dòng)調(diào)整宜黃建站的顯示方式，使網(wǎng)站能夠適用不同顯示終端，在瀏覽器中調(diào)整網(wǎng)站的寬度，無(wú)論在任何一種瀏覽器上瀏覽網(wǎng)站，都能展現(xiàn)優(yōu)雅布局與設(shè)計(jì)，從而大程度地提升瀏覽體驗(yàn)。創(chuàng)新互聯(lián)建站從事“宜黃網(wǎng)站設(shè)計(jì)”,“宜黃網(wǎng)站推廣”以來(lái)，每個(gè)客戶項(xiàng)目都認(rèn)真落實(shí)執(zhí)行。
DB2 數(shù)據(jù)庫(kù)部署
硬件環(huán)境信息：
機(jī)器IP：10.162.64.78
OS版本：RHEL 6.6
內(nèi)存：256G
1.文件系統(tǒng)劃分
查看已經(jīng)創(chuàng)建的PV，VG
# pvs
PV VG Fmt Attr PSize PFree
/dev/sda3 VolGroup00 lvm2 a-- 557.38g 379.38g
# vgs
VG #PV #LV #SN Attr VSize VFree
VolGroup00 1 2 0 wz--n- 557.38g 379.38g
創(chuàng)建LV
# lvcreate -L 20G -n lvdb2log VolGroup00
# lvcreate -L 30G -n lvdb2arch VolGroup00
# lvcreate -L 150G -n lvdb2data VolGroup00
格式化LV
# mkfs.ext4 /dev/VolGroup00/lvdb2log
# mkfs.ext4 /dev/VolGroup00/lvdb2arch
# mkfs.ext4 /dev/VolGroup00/lvdb2data
創(chuàng)建目錄并掛載
# mkdir -p /oradata4
# mkdir -p /db2arch
# mkdir -p /db2log
# mount /dev/VolGroup00/lvdb2log /db2log
# mount /dev/VolGroup00/lvdb2arch /db2arch
# mount /dev/VolGroup00/lvdb2data /oradata4
# tune2fs -c 0 -i 0 /dev/VolGroup00/lvdb2log
# tune2fs -c 0 -i 0 /dev/VolGroup00/lvdb2arch
# tune2fs -c 0 -i 0 /dev/VolGroup00/lvdb2data
# echo "/dev/VolGroup00/lvdb2log /db2log ext4 defaults 0 0" >> /etc/fstab
# echo "/dev/VolGroup00/lvdb2arch /db2arch ext4 defaults 0 0" >> /etc/fstab
# echo "/dev/VolGroup00/lvdb2data /oradata4 ext4 defaults 0 0" >> /etc/fstab
2.安裝所需軟件包
libstdc++.so.6
3.修改系統(tǒng)內(nèi)核參數(shù)
# vi /etc/sysctl.conf
kernel.shmmax = 137438953472 --設(shè)置為物理內(nèi)存的一半或稍大
kernel.shmall = 67108864
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default=262144
net.core.rmem_max=262144
net.core.wmem_default=262144
net.core.wmem_max=262144
4.創(chuàng)建用戶
# groupadd -g 1011 db2iadm1
# groupadd -g 1012 db2fadm1
# useradd -u 1016 -g db2iadm1 -d /home/db2inst1 -m db2inst1
# useradd -u 1017 -g db2fadm1 -d /home/db2fenc1 -m db2fenc1
# echo 'db2inst1' |passwd --stdin db2inst1
# echo 'db2fenc1' |passwd --stdin db2fenc1
# chown db2inst1:db2iadm1 /oradata4
# chown db2inst1:db2iadm1 /db2log
# chown db2inst1:db2iadm1 /db2arch
5.安裝數(shù)據(jù)庫(kù)
# tar zxvf v10.5fp7_linuxx64_universal_fixpack.tar.gz
# cd universal/
# ./db2_install
DBI1324W Support of the db2_install command is deprecated.
Default directory for installation of products - /opt/ibm/db2/V10.5
***********************************************************
Install into default directory (/opt/ibm/db2/V10.5) ? [yes/no]
yes
Specify one of the following keywords to install DB2 products.
SERVER
CONSV
EXP
Enter "help" to redisplay product names.
Enter "quit" to exit.
***********************************************************
SERVER
***********************************************************
Do you want to install the DB2 pureScale Feature? [yes/no]
no
DB2 installation is being initialized.
......
The execution completed successfully.
For more information see the DB2 installation log at
"/tmp/db2_install.log.25687".
6.查看并安裝license
# find / -name db2licm
/opt/ibm/db2/V10.5/adm/db2licm
# /opt/ibm/db2/V10.5/adm/db2licm -l
Product name: "DB2 Enterprise Server Edition"
License type: "License not registered"
Expiry date: "License not registered"
Product identifier: "db2ese"
Version information: "10.5"
# ./db2licm -a /install/isrs.lic
Product name: "DB2 Enterprise Server Edition"
License type: "CPU Option"
Expiry date: "Permanent"
Product identifier: "db2ese"
Version information: "10.5"
7.創(chuàng)建實(shí)例
修改/etc/hosts如下配置：
127.0.0.1 localhost
10.162.64.78 OSS-ZYGL-294
# /opt/ibm/db2/V10.5/instance/db2icrt -a server -p 9999 -u db2fenc1 db2inst1
DBI1446I The db2icrt command is running.
DB2 installation is being initialized.
......
The execution completed successfully.
For more information see the DB2 installation log at "/tmp/db2icrt.log.9284".
DBI1070I Program db2icrt completed successfully.
# /opt/ibm/db2/V10.5/instance/db2ilist
db2inst1
啟動(dòng)實(shí)例： --切換到db2inst1用戶
配置環(huán)境變量 --root用戶和db2inst1用戶
# vi ~/.bash_profile
$ vi ~/.bash_profile
PATH=$PATH:$HOME/bin:/opt/ibm/db2/V10.5/bin
# source ~/.bash_profile
$ source ~/.bash_profile
$ db2start
01/23/2017 13:36:37 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
8.創(chuàng)建DB
$ db2 "create db RESDB on /oradata4 using codeset UTF-8 territory cn"
DB20000I The CREATE DATABASE command completed successfully.
批注：創(chuàng)建數(shù)據(jù)庫(kù)時(shí)若為指定路徑，將使用默認(rèn)路徑，數(shù)據(jù)庫(kù)用戶的家目錄，如下查看：
$ db2 get dbm cfg | grep -i DFTDBPATH
Default database path (DFTDBPATH) = /home/db2inst1
9.參數(shù)調(diào)整
$ db2 connect to resdb
$ db2iauto -on db2inst1 --設(shè)置數(shù)據(jù)庫(kù)實(shí)例開機(jī)自啟動(dòng)
9.1參數(shù)調(diào)整
$ db2 update dbm cfg using SVCENAME 9999 DFT_MON_BUFPOOL on DFT_MON_UOW on NUM_POOLAGENTS 4100
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully.
SQL1362W One or more of the parameters submitted for immediate modification
were not changed dynamically. Client changes will not be effective until the
next time the application is started or the TERMINATE command has been issued.
Server changes will not be effective until the next DB2START command.
批注：需要重啟數(shù)據(jù)庫(kù)生效
9.2日志調(diào)整
--日志位置和大小調(diào)整
$ db2 update db cfg for RESDB using NEWLOGPATH /db2log
$ db2 update db cfg for RESDB using LOGARCHMETH1 disk:/db2arch
$ db2 update db cfg for RESDB using LOGFILSIZ 25600 LOGPRIMARY 13 LOGSECOND 12 CATALOGCACHE_SZ 2048 LOGBUFSZ 4096 UTIL_HEAP_SZ 10240 DB_MEM_THRESH 100 DLCHKTIME 5000 LOCKTIMEOUT 15
9.3其他參數(shù)調(diào)整
db2set DB2_PARALLEL_IO=*
db2set DB2_SKIPINSERTED=YES
db2set DB2_USE_ALTERNATE_PAGE_CLEANING=YES
db2set DB2_EVALUNCOMMITTED=YES
db2set DB2_ANTIJOIN=EXTEND
db2set DB2_SKIPDELETED=YES
db2set DB2CODEPAGE=1208
參數(shù)調(diào)整完畢，數(shù)據(jù)庫(kù)重啟：
$ db2stop
01/23/2017 13:48:24 0 0 SQL1025N The database manager was not stopped because databases are still active.
SQL1025N The database manager was not stopped because databases are still active.
$ db2 force application all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
$ db2stop
01/23/2017 13:49:39 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
$ db2start
01/23/2017 13:50:13 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
此時(shí)需要調(diào)整/etc/security/limits.conf配置文件
db2list1 soft nofile 65536
db2inst1 hard nofile 65536
db2inst1 soft nproc 65536
db2inst1 hard nproc 65536
重新連接數(shù)據(jù)庫(kù)：
$ db2 connect to resdb
SQL1116N A connection to or activation of database "RESDB" failed because the
database is in BACKUP PENDING state. SQLSTATE=57019
批注：若修改數(shù)據(jù)里L(fēng)OGRETAIN參數(shù)，從循環(huán)日志模式改為歸檔模式，則會(huì)導(dǎo)致數(shù)據(jù)庫(kù)BACKUP PENDING，如下處理
$ db2 update db cfg for resdb using LOGRETAIN RECOVERY
SQL1597N Configuring the DB2 environment failed because the specified DB2
configuration parameter is discontinued.
此時(shí)無(wú)法連接，需要對(duì)數(shù)據(jù)庫(kù)做離線全備，使?fàn)顟B(tài)恢復(fù)正常，以便能夠訪問(wèn)。
$ db2 backup db resdb to /dev/null
Backup successful. The timestamp for this backup p_w_picpath is : 20170123135258
$ db2 connect to resdb
10.打開ORACLE兼容模式
$ db2set DB2_COMPATIBILITY_VECTOR=ORA
$ db2set -all |grep 'DB2_COMPATIBILITY_VECTOR'
重啟數(shù)據(jù)庫(kù)
$ db2stop force
$ db2start
11.創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象
$ db2 "create bufferpool BP_DAT_32K size 16000 pagesize 32k"
$ db2 "create bufferpool BP_IDX_32K size 16000 pagesize 32k"
$ db2 "create bufferpool BP_TMP_32K size 16000 pagesize 32k"
$ db2 "CREATE LARGE TABLESPACE TBS_DAT_32k PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4 PREFETCHSIZE AUTOMATIC BUFFERPOOL BP_DAT_32K AUTORESIZE YES INCREASESIZE 1G NO FILE SYSTEM CACHING"
$ db2 "CREATE LARGE TABLESPACE TBS_IDX_32k PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 4 PREFETCHSIZE AUTOMATIC BUFFERPOOL BP_IDX_32K AUTORESIZE YES INCREASESIZE 1G NO FILE SYSTEM CACHING"
$ db2 "CREATE system TEMPORARY TABLESPACE TBS_SYSTMP_32K PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE BUFFERPOOL BP_TMP_32K NO FILE SYSTEM CACHING"
12.數(shù)據(jù)庫(kù)恢復(fù)
創(chuàng)建所需目錄：
$ mkdir -p /oradata4/db2
首先對(duì)備份文件進(jìn)行分析：
$ ls -al | grep -i RESLT4
-rw-r--r-- 1 db2inst1 db2iadm1 13223706624 Jan 19 12:42 RESLT4.0.db2inst1.DBPART000.20170118202238.001
生成restore.clp文件
$ db2 restore db RESLT4 taken at 20170118202238 redirect generate script restore.clp
DB20000I The RESTORE DATABASE command completed successfully.
修改restore.clp文件：
RESTORE DATABASE RESLT4
-- USER
-- USING '
FROM '/oradata4/db2'
TAKEN AT 20170118202238
-- ON '/oradata4/db2'
-- DBPATH ON '
INTO RESDB
-- NEWLOGPATH '/oradata4/db2/db2inst1/NODE0000/SQL00004/LOGSTREAM0000/'
-- WITH
-- BUFFER
-- REPLACE HISTORY FILE
-- REPLACE EXISTING
REDIRECT
-- PARALLELISM
WITHOUT ROLLING FORWARD
-- WITHOUT PROMPTING
;
-- SET STOGROUP PATHS FOR IBMSTOGROUP
-- ON '/oradata4/db2'
-- ;
RESTORE DATABASE RESLT4 CONTINUE;
批注：遠(yuǎn)端備份的數(shù)據(jù)庫(kù)名為RESLT4，遠(yuǎn)端備份路徑為/oradata4/db2,本地?cái)?shù)據(jù)庫(kù)名為RESDB，本地需要先創(chuàng)建/oradata4/db2路徑。
執(zhí)行恢復(fù)操作：
$ db2 -tvf restore.clp
UPDATE COMMAND OPTIONS USING S ON Z ON RESLT4_NODE0000.out V ON
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.
SET CLIENT ATTACH_MEMBER 0
DB20000I The SET CLIENT command completed successfully.
SET CLIENT CONNECT_MEMBER 0
DB20000I The SET CLIENT command completed successfully.
RESTORE DATABASE RESLT4 FROM '/oradata4/db2' TAKEN AT 20170118202238 INTO RESDB REDIRECT WITHOUT ROLLING FORWARD
SQL2529W Warning! Restoring to an existing database that is different from
the backup p_w_picpath database, and the alias name "RESDB" of the existing database
does not match the alias name "RESLT4" of the backup p_w_picpath, and the database
name "RESDB" of the existing database does not match the database name
"RESLT4" of the backup p_w_picpath. The target database will be overwritten by the
backup version. The Roll-forward recovery logs associated with the target
database will be deleted.
Do you want to continue ? (y/n)y
SQL1277W A redirected restore operation is being performed. During a table
space restore, only table spaces being restored can have their paths
reconfigured. During a database restore, storage group storage paths and DMS
table space containers can be reconfigured.
DB20000I The RESTORE DATABASE command completed successfully.
RESTORE DATABASE RESLT4 CONTINUE
DB20000I The RESTORE DATABASE command completed successfully.
應(yīng)用環(huán)境部署
13.WAS應(yīng)用部署連接DB2數(shù)據(jù)庫(kù)
之前部署的WAS環(huán)境連接的都是Oracle庫(kù)，連接DB2的庫(kù)數(shù)據(jù)源配置還是第一次，找了些資料，將與配置Oracle不同的地方記錄如下：
13.1配置DB2環(huán)境變量 --本次WAS部署采用的是單機(jī)環(huán)境
控制臺(tái)界面中選擇Environment-->WebSphere-->variables-->作用域（選擇server1）
點(diǎn)擊"new"創(chuàng)建DB2UNIVERSAL_JDBC_DRIVER_PATH變量值為："/was/DB2lib"
批注：部署was應(yīng)用的主機(jī)創(chuàng)建該目錄，并將所需jar包傳入到該路徑下，jar包位置/opt/ibm/db2/V10.5/java
13.2配置JDBC提供程序：
控制臺(tái)界面中選擇Resources-->JDBC-->JDBC providers-->選擇"server1"，點(diǎn)擊"New"
名稱：DB2 Universal JDBC Driver Provider
數(shù)據(jù)庫(kù)類型選擇"DB2"，提供程序類型選擇"DB2 Universal JDBC Driver Provider"，實(shí)施類型選擇"Connection pool data source"，點(diǎn)擊“Next”
class path設(shè)置：
/was/DB2lib/db2jcc.jar
/was/DB2lib/db2jcc_license_cu.jar
13.3創(chuàng)建J2C用戶
db2inst1/db2inst1
13.4.數(shù)據(jù)源配置
jdbcResTxDataSource jdbc/ResTxDataSource
jdbcoracleResTxDataSource jdbc/oracle/ResTxDataSource
數(shù)據(jù)源連接測(cè)試
14.應(yīng)用部署，測(cè)試驗(yàn)證
重慶分公司
重慶分公司
