DB2安裝部署以及應用部署配置操作

前言：年底了，公司需要部署一套DB2環境+應用系統來對客戶進行相關的功能模塊演示操作，之前對DB2接觸不多，利用這次機會，開啟對DB2的探索學習之路。

DB2 數據庫部署

硬件環境信息：

機器IP：10.162.64.78

OS版本：RHEL 6.6

內存：256G

1.文件系統劃分

查看已經創建的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

創建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

創建目錄并掛載

# 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.修改系統內核參數

# vi /etc/sysctl.conf

kernel.shmmax = 137438953472    --設置為物理內存的一半或稍大

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.創建用戶

# 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.安裝數據庫

# 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.創建實例

修改/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

啟動實例：  --切換到db2inst1用戶

配置環境變量  --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.創建DB

$ db2 "create db RESDB on /oradata4 using codeset UTF-8 territory cn"

DB20000I  The CREATE DATABASE command completed successfully.

批注：創建數據庫時若為指定路徑，將使用默認路徑，數據庫用戶的家目錄，如下查看：

$ db2 get dbm cfg | grep -i DFTDBPATH

 Default database path                       (DFTDBPATH) = /home/db2inst1

9.參數調整

$ db2 connect to resdb

$ db2iauto -on db2inst1   --設置數據庫實例開機自啟動

9.1參數調整

$ 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.

批注：需要重啟數據庫生效

9.2日志調整

--日志位置和大小調整

$ 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其他參數調整

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

參數調整完畢，數據庫重啟：

$ 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.

此時需要調整/etc/security/limits.conf配置文件

db2list1  soft nofile 65536

db2inst1  hard nofile 65536

db2inst1  soft nproc  65536

db2inst1  hard nproc  65536

重新連接數據庫：

$ db2 connect to resdb

SQL1116N  A connection to or activation of database "RESDB" failed because the

database is in BACKUP PENDING state.  SQLSTATE=57019

批注：若修改數據里LOGRETAIN參數，從循環日志模式改為歸檔模式，則會導致數據庫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.

此時無法連接，需要對數據庫做離線全備，使狀態恢復正常，以便能夠訪問。

$ 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'

重啟數據庫

$ db2stop force

$ db2start

11.創建數據庫對象

$ 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.數據庫恢復

創建所需目錄：

$ mkdir -p /oradata4/db2

首先對備份文件進行分析：

$ 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  <username>

-- USING '<password>'

FROM '/oradata4/db2'

TAKEN AT 20170118202238

-- ON '/oradata4/db2'

-- DBPATH ON '<target-directory>'

INTO RESDB

-- NEWLOGPATH '/oradata4/db2/db2inst1/NODE0000/SQL00004/LOGSTREAM0000/'

-- WITH <num-buff> BUFFERS

-- BUFFER <buffer-size>

-- REPLACE HISTORY FILE

-- REPLACE EXISTING

REDIRECT

-- PARALLELISM <n>

WITHOUT ROLLING FORWARD

-- WITHOUT PROMPTING

;

-- SET STOGROUP PATHS FOR IBMSTOGROUP

-- ON '/oradata4/db2'

-- ;

RESTORE DATABASE RESLT4 CONTINUE;

批注：遠端備份的數據庫名為RESLT4，遠端備份路徑為/oradata4/db2,本地數據庫名為RESDB，本地需要先創建/oradata4/db2路徑。

執行恢復操作：

$ 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.

應用環境部署

13.WAS應用部署連接DB2數據庫

之前部署的WAS環境連接的都是Oracle庫，連接DB2的庫數據源配置還是第一次，找了些資料，將與配置Oracle不同的地方記錄如下：

13.1配置DB2環境變量   --本次WAS部署采用的是單機環境

控制臺界面中選擇Environment-->WebSphere-->variables-->作用域（選擇server1）

點擊"new"創建DB2UNIVERSAL_JDBC_DRIVER_PATH變量值為："/was/DB2lib"

批注：部署was應用的主機創建該目錄，并將所需jar包傳入到該路徑下，jar包位置/opt/ibm/db2/V10.5/java

13.2配置JDBC提供程序：

控制臺界面中選擇Resources-->JDBC-->JDBC providers-->選擇"server1"，點擊"New"

名稱：DB2 Universal JDBC Driver Provider

數據庫類型選擇"DB2"，提供程序類型選擇"DB2 Universal JDBC Driver Provider"，實施類型選擇"Connection pool data source"，點擊“Next”

class path設置：

/was/DB2lib/db2jcc.jar

/was/DB2lib/db2jcc_license_cu.jar

13.3創建J2C用戶

db2inst1/db2inst1

13.4.數據源配置

jdbcResTxDataSource          jdbc/ResTxDataSource  

jdbcoracleResTxDataSource      jdbc/oracle/ResTxDataSource

數據源連接測試

14.應用部署，測試驗證