db2top Agent Screen (Part 1)

Agent running in a set of gears

To understand on a deep level how DB2 for LUW executes your SQL, you need to understand the concepts of agents, subagents, sections, and subsections. DB2, especially with the Data Partitioning Feature (DPF),  takes advantage of parallelism, not only in running large numbers of SQL statements simultaneously, but also to execute efficiently the individual statements themselves. When a statement comes into DB2, it can be broken down into multiple subsections and a subagent can process each subsection in parallel with one another to quickly return a result. The db2top Agent screen gives you insight into this process, allowing you to see the state of each subagent and subsection as a statement is executed and helping you identify the source of performance problems such as the occurrence of a sort or hash join overflow. In today’s post, we will examine the Agent screen for non-DPF systems.

The db2top Agent screen is a bit of an oddball. You need to know the agent ID before you attempt to use it, so although you can launch it from anywhere within db2top, you’ll probably want to launch it from a screen where you can see a list of agent IDs, such as the Sessions screen.

Alternatively, if you have a command prompt handy, you can run "db2 list applications" and the get the agent ID (also known as the Appl. Handle) from the third column.

Pressing the ‘a’ key causes a prompt to appear directly under the header on the left side of the screen, as shown below:

db2top home screen with agent prompt
db2top home screen with agent prompt

After you enter the agent ID and press Enter, you are shown a very basic screen with the application ID and state, and, if the application is executing some SQL at the time, you also see the SQL text:

db2top Agent screen with SQL text
db2top Agent screen with SQL text

The footer shows the total CPU and associated and total memory.

When you specify an invalid agent ID at the agent ID prompt, the message "No data available, rc -2" is shown at the top left of an otherwise blank screen.

The ‘k’, ‘i’, ‘X’, and ‘G’ keys have no effect. In fact, the Agent screen has its own set of keys for taking several actions and even has its own help screen, accessed by pressing the ‘h’ key:

db2top Agent help screen
db2top Agent help screen

The ‘S’ key to take a native snapshot behaves the same way as it does for the other screens and will be covered in a separate topic. The ‘L’ key behaves similarly to that of the Dynamic SQL screen, popping up a window containing the SQL text and listing four additional actions you can take. The difference is that you are not prompted to specify a hash value here.

The ‘e’ and ‘x’ keys available on the main agent screen are simply shortcuts to avoid having to press the ‘L’ key first. The ‘r’ key lets you return to the screen from which you launched the agent screen and the ‘l’ (lowercase L) key does the same thing.

Pressing the ‘d’ key displays a table as shown below:

db2top Agent associated agents screen
db2top Agent associated agents screen

The table contains the following columns:

Column Name Definition
Assoc “Yes” if this agent is associated with an application, otherwise “No”.
Agent Tid The unique identifier for the engine dispatchable unit (EDU) for the agent (agent_pid). Except on the Linux® operating system, the EDU ID is mapped to the thread ID. On the Linux operating system, the EDU ID is a DB2® generated unique identifier.
Node Number The number assigned to the node on which the agent is running (node_number).
Memory Size The current size of the agent’s memory pool (pool_cur_size).
Pool Number

A number that represents the agent’s memory pool’s type (pool_id). Can be one of the following:

  • 1 – Application Heap
  • 2 – Database Heap
  • 4 – Log Manager Heap
  • 5 – Backup/Restore/Utility Heap
  • 6 – Statistics Heap
  • 7 – Package Cache Heap
  • 8 – Catalog Cache Heap
  • 11 – Database Monitor Heap
  • 12 – Statement Heap
  • 13 – FCMBP Heap
  • 14 – Import Pool
  • 15 – Other Memory
  • 16 – Buffer Pool Heap
  • 20 – Applications Shared Heap
  • 18 – Sort Shared Heap

Associated agents and non-associated agents are listed separately. At the bottom of each list is a row that shows the total memory size for the agents in the list.

The ‘f’ key is for forcing the application off the database. You are asked to confirm that you really wish to force the application before it goes ahead with the force.

In the next post, we will see a greatly expanded Agent screen when we use db2top on a DPF instance of DB2.

  • David S

    The “db2top Agent screen with SQL text” shown above states it is a basic screen. If you just make the window larger, you see much more information about the agent. Here’s a sample:

    xxx.xxx.xxx.xx.36487.170203174150 (xxx.xxx.xxx.xx 36487], UOW Waiting in the application

    ConnTime..: 09:28:04.813 UOW Start.: 09:48:53.537 Appl name.: db2jcc_applicati DB2 user..: xxxxxxxx
    OS user…: xxxxxxxx Agent id..: 19521 Coord DBP.: 0 Coord id..: 223612
    Client pid: 0 Hash joins: 788 Hash loops: 0 HJoin ovf.: 0
    SQL Stmts.: 29,958 Sorts…..: 6,146 Sort time.: 0.000 Sorts ovf.: 0
    Rows Read.: 0 Rows Sel..: 76,054 Read/Sel..: 0 Rows Wrtn.: 0
    Rows Ins..: 701 Rows Upd..: 786 Rows Del..: 17 Locks held: 0
    Trans…..: 1,280 Open Curs.: 0 Rem Cursor: 0 Memory….: 576.0K
    Dyn. SQL..: 34548 Static SQL: 1280 Cpu Time..: 4.280151 AvgCpuStmt: 0.001

    -+—-+—-+—-+—-+—-+—-+—-+—-+—-+—-+—-+– Static statement [Static Commit] -+—-+—-+—-+—-+—-+—-+—-+—-+—-+—-+—-+-
    Start…..: 09:48:53.561 Stop……: 09:48:53.563 Cpu Time..: 0.000018 Elapse….: 0.002067
    FetchCount: 0 Cost Est..: 0 Card Est..: 0 AgentTop..: 1
    SortTime..: 0 SortOvf…: 0 Sorts…..: 0 Degree….: 1
    Agents….: 1 l_reads…: 0 p_reads…: 0 DataReads.: 0
    IndexReads: 0 TempReads.: 0 HitRatio..: 0.00% MaxDbpCpu.: 0.000000[0]
    IntRowsDel: 0 IntRowsUpd: 0 IntRowsIns: 0