DB2 是众多关系型数据库中的一种， 关系型数据库还包括比较火的Oracle，MySQL
实例
数据库
DB2 进程模型
DB2 通过 db2start 命令启动数据库实例，即启动相应的进程和线程，并分配相应内存。
通过命令db2pd -edu 可以查看当前的进程和线程
Database Member 0 -- Active -- Up 62 days 08:14:02 -- Date 2017-11-30-10.05.03.883031 List of all EDUs for database member 0 db2sysc PID: 50397222
db2wdog PID: 7798978
db2acd PID: 35848426 EDU ID TID Kernel TID EDU Name USR (s) SYS (s)
========================================================================================================================================
25700 25700 85131497 db2agent (DBNAME) 0 299.989122 35.183035
25443 25443 15532885 db2agent (instance) 0 1828.692089 150.714185
25186 25186 12452721 db2agntdp (DBNAME ) 0 1.461192 0.267469
24929 24929 42795125 db2agntdp (DBNAME ) 0 805.001309 69.118498
24672 24672 105906353 db2agent (DBNAME) 0 2396.008347 167.021221
24415 24415 9700293 db2agntdp (DBNAME ) 0 605.400762 58.211041
23390 23390 28836093 db2agntdp (DBNAME ) 0 518.864975 45.122775
22365 22365 11207471 db2agntdp (DBNAME ) 0 924.429698 87.834952
20572 20572 74122051 db2agntdp (DBNAME ) 0 489.859156 35.603083
20315 20315 55116673 db2agent (DBNAME) 0 1204.717099 97.264914
19034 19034 87556285 db2shred (DBNAME) 0 738.600183 193.694051
16216 16216 99287267 db2agent (DBNAME) 0 1558.502767 103.558734
15447 15447 45286347 db2agent (DBNAME) 0 1866.006614 114.073616
15190 15190 79495241 db2agntdp (DBNAME ) 0 897.809516 66.481378
14677 14677 65208411 db2agntdp (DBNAME ) 0 1052.700318 82.686483
14420 14420 57148199 db2agntdp (DBNAME ) 0 1688.816066 153.160482
12883 12883 53346535 db2hadrp.0.1 (DBNAME) 0 154.995066 259.085414
12626 12626 65405899 db2pfchr (DBNAME) 0 102.110779 105.423429
12369 12369 43516805 db2pfchr (DBNAME) 0 101.542923 105.469408
12112 12112 60818307 db2pfchr (DBNAME) 0 102.233034 105.734641
11855 11855 22937697 db2pfchr (DBNAME) 0 166.725686 161.751467
11598 11598 19726521 db2pfchr (DBNAME) 0 167.057019 161.565368
11341 11341 54722735 db2pfchr (DBNAME) 0 167.220564 161.810076
11084 11084 19792675 db2pfchr (DBNAME) 0 373.036013 351.305936
10827 10827 31982535 db2pfchr (DBNAME) 0 372.714635 351.986265
10570 10570 31917011 db2pfchr (DBNAME) 0 372.132674 351.735368
10313 10313 80019597 db2pfchr (DBNAME) 0 637.685985 544.298846
10056 10056 89653343 db2pfchr (DBNAME) 0 638.920129 543.105383
9799 9799 23725045 db2pfchr (DBNAME) 0 638.663849 545.265594
9542 9542 23461991 db2pfchr (DBNAME) 0 1914.608769 1268.442838
9285 9285 17892119 db2pfchr (DBNAME) 0 1911.178506 1268.271233
9028 9028 1508307 db2pfchr (DBNAME) 0 1913.801588 1269.889386
8771 8771 4850473 db2pfchr (DBNAME) 0 2577.512813 1551.460310
8514 8514 69141319 db2pfchr (DBNAME) 0 2579.779872 1550.337113
8257 8257 85852397 db2pfchr (DBNAME) 0 2576.113235 1550.699655
8000 8000 32243725 db2pclnr (DBNAME) 0 3.289553 4.452239
7743 7743 16384245 db2pclnr (DBNAME) 0 3.362988 4.762008
7486 7486 55246863 db2pclnr (DBNAME) 0 3.220689 4.320680
7229 7229 12976899 db2pclnr (DBNAME) 0 3.461353 4.877450
6972 6972 128778295 db2pclnr (DBNAME) 0 3.741682 5.250532
6715 6715 19006235 db2pclnr (DBNAME) 0 3.264976 4.238791
6458 6458 81133611 db2pclnr (DBNAME) 0 3.424206 4.716214
6201 6201 29295565 db2pclnr (DBNAME) 0 3.486839 4.584265
5944 5944 97386669 db2pclnr (DBNAME) 0 4.177199 5.811511
5687 5687 42729587 db2lfr.0 (DBNAME) 0 75.019720 115.088266
5430 5430 68748183 db2loggw (DBNAME) 0 97.933136 459.297855
5173 5173 88277109 db2loggr (DBNAME) 0 68.996509 17.303965
4916 4916 80543967 db2logmgr (DBNAME) 0 10.527265 95.248588
4659 4659 49021949 db2logts (DBNAME) 0 18.935759 7.338372
4402 4402 5899207 db2dlock (DBNAME) 0 2.611647 1.338454
47049 47049 14155891 db2fw11 (DBNAME) 0 1091.336217 276.876167
45250 45250 11928521 db2fw25 (DBNAME) 0 700.739470 55.051686
44736 44736 24969295 db2fw32 (DBNAME) 0 586.312014 51.616603
43451 43451 78905557 db2fw28 (DBNAME) 0 880.459025 82.502268
44222 44222 125763793 db2agent (DBNAME) 0 3811.012351 344.231668
43965 43965 25822145 db2fw22 (DBNAME) 0 811.944363 76.209507
43708 43708 26477321 db2fw3 (DBNAME) 0 29.793174 14.373388
42937 42937 36634789 db2fw27 (DBNAME) 0 411.574972 49.799182
43194 43194 20710375 db2fw1 (DBNAME) 0 3191.766239 268.107187
42680 42680 49873705 db2fw13 (DBNAME) 0 252.360307 54.068209
42166 42166 52232431 db2fw10 (DBNAME) 0 1984.438822 193.381039
42423 42423 89587911 db2fw15 (DBNAME) 0 1344.753677 132.704235
41141 41141 71696563 db2fw7 (DBNAME) 0 2440.620564 217.004290
40884 40884 78577835 db2fw14 (DBNAME) 0 1770.320392 180.802365
40627 40627 57934649 db2fw33 (DBNAME) 0 845.531902 88.320037
40370 40370 11337763 db2fw26 (DBNAME) 0 2132.334267 181.917716
40113 40113 65209303 db2fw6 (DBNAME) 0 1745.009377 203.087557
39856 39856 18351011 db2agent (DBNAME) 0 1981.496332 186.208227
39085 39085 113115321 db2fw30 (DBNAME) 0 876.991509 94.359239
39599 39599 67174565 db2taskd (DBNAME) 0 868.639648 102.717013
39342 39342 53805161 db2fw4 (DBNAME) 0 930.931159 99.678196
38828 38828 23266143 db2agntdp (DBNAME ) 0 1191.078025 136.260251
38059 38059 14353281 db2fw8 (DBNAME) 0 2248.994159 225.973022
37802 37802 59048151 db2wlmd (DBNAME) 0 2163.580966 192.670183
37545 37545 21758897 db2fw17 (DBNAME) 0 1966.702774 173.434204
37031 37031 124387465 db2fw31 (DBNAME) 0 871.212481 69.572017
36774 36774 32506767 db2agntdp (DBNAME ) 0 693.000789 68.179691
36261 36261 131268677 db2agntdp (DBNAME ) 0 2224.748441 239.658299
34980 34980 126222451 db2fw5 (DBNAME) 0 48.324498 12.665919
34467 34467 62063519 db2fw2 (DBNAME) 0 1512.855673 114.011332
31649 31649 5964569 db2agent (DBNAME) 0 1013.758886 117.206200
28319 28319 125501651 db2lused (DBNAME) 0 238.550852 45.960126
28062 28062 106430555 db2fw12 (DBNAME) 0 1486.950147 129.496321
27548 27548 36897697 db2agntdp (DBNAME ) 0 2464.140995 221.535609
23963 23963 70452143 db2fw34 (DBNAME) 0 1.826688 1.075234
23706 23706 10093313 db2agent (DBNAME) 0 1314.294765 111.154238
22937 22937 132055115 db2agntdp (DBNAME ) 0 1193.804844 91.419791
22680 22680 109576403 db2agent (DBNAME) 0 190.683874 37.099074
21653 21653 63111963 db2agntdp (DBNAME ) 0 939.370451 55.506895
21910 21910 18547707 db2fw16 (DBNAME) 0 0.211371 0.559617
22167 22167 43189013 db2agntdp (DBNAME ) 0 9.376437 3.400071
21396 21396 68027363 db2agent (DBNAME) 0 838.400436 59.675734
21139 21139 11338655 db2agent (DBNAME) 0 1738.484546 119.898221
20882 20882 10944725 db2agent (DBNAME) 0 749.498297 52.982475
20113 20113 129958015 db2fw23 (DBNAME) 0 0.241890 0.557962
19599 19599 16187509 db2agntdp (DBNAME ) 0 697.772122 51.105313
18573 18573 67896069 db2fw0 (DBNAME) 0 1013.147295 103.199909
19342 19342 75235385 db2agent (DBNAME) 0 249.361917 13.767459
17804 17804 33489771 db2agntdp (DBNAME ) 0 698.754328 62.321696
17290 17290 34668783 db2agent (DBNAME) 0 1969.385206 155.828127
17033 17033 45940955 db2agntdp (DBNAME ) 0 1329.968911 118.236051
16776 16776 40305413 db2agntdp (DBNAME ) 0 854.799861 64.531847
16007 16007 22413405 db2fw20 (DBNAME) 0 0.187414 0.401443
15750 15750 11666211 db2agent (DBNAME) 0 6.602363 7.696093
14981 14981 88473661 db2agntdp (DBNAME ) 0 554.694749 50.801643
14211 14211 37290897 db2agent (DBNAME) 0 2037.915661 158.137814
13954 13954 47317985 db2fw35 (DBNAME) 0 182.444188 16.677533
13697 13697 27132137 db2agent (DBNAME) 0 780.352072 68.575632
13184 13184 83755147 db2fw24 (DBNAME) 0 81.852439 9.311473
38438 38438 8520563 db2fw18 (DBNAME) 0 0.337650 0.588922
35852 35852 9896049 db2fw19 (DBNAME) 0 2154.575689 239.863611
35338 35338 124846123 db2fw9 (DBNAME) 0 602.489432 91.063575
35081 35081 121045089 db2agent (DBNAME) 0 482.721274 64.003455
23047 23047 30278605 db2agntdp (DBNAME ) 0 1131.170239 111.675293
33258 33258 43974849 db2agntdp (DBNAME ) 0 1289.664496 100.298383
32219 32219 63176935 db2fw29 (DBNAME) 0 826.314710 91.603506
29393 29393 20316385 db2fw21 (DBNAME) 0 2158.631234 232.636159
28879 28879 81854637 db2agntdp (DBNAME ) 0 1438.196953 132.772004
26826 26826 43385701 db2agent (DBNAME) 0 3402.467914 321.347471
24256 24256 25559867 db2pcsd (DBNAME) 0 1078.611303 254.106555
18365 18365 87097431 db2agntdp (DBNAME ) 0 2373.310694 198.576050
18694 18694 52495345 db2stmm (DBNAME) 0 3272.048318 298.719814
17924 17924 109772929 db2agent (DBNAME) 0 5564.523573 491.771041
13555 13555 37683363 db2agent (DBNAME) 0 3898.027706 353.890195
4113 4113 47579995 db2spmlw 0 0.000334 0.000595
3856 3856 38928579 db2spmrsy 0 0.343302 0.560796
3599 3599 98631899 db2resync 0 0.291637 1.686837
3342 3342 2163465 db2tcpcm 0 110.812747 72.724551
3085 3085 131072119 db2tcpcm 0 110.740027 72.984465
2828 2828 66388139 db2tcpcm 0 111.426580 73.525211
2571 2571 49218535 db2tcpcm 0 111.180989 73.158306
2314 2314 42467337 db2tcpcm 0 111.418349 73.462901
2057 2057 61145303 db2tcpcm 0 111.030934 73.149202
1800 1800 29819829 db2ipccm 0 10.765461 7.630077
1543 1543 16974631 db2wlmtm 0 99.055236 98.481514
1286 1286 67437551 db2wlmt 0 71.054473 61.147685
1029 1029 121176191 db2licc 0 0.374244 0.152680
772 772 44565453 db2thcln 0 0.018871 0.006126
515 515 31196077 db2auditd 0 0.000136 0.000009
2 2 132710447 db2alarm 0 1006.849255 514.150755
258 258 113311911 db2sysc 0 180.197486 251.290303
DB2 主进程
| 进程名 | 进程描述 | |
| db2wdog | 实例启动的第一个进程，也是实例的所有其他进程的父进程 | |
| db2sysc | 数据库系统的控制器 | |
| db2acd | 监控数据库运行状况以及自动维护的程序的守护进程 | |
NOTE：
V10.5 版本没有了db2ckpwd 进程
DB2 常用线程
| 线程名 | 线程类型 | 详细解释 |
| db2sysc | 数据库系统控制器 | |
| db2ipccm | IPC 通信管理器 | |
| db2tcpcm | TCP 通信管理器 | |
| db2alarm | 每个EDU都有life 时间，当申请的时间到期时，通知EDU | |
| db2thcln | 线程清理，当一个EDU 终止时，回收资源 | |
| db2licc | ||
| db2wlmt | ||
| db2wlmtm | ||
| db2spmlw | ||
| db2spmrsy | ||
| db2stmm | 此进程是 DB2 V9.1 版本开始新增的进程，用于自动调整数据库对内存的使用。 DB2 V9.1 提供了一项非常重要的功能—— 内存的自调整，就是通过进程 db2stmm 来实现的。这个进程会根据内部策略，周期性地评估数据库对内存的使用情况，并决定是否作出调整 | |
| db2dlock | 本地死锁检测器，每个数据库分区就有这样一个检测器。它扫描锁定列表，并查找死锁情形。当遇到死锁情形时，其中涉及的某个应用程序 / 事务就被选做“牺牲品”并被回滚。在多分区数据库环境中，使用称为 db2glock 的附加线程来协调从每个分区上的 db2dlock EDU 收集来的信息。 db2glock 仅在目录分区上运行 | |
| db2loggr | 数据库日志阅读器，用于处理日志文件以进行事务处理和恢复。该进程在执行下列操作时读取数据库日志文件：事务处理 ( 即回滚 )、重新启动恢复、前滚操作 | |
| db2loggw | 数据库日志记录器。该进程将日志缓冲区的日志记录写入日志文件 | |
| db2pfchr | 缓冲池预取程序。这些进程代表应用程序在读取数据和索引信息之前，从磁盘读该信息并且将该信息读入数据库缓冲池。预取程序异步地执行这个“预读 (read-ahead) ”操作 代表应用程序进行工作的 DB2 代理程序发送预取请求，预取程序为这些请求提供服务。预取程序执行大块 I/O 来更有效地读取数据。每个数据库的预取程序的数量是由 NUM_IOSERVERS 数据库配置参数配置的 |
|
| db2pclnr | 缓冲池页面清除程序。这些进程以异步方式将“脏”页面从缓冲池写回到磁盘。“脏”页面是这样一个页面：在将该页面读入缓冲池后对其进行过更改，并且磁盘上的映像与缓冲池中的映像不再一样 当页面清除程序被“触发”时，它们将同时全部运行。一旦它们完成其分配的工作，就进入睡眠状态，直到被再次触发 页面清除程序的任务是确保缓冲池有空间可以容纳正在被应用程序检索的新页面。 每个数据库的页面清除程序的数量是通过 NUM_IOCLEANERS 数据库配置参数配置 |
|
| db2taskd | 用于分发后台数据库任务。这些任务由称为 db2taskp 的线程执行 | |
| db2wlmd | 用于自动收集工作负载管理统计信息 | |
| db2fw0 | ||
| db2pcsd | ||
| db2evmgi |
db2evm%1%2 (%3)，其中%1可以为： · g—— 全局文件事件监视器 · l—— 本地文件事件监视器 · t—— 表事件监视器 · gp—— 全局管道事件监视器 · lp—— 本地管道事件监视器 %2可以为： · i—— 协调程序 · p—— 不是协调程序 %3是事件监视器名称 |
连接数据库
DB2 提供了两种连接方式：
| 连接方式 | 常用的命令 | |||
| 1 | attach to instance | db2 attach to <instance-name> | 监控和管理实例 | Client application 发送请求给监听程序，监听程序为该连接创建agent，并分配 db2agent，client 与agent 建立连接，之后client 发送请求给agent，agent协助处理请求，直到客户端退出 |
| 2 | connect to DB | db2 connect to <database-name> | 执行sql语句 | Client application 发送请求给监听程序，监听程序为该连接创建agent，并分配 db2agent，创建其他的数据库subagent来服务client的请求，db2pfchr/db2pclnr db2lfr/db2loggw/db2loggr db2dlock |
attach 连接数据库：
Server INFO:
192.168.8.80 db2v105 sample 60000
Client
192.168.8.80 db2cae 1. login the user db2cae
2. catalog node info
db2 catalog tcpip node db2v105 remote 192.168.8.80 server 60000
3. attach to the instance
db2 attach to db2v105 user db2v105 using "vagrant" 查看 edu 变化
EDU ID TID Kernel TID EDU Name USR (s) SYS (s)
========================================================================================================================================
23 140557443655424 17622 db2agent (idle) 0 0.040000 0.010000
22 140557447849728 2571 db2agent (idle) 0 0.890000 0.250000 EDU ID TID Kernel TID EDU Name USR (s) SYS (s)
========================================================================================================================================
23 140557443655424 17622 db2agent (idle) 0 0.040000 0.010000
22 140557447849728 2571 db2agent (instance) 0 0.890000 0.250000 Client 通过node 发送连接请求给db2tcpcm, 然后查看当前数据库中是否存在空闲的db2agent， 如果有则分配给client
connection 连接数据库：
Client:
db2 catalog db sample at node db2v105
db2 connect to sample user db2v105 using "vagrant" 远程连接数据库之后 EDU ID TID Kernel TID EDU Name USR (s) SYS (s)
========================================================================================================================================
52 140557322020608 23326 db2evmgi (DB2DETAILDEADLOCK) 0 0.000000 0.000000
51 140557326214912 23325 db2pcsd (SAMPLE) 0 0.000000 0.000000
50 140557330409216 23324 db2fw1 (SAMPLE) 0 0.000000 0.000000
49 140557334603520 23323 db2fw0 (SAMPLE) 0 0.000000 0.000000
48 140557338797824 23322 db2lused (SAMPLE) 0 0.000000 0.000000
47 140557342992128 23321 db2wlmd (SAMPLE) 0 0.000000 0.000000
46 140557347186432 23320 db2taskd (SAMPLE) 0 0.000000 0.000000
45 140557351380736 23319 db2pfchr (SAMPLE) 0 0.000000 0.000000
44 140557355575040 23318 db2pfchr (SAMPLE) 0 0.000000 0.000000
43 140557359769344 23317 db2pfchr (SAMPLE) 0 0.000000 0.000000
42 140557363963648 23316 db2pfchr (SAMPLE) 0 0.000000 0.000000
41 140557368157952 23315 db2pfchr (SAMPLE) 0 0.000000 0.000000
40 140557372352256 23314 db2pfchr (SAMPLE) 0 0.000000 0.000000
39 140557376546560 23313 db2pfchr (SAMPLE) 0 0.000000 0.000000
38 140557380740864 23312 db2pfchr (SAMPLE) 0 0.000000 0.000000
37 140557384935168 23311 db2pfchr (SAMPLE) 0 0.000000 0.000000
36 140557389129472 23310 db2pfchr (SAMPLE) 0 0.000000 0.000000
35 140557393323776 23309 db2pfchr (SAMPLE) 0 0.000000 0.000000
34 140557397518080 23308 db2pfchr (SAMPLE) 0 0.000000 0.000000
33 140557401712384 23307 db2pfchr (SAMPLE) 0 0.000000 0.000000
32 140557405906688 23306 db2pfchr (SAMPLE) 0 0.000000 0.000000
31 140557410100992 23305 db2pfchr (SAMPLE) 0 0.000000 0.000000
30 140557414295296 23304 db2pfchr (SAMPLE) 0 0.000000 0.000000
29 140557418489600 23303 db2pclnr (SAMPLE) 0 0.000000 0.000000
28 140557422683904 23302 db2pclnr (SAMPLE) 0 0.000000 0.000000
27 140557426878208 23301 db2lfr.0 (SAMPLE) 0 0.000000 0.000000
26 140557431072512 23300 db2loggw (SAMPLE) 0 0.000000 0.000000
25 140557435266816 23299 db2loggr (SAMPLE) 0 0.000000 0.000000
24 140557439461120 23298 db2dlock (SAMPLE) 0 0.000000 0.000000
23 140557443655424 17622 db2stmm (SAMPLE) 0 0.050000 0.020000
22 140557447849728 2571 db2agent (SAMPLE) 0 0.950000 0.340000
21 140557452044032 2382 db2spmlw 0 0.000000 0.000000
20 140557456238336 2381 db2spmrsy 0 0.000000 0.020000
19 140557460432640 2380 db2resync 0 0.710000 1.540000
18 140557464626944 2379 db2tcpcm 0 0.000000 0.000000
17 140557468821248 2378 db2tcpcm 0 0.000000 0.000000
16 140557473015552 2377 db2ipccm 0 0.010000 0.000000
15 140557477209856 2375 db2wlmtm 0 1.910000 0.480000
14 140557481404160 2371 db2wlmt 0 0.840000 0.220000
13 140557485598464 2370 db2licc 0 0.000000 0.000000
12 140557489792768 2369 db2thcln 0 0.000000 0.000000
11 140557493987072 2368 db2alarm 0 4.360000 3.920000
1 140557233940224 2367 db2sysc 0 0.630000 0.220000
connection 连接数据库之后执行Query 过程
Client sends request to coordinator agent
SQL query compiled or existing access plan retrieved from package cache
Plan execution begins
Parallel subagents dispatched (if intra-query parallelism enabled )
Agent threads access database to update or query data
Results flow from parallel subagents to coordinator agent through table-queues (if intra-query parallelism enabled)
SQL results returned from coordinator agent to client
Reference
https://www.ibm.com/developerworks/cn/data/books/db2advan/2/
DB2 9.5 中多线程架构的工作原理 https://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0807kharche/index.html