DB2日常运维命令
最近更新：2024-03-29 11:46:29
一、安装
mkdir /db2
mkdir /db2/soft
mdkir /db2/app
[root@19c01 soft]# tar -zxvf v11.5.9_linuxx64_server_dec.tar.gz -C /db2/app/
server_dec/
server_dec/db2/
server_dec/db2/linuxamd64/
server_dec/db2/linuxamd64/tsamp/
server_dec/db2/linuxamd64/tsamp/Linux/
server_dec/db2/linuxamd64/tsamp/Linux/x86_64/
server_dec/db2/linuxamd64/tsamp/Linux/x86_64/rsct.basic.msg.ja_JP.UTF-8-3.3.1.2-23243.x86_64.rpm
server_dec/db2/linuxamd64/tsamp/Linux/x86_64/rsct.core.msg.it_IT-3.3.1.2-23243.x86_64.rpm
server_dec/db2/linuxamd64/tsamp/Linux/x86_64/rsct.core.utils.msg.es_ES.ISO-8859-1-3.3.1.2-23243.x86_64.rpm
...
安装包准备
[root@19c01 server_dec]# cd /db2/app/server_dec
[root@19c01 server_dec]# ./db2prereqcheck
==========================================================================
Thu Jan 18 15:33:29 2024
Checking prerequisites for DB2 installation. Version "11.5.9.0". Operating system "Linux"
Validating "kernel level " ...
Required minimum operating system kernel level: "3.10.0".
Actual operating system kernel level: "5.4.17".
Requirement matched.
Validating "Linux distribution " ...
Required minimum operating system distribution: "RHEL"; Version: "8"; Service pack: "1".
Actual operating system distribution Version: "8"; Service pack: "5".
Requirement matched.
Validating "ksh symbolic link" ...
Requirement matched.
Validating "Bin user" ...
Requirement matched.
Validating "C++ Library version " ...
Required minimum C++ library: "libstdc++.so.6"
Standard C++ library is located in the following directory: "/usr/lib64/libstdc++.so.6.0.25".
Actual C++ library: "CXXABI_1.3.1"
Requirement matched.
...
安装前检查，因为操作系统版本太新，我用oracle linux8.5，警告可以忽略
[root@19c01 server_dec]# ./db2_install
Requirement not matched for DB2 database "Server" . Version: "11.5.9.0".
Summary of prerequisites that are not met on the current system:
DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "/lib/libpam.so*".
Read the license agreement file in the db2/license directory.
***********************************************************
To accept those terms, enter "yes". Otherwise, enter "no" to cancel the install process. [yes/no]
yes
Default directory for installation of products - /opt/ibm/db2/V11.5
***********************************************************
Install into default directory (/opt/ibm/db2/V11.5) ? [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
***********************************************************
Do you want to install the DB2 pureScale Feature? [yes/no]
no
Requirement not matched for DB2 database "Server" . Version: "11.5.9.0".
Summary of prerequisites that are not met on the current system:
DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "/lib/libpam.so*".
DB2 installation is being initialized.
Total number of tasks to be performed: 59
Total estimated time for all tasks to be performed: 2904 second(s)
Task #1 start
Description: Checking license agreement acceptance
Estimated time 1 second(s)
Task #1 end
Task #2 start
Description: Base Client Support for installation with root privileges
...
Task #58 start
Description: Initializing instance list
Estimated time 5 second(s)
Task #58 end
Task #59 start
Description: Updating global profile registry
Estimated time 3 second(s)
Task #59 end
The execution completed with warnings.
For more information see the DB2 installation log at
"/tmp/db2_install.log.245881".
软件安装完成，存在一些小警告
[root@19c01 server_dec]# ps -ef|grep db2
root 289261 1 0 15:57 ? 00:00:00 /opt/ibm/db2/V11.5/bin/db2fmcd
root 290012 5053 0 15:57 pts/0 00:00:00 grep --color=auto db2
[root@19c01 server_dec]# groupadd db2adm1
[root@19c01 server_dec]# groupadd db2fen1
[root@19c01 server_dec]# useradd -d /home/db2inst1 -m db2inst1 -g db2adm1
[root@19c01 server_dec]# useradd -d /home/db2fenc1 -m db2fenc1 -g db2fen1
[root@19c01 server_dec]# passwd db2inst1
Changing password for user db2inst1.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@19c01 server_dec]# passwd db2fenc1
Changing password for user db2fenc1.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[root@19c01 server_dec]# cd /op
opengauss/ opt/
[root@19c01 server_dec]# cd /opt/
[root@19c01 opt]# ls
ibm oracle.ahf ORCLfmap
[root@19c01 opt]# cd ibm/
[root@19c01 ibm]# ls
db2
[root@19c01 ibm]# cd db2/
[root@19c01 db2]# ls
V11.5
[root@19c01 db2]# cd V11.5/
[root@19c01 V11.5]# ls
acs adsm bnd conv dasfcn desktop federation gse guardium include install java lib32 license map msg properties R samples security64
adm bin cfg das db2tss doc function gskit ha infopop instance json lib64 logs misc pd python Readme security32 tools
创建实例用户
[root@19c01 instance]# ./db2icrt -u db2fenc1 db2inst1
DBI1446I The db2icrt command is running.
DB2 installation is being initialized.
Total number of tasks to be performed: 4
Total estimated time for all tasks to be performed: 309 second(s)
Task #1 start
Description: Setting default global profile registry variables
Estimated time 1 second(s)
Task #1 end
Task #2 start
Description: Initializing instance list
Estimated time 5 second(s)
Task #2 end
Task #3 start
Description: Configuring DB2 instances
Estimated time 300 second(s)
Task #3 end
Task #4 start
Description: Updating global profile registry
Estimated time 3 second(s)
Task #4 end
The execution completed successfully.
For more information see the DB2 installation log at
"/tmp/db2icrt.log.292435".
DBI1070I Program db2icrt completed successfully.
创建实例
[root@19c01 instance]# su - db2inst1
[db2inst1@19c01 ~]$ db2start
01/18/2024 16:01:09 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@19c01 ~]$ db2 create db mydb
DB20000I The CREATE DATABASE command completed successfully.
启动实例并创建数据库
[db2inst1@19c01 ~]$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 entry:
Database alias = MYDB
Database name = MYDB
Local database directory = /home/db2inst1
Database release level = 15.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =
[db2inst1@19c01 ~]$ db2 connect to mydb
Database Connection Information
Database server = DB2/LINUXX8664 11.5.9.0
SQL authorization ID = DB2INST1
Local database alias = MYDB
检查数据库
二、实例管理
UNIX/Linux系统下实例名需要与用户绑定，不同实例需要对应不同的用户名，实例创建在用户目录下。这样，当需要切换不同实例时，只需切换到相应用户即可。如果一个实例停止了，那么实例下所有的数据库将不可用。一个实例的停止将不会影响其他实例下的数据库。
1、创建实例
[root@19c01 instance]# /opt/ibm/db2/V11.5/instance/db2icrt -a server -u db2fenc1 db2inst1
DBI1446I The db2icrt command is running.
DB2 installation is being initialized.
Total number of tasks to be performed: 4
Total estimated time for all tasks to be performed: 309 second(s)
Task #1 start
Description: Setting default global profile registry variables
Estimated time 1 second(s)
Task #1 end
Task #2 start
Description: Initializing instance list
Estimated time 5 second(s)
Task #2 end
Task #3 start
Description: Configuring DB2 instances
Estimated time 300 second(s)
Task #3 end
Task #4 start
Description: Updating global profile registry
Estimated time 3 second(s)
Task #4 end
The execution completed successfully.
For more information see the DB2 installation log at
"/tmp/db2icrt.log.358507".
DBI1070I Program db2icrt completed successfully.
创建之间需要先创建用户，其中db2fenc1用户是指受保护用户，表示将在其下运行受防护的用户定义函数 (UDF) 和受防护的存储过程的用户名
[root@19c01 ~]# ps -ef|grep db2|grep -i db2
root 294814 1 0 16:00 ? 00:00:00 /opt/ibm/db2/V11.5/bin/db2fmcd
root 306978 1 0 16:01 pts/0 00:00:00 db2wdog 0 [db2inst1]
db2inst1 306980 306978 1 16:01 pts/0 00:00:17 db2sysc 0
root 306987 306978 0 16:01 pts/0 00:00:00 db2ckpwd 0
root 306988 306978 0 16:01 pts/0 00:00:00 db2ckpwd 0
root 306989 306978 0 16:01 pts/0 00:00:00 db2ckpwd 0
db2inst1 306991 306978 0 16:01 pts/0 00:00:00 db2vend (PD Vendor Process - 1) 0
db2inst1 306999 306978 0 16:01 pts/0 00:00:00 db2acd 0 ,0,0,0,1,0,0,00000000,0,0,0000000000000000,0000000000000000,00000000,00000000,00000000,00000000,00000000,00000000,0000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,000000018bdfb000,0000000000000000,0000000000000000,1,0,0,,,,,a89f94,14,1e014,2,0,1,0000000000041fc0,0x240000000,0x240000000,1600000,5,2,18
进程
[root@19c01 ~]# su - db2inst1
[db2inst1@19c01 ~]$ ls
db2inst1 sqllib
[db2inst1@19c01 ~]$ cd sqllib/
[db2inst1@19c01 sqllib]$ ls
acs bin conv dasfcn db2profile federation gskit java lib64 msg profile.env samples spmlog uif
adm bnd ctrl db2cshrc db2systm fm.19c01.reg hmonCache json log nodes python security sqldbdir usercshrc
adsm cfg ctrlha db2dump db2tss function include lib map nodes.reg R security32 tmp userprofile
backup cfgcache ctrlhamirror db2nodes.cfg doc gse infopop lib32 misc pd Readme security64 tools
[db2inst1@19c01 sqllib]$
实例配置目录
2、管理实例
2.1、停止实例
[db2inst1@19c01 sqllib]$ db2stop
2024-01-18 16:27:08 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
[db2inst1@19c01 sqllib]$ ps -ef|grep db2|grep -i db2
root 294814 1 0 16:00 ? 00:00:00 /opt/ibm/db2/V11.5/bin/db2fmcd
2.2、启动实例
[db2inst1@19c01 sqllib]$ db2start
01/18/2024 16:27:55 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
[db2inst1@19c01 sqllib]$ ps -ef|grep db2|grep -i db2
root 294814 1 0 16:00 ? 00:00:00 /opt/ibm/db2/V11.5/bin/db2fmcd
root 334052 5053 0 16:25 pts/0 00:00:00 su - db2inst1
db2inst1 334053 334052 0 16:25 pts/0 00:00:00 -bash
root 338135 1 0 16:27 pts/0 00:00:00 db2wdog 0 [db2inst1]
db2inst1 338137 338135 11 16:27 pts/0 00:00:01 db2sysc 0
root 338144 338135 0 16:27 pts/0 00:00:00 db2ckpwd 0
root 338145 338135 0 16:27 pts/0 00:00:00 db2ckpwd 0
root 338146 338135 0 16:27 pts/0 00:00:00 db2ckpwd 0
db2inst1 338148 338135 0 16:27 pts/0 00:00:00 db2vend (PD Vendor Process - 1) 0
db2inst1 338156 338135 1 16:27 pts/0 00:00:00 db2acd 0 ,0,0,0,1,0,0,00000000,0,0,0000000000000000,0000000000000000,00000000,00000000,00000000,00000000,00000000,00000000,0000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,000000018bdfb000,0000000000000000,0000000000000000,1,0,0,,,,,a89f94,14,1e014,2,0,1,0000000000041fc0,0x240000000,0x240000000,1600000,1d,2,1003c
在root用户下创建实例的时候会直接将实例拥有者的环境变量调整好。
2.3、断开所有链接
[db2inst1@19c01 sqllib]$ db2 force applications all
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
2.4、强制停止
[db2inst1@19c01 ~]$ db2stop force
2024-01-18 16:30:37 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
有连接的时候无法停止，可以用这两个方法处理
2.5、查看有哪些实例
[db2inst1@19c01 ~]$ db2ilist
db2inst1
查看有哪些注册资源
[db2inst1@19c01 ~]$ db2greg -dump
S,TSA,4.1.1.1,/opt/IBM/tsamp,DG_NOT_ALLOWED,DB2_INSTALLED,0,0,-,1705564605,0
S,RSCT,3.3.1.2,/usr/sbin/rsct,DG_NOT_ALLOWED,DB2_INSTALLED,0,0,-,1705564605,0
S,PCMK,2.1.6.4,/usr/sbin,DG_NOT_ALLOWED,DB2_INSTALLED,0,0,-,1705564605,0
S,DB2,11.5.9.0,/opt/ibm/db2/V11.5,,,0,0,,1705564644,0
V,DB2GPRF,DB2SYSTEM,19c01,/opt/ibm/db2/V11.5,
I,DB2,11.5.9.0,db2inst1,/home/db2inst1/sqllib,,1,0,/opt/ibm/db2/V11.5,,
2.6、强制终止
用于应对db2stop force无法关闭的情况
[db2inst1@19c01 ~]$ db2_kill
Application ipclean: Removing DB2 engine and client IPC resources for db2inst1(1003)
[db2inst1@19c01 ~]$ ipclean
Application ipclean: Removing DB2 engine and client IPC resources for db2inst1(1003)
2.7、删除实例
[db2inst1@19c01 ~]$ db2stop force
2024-01-18 16:39:06 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.
[root@19c01 instance]# ./db2idrop db2inst1
DBI1446I The db2idrop command is running.
DB2 installation is being initialized.
Total number of tasks to be performed: 2
Total estimated time for all tasks to be performed: 305 second(s)
Task #1 start
Description: Initializing instance list
Estimated time 5 second(s)
Task #1 end
Task #2 start
Description: Configuring DB2 instances
Estimated time 300 second(s)
Task #2 end
The execution completed successfully.
For more information see the DB2 installation log at
"/tmp/db2idrop.log.351909".
DBI1070I Program db2idrop completed successfully.
[root@19c01 instance]# pwd
/opt/ibm/db2/V11.5/instance
2.7、实例参数
[db2inst1@19c01 ~]$ db2 get dbm cfg
Database Manager Configuration
Node type = Enterprise Server Edition with local and remote clients
Database manager configuration release level = 0x1500
CPU speed (millisec/instruction) (CPUSPEED) = 7.085165e-08
Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02
Max number of concurrently active databases (NUMDB) = 32
Federated Database System Support (FEDERATED) = NO
Transaction processor monitor name (TP_MON_NAME) =
Default charge-back account (DFT_ACCOUNT_STR) =
Java Development Kit installation path (JDK_PATH) = /home/db2inst1/sqllib/java/jdk64
Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) = /home/db2inst1/sqllib/db2dump/ $m
Current member resolved DIAGPATH = /home/db2inst1/sqllib/db2dump/DIAG0000/
Alternate diagnostic data directory path (ALT_DIAGPATH) =
Current member resolved ALT_DIAGPATH =
Size of rotating db2diag & notify logs (MB) (DIAGSIZE) = 0
Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = OFF
Lock (DFT_MON_LOCK) = OFF
2.8、数据库参数
[db2inst1@19c01 ~]$ db2 get cfg for test
SQL0104N An unexpected token "cfg" was found following "GET". Expected
tokens may include: "ADMIN". SQLSTATE=42601
[db2inst1@19c01 ~]$ db2 get db cfg for test
Database Configuration for Database test
Database configuration release level = 0x1500
Database release level = 0x1500
Update to database level pending = NO (0x0)
Database territory = CN
Database code page = 1208
Database code set = UTF-8
Database country/region code = 86
Database collating sequence = IDENTITY
归档相关参数,和oracle的db_recover_dest_1相似
[db2inst1@19c01 ~]$ db2 get db cfg for test|grep ARCH
First log archive method (LOGARCHMETH1) = OFF
Archive compression for logarchmeth1 (LOGARCHCOMPR1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =......
登录MES账号 查看全文详情
登录/注册
