DB2-LAB1-Database Manager Instance
实验目标
Create a new DB2 instance
- Run the db2icrt command to create a new DB2 database manager instance.
- Issue db2set and db2 update commands to configure the DB2 instance.
- Utilize the db2start and db2stop commands to start and stop a DB2 instance.
- Run db2pd commands to check the DB2 instance configuration and status.
实验过程
Task 1: 生成一个DB2 instance
创建一个实例，命名为inst23。
因为使用的是Linux版本，和课程指导中的Window版指令所区别。
对应指导中四步，在Linux中对应的命令为：
- 配置用户
➜ ~ groupadd -g 2000 db2iadm1
➜ ~ groupadd -g 2001 db2fadm1
➜ ~ useradd -m -g db2iadm1 -d /home/db2inst1 db2inst1
➜ ~ useradd -m -g db2fadm1 -d /home/db2fenc1 db2fenc1
➜ ~ passwd db2inst1
New password:
Retype new password:
passwd: password updated successfully
➜ ~ passwd db2fenc1
New password:
Retype new password:
passwd: password updated successfully
- 创建instance
来到安装目录 /opt/ibm/db2/V11.5/instance ，运行：
chmod -R 755 *
./db2icrt -p 50000 -u db2fenc1 db2inst1
- 创建数据库
接下来切到 db2inst1 用户
su db2inst1
进入/home/db2inst1/sqllib/samples并创建 SAMPLE 数据库：
$ db2sampl
Starting the DB2 instance...
Creating database "SAMPLE"...
Connecting to database "SAMPLE"...
Creating tables and data in schema "DB2INST1"...
Creating tables with XML columns and XML data in schema "DB2INST1"...
Stopping the DB2 instance...
'db2sampl' processing complete.
- 查看DB，启动DB
$ db2 list db directory
System Database Directory
Number of entries in the directory = 1
Database 1 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 =
查看DB2
$ db2level
DB21085I This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL11050" with level
identifier "0601010F".
Informational tokens are "DB2 v11.5.0.0", "s1906101300", "DYN1906101300AMD64",
and Fix Pack "0".
Product is installed at "/opt/ibm/db2/V11.5".
启动Db2：
$ db2start
03/30/2022 14:39:21 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.
连接到 SAMPLE 数据库：
$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 11.5.0.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
运行SQL：
$ db2 "select * from sysibm.sysdummy1"
IBMREQD
-------
Y
1 record(s) selected.
关闭连接
$ db2 terminate
DB20000I The TERMINATE command completed successfully.
停止DB2
$ db2stop 05/17/2024 18:28:30 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. $
配置实例，允许tcpip访问，并设置端口。注意端口设置要和其他的instance区分开。
$ db2set db2comm=tcpip $ db2set -all [i] DB2COMM=TCPIP [g] DB2SYSTEM=shahuang-lt $ db2 update dbm cfg using svcename 50230 DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. $
启动DB2以激活新配置的实例
$ db2start 05/18/2024 15:21:19 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful.
$
查看当前示例运行的所有进程
$ db2pd -edus
Database Member 0 -- Active -- Up 0 days 00:01:40 -- Date 2024-05-18-15.22.57.189307
List of all EDUs for database member 0
db2sysc PID: 267634
db2wdog PID: 267632
db2acd PID: 267658
EDU ID TID Kernel TID EDU Name USR (s) SYS (s)
===================================================================================================================
22 140347267081792 267656 db2spmlw 0 0.000000 0.000000
21 140347271276096 267655 db2spmrsy 0 0.000000 0.000000
20 140347275470400 267654 db2resync 0 0.010000 0.000000
19 140347279664704 267649 db2tcpcm 0 0.000000 0.000000
18 140347283859008 267648 db2tcpcm 0 0.000000 0.000000
17 140347288053312 267647 db2tcpcm 0 0.000000 0.000000
16 140347292247616 267646 db2ipccm 0 0.000000 0.000000
15 140347296441920 267644 db2wlmtm 0 0.200000 0.080000
14 140347300636224 267640 db2wlmt 0 0.000000 0.000000
13 140347304830528 267639 db2licc 0 0.000000 0.000000
12 140347309024832 267638 db2thcln 0 0.000000 0.000000
11 140347313219136 267637 db2alarm 0 0.020000 0.000000
1 140347053172288 267636 db2sysc 0 0.020000 0.010000
$
配置DB2的诊断文件的目录与大小
TODO:
配置成功后，重启DB2，并查看配置
TODO:
通过DB2命令查看当前instance的配置。比如可以通过这个命令来查看刚才设置的诊断文档路径。
TODO
$ mkdir /home/db2inst1/diag $ db2 update dbm cfg using diagpath /home/db2inst1/diag diagsize 20 DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed successfully. $ db2stop force 05/18/2024 15:32:16 0 0 SQL1064N DB2STOP processing was successful. SQL1064N DB2STOP processing was successful. $ db2start 05/18/2024 15:32:22 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. $ db2pd -dbmcfg | more Database Member 0 -- Active -- Up 0 days 00:00:12 -- Date 2024-05-18-15.32.33.115000 Database Manager Configuration Settings: Description Memory Value Disk Value RELEASE 0x1500 0x1500 INSTANCE_USAGE DEFAULT DEFAULT CPUSPEED(millisec/instruction) 6.297924e-08 6.297924e-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) DB2IADM1 SYSADM_GROUP (disk) DB2IADM1 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(3480918) AUTOMATIC(3480918) 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 shahuang shahuang SPM_LOG_FILE_SZ 256 256 SPM_MAX_RESYNC 20 20 SPM_LOG_PATH SVCENAME 50230 50230 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(8) AUTOMATIC(8) FCM_PARALLELISM_INT RECOMPUTE(8) 8 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(32) COMPUTED WLM_DISP_CPU_SHARES NO NO WLM_DISP_MIN_UTIL 5 5 KCFD_CFG_SIGNATURE 37 40 COMM_EXIT_LIST (memory) COMM_EXIT_LIST (disk) KEYSTORE_TYPE NONE NONE KEYSTORE_LOCATION (memory) KEYSTORE_LOCATION (disk) PYTHON_PATH (memory) PYTHON_PATH (disk) $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ ^[[A^[[B^C $ db2 get dbm cfg | find "path" find: ‘path’: No such file or directory $ ^[[A^[[B^C $ db2 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) = $
浙公网安备 33010602011771号