DB2 Universal Database Process detailed

Introduced

UNIX and Linux users often check the processes running on the server for problem analysis and check the resources consumed on the server. This information is useful not only for system administrators who troubleshoot and analyze resources, but also for developing high availability and monitoring DB2 processes to determine when to perform some behavior, such as database restarts, or to perform necessary server error recovery (failover) error recovery scripts.

If you are using AIX, you must use the PS-EF command to check the process. On Solaris and HP-UX, PS-EF only displays the Prefetchers process (the primary DB2SYSC engine process) for all server-side processes (such as agents, loggers, page cleaners, and DB2). If you use Solaris or HP-UX, you can see these processes using the/USR/UCB/PS-AXW command. These versions of the PS command can work on Linux.

When you execute this command on a computer that is running DB2 Universal database client or Server software, you can see that more than one DB2 process is listed. The purpose of this article is to illustrate these processes and explain what they do and when they are run. By reading this article you can understand each process of DB2, and when you see these processes, you can understand what DB2 is doing.

Note: How processes are performed in DB2 are slightly different for Windows and Linux, and for UNIX environments. In Windows, there is only one process (DB2SYSC), under which each engine allocation unit (EDU) executes as a thread. Although this article discusses processes, they should be considered threads in a Windows environment. In Windows Task Manager you can see the DB2SYSC process (Db2syscs.exe) for each instance. Other Windows Services/processes can also be displayed, and this article we will explain what they are.

Warning: Do not interfere directly with the DB2 process in a normal DB2 environment. Using the kill-9 command in Linux or UNIX to remove a DB2 process may cause DB2 to behave abnormally. If the removal process will cause the entire DB2 instance to stop. The purpose of this article is to understand these processes and not to maintain them directly.

Why to view the DB2 process

Our personal experience has shown the value of this knowledge, and the customers we visit are asking us for such information. Look at the real situation below and see how you can check the DB2 process running on your system to solve the problem:

Scenario 1: Rare Buffer pool page cleanup

A customer who runs an E-commerce Web site and uses DB2 as a database server reports that the database responds to the application for a long time during multiple periods of the day. The database snapshots do not show any unusual behavior during these periods. By examining the CPU utilization of one of the session's processes, you can find that the I/O cleaner (DB2PCLNR) consumes more than 90% of the CPU time. Next, we eliminate this situation by looking at the I/O cleanup process triggers and adjusting them appropriately, improving the processing power of the E-commerce site by more than 50%.

Situation 2: The real situation

Although we visited an IBM business partner and performed some DB2 performance tuning, we still encountered an ordinary response time delay. The application List command does not show any processes that are not normal at this time. Before getting the DB2 snapshot, we looked at the DB2 process running on the DB2 server and found that the Db2rebal process was running. When a container is added to the DMS table space, the process is used to perform data equalization again. The customer admitted that day it added a container to a tablespace that contained a 40GB table. When the rebalancing is complete, the response speed of the query returns to normal.

Look at notifications and diagnostics logs

Management notification logs and diagnostic logs (Db2diag.log) are important tools used by system administrators to understand database activities and functions. Normally they contain information about the DB2 process, and the following example shows 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 source's process ID number is 78121. The name of the process is db2agent, and it connects to a database called Test. Learn what each process is doing to help you understand the system Management notification log and Db2diag.log content.