IBM DB2 Database

Install: DB2 v9.7 on CentOS

系統環境
安裝前步驟

準備安裝檔:

  1. DB2_AESE_97_Linux_x86-64.tar.gz,DB2 主程式,必須由支援廠商提供。
  2. v9.7fp11_linuxx64_universal_fixpack.tar.gz,DB2 fixpack,可從官網下載。
     

CentOS 安裝模式:Minimal Desktop

需要的其它套件:

NOTE:

- libstdc++ 需要 32bit 與 64bit 都安裝。

- 需要的 32bit 的套件都應該在原始安裝來源目錄內,如果找不到,可能少了 DVD2 的安裝來源。

- CentOS 7 的 32bit 套件包含在 CentOS-7.?-x86_64-Everything-??? ISO 檔內。

CentOS 6, 7)

# yum install libaio ksh rsh-server pam-devel nfs-utils compat-libstdc++-33
# yum install libstdc++.i686 pam-devel.i686 compat-libstdc++-33.i686

# rpm -qa | grep "ksh\|openssh\|rsh-server\|nfs-utils\|libaio\|libstdc++\|libstdc++.i686\|pam-devel\|pam-devel.i686\|compat-libstdc++"
libstdc++-4.4.7-16.el6.x86_64
nfs-utils-lib-1.1.5-11.el6.x86_64
openssh-clients-5.3p1-111.el6.x86_64
pam-devel-1.1.1-20.el6.i686
libaio-0.3.107-10.el6.x86_64
ksh-20120801-28.el6.x86_64
libstdc++-4.4.7-16.el6.i686
openssh-server-5.3p1-111.el6.x86_64
pam-devel-1.1.1-20.el6.x86_64
nfs-utils-1.2.3-64.el6.x86_64
openssh-5.3p1-111.el6.x86_64
rsh-server-0.17-64.el6.x86_64

CentOS 5.11)

#> yum install pdksh nfs-utils rsh-server compat-libstdc++-296.i386 compat-libstdc++-33.i386 compat-libstdc++-33.x86_64 openssh

CentOS 系統調校:

關閉 SELinux
#> setenforce 0
#> sed -i 's/SELINUX=.*$/SELINUX=disabled/' /etc/selinux/config  

停用 NetworkManager
#> service NetworkManager stop
#> chkconfig NetworkManager off
安裝開始

主程式安裝

tar xzf DB2_AESE_97_Linux_x86-64.tar.gz
cd aese/
./db2prereqcheck
./db2_install -f NOTSAMP

-f NOTSAMP : 不要安裝 SAMP (System Automation for Multiplatforms),因為 CentOS 不支援這功能。

Default directory for installation of products - /opt/ibm/db2/V9.7

***********************************************************
Do you want to choose a different directory to install [yes/no] ?
no

Specify one of the following keywords to install DB2 products.

  AESE
  CLIENT
  RTCL

Enter "help" to redisplay product names.

Enter "quit" to exit.

***********************************************************
AESE

更新檔 fixpack 安裝
檔案名稱: v9.7fp11_linuxx64_universal_fixpack.tar.gz

tar xzf v9.7fp11_linuxx64_universal_fixpack.tar.gz
cd universal/
./db2prereqcheck

如果出現錯誤:
- DBT3534W  The db2prereqcheck utility determined that ASLR is set to ON and that this could cause issues with some tools.

解決: 編輯 /etc/sysctl.conf, 加上這一行
kernel.randomize_va_space = 0
執行  echo 0 > /proc/sys/kernel/randomize_va_space

- WARNING:
   The 32-bit library file libpam.so is not found on the system.

解決: 安裝這些套件
yum install pam-devel pam-devel.i686

- WARNING:
   The 32 bit library file libstdc++.so.5 is not found on the system.

解決:yum install compat-libstdc++-33.i686

./installFixPack

Enter full path name for the install directory -
------------------------------------------------
/opt/ibm/db2/V9.7 <== 輸入 DB2 主程式安裝目錄

錯誤訊息:

ERROR: The installFixPack command is not able to detect if there are any DB2
libraries still loaded in memory in the current installation copy. Unload all
DB2 libraries and re-issue the installFixPack command with the '-f db2lib'
parameter. Note: All DB2 libraries must be unloaded before re-issuing the
installFixPack command with the '-f db2lib' parameter, otherwise, some
applications might not work properly and might need to be restarted to function
properly against the updated DB2 instance.

Solution: Reboot the host, alternatively, manually clean up the below processes and rerun the db2prechk command to verify that it returns 0.

For Linux)

lsof +D /opt/ibm/db2/V9.7

For AIX)

/usr/bin/genld -l | grep -p db2
可支援的 Linux 版本與 Hypervisors (需要虛擬化時)

DB2_v97_Linux_Supported.png


安裝後的步驟

新增 DB Instance
groupadd db2adm
useradd -g db2adm -c "DB2 Instance User" -d /devinst devinst

cd /opt/ibm/db2/V9.7/instance/
./db2icrt -s ese -u devinst devinst 

# for Client only
./db2icrt -s client <app-username>

移除 Instance

cd /opt/ibm/db2/V11.1/instance

# List current instances
./db2ilist

# Remove the instance
# NOTE: The directory sqllib/ is supposed to be deleted after removing instance.
# If it isn't for some reason you have to manually remove the directory sqllib/ in the home directory.
./db2idrop <instance-name-id>
匯入 DB2 授權檔

注意事項:

TIP

_c 以 CPU 計價
_t 試用版
_u 以 USER 數計價

解開檔案

su - db2inst
unzip  DB2_AESE_CPU_QS_Activation_97.zip
cd  aese_c/

匯入前的授權資訊

db2licm -l

Product name: "DB2 Advanced Enterprise Server Edition"
License type: "License not registered"
Expiry date: "License not registered"
Product identifier: "db2aese"
Version information: "9.7"

db2level

DB21085I This instance or install (instance name, where applicable: "mydev")
uses "64" bits and DB2 code release "SQL0907B" with level identifier
"080C0107".
Informational tokens are "DB2 v9.7.0.11", "s150922", "IP23944", and Fix Pack
"11".
Product is installed at "/opt/ibm/db2/V9.7".

開始匯入

db2licm -a db2/license/db2aese_c.lic

LIC1402I License added successfully.

LIC1426I This product is now licensed for use as outlined in your License Agreement. USE OF THE PRODUCT CONSTITUTES ACCEPTANCE OF THE TERMS OF THE IBM LICENSE AGREEMENT, LOCATED IN THE FOLLOWING DIRECTORY: "/opt/ibm/db2/V9.7/license/en_US.iso88591"

匯入後的授權資訊

db2licm -l

Product name: "DB2 Advanced Enterprise Server Edition"
License type: "CPU Option"
Expiry date: "Permanent"
Product identifier: "db2aese"
Version information: "9.7"
Enforcement policy: "Soft Stop"

TIPs:

若要移除授權可以執行

db2licm -r [Product identifier]

DB2 Instance 啟動
#> su - mydev
$> db2 "update dbm cfg using svcename DB2_mydev"
$> db2set DB2COMM=TCPIP
$> db2set DB2CODEPAGE=950
$> db2set DB2AUTOSTART=NO
$> db2start
$> ps -ef | grep db2sysc

TIPs:

如果要變更預設的 Port,可以修改 /etc/services。


DBM cfg
$> db2 "update dbm cfg using HEALTH_MON OFF"
$> db2 "update dbm cfg using DIAGSIZE 50"
DB cfg
## Turn some AUTO* options off
$> db2 "update db cfg for <db-name> using AUTO_MAINT off"
$> db2 "update db cfg for <db-name> using AUTO_TBL_MAINT off"
$> db2 "update db cfg for <db-name> using AUTO_RUNSTATS off"
$> db2 "update db cfg for <db-name> using AUTO_STMT_STATS off"

## Set the Active Log
$> db2 "update db cfg for DEVMMDB using LOGFILSIZ 12800"
$> db2 "update db cfg for DEVMMDB using LOGPRIMARY 10"
$> db2 "update db cfg for DEVMMDB using LOGSECOND 10"
$> db2 "update db cfg for DEVMMDB using NEWLOGPATH /home/devmm/DEVMMDB/log"
$> db2 "update db cfg for DEVMMDB using BLK_LOG_DSK_FUL YES" 

## If enabled HADR
$> db2 "update db cfg for <db-name> using BLOCKNONLOGGED yes"
重建一個新的 db2dump 目錄

為了要將 db2dump 目錄獨立出來,在新增目錄時必須加上一些特殊權限

$> mkdir db2dump
$> chmod 0777 db2dump
$> chmod g+s db2dump
$> chmod o+t db2dump
$> ls -l db2dump
drwxrwsrwt 3 istdw db2adm 4096 Aug 11 16:40 db2dump/


Install: DB2 v11 on CentOS

DB2 Database Server v11.1.4fp5

主程式安裝
# RedHat/CentOS 6/7
yum install libaio ksh rsh-server sg3_utils pam-devel nfs-utils compat-libstdc++-33
yum install libstdc++.i686 pam-devel.i686 compat-libstdc++-33.i686

# RedHat/CentOS 8
yum install libaio ksh sg3_utils pam-devel nfs-utils
yum install libstdc++.i686 pam-devel.i686

TIP: 安裝檔裡有一個檢查相依性套件的工具 db2prereqcheck,執行後,最後下方的輸出如果沒有顯示類似的內容,就表示可以繼續執行安裝。

DBT3507E The db2prereqcheck utility failed to find the following package or file: "sg3_utils".

開始安裝

tar xzf DB2_AESE_PVU_11.1_Svr_Linux_86-64.tar.gz
cd server_aese_c/
./db2prereqcheck -i
./db2_install -f NOTSAMP

***********************************************************
Install into default directory (/opt/ibm/db2/V11.1) ? [yes/no]

yes <===

Specify one of the following keywords to install DB2 products.

SERVER
CONSV
CLIENT
RTCL

Enter "help" to redisplay product names.

Enter "quit" to exit.

***********************************************************

SERVER <===

更新 FixPack
tar xzf v11.1.4fp5_linuxx64_universal_fixpack.tar.gz
cd universal/
./db2prereqcheck -i
./installFixPack

Enter the full path of the base installation directory:

-------------------------------------------------------
/opt/ibm/db2/V11.1 <== 輸入預設安裝目錄

Do you want to choose a different installation directory for the fix pack? [yes/no]

------------------------------------------------------------------------------------
no <==

完成。

FAQ

Q: 在 Fixpack 6+ 以後版本,執行 db2prereqcheck 出現一個錯誤

Validating "ipcmni_extend kernel parameter " ...
WARNING : Requirement not matched.

A: 如果主機的 RAM 小於 128 GB,可以忽略這個;如果 RAM 大於 128 GB,在設定 kernel 參數可能發生某些數值超過限制的錯誤,這時候就必須修改開機的 kernel 參數,加上 ipcmni_extend,詳細步驟可參閱 https://access.redhat.com/solutions/4968021

DB2 管理

資料庫

新增資料庫

create db MYDB on /db2_home/istv11/MYDB using codeset big5 territory TW collate using identity
 USER TABLESPACE MANAGED BY DATABASE USING (FILE '/db2_home/istv11/MYDB/TBS_DMS/userspace_4k_c01' 524288)
 TEMPORARY TABLESPACE MANAGED BY SYSTEM USING ('/db2_home/istv11/MYDB/TBS_SMS/tempspace1') ;
TIP: 語系編碼 codeset 除了 big5,也有 utf-8。
TIP: 區域碼 territory TW 是台灣。 
執行 SQL 時顯示開始與結束時間
select 'Begin Time:',char(current date,iso)||' '||char(current time,iso) from sysibm.sysdummy1;
--
--
select 'End Time:',char(current date,iso)||' '||char(current time,iso) from sysibm.sysdummy1; 
快速產生所有資料表的 runstats 腳本
db2 -x "select 'runstats on table',substr(rtrim(tabschema)||'.'||rtrim(tabname),1,35),'with distribution and detailed indexes all allow write access;'from syscat.tables where type = 'T' and tabschema not in ('SYSIBM','ASN')" > runstats_all.sql

Kernel parameters

DB2 v11

/etc/sysctl.d/01-db2.conf:

# For DB2 server with 30 GB RAM
# - Get the RAM size by following the command 'free -g'
# - Get the PAGESIZE by following the command 'getconf PAGESIZE'
#
# SHMMNI = <RAM in GB> * 256
# SHMMAX = <RAM in bytes>
# SHMALL = <RAM in pages> * 2
# kernel.sem=<SEMMSL> <SEMMNS> <SEMOPM> <SEMMNI>
# kernel.sem = 250 256000 32 256*<size of RAM in GB>
# MSGMNI = 1024*<size of RAM in GB>
#
kernel.shmmni = 7680
kernel.shmmax = 32212254720
kernel.shmall = 15728640
kernel.sem = 250 256000 32 7680
kernel.msgmni = 30720
kernel.msgmax = 65536
kernel.msgmnb = 65536
# IBM Recommended
vm.swappiness = 5
vm.overcommit_memory = 0

Update: DB2 v11.1 FP5 to FP6

Files to require
FixPack 更新程序

1. 確認目前安裝的 FixPack 與路徑

# su - root
# /opt/IBM/db2/V11.1/install/db2ls

Install Path Level Fix Pack Special Install Number Install Date Installer UID
---------------------------------------------------------------------------------------------------------------------
/opt/IBM/db2/V11.1 11.1.4.5 5 Sun Sep 26 03:57:30 2021 EDT 0

2. 解開 FixPack 6

# gunzip -dc v11.1.4fp6_aix64_universal_fixpack.tar.gz |tar -xf -

3. 將 FixPack 6 安裝至另個路徑

./installFixPack -b <current-installation-dir> -p <install-FP-dir> -y -l /tmp/db2fp.out

# cd  <FixPack6_path>/universal
# ./installFixPack -b /opt/IBM/db2/V11.1 -p /opt/IBM/db2/V11.1_2 -y -l /tmp/db2fp6.out

4. 再確認目前安裝的 FixPack 與路徑

# /opt/IBM/db2/V11.1/install/db2ls

Install Path Level Fix Pack Special Install Number Install Date Installer UID
---------------------------------------------------------------------------------------------------------------------
/opt/IBM/db2/V11.1 11.1.4.5 5 Sun Sep 26 03:57:30 2021 EDT 0
/opt/IBM/db2/V11.1_2 11.1.4.6 6 Sun Sep 26 04:30:09 2021 EDT 0

5. 停止 db2 instance

# su - instance_user
# db2stop

6. 更新 db2 instance

# su - root
# cd /opt/IBM/db2/V11.1_2/instance
# ./db2iupdt instance_user

7. 確認 db2 instance 狀況

# su - instance_user
# db2level
# db2licm -l

# db2start

8. Update the system catalog objects  (只需再 Primary DB 執行)

# su - instance_user
# db2updv111 -d database_name

9. Binding bind files after installing fix packs (只需再 Primary DB 執行)

# su - instance_user
# db2 connect to database_name

# cd sqllib/bnd
# db2 BIND db2schema.bnd BLOCKING ALL GRANT PUBLIC SQLERROR CONTINUE
# db2 BIND @db2ubind.lst BLOCKING ALL GRANT PUBLIC ACTION ADD
# db2 BIND @db2cli.lst BLOCKING ALL GRANT PUBLIC ACTION ADD
Optional: Validate the installation
# su - root
# cd /opt/IBM/db2/V11.1_2/bin/
# ./db2val -o

DBI1379I The db2val command is running. This can take several minutes.

DBI1335I Installation file validation for the DB2 copy installed at
/opt/IBM/db2/V11.1_2 was successful.

DBI1343I The db2val command completed successfully. For details, see
the log file /tmp/db2val-211005_172717.log.


Q & A

Changed instance name

Instance 原帳號名稱變更後,就無法正常執行任何指令

$ db2level
SQL10007N Message "-10003" could not be retrieved.  Reason code: "3".

Solution: 編輯 ~/sqllib/db2profile

NOTE: 這方法不適用在兩個不同帳號的 instance 移轉。

...
DB2INSTANCE=<new-account-name>
export DB2INSTANCE
INSTHOME=<new-account-home>
...