In part 3 of this series on the db2top Agent screen, we will see statement-level and application-level information displayed on the Agent screen for DB2 systems that use the Data Partitioning Feature (DPF). Among other things, these higher level aggregates can help you identify problems of low sort heap allocation at the application level or heavy sort heap usage at the individual statement level. They also let you observe not only what kind of statement is being processed (dynamic, static, non-SQL) but also what operation is being performed on the statement, such as whether it is being compiled or executed.
The top set of attributes are application-level attributes and are grouped into a variable number of columns, depending on the screen width, as shown below:
Listing from left to right each of the 32 attributes above the "Dynamic statement" dividing line, they are:
| Name | Definition |
|---|---|
| ConnTime | The date and time that the application started the connection request (appl_con_time). |
| UOW Start | The date and time that the unit of work first required database resources (uow_start_time). |
| Appl name | The name of the application running at the client, as known to the database or DB2® Connect™ server (appl_name). |
| DB2 user | The authorization ID of the user who invoked the application that is being monitored (auth_id). |
| OS user | The ID that the user specified when logging in to the operating system. This ID is distinct from auth_id, which the user specifies when connecting to the database (execution_id). |
| Agent id | A system-wide unique ID for the application (agent_id). |
| Coord DBP | In a multi-node system, the node number of the node where the application connected or attached to the instance (coord_node). |
| Coord id | The engine dispatchable unit (EDU) identifier of the coordinator agent for the application (coord_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. |
| Client pid | The process ID of the client application that made the connection to the database (client_pid). |
| Hash joins | The total number of hash joins executed in the application (total_hash_joins). |
| Hash loops | The total number of times in the application that a single partition of a hash join was larger than the available sort heap space (total_hash_loops). |
| HJoin ovf | hash_join_overflows + hash_join_small_overflows |
| SQL Stmts | The number of SQL SELECT, Data Definition Language (DDL), UPDATE, INSERT, and DELETE statements that were executed in the application (select_sql_stmts + ddl_sql_stmts + uid_sql_stmts). |
| Sorts | The total number of sorts in the application that have been executed (total_sorts). |
| Sort time | The total elapsed time for all sorts in the application that have been executed (total_sort_time). This value is reported in milliseconds. |
| Sorts ovf | The total number of sorts in the application that ran out of sort heap and may have required disk space for temporary storage (sort_overflows). |
| Rows Read | The number of rows read from tables for the application (rows_read). |
| Rows Sel | This is the number of rows that have been selected and returned to the application (rows_selected). |
| Read/Sel | The average number of reads per row returned to the application (rows_read / rows_selected). |
| Rows Wrtn | This is the number of rows changed (inserted, deleted or updated) in the table for the application (rows_written). |
| Rows Ins | The number of row insertions attempted by the application (rows_inserted). |
| Rows Upd |
This is the number of row updates attempted by the application (rows_updated). |
| Rows Del | This is the number of row deletions attempted by the application (rows_deleted). |
| Locks held | The number of locks currently held (locks_held). |
| Trans | The total number of SQL COMMIT and SQL ROLLBACK statements that have been attempted by the application (commit_sql_stmts + rollback_sql_stmts). |
| Open Curs | The number of local cursors currently open for this application, including those cursors counted by open_loc_curs_blk (open_loc_curs). |
| Rem Cursor | The number of remote cursors currently open for this application, including those cursors counted by open_rem_curs_blk (open_rem_curs). |
| Memory | The sum of the current sizes of the memory pools for the applications.
sum(pool_cur_size) where sum() is the sum across all applications |
| Dyn. SQL | The number of dynamic SQL statements that were attempted by the application (dynamic_sql_stmts). |
| Static SQL | The number of static SQL statements that were attempted (static_sql_stmts). |
| Cpu Time | The total system and user CPU time (in seconds and microseconds) used by database manager agent process (agent_usr_cpu_time + agent_sys_cpu_time). |
| AvgCpuStmt | The average amount of CPU time consumed per SQL statement atttempted by the application.
(agent_usr_cpu_time + agent_sys_cpu_time) / (static_sql_stmts + dynamic_sql_stmts) |
The dividing line shows the statement type (stmt_type) and the current operation of the statement (stmt_operation) in brackets:
Possible values for the statement type are:
- Static statement
- Dynamic statement
- Non SQL stmt
- Unknown
Possible values for the statement operation are:
- Prepare
- Execute
- Execute Immediate
- Open
- Fetch
- Close
- Describe
- Static Commit
- Static Rollback
- Free Locator
- Prepare to Commit
- Call stored proc
- Select statement
- Prep. and open
- Prep. and exec
- Compile
- Set stmt
- Runstats
- Reorg
- Rebind package
- Redistribute
- Get Tb Auth.
- Get Adm Auth.
- Unknown [x] where x is an operation constant from sqlmon.h
Below the dividing line, there is a second group of attributes, this time at the statement level:
There are 23 attributes in all and they are described below:
| Name | Definition |
|---|---|
| Start | The time when the most recent statement operation started executing (stmt_start). |
| Stop | The time when the most recent statement operation stopped executing (stmt_stop). |
| Cpu Time | The total CPU time (in seconds and microseconds) used by the currently executing statement (stmt_usr_cpu_time + stmt_sys_cpu_time). |
| Elapse | The elapsed execution time of the most recently completed statement (stmt_elapsed_time). |
| FetchCount | The number of successful physical fetches (fetch_count). |
| Cost Est | Estimated cost for a query, as determined by the SQL compiler (query_cost_estimate). This value is reported in timerons. |
| Card Est | An estimate of the number of rows that will be returned by a query (query_card_estimate). |
| AgentTop | The maximum number of agents that were used when executing the statement (agents_top), |
| SortTime | The total elapsed time for all sorts that have been executed for the statement (total_sort_time). This value is reported in milliseconds. |
| SortOvf | The total number of sorts that ran out of sort heap and may have required disk space for temporary storage (sort_overflows). |
| Sorts | The total number of times that a set of data was sorted in order to process the statement operation (stmt_sorts). |
| Degree | The degree of parallelism requested when the query was bound (degree_parallelism). |
| Agents | Number of concurrent agents currently executing the statement (num_agents). |
| l_reads | The number of pages that have been requested by the statement to be read from the buffer pool.
pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads |
| p_reads | The number of pages requested by the statement that were physically read in from the table space containers. pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads |
| DataReads | The number of data pages that have been requested by the statement from the buffer pool (logical) for regular and large table spaces(pool_data_l_reads). |
| IndexReads |
The number of index pages that have been requested by the statement from the buffer pool (logical) for regular and large table spaces (pool_index_l_reads). |
| TempReads | The number of pages that have been requested by the statement from the buffer pool (logical) for temporary table spaces (pool_temp_data_l_reads + pool_temp_index_l_reads). |
| HitRatio | The percentage of pages that have been requested by the statement from the buffer pool that did not require a physical read in from the table space containers.
100% – (pool_data_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_index_p_reads) / (pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads) |
| MaxDbpCpu | The maximum CPU time for the statement at a single partition and the partition number of that partition.
max(sum(ss_sys_cpu_time + ss_usr_cpu_time)) where sum() is the sum across all subsections for the statement and max() chooses the maximum among all partitions. |
| IntRowsDel | The number of rows deleted from the database as a result of internal activity (int_rows_deleted). |
| IntRowsUpd | The number of rows updated from the database as a result of internal activity (int_rows_updated). |
| IntRowsIns | The number of rows inserted from the database as a result of internal activity (int_rows_inserted). |