一、安装
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) =
Second log archive method (LOGARCHMETH2) = OFF
Archive compression for logarchmeth2 (LOGARCHCOMPR2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =
Number of log archive retries on error (NUMARCHRETRY) = 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
三、数据库管理
1、创建数据库
[db2inst1@19c01 ~]$ db2 create database test;
DB20000I The CREATE DATABASE command completed successfully.
2、查看数据库
[db2inst1@19c01 ~]$ db2 connect to test;
Database Connection Information
Database server = DB2/LINUXX8664 11.5.9.0
SQL authorization ID = DB2INST1
Local database alias = TEST
3、激活数据库
[db2inst1@19c01 ~]$ db2 activate database test;
DB20000I The ACTIVATE DATABASE command completed successfully.
[db2inst1@19c01 ~]$ db2 deactivate database test;
DB20000I The DEACTIVATE DATABASE command completed successfully.
4、删除数据库
[db2inst1@19c01 ~]$ db2 drop database test;
SQL1035N The operation failed because the specified database cannot be
connected to in the mode requested. SQLSTATE=57019
[db2inst1@19c01 ~]$ db2 force application all;
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
[db2inst1@19c01 ~]$ db2 drop database test;
DB20000I The DROP DATABASE command completed successfully.
[db2inst1@19c01 ~]$ db2 list db directory
SQL1057W The system database directory is empty. SQLSTATE=01606
四、表空间管理
默认系统会创建三个表空间
系统表空间、 临时表空间、 用户表空间
1、查看表空间
[db2inst1@19c01 ~]$ db2 list tablespaces;
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
[db2inst1@19c01 ~]$ db2 list tablespaces show detail
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 32768
Useable pages = 32764
Used pages = 29040
Free pages = 3724
High water mark (pages) = 29040
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 8192
Useable pages = 8160
Used pages = 96
Free pages = 8064
High water mark (pages) = 96
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
查看表空间的配置信息，使用情况及容器信息
[db2inst1@19c01 ~]$ db2pd -d test -tablespaces
Database Member 0 -- Database TEST -- Active -- Up 0 days 00:02:02 -- Date 2024-01-18-17.48.03.088764
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
0x00007F7FA1EA4540 0 DMS Regular 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSCATSPACE
0x00007F7FA1EB1A00 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE1
0x00007F7FA1EBEEC0 2 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes USERSPACE1
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped TrackmodState
0x00007F7FA1EA4540 0 32768 32764 29040 0 3724 29040 29040 0x00000000 0 0 No n/a
0x00007F7FA1EB1A00 1 1 1 1 0 0 - - 0x00000000 0 0 No n/a
0x00007F7FA1EBEEC0 2 8192 8160 96 0 8064 96 96 0x00000000 0 0 No n/a
Tablespace Autoresize Statistics:
Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF
0x00007F7FA1EA4540 0 Yes Yes 33554432 -1 No None None No
0x00007F7FA1EB1A00 1 Yes No 0 0 No 0 None No
0x00007F7FA1EBEEC0 2 Yes Yes 33554432 -1 No None None No
Tablespace Storage Statistics:
Address Id DataTag Rebalance SGID SourceSGID
0x00007F7FA1EA4540 0 0 No 0 -
0x00007F7FA1EB1A00 1 0 No 0 -
0x00007F7FA1EBEEC0 2 -1 No 0 -
Containers:
Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
0x00007F7FA1E9C120 0 0 File 32768 32764 0 0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000000/C0000000.CAT
0x00007F7FA1EEE000 1 0 Path 1 1 0 0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000001/C0000000.TMP
0x00007F7FA1E7E5C0 2 0 File 8192 8160 0 0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000002/C0000000.LRG
通过snapshot获取
[db2inst1@19c01 ~]$ db2 get snapshot for tablespaces on test
Tablespace Snapshot
First database connect timestamp = 2024-01-18 17:46:01.431981
Last reset timestamp =
Snapshot timestamp = 2024-01-18 18:05:31.198303
Database name = TEST
Database path = /home/db2inst1/db2inst1/NODE0000/SQL00002/MEMBER0000/
Input database alias = TEST
Number of accessed tablespaces = 4
Tablespace name = SYSCATSPACE
Tablespace ID = 0
Tablespace Type = Database managed space
Tablespace Content Type = All permanent data. Regular table space.
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 4
Automatic Prefetch size enabled = Yes
Buffer pool ID currently in use = 1
Buffer pool ID next startup = 1
Using automatic storage = Yes
Auto-resize enabled = Yes
File system caching = None
2、创建表空间
表空间分为SMS、DMS和自动存储管理表空间。
managed by database表示空间的分配和管理由DB2负责，即DMS（database-managed space）。Using指定表空间的容器，DMS支持的容器类型是文件和裸设备（raw device，可理解成没有格式化的盘）。DMS类型的表空间在创建时即分配空间，创建后可通过命令对表空间容器进行增删改。对于数据来说，建议用DMS管理。
[db2inst1@19c01 MEMBER0000]$ db2 create bufferpool p1 size 1000 pagesize 8k;
[db2inst1@19c01 TEST]$ db2 "CREATE TABLESPACE t2 PAGESIZE 8k MANAGED BY DATABASE USING (FILE '/home/db2inst1/db2inst1/NODE0000/TEST/data/t201.dbf' 5M,FILE '/home/db2inst1/db2inst1/NODE0000/TEST/data/t202.dbf' 5M) bufferpool p1";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 data]$ du -sh *
5.0M t201.dbf
5.0M t202.dbf
managed by system表示空间的分配和管理由操作系统负责，即SMS（system-managed space）。Using指定表空间的容器，SMS支持的容器类型只能是目录，并且无需指定大小，只要路径所属的文件系统有空间，就可以被表空间使用。SMS的优点是比较容易管理，缺点是性能比DMS差一些，大概差5%～10%左右。对于临时表空间来说，建议用SMS管理。
[db2inst1@19c01 data]$ db2 "CREATE system TEMPORARY TABLESPACE temp_sys PAGESIZE 8k MANAGED by SYSTEM USING('/home/db2inst1/db2inst1/NODE0000/TEST/data/temp') BUFFERPOOL p1";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 data]$ ls -lsa
总用量 10240
0 -rw------- 1 db2inst1 db2adm1 0 1月 18 19:08 .SQLCRT.FLG
5120 -rw------- 1 db2inst1 db2adm1 5242880 1月 18 19:08 t201.dbf
5120 -rw------- 1 db2inst1 db2adm1 5242880 1月 18 19:08 t202.dbf
0 drwx------ 2 db2inst1 db2adm1 24 1月 18 19:08 temp
[db2inst1@19c01 data]$ db2 "CREATE user TEMPORARY TABLESPACE temp_user PAGESIZE 8k MANAGED by SYSTEM USING('/home/db2inst1/db2inst1/NODE0000/TEST/data/temp2') BUFFERPOOL p1";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 data]$ ls -lsa
总用量 10240
0 -rw------- 1 db2inst1 db2adm1 0 1月 18 19:08 .SQLCRT.FLG
5120 -rw------- 1 db2inst1 db2adm1 5242880 1月 18 19:08 t201.dbf
5120 -rw------- 1 db2inst1 db2adm1 5242880 1月 18 19:08 t202.dbf
0 drwx------ 2 db2inst1 db2adm1 24 1月 18 19:08 temp
0 drwx------ 2 db2inst1 db2adm1 24 1月 18 19:08 temp2
自动存储管理（automatic storage），目的是简化表空间的监控和管理。当表空间创建时，只需提供表空间名，而无需指定容器类型和大小。自动存储管理底层使用的仍然是DMS或SMS类型，只不过是容器不需指定而已。对于数据表空间，DB2会选用DMS方式管理；对于临时表空间，会选用SMS方式管理。自动存储管理表空间的数据存在建库时指定的ON目录，也叫自动存储路径。只有建库时启用了automatic storage yes，表空间才支持自动存储管理。
[db2inst1@19c01 data]$ db2 create tablespace t3;
[db2inst1@19c01 data]$ db2 alter bufferpool p1 size 2000;
DB20000I The SQL command completed successfully.
3、表空间管理
[db2inst1@19c01 data]$ db2 list tablespaces;
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
[db2inst1@19c01 data]$ db2 list tablespaces show detail;
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 32768
Useable pages = 32764
Used pages = 29336
Free pages = 3428
High water mark (pages) = 29336
Page size (bytes) = 4096
Extent size (pages) = 4
Prefetch size (pages) = 4
Number of containers = 1
Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
[db2inst1@19c01 data]$ db2 list tablespace containers for 8 show detail;
Tablespace Containers for Tablespace 8
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/TEST/T0000008/C0000000.LRG
Type = File
Total pages = 8192
Useable pages = 8160
Accessible = Yes
[db2inst1@19c01 data]$ db2pd -d test -tablespaces
Database Member 0 -- Database TEST -- Active -- Up 0 days 01:22:44 -- Date 2024-01-18-19.08.45.878950
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
0x00007F7FA1EA4540 0 DMS Regular 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSCATSPACE
0x00007F7FA1EB1A00 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE1
0x00007F7FA1EBEEC0 2 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes USERSPACE1
0x00007F7FFAA9B2C0 3 DMS Large 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSTOOLSPACE
0x00007F7FFCFC0040 4 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes T1
0x00007F7FFCFCF620 5 DMS Large 8192 32 Yes 64 2 2 Def 2 0 31 Yes T2
0x00007F7FFCFDDE20 6 SMS SysTmp 8192 32 Yes 32 2 2 On 1 0 31 No TEMP_SYS
0x00007F7FFCFED300 7 SMS UsrTmp 8192 32 Yes 32 2 2 On 1 0 31 No TEMP_USER
0x00007F7FFCFFC7E0 8 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes T3
Tablespace Statistics:
Address Id TotalPgs UsablePgs UsedPgs PndFreePgs FreePgs HWM Max HWM State MinRecTime NQuiescers PathsDropped TrackmodState
0x00007F7FA1EA4540 0 32768 32764 29384 0 3380 29384 29384 0x00000000 0 0 No n/a
0x00007F7FA1EB1A00 1 1 1 1 0 0 - - 0x00000000 0 0 No n/a
0x00007F7FA1EBEEC0 2 8192 8160 96 0 8064 96 96 0x00000000 0 0 No n/a
0x00007F7FFAA9B2C0 3 8192 8188 144 0 8044 144 144 0x00000000 0 0 No n/a
0x00007F7FFCFC0040 4 8192 8160 96 0 8064 96 96 0x00000000 0 0 No n/a
0x00007F7FFCFCF620 5 1280 1216 96 0 1120 96 96 0x00000000 0 0 No n/a
0x00007F7FFCFDDE20 6 1 1 1 0 0 - - 0x00000000 0 0 No n/a
0x00007F7FFCFED300 7 1 1 1 0 0 - - 0x00000000 0 0 No n/a
0x00007F7FFCFFC7E0 8 8192 8160 96 0 8064 96 96 0x00000000 0 0 No n/a
Tablespace Autoresize Statistics:
Address Id AS AR InitSize IncSize IIP MaxSize LastResize LRF
0x00007F7FA1EA4540 0 Yes Yes 33554432 -1 No None None No
0x00007F7FA1EB1A00 1 Yes No 0 0 No 0 None No
0x00007F7FA1EBEEC0 2 Yes Yes 33554432 -1 No None None No
0x00007F7FFAA9B2C0 3 Yes Yes 33554432 -1 No None None No
0x00007F7FFCFC0040 4 Yes Yes 33554432 -1 No None None No
0x00007F7FFCFCF620 5 No No -8192 0 No 0 None No
0x00007F7FFCFDDE20 6 No No 0 0 No 0 None No
0x00007F7FFCFED300 7 No No 0 0 No 0 None No
0x00007F7FFCFFC7E0 8 Yes Yes 33554432 -1 No None None No
Tablespace Storage Statistics:
Address Id DataTag Rebalance SGID SourceSGID
0x00007F7FA1EA4540 0 0 No 0 -
0x00007F7FA1EB1A00 1 0 No 0 -
0x00007F7FA1EBEEC0 2 -1 No 0 -
0x00007F7FFAA9B2C0 3 -1 No 0 -
0x00007F7FFCFC0040 4 -1 No 0 -
0x00007F7FFCFCF620 5 0 No - -
0x00007F7FFCFDDE20 6 0 No - -
0x00007F7FFCFED300 7 0 No - -
0x00007F7FFCFFC7E0 8 -1 No 0 -
Containers:
Address TspId ContainNum Type TotalPgs UseablePgs PathID StripeSet Container
0x00007F7FA1E9C120 0 0 File 32768 32764 0 0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000000/C0000000.CAT
0x00007F7FA1EEE000 1 0 Path 1 1 0 0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000001/C0000000.TMP
0x00007F7FA1E7E5C0 2 0 File 8192 8160 0 0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000002/C0000000.LRG
0x00007F7FFAAAA000 3 0 File 8192 8188 0 0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000003/C0000000.LRG
0x00007F7FFCFCE000 4 0 File 8192 8160 0 0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000004/C0000000.LRG
0x00007F7FFCFBB980 5 0 File 640 608 - 0 /home/db2inst1/db2inst1/NODE0000/TEST/data/t201.dbf
0x00007F7FFCFBBC08 5 1 File 640 608 - 0 /home/db2inst1/db2inst1/NODE0000/TEST/data/t202.dbf
0x00007F7FFCFBCA60 6 0 Path 1 1 - 0 /home/db2inst1/db2inst1/NODE0000/TEST/data/temp
0x00007F7FFCFBD240 7 0 Path 1 1 - 0 /home/db2inst1/db2inst1/NODE0000/TEST/data/temp2
0x00007F7FFD00B000 8 0 File 8192 8160 0 0 /home/db2inst1/db2inst1/NODE0000/TEST/T0000008/C0000000.LRG
get snapshot for看的更详细
[db2inst1@19c01 data]$ db2 get snapshot for tablespaces on test
Tablespace Snapshot
First database connect timestamp = 2024-01-18 17:46:01.431981
Last reset timestamp =
Snapshot timestamp = 2024-01-18 19:08:45.905506
Database name = TEST
Database path = /home/db2inst1/db2inst1/NODE0000/SQL00002/MEMBER0000/
Input database alias = TEST
Number of accessed tablespaces = 9
Tablespace name = SYSCATSPACE
Tablespace ID = 0
Tablespace Type = Database managed space
Tablespace Content Type = All permanent data. Regular table space.
Tablespace Page size (bytes) = 4096
Tablespace Extent size (pages) = 4
Automatic Prefetch size enabled = Yes
Buffer pool ID currently in use = 1
4、调整大小
对于SMS类型表空间，不支持表空间容器的更改，只能更改容器路径所属的文件系统大小。
对于DMS表空间，提供了几个方法更改表空间容器。其中Add用来增加新的容器，Drop删除容器，Extend用来扩展已有容器大小，Reduce用来缩减已有容器大小，Resize重新设定容器大小。对于Add和Drop操作，表空间容器间会发生数据重新平衡（Rebalance）。对于Reduce和Resize操作，需要确保更改后的表空间容器有足够的空间，否则DB2会拒绝该操作。
扩展容器
[db2inst1@19c01 data]$ db2 "alter tablespace t2 extend ( file '/home/db2inst1/db2inst1/NODE0000/TEST/data/t201.dbf' 10M,FILE '/home/db2inst1/db2inst1/NODE0000/TEST/data/t202.dbf' 20M)";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 data]$ du -sh *
15M t201.dbf
25M t202.dbf
添加新容器
[db2inst1@19c01 data]$ db2 "alter tablespace t2 add (file '/home/db2inst1/db2inst1/NODE0000/TEST/data/t203.dbf' 30M)";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 data]$ du -sh *
15M t201.dbf
25M t202.dbf
30M t203.dbf
用完之后再用新容器
[db2inst1@19c01 data]$ db2 "alter tablespace t2 begin new stripe set (file '/home/db2inst1/db2inst1/NODE0000/TEST/data/t204.dbf' 30M)";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 data]$ du -sh *
15M t201.dbf
25M t202.dbf
30M t203.dbf
30M t204.dbf
自动存储路径
[db2inst1@19c01 data]$ db2pd -d test -storagepath
Database Member 0 -- Database TEST -- Active -- Up 0 days 02:01:45 -- Date 2024-01-18-19.47.46.762526
Storage Group Configuration:
Address SGID Default DataTag Name
0x00007F7F9D58B820 0 Yes 0 IBMSTOGROUP
Storage Group Statistics:
Address SGID State Numpaths NumDropPen
0x00007F7F9D58B820 0 0x00000000 1 0
Storage Group Paths:
Address SGID PathID PathState PathName
0x00007F7F9D5AF000 0 0 InUse /home/db2inst1
[db2inst1@19c01 ~]$ db2 "alter database test add storage on '/home/db2inst1/data2'";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 ~]$ db2 "alter tablespace t3 rebalance";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 ~]$ du -sh data2
32M data2
[db2inst1@19c01 ~]$ db2 list tablespace containers for 8 show detail;
Tablespace Containers for Tablespace 8
Container ID = 0
Name = /home/db2inst1/db2inst1/NODE0000/TEST/T0000008/C0000000.LRG
Type = File
Total pages = 8192
Useable pages = 8160
Accessible = Yes
Container ID = 1
Name = /home/db2inst1/data2/db2inst1/NODE0000/TEST/T0000008/C0000001.LRG
Type = File
Total pages = 8192
Useable pages = 8160
Accessible = Yes
当新增了存储路径，只要对自动存储表空间执行了rebalance操作，就可以立即使用这个存储路径
5、转换管理方式
[db2inst1@19c01 ~]$ db2 "CREATE TABLESPACE t4 PAGESIZE 8k MANAGED BY DATABASE USING (FILE '/home/db2inst1/db2inst1/NODE0000/TEST/data/t401.dbf' 5M,FILE '/home/db2inst1/db2inst1/NODE0000/TEST/data/t402.dbf' 5M) bufferpool p1";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 ~]$ alter tablespace t4 managed by automatic storage;
bash: alter: 未找到命令...
[db2inst1@19c01 ~]$ db2 "alter tablespace t4 managed by automatic storage";
DB20000I The SQL command completed successfully.
其它方式
db2 RESTORE DATABASE test TABLESPACE t3 REDIRECT
db2 SET TABLESPACE CONTAINERS FOR 3 USING AUTOMATIC STORAGE
db2 RESTORE DATABASE test CONTINUE
db2 ROLLFORWARD DATABASE test TO END OF LOGS AND STOP
五、模式
1、schema
schema只是分类用,因为连接用户为操作系统用户，此处与oracle不一样。创建schema两种方式
- 方式一
显示命令创建
[db2inst1@19c01 ~]$ db2 'create schema cy';
DB20000I The SQL command completed successfully.
[db2inst1@19c01 ~]$ db2 "create table cy.t1(id int,name varchar(10))";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 ~]$ db2 "insert into cy.t1 values(1,'szs')";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 ~]$ db2 "commit";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 ~]$ db2 "select * from cy.t1";
ID NAME
----------- ----------
1 szs
1 record(s) selected.
- 方式二
创建对象时自动创建
[db2inst1@19c01 ~]$ db2 "create table scott.test(id int,name varchar(10))";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 ~]$ db2 "insert into scott.test values(1,'yc')";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 ~]$ db2 "commit";
DB20000I The SQL command completed successfully.
[db2inst1@19c01 ~]$ db2 "select * from scott.test";
ID NAME
----------- ----------
1 yc
1 record(s) selected.
2、默认schema
- SYSIBM模式下的对象存储的是系统数据字典表。
- SYSCAT模式下的对象是系统视图，可通过这些视图查看各种数据库对象信息。
- SYSIBMADM是V9引入的系系统管理视图模式。
- SYSSTAT是统计视图模式，该模式下包含9个视图，用来为DB2优化器提供统计信息。
[db2inst1@19c01 ~]$ db2 "select schemaname from syscat.schemata";
SCHEMANAME
------------------------------------
CY
NULLID
SCOTT
SQLJ
SYSCAT
SYSFUN
SYSIBM
SYSIBMADM
SYSIBMINTERNAL
SYSIBMTS
SYSPROC
SYSPUBLIC
SYSSTAT
SYSTOOLS
14 record(s) selected.
查看表结构
[db2inst1@19c01 ~]$ db2 "describe table syscat.schemata";
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
SCHEMANAME SYSIBM VARCHAR 128 0 No
OWNER SYSIBM VARCHAR 128 0 No
OWNERTYPE SYSIBM VARCHAR 1 0 No
DEFINER SYSIBM VARCHAR 128 0 No
DEFINERTYPE SYSIBM CHARACTER 1 0 No
CREATE_TIME SYSIBM TIMESTAMP 10 6 No
AUDITPOLICYID SYSIBM INTEGER 4 0 Yes
AUDITPOLICYNAME SYSIBM VARCHAR 128 0 Yes
AUDITEXCEPTIONENABLED SYSIBM CHARACTER 1 0 No
DATACAPTURE SYSIBM VARCHAR 1 0 No
ROWMODIFICATIONTRACKING SYSIBM VARCHAR 1 0 No
QUIESCED SYSIBM VARCHAR 1 0 No
REMARKS SYSIBM VARCHAR 254 0 Yes
13 record(s) selected.
查看对象
[db2inst1@19c01 ~]$ db2 list tables for schema sysstat;
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
COLDIST SYSSTAT V 2024-01-18-17.39.28.580144
COLGROUPDIST SYSSTAT V 2024-01-18-17.39.28.584728
COLGROUPDISTCOUNTS SYSSTAT V 2024-01-18-17.39.28.588203
COLGROUPS SYSSTAT V 2024-01-18-17.39.28.591251
COLUMNS SYSSTAT V 2024-01-18-17.39.28.594706
FUNCTIONS SYSSTAT V 2024-01-18-17.39.28.599518
INDEXES SYSSTAT V 2024-01-18-17.39.28.605687
ROUTINES SYSSTAT V 2024-01-18-17.39.28.611575
TABLES SYSSTAT V 2024-01-18-17.39.28.616332
9 record(s) selected.
查看表信息
[db2inst1@19c01 ~]$ db2 "select TABSCHEMA,TABNAME,OWNER,TYPE from syscat.tables";
TABSCHEMA TABNAME OWNER TYPE
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----
SYSIBM SYSTABLES SYSIBM T
SYSIBM SYSCOLUMNS SYSIBM T
SYSIBM SYSINDEXES
六、导入导出
导出文件格式有DEL、ASC、PC/IXF和WSF。
- DEL
文本文件，定界ASCII格式（DEL）是DB2用于数据交换的最常用格式。这种格式包含ASCII数据，使用字符分隔符分隔列值，分隔符用来标识数据元素的起始和结束 - ASC
文本文件，定长ASCII（ASC）格式，顾名思义，这种文件类型包含定长ASCII数据，每个数据长度与列定义相同，不足的用空格补齐，行与行之间通过换行符分隔 - PC/IXF
二进制文件，PC/IXF（IXF）是IBM特定的二进制格式，适用于在异构平台间进行数据迁移，IXF的优点是数据占用空间小，而且包含表结构的定义，可以通过IXF文件重建表。 - WSF
已经淘汰
常用工具有export、 import、 load、db2look、db2move和db2dart
1、export
[db2inst1@19c01 ~]$ db2 "export to cy.del of del messages cy.txt select id,name from cy.t1";
Number of rows exported: 1
[db2inst1@19c01 ~]$ cat cy.del
1,"szs"
[db2inst1@19c01 ~]$ db2 "select * from cy.t1";
ID NAME
----------- ----------
1 szs
1 record(s) selected.
[db2inst1@19c01 ~]$ db2 "export to cy.ixf of ixf messages cy.txt select id,name from cy.t1";
Number of rows exported: 1
2、import
[db2inst1@19c01 ~]$ db2 "import from cy.del of del messages cy_imp.txt insert into cy.t1";
Number of rows read = 1
Number of rows skipped = 0
Number of rows inserted = 1
Number of rows updated = 0
Number of rows rejected = 0
Number of rows committed = 1
[db2inst1@19c01 ~]$ db2 "select * from cy.t1";
ID NAME
----------- ----------
1 szs
1 szs
2 record(s) selected.
3、load
[db2inst1@19c01 ~]$ db2 "load from cy.del of del messages load.msg insert into cy.t1"
Number of rows read = 2
Number of rows skipped = 0
Number of rows loaded = 2
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 2
[db2inst1@19c01 ~]$ db2 "select * from cy.t1";
ID NAME
----------- ----------
1 szs
1 szs
1 szs
2 yc
4 record(s) selected.
4、db2look+db2move
主要用于跨库迁移
db2look导出元数据
[db2inst1@19c01 ~]$ db2look -d test -e -l -o test.ddl
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- Creating DDL for table(s)
-- Output is sent to file: test.ddl
-- Binding package automatically ...
-- Bind is successful
-- Binding package automatically ...
-- Bind is successful
[db2inst1@19c01 ~]$ cat test.ddl
-- This CLP file was created using DB2LOOK Version "11.5"
-- Timestamp: 2024年01月18日 星期四 21时16分35秒
-- Database Name: TEST
-- Database Manager Version: DB2/LINUXX8664 Version 11.5.9.0
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY
-- Alternate collating sequence(alt_collate): null
-- varchar2 compatibility(varchar2_compat): OFF
CONNECT TO TEST;
------------------------------------
-- DDL Statements for BUFFERPOOLS --
------------------------------------
CREATE BUFFERPOOL "P1" SIZE 2000 PAGESIZE 8192;
CONNECT RESET;
CONNECT TO TEST;
--------------------------
-- Mimic Storage groups --
--------------------------
ALTER STOGROUP "IBMSTOGROUP"
OVERHEAD 6.725000
DEVICE READ RATE 100.000000
DATA TAG NONE
SET AS DEFAULT;
db2move导出数据
[db2inst1@19c01 ~]$ db2move test export
Application code page not determined, using ANSI codepage 1208
***** DB2MOVE *****
Action: EXPORT
Start time: Thu Jan 18 21:17:53 2024
Connecting to database TEST ... successful! Server : DB2 Common Server V11.5.9
Binding package automatically ... /home/db2inst1/sqllib/bnd/db2common.bnd ... successful!
Binding package automatically ... /home/db2inst1/sqllib/bnd/db2move.bnd ... successful!
EXPORT: 152 rows from table "SYSTOOLS"."HMON_ATM_INFO"
EXPORT: 0 rows from table "SYSTOOLS"."HMON_COLLECTION"
EXPORT: 5 rows from table "SYSTOOLS"."POLICY"
EXPORT: 4 rows from table "CY "."T1"
EXPORT: 1 rows from table "SCOTT "."TEST"
Disconnecting from database ... successful!
End time: Thu Jan 18 21:17:53 2024
5、db2dart
类似于oracle的odu。
六、redo
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) =
Second log archive method (LOGARCHMETH2) = OFF
Archive compression for logarchmeth2 (LOGARCHCOMPR2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =
Number of log archive retries on error (NUMARCHRETRY) = 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
2、日志
[db2inst1@19c01 ~]$ db2 get snapshot for database on test|grep Log
Log space available to the database (Bytes)= 103609321
Log space used by the database (Bytes) = 736279
Log pages read = 0
Log read time (sec.ns) = 0.000000000
Log pages written = 5458
Log write time (sec.ns) = 0.324447000
Log data found in buffer = 0
Log to be redone for recovery (Bytes) = 9083927
Log accounted for by dirty pages (Bytes) = 9083927
3、归档
oracle的list archive log all;
[db2inst1@19c01 ~]$ db2 list history archive log all for test
List History File for test
Number of matching file entries = 0
4、调整日志参数
db2 udpate db cfg for sample using logsecond 20
db2 udpate db cfg for sample using logprimary 20
db2 udpate db cfg for sample using logprimary 10000
七、运维
1、统计信息
收集的相关所有统计信息
[db2inst1@19c01 ~]$ db2 "RUNSTATS ON TABLE cy.t1 ON ALL COLUMNS WITH DISTRIBUTION AND DETAILED INDEXES ALL"
DB20000I The RUNSTATS command completed successfully.
同时对表和索引收集
[db2inst1@19c01 ~]$ db2 "RUNSTATS ON table cy.t1 for indexes all"
DB20000I The RUNSTATS command completed successfully.
采样10%
[db2inst1@19c01 ~]$ db2 "RUNSTATS ON TABLE cy.t1 TABLESAMPLE bernoulli(10)"
DB20000I The RUNSTATS command completed successfully.
为收集统计信息的表
db2 "select char(tabname,20) as tabname, stats_time from syscat.tables where STATS_TIME is NULL;
RUNSTATS只能收集单表，使用中需要配置脚本使用
2、碎片整理
碎片检查
[db2inst1@19c01 ~]$ db2 reorgchk on schema cy;
Doing RUNSTATS ....
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
SCHEMA.NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: CY.T1
4 0 2 2 - 88 0 2 100 ---
----------------------------------------------------------------------------------------
Index statistics:
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on non-empty leaf pages) > MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index with one less level / Amount of space required for all keys) < 100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs) < 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of leaf pages) < 20
SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD NLEAF_PAGE_OVERHEAD PCT_PAGES_SAVED F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
for indexes that are not in the same sequence as the base table. When multiple
indexes are defined on a table, one or more indexes may be flagged as needing
REORG. Specify the most important index for REORG sequencing.
Tables defined using the ORGANIZE BY clause and the corresponding dimension
indexes have a '*' suffix to their names. The cardinality of a dimension index
is equal to the Active blocks statistic of the table.
表重组
[db2inst1@19c01 ~]$ db2 reorg table cy.t1;
DB20000I The REORG command completed successfully.
在线重组
[db2inst1@19c01 ~]$ db2 reorg table cy.t1 inplace allow write access;
DB20000I The REORG command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.
重组索引
[db2inst1@19c01 ~]$ db2 reorg indexes all for table cy.t1;
3、Rebind
执行计划重绑定，db2的package编译后会把执行计划绑定到package，如果新建索引导致sql更优，可以rebind
查看包
[db2inst1@19c01 ~]$ db2 list packages;
[db2inst1@19c01 ~]$ db2 list packages for schema cy;
db2 rebind package xxxx
Rebind命令只能针对每个package，如果需要对所有package重新绑定，可以考虑用db2rbind命令。
[db2inst1@19c01 ~]$ db2rbind test -l rbind.log all
Rebind done successfully for database 'TEST'.
Package 'SYSIBMADM.P970289398' of database 'TEST' succeeded to rebind. UniqueID = 4F41676E5253426F
Package 'SYSIBMADM.P984998992' of database 'TEST' succeeded to rebind. UniqueID = 6B426A6E5253426F
Package 'SYSIBMADM.P985736971' of database 'TEST' succeeded to rebind. UniqueID = 4B41676E5253426F
Package 'SYSIBMADM.P994233900' of database 'TEST' succeeded to rebind. UniqueID = 6A42656E5253426F
Package 'SYSIBMADM.P99460893' of database 'TEST' succeeded to rebind. UniqueID = 7241686E5253426F
Package 'SYSIBMADM.P994943506' of database 'TEST' succeeded to rebind. UniqueID = 6442696E5253426F
Package 'SYSIBMADM.P995815736' of database 'TEST' succeeded to rebind. UniqueID = 3141686E5253426F
Package 'SYSIBMINTERNAL.P791454727' of database 'TEST' succeeded to rebind. UniqueID = 4341656E5253426F
Package 'SYSPROC.P390428019' of database 'TEST' succeeded to rebind. UniqueID = 4B42636E5253426F
Succeeded to rebind = 400
Failed to rebind = 0 (or more)
Ending time .... Thu Jan 18 21:50:02 2024
4、表空间占用
[db2inst1@19c01 ~]$ db2 list tablespaces show detail
Tablespaces for Current Database
Tablespace ID = 0
Name = SYSCATSPACE
Type = Database managed space
Contents = All permanent data. Regular table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 57344
Useable pages = 57340
Used pages = 49796
Free pages = 7544
High water mark (pages) = 49796
Page size (bytes) = 4096
db2 "select substr(tbsp_name,1,30) as TABLESPACE_NAME, substr(TBSP_TYPE,1,10) as
TBSP_TYPE, substr(tbsp_content_type,1,10) as
TABLESPACE_TYPE,sum(tbsp_total_size_kb)/1024 as
TOTAL_MB,sum(tbsp_used_size_kb)/1024 as USED_MB, sum(tbsp_free_size_kb)/1024 as
FREE_MB, tbsp_page_size AS PAGE_SIZE from SYSIBMADM.TBSP_UTILIZATION where
TBSP_TYPE!='SMS' group by tbsp_name,tbsp_type, tbsp_content_type,tbsp_page_size
order by 1";
[db2inst1@19c01 ~]$ db2 "select substr(tbsp_name,1,30) as TABLESPACE_NAME, substr(TBSP_TYPE,1,10) as
> TBSP_TYPE, substr(tbsp_content_type,1,10) as
> TABLESPACE_TYPE,sum(tbsp_total_size_kb)/1024 as
> TOTAL_MB,sum(tbsp_used_size_kb)/1024 as USED_MB, sum(tbsp_free_size_kb)/1024 as
> FREE_MB, tbsp_page_size AS PAGE_SIZE from SYSIBMADM.TBSP_UTILIZATION group by tbsp_name,tbsp_type, tbsp_content_type,tbsp_page_size
> order by 1";
TABLESPACE_NAME TBSP_TYPE TABLESPACE_TYPE TOTAL_MB USED_MB FREE_MB PAGE_SIZE
------------------------------ ---------- --------------- -------------------- -------------------- -------------------- -----------
SYSCATSPACE DMS ANY 224 194 29 4096
SYSTOOLSPACE DMS LARGE 32 0 31 4096
T1 DMS LARGE 32 0 31 4096
T2 DMS LARGE 100 0 98 8192
T3 DMS LARGE 64 0 63 4096
T4 DMS LARGE 11 0 9 8192
TEMPSPACE1 SMS SYSTEMP 0 0 0 4096
TEMP_SYS SMS SYSTEMP 0 0 0 8192
TEMP_USER SMS USRTEMP 0 0 0 8192
USERSPACE1 DMS LARGE 32 0 31 4096
10 record(s) selected.
5、获取某个表/索引占用空间的大小
db2pd的tcbstats可以查看表的TCB信息，其中DataSize字段用来表示表的页数，乘以页大小即为表的大小。使用该方法时，只有该表被访问过才会显示出来：
[db2inst1@19c01 ~]$ db2pd -d test -tcbstats|grep T1
0x00007F7FFD020188 4 5 n/a 4 5 T1 CY Perm 1 0 0 0 No
0x00007F7FFD020188 T1 CY 8 0 0 0 0 36 0 2 0 0 0 0 0 0 0 0 0 - -
[db2inst1@19c01 ~]$
SYSIBMADM.ADMINTABINFO管理视图
[db2inst1@19c01 ~]$ db2 describe table SYSIBMADM.ADMINTABINFO
Data type Column
Column name schema Data type name Length Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
TABSCHEMA SYSIBM VARCHAR 128 0 Yes
TABNAME SYSIBM VARCHAR 128 0 Yes
TABTYPE SYSIBM CHARACTER 1 0 Yes
DBPARTITIONNUM SYSIBM SMALLINT 2 0 Yes
DATA_PARTITION_ID SYSIBM INTEGER 4 0 Yes
AVAILABLE SYSIBM CHARACTER 1 0 Yes
DATA_OBJECT_L_SIZE SYSIBM BIGINT 8 0 Yes
DATA_OBJECT_P_SIZE SYSIBM BIGINT 8 0 Yes
INDEX_OBJECT_L_SIZE SYSIBM BIGINT 8 0 Yes
INDEX_OBJECT_P_SIZE SYSIBM BIGINT 8 0 Yes
LONG_OBJECT_L_SIZE SYSIBM BIGINT 8 0 Yes
LONG_OBJECT_P_SIZE SYSIBM BIGINT 8 0 Yes
LOB_OBJECT_L_SIZE SYSIBM BIGINT 8 0 Yes
LOB_OBJECT_P_SIZE SYSIBM BIGINT 8 0 Yes
XML_OBJECT_L_SIZE SYSIBM BIGINT 8 0 Yes
XML_OBJECT_P_SIZE SYSIBM BIGINT 8 0 Yes
INDEX_TYPE SYSIBM SMALLINT 2 0 Yes
REORG_PENDING SYSIBM CHARACTER 1 0 Yes
INPLACE_REORG_STATUS SYSIBM VARCHAR 10 0 Yes
LOAD_STATUS SYSIBM VARCHAR 12 0 Yes
READ_ACCESS_ONLY SYSIBM CHARACTER 1 0 Yes
NO_LOAD_RESTART SYSIBM CHARACTER 1 0 Yes
NUM_REORG_REC_ALTERS SYSIBM SMALLINT 2 0 Yes
INDEXES_REQUIRE_REBUILD SYSIBM CHARACTER 1 0 Yes
LARGE_RIDS SYSIBM CHARACTER 1 0 Yes
LARGE_SLOTS SYSIBM CHARACTER 1 0 Yes
DICTIONARY_SIZE SYSIBM BIGINT 8 0 Yes
BLOCKS_PENDING_CLEANUP SYSIBM BIGINT 8 0 Yes
STATSTYPE SYSIBM CHARACTER 1 0 Yes
XML_RECORD_TYPE SYSIBM SMALLINT 2 0 Yes
RECLAIMABLE_SPACE SYSIBM BIGINT 8 0 Yes
XML_DICTIONARY_SIZE SYSIBM BIGINT 8 0 Yes
AMT_STATUS SYSIBM VARCHAR 12 0 Yes
SPARSE_BLOCKS SYSIBM BIGINT 8 0 Yes
STATS_ROWS_MODIFIED SYSIBM BIGINT 8 0 Yes
RTS_ROWS_MODIFIED SYSIBM BIGINT 8 0 Yes
STATS_DBPARTITION SYSIBM CHARACTER 3 0 Yes
COL_OBJECT_L_SIZE SYSIBM BIGINT 8 0 Yes
COL_OBJECT_P_SIZE SYSIBM BIGINT 8 0 Yes
STATSPROFTYPE SYSIBM CHARACTER 1 0 Yes
TENANT_ID SYSIBM BIGINT 8 0 Yes
TENANT_NAME SYSIBM VARCHAR 128 0 Yes
42 record(s) selected.
[db2inst1@19c01 ~]$ db2 "SELECT (data_object_p_size+index_object_p_size+long_object_p_size+lob_object_p_size + xml_object_p_size) as total_p_size FROM sysibmadm.admintabinfo where tabname='T1' "
TOTAL_P_SIZE
--------------------
256
1 record(s) selected.
八、监控
1、找出执行时间sql
db2 "SELECT rows_read / (num_executions + 1) as avg_rows_read,
rows_written / (num_executions + 1) as avg_rows_written,
stmt_sorts / (num_executions + 1) as avg_sorts,
total_exec_time / (num_executions + 1) as avg_exec_time,
substr(stmt_text,1,1000) as SQL_Stmt
FROM SYSIBMADM.SNAPDYN_SQL ORDER BY avg_exec_time desc fetch first 20 rows only"
2、找出排序最多sql
db2 "SELECT stmt_sorts / (num_executions + 1) as avg_sorts,
total_exec_time / (num_executions + 1) as avg_exec_time,
substr(stmt_text,1,1000) as SQL_Stmt
FROM SYSIBMADM.SNAPDYN_SQL ORDER BY avg_sorts desc fetch first 20 rows only"
3、db2pd监控工具
1、-appl监控引用程序
与db2 list applications show detail命令类似
[db2inst1@19c01 ~]$ db2pd -db test -appl
Database Member 0 -- Database TEST -- Active -- Up 0 days 04:21:29 -- Date 2024-01-18-22.07.30.607321
Applications:
Address AppHandl [nod-index] NumAgents CoorEDUID Status C-AnchID C-StmtUID L-AnchID L-StmtUID Appid WorkloadID WorkloadOccID CollectActData CollectActPartition CollectSectionActuals
0x0000000203E40080 131 [000-00131] 1 224 ConnectCompleted 0 0 0 0 *LOCAL.DB2.240118094609 0 0 N C N
0x0000000203EEA8C0 124 [000-00124] 1 217 ConnectCompleted 0 0 0 0 *LOCAL.DB2.240118094602 0 0 N C N
0x0000000203BB0220 130 [000-00130] 1 223 ConnectCompleted 0 0 0 0 *LOCAL.DB2.240118094608 0 0 N C N
0x0000000202ED8240 123 [000-00123] 1 94 UOW-Waiting 0 0 350 1 *LOCAL.db2inst1.240118094601 1 1 N C N
0x0000000203B98980 129 [000-00129] 1 222 ConnectCompleted 0 0 0 0 *LOCAL.DB2.240118094607 0 0 N C N
0x0000000203790220 135 [000-00135] 1 228 ConnectCompleted 0 0 0 0 *LOCAL.DB2.240118094613 0 0 N C N
0x000000020391F1C0 128 [000-00128] 1 221 ConnectCompleted 0 0 0 0 *LOCAL.DB2.240118094606
2、-edus
该参数输出系统中所有EDU的列表，其中最后两列代表着该EDU自从启动以来所执行的用户CPU与系统CPU时间，可以用来监测EDU的CPU消耗率
该输出为累计值
[db2inst1@19c01 ~]$ db2pd -edus
Database Member 0 -- Active -- Up 0 days 05:24:51 -- Date 2024-01-18-22.08.12.125028
List of all EDUs for database member 0
db2sysc PID: 375296
db2wdog PID: 375294
db2acd PID: 375321
EDU ID TID Kernel TID EDU Name USR (s) SYS (s)
===================================================================================================================
236 140190223951616 623589 db2agent (idle) 0 5.410000 2.510000
229 140190207174400 450127 db2agent (instance) 0 0.100000 0.200000
228 140190244923136 447099 db2evmgi (DB2DETAILDEADLOCK) 0 0.000000 0.000000
227 140190190397184 447098 db2cmpd (TEST) 0 0.450000 0.350000
226 140190215563008 447097 db2mcd (TEST) 0 0.130000 1.220000
224 140190194591488 447095 db2pcsd (TEST) 0 0.880000 0.150000
223 140190219757312 447094 db2fw1 (TEST) 0 0.000000 0.000000
222 140190198785792 447093 db2fw0 (TEST) 0 0.000000 0.000000
221 140190265894656 447092 db2dbctrld (TEST) 0 1.270000 0.290000
220 140190202980096 447091 db2lused (TEST) 0 0.610000 0.330000
219 140190278477568 447090 db2wlmd (TEST) 0 0.000000 0.000000
218 140190182008576 447089 db2taskd (TEST) 0 0.370000 0.470000
217 140190211368704 447088 db2stmm (TEST) 0 0.510000 0.430000
216 140190236534528 447087 db2pfchr (TEST) 0 0.000000 0.000000
215 140190232340224 447086 db2pfchr (TEST) 0 0.000000 0.010000
214 140190228145920 447085 db2pfchr (TEST) 0 0.010000 0.020000
213 140190274283264 447084 db2pclnr (TEST) 0 0.010000 0.000000
212 140190249117440 447083 db2pclnr (TEST) 0 0.010000 0.000000
211 140190257506048 447082 db2lfr.0 (TEST) 0 2.370000 1.800000
可获取该段时间之内的CPU消耗量，输出结果中USR DELTA与SYS DELTA列是用户CPU与系统CPU对于该线程的消耗差。
[db2inst1@19c01 ~]$ db2pd -edus interval=3
Database Member 0 -- Active -- Up 0 days 05:26:37 -- Date 2024-01-18-22.09.58.666551
List of all EDUs for database member 0
db2sysc PID: 375296
db2wdog PID: 375294
db2acd PID: 375321
EDU ID TID Kernel TID EDU Name USR (s) SYS (s) USR DELTA SYS DELTA
===============================================================================================================================================
236 140190223951616 623589 db2agntdp (TEST ) 0 5.410000 2.520000 0.000000 0.000000
1 140190064568064 375299 db2sysc 0 2.560000 0.090000 0.000000 0.000000
11 140190324614912 375301 db2alarm 0 0.160000 3.160000 0.000000 0.000000
12 140190320420608 375302 db2thcln 0 0.000000 0.000000 0.000000 0.000000
13 140190316226304 375303 db2licc 0 0.000000 0.000000 0.000000 0.000000
14 140190312032000 375304 db2wlmt 0 0.090000 0.180000 0.000000 0.000000
15 140190307837696 375308 db2wlmtm 0 0.520000 0.200000 0.000000 0.000000
16 140190303643392 375314 db2ipccm 0 0.010000 0.030000 0.000000 0.000000
17 140190299449088 375315 db2tcpcm 0 0.000000 0.000000 0.000000 0.000000
18 140190295254784 375316 db2tcpcm 0 0.000000 0.000000 0.000000 0.000000
19 140190291060480 375317 db2resync 0 0.240000 0.710000 0.000000 0.000000
20 140190286866176 375318 db2spmrsy 0 0.000000 0.000000 0.000000 0.000000
3、 -osinfo（操作系统信息）
[db2inst1@19c01 ~]$ db2pd -osinfo
Operating System Information:
OSName: Linux
NodeName: 19c01
Version: 5
Release: 4
Machine: x86_64
Distros: Red Hat Enterprise Linux Server 8.5
CPU Information:
TotalCPU OnlineCPU ConfigCPU Speed(MHz) HMTDegree Cores/Socket
2 2 2 2400 1 1
CPU Cache Information:
L2 L3
262144 8388608
Physical Memory and Swap (Megabytes):
TotalMem FreeMem AvailMem TotalSwap FreeSwap
7642 331 n/a 8192 8190
Virtual Memory (Megabytes):
Total Reserved Available Free
15834 n/a n/a 8521
Message Queue Information:
MsgSeg MsgMax MsgMap MsgMni MsgTql MsgMnb MsgSsz
n/a 65536 65536 32768 65536 65536 16
Shared Memory Information:
ShmMax ShmMin ShmIds ShmSeg
4398046511104 1 32768 32768
Semaphore Information:
SemMap SemMni SemMns SemMnu SemMsl SemOpm SemUme SemUsz SemVmx SemAem
6400000 32768 6400000 6400000 250 1000 n/a 20 32767 32767
CPU Load Information:
Short Medium Long
0.080000 0.090000 0.090000
CPU Usage Information (percent):
Total Usr Sys Wait Idle
1.000000 1.000000 0.000000 0.000000 98.500000
4、-bufferpool（缓冲池信息）
[db2inst1@19c01 ~]$ db2pd -db test -bufferpool
Database Member 0 -- Database TEST -- Active -- Up 0 days 04:26:38 -- Date 2024-01-18-22.12.39.442958
Bufferpools:
First Active Pool ID 1
Max Bufferpool ID 2
Max Bufferpool ID on Disk 2
Num Bufferpools 6
Address Id Name PageSz PA-NumPgs BA-NumPgs BlkSize NumTbsp PgsToRemov CurrentSz PostAlter SuspndTSCt Automatic
0x00007F7F9D628700 1 IBMDEFAULTBP 4096 298273 0 0 6 0 298273 298273 0 True
0x00007F7FFCFAAF60 2 P1 8192 2000 0 0 5 0 2000 2000 0 False
0x00007F7F9D5F4180 4096 IBMSYSTEMBP4K 4096 16 0 0 0 0 16 16 0 False
0x00007F7F9D6012E0 4097 IBMSYSTEMBP8K 8192 16 0 0 0 0 16 16 0 False
0x00007F7F9D60E440 4098 IBMSYSTEMBP16K 16384 16 0 0 0 0 16 16 0 False
0x00007F7F9D61B5A0 4099 IBMSYSTEMBP32K 32768 16 0 0 0 0 16 16 0 False
Bufferpool Statistics for all bufferpools (when BUFFERPOOL monitor switch is ON):
BPID DatLRds DatPRds HitRatio TmpDatLRds TmpDatPRds HitRatio IdxLRds IdxPRds HitRatio TmpIdxLRds TmpIdxPRds HitRatio
1 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
2 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
4096 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
4097 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
4098 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
4099 0 0 00.00% 0 0 00.00% 0 0 00.00% 0 0 00.00%
BPID DataWrts IdxWrts DirRds DirRdReqs DirRdTime DirWrts DirWrtReqs DirWrtTime
1 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0
4096 0 0 0 0 0 0 0 0
4097 0 0 0 0 0 0 0 0
4098 0 0 0 0 0 0 0 0
4099 0 0 0 0 0 0 0 0
BPID AsDatRds AsDatRdReq AsIdxRds AsIdxRdReq AsRdTime AsDatWrts AsIdxWrts AsWrtTime
1 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0
4096 0 0 0 0 0 0 0 0
4097 0 0 0 0 0 0 0 0
4098 0 0 0 0 0 0 0 0
4099 0 0 0 0 0 0 0 0
BPID TotRdTime TotWrtTime VectIORds VectIOReq BlockIORds BlockIOReq FilesClose NoVictAvl UnRdPFetch
1 0 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0 0
4096 0 0 0 0 0 0 0 0 0
4097 0 0 0 0 0 0 0 0 0
4098 0 0 0 0 0 0 0 0 0
4099 0 0 0 0 0 0 0 0 0
5、 -logs（日志信息）
[db2inst1@19c01 ~]$ db2pd -db test -log
Database Member 0 -- Database TEST -- Active -- Up 0 days 04:27:23 -- Date 2024-01-18-22.13.24.837145
Logs:
Current Log Number 6
Pages Written 224
Cur Commit Disk Log Reads 0
Cur Commit Total Log Reads 0
Method 1 Archive Status n/a
Method 1 Next Log to Archive n/a
Method 1 First Failure n/a
Method 2 Archive Status n/a
Method 2 Next Log to Archive n/a
Method 2 First Failure n/a
Extraction Status n/a (0)
Extraction Throttle Reason n/a
Current Log to Extract n/a
Log Chain ID 0
Current LSO 74966679
Current LSN 0x0000000000072721
Address StartLSN StartLSO State Size Pages Filename
0x00007F7F9D57F470 0000000000060B66 65705121 0x00000000 1024 1024 S0000004.LOG
0x00007F7F9D57FDD0 000000000006901A 69878945 0x00000000 1024 1024 S0000005.LOG
0x00007F7F9D580730 0000000000070E15 74052769 0x00000000 1024 1024 S0000006.LOG
0x00007F7F9D581090 0000000000000000 78226593 0x00000000 1024 1024 S0000007.LOG
0x00007F7F9D5819F0 0000000000000000 82400417 0x00000000 1024 1024 S0000008.LOG
0x00007F7F9D582350 0000000000000000 86574241 0x00000000 1024 1024 S0000009.LOG
0x00007F7F9D582CB0 0000000000000000 90748065 0x00000000 1024 1024 S0000010.LOG
0x00007F7F9D583610 0000000000000000 94921889 0x00000000 1024 1024 S0000011.LOG
0x00007F7F9D583F70 0000000000000000 99095713 0x00000000 1024 1024 S0000012.LOG
0x00007F7F9D57C670 0000000000000000 103269537 0x00000000 1024 1024 S0000000.LOG
0x00007F7F9D57D850 0000000000000000 107443361 0x00000000 1024 1024 S0000001.LOG
0x00007F7F9D57E1B0 0000000000000000 111617185 0x00000000 1024 1024 S0000002.LOG
0x00007F7F9D57EB10 0000000000000000 115791009 0x00000000 1024 1024 S0000003.LOG
6、-tablespaces（表空间信息）
[db2inst1@19c01 ~]$ db2pd -db test -tablespaces
Database Member 0 -- Database TEST -- Active -- Up 0 days 04:27:46 -- Date 2024-01-18-22.13.47.670246
Tablespace Configuration:
Address Id Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe LastConsecPg RSE Name
0x00007F7FA1EA4540 0 DMS Regular 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSCATSPACE
0x00007F7FA1EB1A00 1 SMS SysTmp 4096 32 Yes 32 1 1 On 1 0 31 No TEMPSPACE1
0x00007F7FA1EBEEC0 2 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes USERSPACE1
0x00007F7FFAA9B2C0 3 DMS Large 4096 4 Yes 4 1 1 Def 1 0 3 Yes SYSTOOLSPACE
0x00007F7FFCFC0040 4 DMS Large 4096 32 Yes 32 1 1 Def 1 0 31 Yes T1
0x00007F7FFCFCF620 5 DMS Large 8192 32 Yes 96 2 2 Def 4 1 31 Yes T2
0x00007F7FFCFDDE20 6 SMS SysTmp 8192 32 Yes 32 2 2 On 1 0 31 No TEMP_SYS
0x00007F7FFCFED300 7 SMS UsrTmp 8192 32 Yes 32 2 2 On 1 0 31 No TEMP_USER
0x00007F7FFCFFC7E0 8 DMS Large 4096 32 Yes 64 1 1 Def 2 0 31 Yes T3
0x00007F7FFD011060 9 DMS Large 8192 32 Yes 64 2 2 Def 4 1 31 Yes T4
7、 -locks（锁信息）
该参数可以列出数据库中所有的锁列表。这个参数对系统所造成的性能影响远小于繁忙系统中的锁快照，当使用该参数与-applications和-agents结合时可以用来诊断系统中锁相关的问题。
[db2inst1@19c01 ~]$ db2pd -db test -locks
Database Member 0 -- Database TEST -- Active -- Up 0 days 04:29:14 -- Date 2024-01-18-22.15.15.205255
Locks:
Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID
8、-agents（代理信息）
参数可以得到系统中的代理信息，当系统使用多分区或分区内并行时，每一个应用程序可能会对应若干个代理线程。使用该参数可以将所有的代理线程与应用程序互相对应。
[db2inst1@19c01 ~]$ db2pd -db test -agents
Option -agents is an instance scope option. The database option has been ignored.
Database Member 0 -- Active -- Up 0 days 05:32:27 -- Date 2024-01-18-22.15.48.348156
Agents:
Current agents: 15
Idle agents: 0
Active coord agents: 14
Active agents total: 14
Pooled coord agents: 1
Pooled agents total: 1
Address AppHandl [nod-index] AgentEDUID Priority Type State ClientPid Userid ClientNm Rowsread Rowswrtn LkTmOt DBName LastApplId LastPooled
0x0000000203EA0080 90 [000-00090] 95 0 Coord Inst-Active 439948 db2inst1 db2bp 0 0 NotSet n/a *LOCAL.DB2.240118093926 Thu Jan 18 17:39:15
0x0000000203970080 123 [000-00123] 94 0 Coord Inst-Active 439948 db2inst1 db2bp 3257 251 NotSet TEST *LOCAL.db2inst1.240118094601 Thu Jan 18 17:43:23
0x0000000202C0FDC0 124 [000-00124] 217 0 Coord Inst-Active 439948 db2inst1 db2stmm 0 0 NotSet TEST *LOCAL.DB2.240118094602 n/a
0x0000000202C1FC60 125 [000-00125] 218 0 Coord Inst-Active 439948 db2inst1 db2taskd 12 0 NotSet TEST *LOCAL.DB2.240118094603 n/a
0x000000020397FF20 126 [000-00126] 219 0 Coord Inst-Active 439948 db2inst1 db2wlmd 0 0 NotSet TEST *LOCAL.DB2.240118094604 n/a
0x000000020398FDC0 127 [000-00127] 220 0 Coord Inst-Active 439948 db2inst1 db2lused 0 203 3 TEST *LOCAL.DB2.240118094605 n/a
0x000000020399FC60 128 [000-00128] 221 0 Coord Inst-Active 439948 db2inst1 db2dbctr 0 0 NotSet TEST *LOCAL.DB2.240118094606 n/a
0x0000000203EAFF20 129 [000-00129] 222 0 Coord Inst-Active 439948 db2inst1 db2fw0 0 0 3 TEST *LOCAL.DB2.240118094607 n/a
0x0000000203EBFDC0 130 [000-00130] 223 0 Coord Inst-Active 439948 db2inst1 db2fw1 0 0 3 TEST *LOCAL.DB2.240118094608 n/a
0x0000000203ECFC60 131 [000-00131] 224 0 Coord Inst-Active 439948 db2inst1 db2pcsd 0 0 NotSet TEST *LOCAL.DB2.240118094609 n/a
0x00000002038BFF20 133 [000-00133] 226 0 Coord Inst-Active 439948 db2inst1 db2mcd 0 0 NotSet TEST *LOCAL.DB2.240118094611 n/a
0x00000002038CFDC0 134 [000-00134] 227 0 Coord Inst-Active 439948 db2inst1 db2cmpd 0 0 NotSet TEST *LOCAL.DB2.240118094612 n/a
0x00000002038DFC60 135 [000-00135] 228 0 Coord Inst-Active 439948 db2inst1 db2evmg_ 0 0 3 TEST *LOCAL.DB2.240118094613 n/a
0x00000002038B0080 512 [000-00512] 229 0 Coord Inst-Active 375321 db2inst1 db2acd 0 0 0 0 n/a n/a Thu Jan 18 22:13:23
0x0000000203830080 0 [000-00000] 236 0 Coord Pooled n/a n/a n/a 0 0 5 TEST *LOCAL.db2inst1.240118141328
9、-static（静态语句）
该参数会列出系统中存在的静态语句包的信息，以及每个包所执行的次数。通过该信息，用户可以估算出哪些静态存储过程被调用的次数最多。
[db2inst1@19c01 ~]$ db2pd -db test -static
Database Member 0 -- Database TEST -- Active -- Up 0 days 04:30:43 -- Date 2024-01-18-22.16.44.550111
Static Cache:
Current Memory Used 1817442
Total Heap Size 12141854
Cache Overflow Flag 0
Number of References 2907
Number of Package Inserts 814
Number of Section Inserts 8
Packages:
Address Schema PkgName Version UniqueID TenantID NumSec UseCount NumRef Iso QOpt Blk Lockname
0x00007F7FF39A91A0 NULLID SYSSH200 SYSLVL01 0 0 0 10 CS 5 B 5359534C564C3031C0115A2AC1
0x00007F7FFCF3EC20 NULLID SQLC2P31 AAAAAGFk 0 0 0 7 CS 5 B 414141414147466B66FCCCC6C1
0x00007F7FFCF3D420 NULLID SYSSH100 SYSLVL01 0 0 0 46 UR 5 B 5359534C564C3031C177B8B2C1
0x00007F7FF39AEF80 NULLID SYSLH202 SYSLVL01 0 0 0 5 CS 5 B 5359534C564C3031A94ADB96C1
Sections:
Address Schema PkgName UniqueID SecNo NumRef UseCount StmtType Cursor W-Hld Sect Size Num Copies
0x00007F7FFAB0F848 NULLID SQLC2P31 AAAAAGFk 204 0 0 13 NO 0 0
0x00007F7FFAB0FC00 NULLID SQLC2P31 AAAAAGFk 205 0 0
10、-dynamic（动态SQL信息）
[db2inst1@19c01 ~]$ db2pd -db test -dynamic
Database Member 0 -- Database TEST -- Active -- Up 0 days 04:31:40 -- Date 2024-01-18-22.17.41.506001
Dynamic Cache:
Current Memory Used 1817442
Total Heap Size 12141854
Cache Overflow Flag 0
Number of References 3128
Number of Statement Inserts 395
Number of Statement Deletes 355
Number of Variation Inserts 375
Number of Statements 40
Dynamic SQL Statements:
Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text
0x00007F7FFCF3CCA0 22 3 1 1 15 15 SET TENANT ?
0x00007F7FFE354000 28 1 0 0 4 4 SELECT COLNO FROM SYSCAT.COLUMNS WHERE TABSCHEMA='CY' AND TABNAME='T1' AND (TYPENAME='BINARY' OR TYPENAME='VARBINARY')
0x00007F7FFE38B2E0 43 1 1 1 1 1 select substr(tbsp_name,1,30) as TABLESPACE_NAME, substr(TBSP_TYPE,1,10) as TBSP_TYPE, substr(tbsp_content_type,1,10) as TABLESPACE_TYPE,sum(tbsp_total_size_kb)/1024 as TOTAL_MB,sum(tbsp_used_size_kb)/1024 as USED_MB, sum(tbsp_free_size_kb)/1024 as FREE_MB, tbsp_page_size AS PAGE_SIZE from SYSIBMADM.TBSP_UTILIZATION where TBSP_TYPE!='SMS' group by tbsp_name,tbsp_type, tbsp_content_type,tbsp_page_size order by 1
0x00007F7FFE392AE0 94 1 0 0 2 1 LOCK TABLE CY.T1 IN EXCLUSIVE MODE
0x00007F7FFCF3F000 127 2 1 1 1 1 SELECT COUNT(DISTINCT NODENUM) FROM SYSIBM.SYSNODEGROUPDEF
0x00007F7FFE386020 132 1 0 0 1 0 DELETE FROM CY.T1
0x00007F7FFE39F9C0 148 1 0 0 1 1 SELECT NAME, IDENTITY, GENERATED, HIDDEN, PARTKEYSEQ FROM SYSIBM.SYSCOLUMNS A, TABLE(SYSPROC.BASE_TABLE('CY','T1')) B where A.TBNAME=B.BASENAME AND A.TBCREATOR=B.BASESCHEMA AND (GENERATED = 'A' OR IDENTITY = 'Y' OR IDENTITY = 'T' OR HIDDEN = 'I' OR (HIDDEN = 'S' AND NAME IN ('CREAT
11、-tcbstats（表状态信息)
[db2inst1@19c01 ~]$ db2pd -db test -tcbstats
Database Member 0 -- Database TEST -- Active -- Up 0 days 04:32:29 -- Date 2024-01-18-22.18.30.340661
TCB Table Information:
Address TbspaceID TableID PartID MasterTbs MasterTab TableName SchemaNm ObjClass DataSize LfSize LobSize XMLSize IxReqRebld
0x00007F7FF783CE08 0 1 n/a 0 1 SYSBOOT SYSIBM Perm 1 0 0 0 No
0x00007F7FFCFDD088 5 -1 n/a 5 -1 Perm 1 0 0 0 No
0x00007F7FFAA3B688 0 5 n/a 0 5 SYSTABLES SYSIBM Perm 61 0 1024 0 No
0x00007F7FFD00B888 8 -1 n/a 8 -1 Perm 1 0 0 0 No
0x00007F7FFAAB0108 3 4 n/a 3 4 POLICY SYSTOOLS Perm 1 0 64 0 No
0x00007F7FFAA85288 0 6 n/a 0 6 SYSCOLUMNS SYSIBM Perm 378 0 1 0 No
0x00007F7FFCFA3B08 3 5 n/a 3 5 HMON_ATM_INFO SYSTOOLS Perm 15 0 0 0 No
0x00007F7FFAABD608 0 7 n/a 0 7 SYSINDEXES SYSIBM Perm 44 0 1 0 No
0x00007F7FFCFA6008 3 6 n/a 3 6 HMON_COLLECTION SYSTOOLS Perm 1 0 0 0 No
0x00007F7FFAA8BE08 0 8 n/a 0 8 SYSCOLPROPERTIES SYSIBM Perm 1 0 0 0 No
0x00007F7FFAAC1488 0 9 n/a 0 9 SYSINDEXCOLUSE SYSIBM Perm 18 0 1 0 No
0x00007F7FFAAB8B88 0 10 n/a 0 10 SYSDATAPARTITIONS SYSIBM Perm 8 0 0 0 No
4、db2top
类似与oratop
db2top -d test
[-]22:19:49,refresh=2secs(0.001) Sessions Linux,member=[1/1],DB2INST1:TEST
[d=Y,a=N,e=N,p=ALL] [qp=off]
┌──────────────┬────────────┬────────────┬────────────┬───────────┐
Local sessions...: 1 │ │ 25%│ 50%│ 75%│ 100%│ Local in exec....: 0
Remote sessions..: 11 │ActSessions │ │ Remote in exec...: 0
FCM buffers sent.: 0 │Sys/Usr Cpu% │ │ Active utilities.: 0
FCM buffers rec..: 0 │r+w/sec% │ │ FCM buffers LWM..: 128
Piped sorts req..: 0 └──────────────┴──────────────────────────────────────────────────┘ Piped sorts acc..: 0
Max Agents.......: 0 Agents stolen....: 0 Agent overflows..: 0 Connections HWM..: 16
Sheapthres.......: 0 Max Sort Heap....: 0 Sort heap alloc..: 0 Private memory...: 21.9M
Dynamic SQL stmts: 0 Static SQL stmts.: 0 Post hash joins..: 0 Post sorts.......: 0
Bufferpool reads.: 0 Bufferpool writes: 0 Direct reads.....: 0 Direct writes....: 0
Application Cpu% IO% Mem% Application Application Delta Delta Delta Delta Delta Sess Assoc.
Handle(Stat) Total Total Total Status Name RowsRead/s RowsWritten/s IOReads/s IOWrites/s TQr+w/s Memory Agents
------------ ------- ------- ------- ----------------------------------- --------------- -------------- -------------- -------------- -------------- -------------- -------- ------
123(i) 0.00% 0.00% 25.76% UOW Waiting in the application db2bp 0 0 0 0 0 1.0M 1
后面部分整理自《DB2数据库管理最佳实践》作者：徐明伟,王涛 电子工业出版社
最后修改时间：2024-01-21 19:39:58
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
