Everything You Wanted to Know About DB2 Universal Database Processes

Document options Document options requiring JavaScript are not displayed My developerWorks needs you! Connect to your technical community Rate this page Help us improve this content

Level: Introductory

Dwaine Snow (mailto:dsnow@ca.ibm.com), DB2 UDB and Informix IDS Product Manager, IBM Toronto Lab
Raul Chong (mailto:rfchong@ca.ibm.com), DB2 UDB Consultant, IBM Toronto Lab

24 Apr 2003

Ever wonder what all those DB2 processes actually do? This article describes the processes that DB2 UDB uses on Linux, UNIX and Windows, discusses why you might need to understand these processes, and details their functions.

© 2003 International Business Machines Corporation. All rights reserved.

This article was written for IBM® DB2® Universal Database^TM v8.1 for Linux, UNIX®, and Windows®

Introduction

UNIX and Linux users commonly examine the processes that are running on their servers to perform problem analysis as well as to examine the resources being consumed within the server. This information is useful not only to administrators who are performing problem and resource analysis, but also to those developing high availability and failover scripts that monitor the DB2 processes to determine when an action such as a database restart or server failover is needed.

Using AIX, you would use the command ps -ef in order to examine processes. On Solaris and HP-UX, ps -ef will only show the db2sysc process (the main DB2 engine process) for all server-side processes (eg: agents, loggers, page cleaners, and prefetchers). If you're using Solaris or HP-UX, you can see these side processes with the command /usr/ucb/ps -axw. Both of these versions of the ps command work on Linux.

When performing this command on a computer running the DB2 Universal Database client or server software, you may see several DB2 processes listed. The purpose of this article is to document these processes and explain what they do and when they may run. Although the most important processes are documented in Chapter 2 of the manual DB2 UDB V8 Administration Guide - Performance, the list is not complete. By reading this article you will be able to understand each of the DB2 processes, and you can begin to see what actions DB2 is performing when you see those processes.

Note that the implementation of how work is performed in DB2 is somewhat different on Windows than Linux and UNIX-based environments. In Windows, there is only one process (db2sysc) under which each of the engine dispatchable units (EDUs) is implemented as a thread. Though the article discusses processes, they should be thought of as threads in the Windows environment. From the Windows Task Manager you will be able to see the db2sysc process (db2syscs.exe) for each instance. Other Windows services/processes will also be displayed, and we will explain what they are in this article.

Warning! Do not meddle directly with the DB2 processes in a healthy DB2 environment. Killing a DB2 process with the kill -9 command in Linux or UNIX may cause undesirable behaviors in DB2. Killing the db2sysc process, for example, will bring the entire DB2 instance down. The idea here is to gain understanding of the processes but not to manipulate them directly.

Back to top

Why look at DB2 processes?

Our personal experiences have shown the value of this knowledge, and the customers we have visited have asked us for this type of information. Not yet convinced? Take a look at the following real-life scenarios, and see for yourself how problems were resolved by reviewing the DB2 processes running on a system:

Scenario 1: Infrequent cleaning of buffer pool pages

A customer running an eCommerce site and using DB2 as the database server reported that at various times throughout the day, the database response time for application requests would become very long. Database snapshots did not show anything abnormal occurring at these times. By examining the CPU usage of the processes on the server at one of these times we were able to determine that the I/O cleaners (db2pclnr) were consuming over 90% of the CPU time. By subsequently looking at the I/O cleaner triggers and tuning them appropriately, we eliminated this condition and the eCommerce site was able to handle over 50% higher throughput on a daily basis.

Scenario 2: The truth comes out

While visiting an IBM business partner to perform some DB2 performance tuning, we encountered a general slowdown in query response time. A list applications command didn't show anything out of the ordinary running at the time. Before taking DB2 snapshots, we took a look at the DB2 processes running on the DB2 server and found that the db2rebal process was running. This process is used to perform a rebalancing of the data when a container is added to a DMS table space. The customer "confessed" that earlier that day he had added one container to a tablespace containing a 40 GB table. Once the rebalancing finished, the queries returned to their original response times.

Shedding some light on the notification and diagnostic logs

The administration notification log and the diagnostic log (db2diag.log) are important tools that administrators use to understand database activities and functions. These normally contain information about DB2 processes as shown in the example below taken from a db2diag.log entry:

 
2000-03-06-11.53.18.001160    Instance:myInst   Node:000  
PID:78121(db2agent (TEST)) TID:352    
Appid:*LOCAL.payroll.000306140834 
lock_manager          sqlplrq    Probe:111   Database:TEST  
DIA9999E  An internal return code occurred. Report the following: 
"0xFFFFE10E". 

In this example, the message came from process ID number 78121. The name of this process is db2agent and it is connected to the database named TEST. Understanding what a process does can help you make sense of the entries in the administration notification log and db2diag.log.

Back to top

The DB2 process model

Although the DB2 process model is explained in the DB2 Administration Guide - Performance manual, we will give you a brief overview here. We'll first describe the concept of an agent.

Agents

An agent can be thought of as a 'worker' that performs all database operations on behalf of an application. There are two main types of DB2 agents:

• Coordinator Agent (db2agent)
  A coordinator agent (or a coordinating agent) coordinates the work on behalf of an application and communicates to other agents using interprocess communication (IPC) or remote communication protocols. All connection requests from client applications, whether they are local or remote, are allocated a corresponding coordinator agent.
• Subagent (db2agntp)
  When the intra_parallel database manager configuration parameter is enabled, the coordinator agent distributes the database requests to subagents (db2agntp). These agents perform the requests for the application. Once the coordinator agent is created, it handles all database requests on behalf of its application by coordinating subagents (db2agent) that perform requests on the database.

When an agent or subagent completes its work it becomes idle. When a subagent becomes idle, its name changes from db2agntp to db2agnta.

For example:

db2agntp processes are active subagents which are currently performing work for the coordinator agent. These processes will only exist when intra-partition parallelism is enabled.

db2agnta processes are idle subagents that were used in the past by a coordinator agent.

Idle agents reside in an agent pool. These agents are available for requests from coordinator agents operating on behalf of client programs, or from subagents operating on behalf of existing coordinator agents. The number of available agents is dependent on the database manager configuration parameters maxagents and num_poolagents.

We'll describe other types of agents like the parallel recovery agent (db2agnsc) later in the article.

The following two figures show the DB2 Process model.

Each of the circles in Figure 1 represent engine dispatchable units (EDUs) which are known as processes on Linux/UNIX platforms, and threads on Windows .

Applications A (App A) and B (App B) are local applications running in the same machine where the DB2 server resides. When these applications issue a CONNECT to a database, the db2ipccm listener process establishes the communication between the database manager and the application. db2ipccm will also work with a coordinator agent EDU (db2agent) which contacts the client application directly to establish shared memory communications between the client application and the coordinator. Once this communication is established, the application at the local client is connected to the database.

Application C (App C) is a remote application residing in a machine different than the one used for the DB2 Server. A remote client establishes TCP/IP communications through the db2tcpcm listener process. The db2tcpcm then works with a db2agent, which becomes the coordinator agent for the application and passes the connection to this agent. After this, the coordinator agent contacts the remote client application and is connected to the database.

Figure 2 is similar to Figure 1, but is applicable to partitioned databases. Node0000 and Node0001 represent two different machines where part of a database resides. The processes and interaction between them is the same as described in Figure 1; however, there are some other processes applicable only to this environment. The db2fcmd process for example is the Fast Communication Manager process used to manage the communication between the different partitions. The table in the next section will describe in more detail other processes applicable to partitioned databases.

Back to top

The processes

The following tables lists all the DB2 processes per instance, per database, and by function. Note that some of the processes in the tables below are not listed in alphabetical order, but grouped based on functionality. If you would like to look for a process in alphabetical order, refer to Table 7 below.

Table 2. Per instance and per connection

Table 3. Per instance and per active database

Table 4. Other processes classified by function

Table 5. Some commonly used executables

Table 6. Other Windows services/processes

Table 7. All processes in alphabetical order

#	Process/Windows service/Executable name	Link to table providing more details
1	db2	Table 5
2	db2agent	Table 2
3	db2agentg	Table 2
4	db2agnsc	Table 2
5	db2agnta	Table 2
6	db2agntp	Table 2
7	db2bm	Table 4
8	db2bp	Table 5
9	db2cart	Table 1
10	db2chkau	Table 1
11	db2ckpw	Table 1
12	db2cmd	Table 5
14	db2dasrrm.exe	Table 6
15	db2dasstm.exe	Table 6
16	db2disp	Table 1
17	db2dlock	Table 3
18	db2estor	Table 3
19	db2fcmd	Table 1
20	db2fmcd	Table 1
21	db2fmd	Table 1
22	db2fmp	Table 1 and Table 6
23	d2fmtlg	Table 1
24	db2gds	Table 1
25	db2glock	Table 1
26	db2govd	Table 1
27	db2ipccm	Table 2
28	db2jds.exe	Table 8
29	db2lbmX	Table 4
30	db2lbs	Table 4
31	db2lcata	Table 4
32	db2lfrmX	Table 4
33	db2lfs	Table 4
34	db2licd.exe	Table 6
35	db2linit	Table 4
36	db2lload	Table 4
37	db2llqcl	Table 4
38	db2lmctk	Table 4
39	db2lmibm	Table 4
40	db2lmitk	Table 4
41	db2lmr	Table 4
42	db2lmwX	Table 4
43	db2loggr	Table 3
44	db2loggw	Table 3
45	db2logts	Table 3
46	db2lpart	Table 4
47	db2lpprt	Table 4
48	db2lrdfl	Table 4
49	db2lrid	Table 4
50	db2ltsc	Table 4
51	db2lurex	Table 4
52	db2med	Table 4
53	db2panic	Table 1
54	db2pclnr	Table 3
55	db2pdbc	Table 1
56	db2pfchr	Table 3
57	db2rcmd.exe	Table 6
58	db2rebal	Table 1
59	db2reorg	Table 4
60	db2resyn	Table 1
61	db2sec.exe	Table 6
62	db2snacm	Table 2
63	db2srvlst	Table 1
64	db2start	Table 5
65	db2star2	Table 5
66	db2stop	Table 5
67	db2stop2	Table 5
68	db2sysc	Table 1
69	db2syscs.exe	Table 6
70	db2tcpcm	Table 2
71	db2tcpdm	Table 2
72	db2wdog	Table 1
73	dlasync	Table 1
74	IWH2SERV.EXE	Table 6

Back to top

Examples

The following section shows you examples of the output you may obtain when executing the ps -ef command in AIX.

After a db2start:

 
    root 49504     1   0 13:13:07      -  0:00 db2wdog 
db2inst1 22142 49180   0 13:13:10      -  0:00 db2gds 
db2inst1 43072 49180   0 13:13:17      -  0:00 db2syslog 
db2inst1 45294 74134   0 12:12:43  pts/2  0:00 /usr/bin/ksh 
db2inst1 49180 49504   0 13:13:10      -  0:00 db2sysc 
db2inst1 55920 49180   0 13:13:19      -  0:00 db2resync 
db2inst1 59012 22142   0 13:13:19      -  0:00 db2srvlst 
db2inst1 60680 49180   0 13:13:17      -  0:00 db2ipccm 

The database manager configuration file has the following settings that affect the processes that you will initially see:

 
Max number of existing agents           (MAXAGENTS) = 200 
Agent pool size                    (NUM_POOLAGENTS) = 100(calculated) 
Initial number of agents in pool   (NUM_INITAGENTS) = 0 

Because NUM_INITAGENTS is zero, there will be no 'db2agent (idle)' processes displayed at db2start time. If for example NUM_INITAGENTS had been set to 5 before the db2start, then these extra processes would have shown after issuing a db2start:

 
db2inst1 35542 59814   0 16:25:57      -  0:00 db2agent (idle) 
db2inst1 43096 59814   0 16:25:57      -  0:00 db2agent (idle) 
db2inst1 49628 59814   0 16:25:57      -  0:00 db2agent (idle) 
db2inst1 58170 59814   0 16:25:57      -  0:00 db2agent (idle) 
db2inst1 64012 59814   0 16:25:57      -  0:00 db2agent (idle) 

After connecting to database SAMPLE (NUM_INITAGENTS still left at zero)

 
    root 49504     1   0 13:13:07    -  0:00 db2wdog 
db2inst1 25844 35124   0 16:04:50    -  0:00 db2pfchr 
db2inst1 35124 65638   0 16:04:17    -  0:00 db2gds 
db2inst1 35540 35124   0 16:04:50    -  0:00 db2loggr (SAMPLE) 
db2inst1 41940 65638   0 16:04:19    -  0:00 db2resync 
db2inst1 45058 35124   0 16:04:50    -  0:00 db2pfchr 
db2inst1 49300 35124   0 16:04:19    -  0:00 db2srvlst 
db2inst1 49626 35124   0 16:04:50    -  0:00 db2dlock (SAMPLE) 
db2inst1 55852 65638   0 16:04:17    -  0:00 db2ipccm 
db2inst1 58168 35124   0 16:04:50    -  0:00 db2loggw (SAMPLE) 
db2inst1 59048 35124   0 16:04:50    -  0:00 db2pfchr 
db2inst1 64010 55852   0 16:04:50    -  0:00 db2agent (SAMPLE) 
db2inst1 65638 22238   0 16:04:17    -  0:00 db2sysc 
db2inst1 70018 35124   0 16:04:50    -  0:00 db2pclnr 
db2inst1 72120 35124   0 16:04:51    -  0:00 db2event (DB2DETAILDEADLOCK) 
db2inst1 74198 65638   0 16:04:17    -  0:00 db2syslog 
db2inst1 74578     1   0 16:04:47    -  0:00 /home/db2inst1/sqllib/bin/db2bp  
  50112C14631 5 
 

After connecting to the SAMPLE database, the 'db2agent (SAMPLE)' process appears. This process indicates there is in fact a connection to the SAMPLE database. If we issued the command:

 
db2 connect reset 

The db2agent (SAMPLE) would now become db2agent (idle). This is because NUM_POOLAGENTS is set to a number greater than zero, which means the agent will remain allocated in the pool, though it is idle. If NUM_POOLAGENTS had been set to zero, then after the 'connect reset', there would have been no db2agent process running.

The database configuration file for the SAMPLE database has these settings:

 
Number of asynchronous page cleaners   (NUM_IOCLEANERS) = 1 
Number of I/O servers                   (NUM_IOSERVERS) = 3 

Note that there are three db2pfchr processes which correspond to the value of NUM_IOSERVERS, and one db2pclnr process which corresponds to the value of NUM_IOCLEANERS.

Back to top

Conclusion

There are many other processes that will appear and disappear depending on different DB2 actions and configuration settings. We've shown you some sample scenarios that illustrate how you can investigate which processes are running, what these processes indicate, and how they are affected by the database settings. Now you can use this knowledge to improve your ability to administer DB2 databases.

Acknowledgements

Special thanks to Bob Harbus of the IBM Toronto Lab Services Consulting team for his input to this document.

About the authors

		Dwaine Snow is the product manager for DB2 UDB partitioned databases as well as Query Patroller and IBM Informix IDS. Dwaine has worked for the past 12 years at IBM, and has worked with DB2 UDB for the past 8 years. You can reach Dwaine at dsnow@ca.ibm.com .

		Raul F. Chong is a database consultant from the IBM Toronto Laboratory and works primarily with IBM Business Partners. Raul has worked for five years in IBM, three of them in DB2 Technical Support, and two of them as a consultant specializing in database application development and migrations from other RDBMS to DB2. You can reach Raul at rfchong@ca.ibm.com .

Rate this page

Please take a moment to complete this form to help us better serve you. Did the information help you to achieve your goal? Yes No Don't know   Please provide us with comments to help improve this page:   How useful is the information? 1 2 3 4 5 Not useful Extremely useful

Share this.... Digg this story del.icio.us Slashdot it!

Back to top