DB2大型数据库创建实例、修改配置_db2创建实例-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_61703384/article/details/139361623

实验目的：

创建一个新的DB2实例

运行db2icrt命令创建一个新的DB2数据库管理器实例
发出db2set和db2 update命令来配置db2实例
使用db2start和db2stop命令启动和停止DB2实例
运行db2pd命令检查DB2实例配置和状态

登录DB2所在的系统并查看当前DB2所在的实例

db2inst1用户是安装DB2数据库创建的用户，后续也可以通过新的组与用户来创建DB2用户。

切换到DB2用户登录到Linux系统。

su - db2inst1

输入密码即登陆成功。

查看当前实例：

db2 get instance

输出：

The current database manager instance is: db2inst1

列出当前实例的数据库相关目录：

db2 list db directory

输出：

System Database Directory

Number of entries in the directory = 2

Database 1 entry:

Database alias = MUSICDB

Database name = MUSICDB

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 =

Database 2 entry:

Database alias = SAMPLE

Database name = SAMPLE

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用户，二者选其一执行即可，没有报错即

set db2instance=db2inst1

db2set db2instdef=db2inst1

创建新的DB2实例inst23

在Windows系统上可以直接使用db2icrt inst23命令创建新的数据库管理实例，创建完成后需要set db2instance=inst23或db2set db2instdef=inst23设置当前的实例，设置完成后可以使用 db2set -all命令看到设置结果。

Linux可以通过创建新用户、组的方式来创建新实例。

登录DB2后需要执行exit指令退出DB2登录，然后在Linux操作空间执行以下命令：

先切换到DB2的安装目录，我的安装目录是/opt/ibm/db2/V11.5/instance/。

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

创建新的实例inst23前先创建新的组inst23：

sudo groupadd inst23

然后创建新用户inst23:

sudo useradd -d /home/inst23 -m inst23 -g inst23

修改inst23用户的密码：

sudo passwd db2inst23

输入两次密码即可完成新用户密码的设置。

现在可以创建新的实例inst23：

sudo ./in2icrt -u inst23 inst23

在Linux使用以下命令创建实例：

b2icrt inst23

输出：

-sh: 3: db2icrt: not found

报错原因：这条命令式Windows的。但笔者是在Linux安装的DB2，需要通过创建新组和用户来创建新实例。

检验创建新用户与实例成功：

先切换用户：

su - inst23

然后启动数据库实例：

db2start

关停数据库实例：

db2stop

设置当前实例为db2inst1

set db2instance=db2inst1

没有输出，没有报错就是成功了。

也可以使用命令：

db2set db2instdef=db2inst1

但是Linux系统报错了，

DBI1309E System error.

Explanation:

The tool encountered an operating system error.

User response:

A system error was encountered during registry access. Ensure that there

is enough space on the file system where the registry is located, and

that there is a valid LAN connection if the registry is remote.

查看当前实例的设置

db2set -all

输出：

[i] DB2COMM=TCPIP

[g] DB2SYSTEM=VM-4-2-ubuntu

允许tcpip应用程序连接实例

设置当前实例启用客户机TCP/IP通信

b2set db2comm=tcpip

再查看设置：

db2set -all

输出：

[i] DB2COMM=TCPIP

[g] DB2SYSTEM=VM-4-2-ubuntu

有“[i] DB2COMM=TCPIP”说明设置成功了。

设置TCP/IP端口号为50230（只要不是默认的50000，一个65535以内的大数字即可）

db2 update dbm cfg using svcename 50230

这里由于后续需要连接DMC程序，不修改端口了。

修改端口后需要重启实例：

db2start

输出：

05/31/2024 21:30:28 0 0 SQL1026N The database manager is already active.

SQL1026N The database manager is already active.

查看数据库的管理器配置

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) = 9.446887e-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

   Sort (DFT_MON_SORT) = OFF

   Statement (DFT_MON_STMT) = OFF

   Table (DFT_MON_TABLE) = OFF

   Timestamp (DFT_MON_TIMESTAMP) = ON

   Unit of work (DFT_MON_UOW) = OFF

Monitor health of instance and databases (HEALTH_MON) = OFF

SYSADM group name (SYSADM_GROUP) = DB2ADM1

SYSCTRL group name (SYSCTRL_GROUP) =

SYSMAINT group name (SYSMAINT_GROUP) =

SYSMON group name (SYSMON_GROUP) =

Client Userid-Password Plugin (CLNT_PW_PLUGIN) =

Client Kerberos Plugin (CLNT_KRB_PLUGIN) =

Group Plugin (GROUP_PLUGIN) =

GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =

Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED

Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =

Server Userid-Password Plugin (SRVCON_PW_PLUGIN) =

Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED

Cluster manager =

Database manager authentication (AUTHENTICATION) = SERVER

Alternate authentication (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED

Cataloging allowed without authority (CATALOG_NOAUTH) = NO

Trust all clients (TRUST_ALLCLNTS) = YES

Trusted client authentication (TRUST_CLNTAUTH) = CLIENT

Bypass federated authentication (FED_NOAUTH) = NO

Default database path (DFTDBPATH) = /home/db2inst1

Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(90)

Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 65536

Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0

Global instance memory (% or 4KB) (INSTANCE_MEMORY) = AUTOMATIC(383361)

Member instance memory (% or 4KB) = GLOBAL

Agent stack size (AGENT_STACK_SZ) = 1024

Sort heap threshold (4KB) (SHEAPTHRES) = 0

Directory cache support (DIR_CACHE) = YES

Application support layer heap size (4KB) (ASLHEAPSZ) = 15

Max requester I/O block size (bytes) (RQRIOBLK) = 65535

Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

Priority of agents (AGENTPRI) = SYSTEM

Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(100)

Initial number of agents in pool (NUM_INITAGENTS) = 0

Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(200)

Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)

Keep fenced process (KEEPFENCED) = YES

Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)

Initial number of fenced processes (NUM_INITFENCED) = 0

Index re-creation time and redo index build (INDEXREC) = RESTART

Transaction manager database name (TM_DATABASE) = 1ST_CONN

Transaction resync interval (sec) (RESYNC_INTERVAL) = 180

SPM name (SPM_NAME) = VM_4_2_u

SPM log size (SPM_LOG_FILE_SZ) = 256

SPM resync agent limit (SPM_MAX_RESYNC) = 20

SPM log path (SPM_LOG_PATH) =

TCP/IP Service name (SVCENAME) = 50000

Discovery mode (DISCOVER) = SEARCH

Discover server instance (DISCOVER_INST) = ENABLE

SSL server keydb file (SSL_SVR_KEYDB) =

SSL server stash file (SSL_SVR_STASH) =

SSL server certificate label (SSL_SVR_LABEL) =

SSL service name (SSL_SVCENAME) =

SSL cipher specs (SSL_CIPHERSPECS) =

SSL versions (SSL_VERSIONS) =

SSL client keydb file (SSL_CLNT_KEYDB) =

SSL client stash file (SSL_CLNT_STASH) =

Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY

Enable intra-partition parallelism (INTRA_PARALLEL) = NO

Maximum Asynchronous TQs per query (FEDERATED_ASYNC) = 0

Number of FCM buffers (FCM_NUM_BUFFERS) = AUTOMATIC(4096)

FCM buffer size (FCM_BUFFER_SIZE) = 32768

Number of FCM channels (FCM_NUM_CHANNELS) = AUTOMATIC(2048)

FCM parallelism (FCM_PARALLELISM) = AUTOMATIC(2)

Node connection elapse time (sec) (CONN_ELAPSE) = 10

Max number of node connection retries (MAX_CONNRETRIES) = 5

Max time difference between nodes (min) (MAX_TIME_DIFF) = 60

db2start/db2stop timeout (min) (START_STOP_TIME) = 10

WLM dispatcher enabled (WLM_DISPATCHER) = NO

WLM dispatcher concurrency (WLM_DISP_CONCUR) = COMPUTED

WLM dispatcher CPU shares enabled (WLM_DISP_CPU_SHARES) = NO

WLM dispatcher min. utilization (%) (WLM_DISP_MIN_UTIL) = 5

Communication buffer exit library list (COMM_EXIT_LIST) =

Current effective arch level (CUR_EFF_ARCH_LVL) = V:11 R:5 M:4 F:0 I:0 SB:0

Current effective code level (CUR_EFF_CODE_LVL) = V:11 R:5 M:4 F:0 I:0 SB:0

Keystore type (KEYSTORE_TYPE) = NONE

Keystore location (KEYSTORE_LOCATION) =

Path to python runtime (PYTHON_PATH) =

或者使用快捷方式查看端口号：

db2 get dbm cfg | grep "SVCENAME"

输出：

TCP/IP Service name (SVCENAME) = 50000

SSL service name (SSL_SVCENAME) =

这里笔者没有改端口，所以还是默认的50000。

Linux的过滤功能使用”grep”，Windows过滤使用“find”，如db2 get dbm cfg | find "SVCENAME"，否则可能会报错“find：’SVCENAME’：No such file or directory.”。

查看DB2实例的当前性能指标

db2pd -edus
输出：
Database Member 0 -- Active -- Up 5 days 08:37:43 -- Date 2024-05-31-21.30.49.158468

List of all EDUs for database member 0

db2sysc PID: 1582019

db2wdog PID: 1582017

db2acd PID: 1582056

EDU ID TID Kernel TID EDU Name USR (s) SYS (s)

===================================================================================================================

21 140139271546624 1582054 db2spmlw 0 0.000000 0.000000

20 140139275740928 1582053 db2spmrsy 0 0.050000 0.040000

19 140139279935232 1582052 db2resync 0 37.650000 0.000000

18 140139284129536 1582032 db2tcpcm 0 0.280000 0.200000

17 140139288323840 1582031 db2tcpcm 0 0.280000 0.190000

16 140139292518144 1582030 db2ipccm 0 0.290000 0.170000

15 140139296712448 1582028 db2wlmtm 0 16.400000 10.440000

14 140139300906752 1582024 db2wlmt 0 9.380000 4.860000

13 140139305101056 1582023 db2licc 0 0.000000 0.010000

12 140139309295360 1582022 db2thcln 0 0.040000 0.050000

11 140139313489664 1582021 db2alarm 0 80.530000 45.940000

1 140139053442816 1582020 db2sysc 0 104.070000 8.420000

定义一个特定的诊断数据路径

Linux系统创建文件命令并赋权

sudo mkdir /home/db2inst1/diag

sudo chmod 777 /home/db2inst1/diag

查看结果：

ls -l /home/db2inst1

输出：

total 12

drwxrwxr-x 3 db2inst1 db2adm1 4096 May 15 00:06 db2inst1

drwxrwxrwx 2 root root 4096 May 31 21:39 diag

drwxrwsr-t 25 db2inst1 db2adm1 4096 May 15 00:03 sqllib

修改数据库管理器的diagpath

db2 update dbm cfg using diagpath //home/db2inst1/diag diagsize 20

输出：

DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed

successfully.

如果执行上述命令报错”SQL5137N The entry in the database manager configuration file for the diagnostic directory path (diagpath) is invalid.”，则需要退出到Linux系统，给这个文件赋予权限，比如“sudo chmod 777 /home/inst23/diag”

强制关闭实例连接：

db2stop force

输出：

05/31/2024 21:42:14 0 0 SQL1064N DB2STOP processing was successful.

SQL1064N DB2STOP processing was successful.

再启动实例：

db2start

输出：

05/31/2024 21:42:20 0 0 SQL1063N DB2START processing was successful.

SQL1063N DB2START processing was successful.

查看配置：

db2pd -dbmcfg | more

输出：

Database Member 0 -- Active -- Up 0 days 00:00:39 -- Date 2024-05-31-21.42.57.145688

Database Manager Configuration Settings:

Description Memory Value Disk Value

RELEASE 0x1500 0x1500

INSTANCE_USAGE DEFAULT DEFAULT

CPUSPEED(millisec/instruction) 9.446887e-08 9.446887e-08

COMM_BANDWIDTH(MB/sec) 1.000000e+02 1.000000e+02

NUMDB 32 32

NUMDB_INT NEEDS RECOMPUTE(32) NEEDS RECOMPUTE(32)

FEDERATED NO NO

TP_MON_NAME

DFT_ACCOUNT_STR

JDK_PATH (memory) /home/db2inst1/sqllib/java/jdk64

JDK_PATH (disk) /home/db2inst1/sqllib/java/jdk64

DIAGLEVEL 3 3

NOTIFYLEVEL 3 3

DIAGPATH (memory) //home/db2inst1/diag/

DIAGPATH (disk) //home/db2inst1/diag/

DIAGPATH_RESOLVED (memory) //home/db2inst1/diag/

DIAGPATH_RESOLVED (disk) //home/db2inst1/diag/

ALT_DIAGPATH (memory)

ALT_DIAGPATH (disk)

ALT_DIAGPATH_RESOLVED (memory)

ALT_DIAGPATH_RESOLVED (disk)

DIAGSIZE (MB) 20 20

DFT_MON_BUFPOOL OFF OFF

DFT_MON_LOCK OFF OFF

DFT_MON_SORT OFF OFF

DFT_MON_STMT OFF OFF

DFT_MON_TABLE OFF OFF

DFT_MON_TIMESTAMP ON ON

DFT_MON_UOW OFF OFF

HEALTH_MON OFF OFF

SYSADM_GROUP (memory) DB2ADM1

SYSADM_GROUP (disk) DB2ADM1

SYSCTRL_GROUP (memory)

SYSCTRL_GROUP (disk)

SYSMAINT_GROUP (memory)

SYSMAINT_GROUP (disk)

SYSMON_GROUP (memory)

SYSMON_GROUP (disk)

CLNT_PW_PLUGIN

CLNT_KRB_PLUGIN

GROUP_PLUGIN

LOCAL_GSSPLUGIN

SRV_PLUGIN_MODE UNFENCED UNFENCED

SRVCON_GSSPLUGIN_LIST

SRVCON_PW_PLUGIN

SRVCON_AUTH

AUTHENTICATION SERVER SERVER

ALTERNATE_AUTH_ENC

CATALOG_NOAUTH NO NO

TRUST_ALLCLNTS YES YES

TRUST_CLNTAUTH CLIENT CLIENT

FED_NOAUTH NO NO

DFTDBPATH (memory) /home/db2inst1

DFTDBPATH (disk) /home/db2inst1

MON_HEAP_SZ (4KB) AUTOMATIC(90) AUTOMATIC(90)

JAVA_HEAP_SZ (4KB) 65536 65536

AUDIT_BUF_SZ (4KB) 0 0

INSTANCE_MEMORY (% or 4KB) AUTOMATIC(383361) AUTOMATIC(383361)

RSTRT_LIGHT_MEM (4KB) AUTOMATIC(10) AUTOMATIC(10)

RSTRT_LIGHT_MEM_INT (4KB) NEEDS RECOMPUTE(0) NEEDS RECOMPUTE(0)

AGENT_STACK_SZ 1024 1024

BACKBUFSZ (4KB) 1024 1024

RESTBUFSZ (4KB) 1024 1024

SHEAPTHRES (4KB) 0 0

DIR_CACHE YES YES

ASLHEAPSZ (4KB) 15 15

RQRIOBLK (bytes) 65535 65535

UTIL_IMPACT_LIM 10 10

AGENTPRI SYSTEM SYSTEM

NUM_POOLAGENTS AUTOMATIC(100) AUTOMATIC(100)

NUM_INITAGENTS 0 0

MAX_COORDAGENTS AUTOMATIC(200) AUTOMATIC(200)

MAX_CONNECTIONS AUTOMATIC(MAX_COORDAGENTS) AUTOMATIC(MAX_COORDAGENTS)

KEEPFENCED YES YES

FENCED_POOL AUTOMATIC(MAX_COORDAGENTS) AUTOMATIC(MAX_COORDAGENTS)

NUM_INITFENCED 0 0

INDEXREC RESTART RESTART

TM_DATABASE 1ST_CONN 1ST_CONN

RESYNC_INTERVAL (secs) 180 180

SPM_NAME VM_4_2_u VM_4_2_u

SPM_LOG_FILE_SZ 256 256

SPM_MAX_RESYNC 20 20

SPM_LOG_PATH

SVCENAME 50000 50000

DISCOVER SEARCH SEARCH

DISCOVER_INST ENABLE ENABLE

SSL_SVR_KEYDB (memory)

SSL_SVR_KEYDB (disk)

SSL_SVR_STASH (memory)

SSL_SVR_STASH (disk)

SSL_SVR_LABEL (memory)

SSL_SVR_LABEL (disk)

SSL_SVCENAME

SSL_CIPHERSPECS (memory)

SSL_CIPHERSPECS (disk)

SSL_VERSIONS (memory)

SSL_VERSIONS (disk)

SSL_CLNT_KEYDB (memory)

SSL_CLNT_KEYDB (disk)

SSL_CLNT_STASH (memory)

SSL_CLNT_STASH (disk)

MAX_QUERYDEGREE ANY ANY

INTRA_PARALLEL NO NO

FCM_NUM_BUFFERS (4KB) AUTOMATIC(4096) AUTOMATIC(4096)

FCM_NUM_CHANNELS AUTOMATIC(2048) AUTOMATIC(2048)

FCM_PARALLELISM AUTOMATIC(2) AUTOMATIC(2)

FCM_PARALLELISM_INT RECOMPUTE(2) 2

FCM_BUFFER_SIZE 32768 32768

FCM_BUFFER_SIZE_INT RECOMPUTE(32768) 32768

CONN_ELAPSE (secs) 10 10

MAX_CONNRETRIES 5 5

MAX_TIME_DIFF (mins) 60 60

START_STOP_TIME (mins) 10 10

WLM_DISPATCHER NO NO

WLM_DISP_CONCUR COMPUTED(8) COMPUTED

WLM_DISP_CPU_SHARES NO NO

WLM_DISP_MIN_UTIL 5 5

KCFD_CFG_SIGNATURE 45 48

COMM_EXIT_LIST (memory)

COMM_EXIT_LIST (disk)

KEYSTORE_TYPE NONE NONE

KEYSTORE_LOCATION (memory)

KEYSTORE_LOCATION (disk)

PYTHON_PATH (memory)

PYTHON_PATH (disk)

可以看到diagpath相关路径都已修改为/home/inst23/diag，且diagsize大小都已经修改为20

或者直接使用过滤查看配置

b2 get dbm cfg | grep "path"

输出：

Java Development Kit installation path (JDK_PATH) = /home/db2inst1/sqllib/java/jdk64

Diagnostic data directory path (DIAGPATH) = //home/db2inst1/diag/

Alternate diagnostic data directory path (ALT_DIAGPATH) =

Default database path (DFTDBPATH) = /home/db2inst1

SPM log path (SPM_LOG_PATH) =

Windows命令为：db2 get dbm cfg | find "path"