DB2 system status and statistics
The DB2 system views provide an enormous amount of data about how a DB2 subsystem is running.
This data includes:
- Many status indicators, including warning flags and utilization measurements (pools, paging, and so forth)
- All DB2 statistics fields (IFCIDs 1 and 2)
- Buffer pool statistics totals
- DDF location statistics totals
- Additional statistics on group buffer pools for data sharing
- DB2 Analytics Accelerator statistics fields
Views for DB2 system analysis are organized into several groups, as follows:
- DB2 system status views
- User activity views
- Lock views
- Pool/page set views
- EDM pool views
- Logging views
- DDF views
For each major area, SSI views of the most important interval statistics per DB2 are available. Most SSI views have a hyperlink to a detail view showing all related fields as both interval and session values for the selected DB2.
Each SSI view can also be expanded to show a different time perspective, either multiple intervals per DB2, or even an earlier time period. Use the TIME command to retrieve the historical data. You can use the command, INCLUDE TIME, to display a column showing the time; EXCLUDE TIME removes it from the display.
You can access all the views for DB2 systems analysis by:
- STDB2 view header, which contains navigation aids and hyperlinks to related views (use the Show Header toggle to display the header)
- DB2 Statistics easy menu (EZDSTAT), shown in the following figure.
COMMAND ===> SCROLL ===> CSR
CURR WIN ===> 1 ALT WIN ===>
W1 =EZDSTAT===========DEEO=====*========05JUL2016==14:14:27====MVDB2====D====
. Exceptions | Place cursor on | . Hit Ratios
. Activity Rates | menu item and | . Group Buffer Pools
. DB2 System CPU | press ENTER | . RID Pool
. Query Parallelism +----------------------+ . Data Set Activity
. DB2 Command Counts . EDM Pool
. Storage Statistics . Temp and Work File
. Accelerator Stats . Set DB2 Subsystem
(Context)
User Activity Logging
. User Summary . Logging
. SQL Counts Locks . Checkpoint Activity
. SQL Counts II . Locks
. Subsystem Services . Global Locking DDF
. Bind Activity . Latches . DDF Statistics
. Authorization Checks . Distributed
. Routine Activity
. Dynamic SQL Cache
. Static SQL Cache . Return...
The easy menu, EZDSTATD, provides similar options when looking at just one DB2. EZDSTAT hyperlinks generally go to tabular views that show one row per DB2 in the current context. EZDSTATD hyperlinks go directly to the detail views for that DB2.
The following table lists all the views that are available for DB2 systems:
View name | Group | Type | Description |
|---|---|---|---|
AASERV | System Status | Tabular | Data Analytics Accelerator (DAA) device configuration Provides status and configuration data for a list of connected DAA devices. |
AASERVD | System Status | Detail | DAA device configuration detail Provides detail status and configuration data for a selected DAA device. |
AASTATS | System Status | Tabular | Statistics for each Attached DB2 Analytics Accelerator Provides activity statistics for the current interval and status for DAA devices. |
AASTATSD | System Status | Detail | Detail Statistics for a DAA Device Provides detail statistics for a selected DAA device for each of these periods:
|
AASQL | System Status | Tabular | Statistics for SQL activity on DAA devices Provides statistics for SQL on DAA devices for the current interval. |
AASQLD | System Status | Detail | Statistics for SQL activity on a DAA device Provides detail SQL activity statistics for a selected DAA device for each of these periods:
|
BFGBPADZ | Pool/Page Set | Detail | Global Buffer Pool Activity Detail - All Buffers Provides statistics about group buffer pools for each of these periods:
Use this view to analyze total global activity in the group buffer pools for data sharing in all pools and all DB2 members combined. For views per individual buffer pool, see Buffer-pools. |
BFRPLDZ | Pool/Page Set | Detail | Buffer Pool Statistics - All Pools Provides statistics about buffer pools for each of these periods:
Use this view to analyze total buffer pool activity in all pools combined. For views per individual buffer pool, see Buffer-pools. |
STAGENT | User Activity | Tabular | Agent Services Provides statistics about agent services in the current interval |
STAGENTD | User Activity | Detail | Agent Services Detail Provides statistics about agent services for each of these periods:
|
STAUTH | User Activity | Tabular | Authorization Checks Provides statistics about authorization checks for PLANs, packages, and user-defined function or stored procedure routines |
STAUTHD | User Activity | Detail | Authorization Checks Detail Provides statistics about authorization checks for PLANs, packages, and user-defined function or stored procedure routines for each of these periods:
|
STBFRPL | Pool/Page Set | Tabular | Buffer Pool Statistics Provides statistics about all defined buffer pools per DB2 in the current interval Use this view to analyze total buffer pool activity in all pools combined. For views per individual buffer pool, see Buffer-pools. |
STBIND | User Activity | Tabular | Bind Statistics Provides statistics about binds in the current interval |
STBINDD | User Activity | Detail | Bind Statistics Detail Provides statistics about binds for each of these periods:
|
STCHKP | Logging | Tabular | Checkpoint Data Provides statistics about checkpoint activity in the current interval and includes data capture statistics |
STCHKPD | Logging | Detail | Checkpoint Data Detail Provides statistics about checkpoint activity and data capture for each of these periods:
|
STCMDS | System Status | Tabular | DB2 Commands Provides statistics about DB2 commands that were executed in the current interval |
STCMDSD | System Status | Detail | DB2 Commands Detail Provides statistics about DB2 commands that were executed for each of these periods:
|
STDB2 | System Status | Tabular | DB2 Activity Overview SSI overview of the status of all DB2 subsystems in the context For each DB2, it displays activity rates, number of exceptions, and warning flags. Use this view to check the level of activity per DB2 and quickly determine if exceptions are outstanding. Use the Show/Hide Header toggle to display additional hyperlinks to useful related views, and for a description of the row hyperlinks in the view. |
STDASHB | System Status | Tabular | Status Dashboard Shows critical, current interval measurements for multiple DB2 subsystems in an SSI context This view shows the 'Top 10' measurements, highlighted in reverse video. Green highlighting indicates acceptable values. Red highlighting indicates values that should be analyzed for potential problems. This view can help you quickly gauge the status of all the DB2 subsystems in the current SSI context. You can adjust the thresholds to tailor the view to your environment. STDASHB also provides many other useful status and exception measurements. |
STDB2D | System Status | Detail | DB2 Status Detail–Interval Shows the overall status and activity within the selected DB2 subsystem for the current recording interval Use this view to see information concerning key indicators that apply to the entire subsystem. For example, active users, threads by type, DB2 CPU usage, locking, paging, and buffer and Environmental Data Manager (EDM) pool activity and status. Exception conditions that are detected by the background Exception Sampler or active MainView for DB2 monitors are also shown. This information provides an overall view of DB2 system activity and shows problems within DB2 that can be diagnosed with MainView for DB2 facilities. |
STDB2DS | System Status | Detail | DB2 Status Detail–Session Shows the overall status and activity within the selected DB2 subsystem for the current session since DB2 was started Use this view to see information concerning key indicators that apply to the entire subsystem. For example, active users, threads by type, DB2 CPU usage, locking, paging, and buffer and Environmental Data Manager (EDM) pool activity and status. Exception conditions detected by the background Exception Sampler or active MainView for DB2 monitors are also shown. This information provides an overall view of DB2 system activity and shows problems within DB2 that can be diagnosed with MainView for DB2 facilities. |
STDB2SYS | System Status | Tabular | DB2 System Information Provides DB2 system information on CPU utilization in the current interval You can use this view to compare the CPU usage per DB2 address space. |
STDBSYSD | System Status | Detail | DB2 System Information Detail Provides DB2 system information for each of these periods:
Use this view to see CPU usage for all DB2 address spaces. Some additional diagnostic fields are included. |
STDDF | DDF | Tabular | DDF Statistics Provides statistics about total DDF activity for the current interval Use this view to analyze DDF requester and server thread activity that is sent and received for each DB2, including the number of SQL requests, network messages, and the effectiveness of blocking. |
STDDFD | DDF | Detail | DDF Statistics Detail Provides statistics about total DDF activity for each of these periods:
Use this view to analyze DDF requester and server thread activity that is sent and received for each DB2, including the number of SQL requests, network messages, and the effectiveness of blocking. |
STDIST | DDF | Tabular | DBAT Statistics Provides statistics about Database Access Threads (DBATs) for the current interval Use this view to manage DBAT thread and DDF connection usage for a DB2 acting as the server. |
STDISTD | DDF | Detail | DBAT Statistics Detail Provides statistics about DBATs for each of these periods:
Use this view to to manage DBAT thread and DDF connection usage for a DB2 acting as the server. |
STDSA | Pool/Page Set | Tabular | Data Set Activity Provides statistics about data set activity in the current interval Use this view to analyze open/close activity. |
STDSAD | Pool/Page Set | Detail | Data Set Activity Detail Provides statistics about data set activity for each of these periods:
Use this view to analyze open/close activity. |
STEDMP | EDM Pool | Tabular | EDM Pool Statistics Provides statistics about the EDM pool in the current interval It also includes information about the dynamic SQL cache data space, or the separate statement cache and DBD pools above the 2-GB bar. Use this view to analyze EDM pool utilization and performance. |
STEDMPD | EDM Pool | Detail | EDM Pool Statistics Detail Provides statistics about the EDM pool for each of these periods:
It also includes information about the dynamic SQL cache data space, or the separate statement cache and DBD pools above the 2-GB bar. Use this view to analyze EDM pool utilization and performance. |
STEXC | System Status | Tabular | Exception Conditions Provides an overview of exception conditions Use this view to see, in more detail, which types of exceptions might be outstanding per DB2. Hyperlinks lead to more detail for each kind of exception. |
STGBFRPD | Pool/Page Set | Detail | Global Buffer Pool Statistics Detail Provides statistics about group buffer pools for each of these periods:
Use this view to analyze activity in the group buffer pools for one data sharing member in all pools combined. For views per individual buffer pool, see Buffer-pools. |
STGBFRPL | Pool/Page Set | Tabular | Global Buffer Pool Statistics Provides statistics about group buffer pools in the current interval Use this view to analyze total global activity in the group buffer pools for data sharing in all pools combined. For views per individual buffer pool, see Buffer-pools. |
STGBGRPD | Pool/Page Set | Detail | Global Buffer Pool Group Statistics Detail Provides statistics about group buffer pools for each of these periods:
Use this view to analyze total global activity in the group buffer pools for data sharing in all pools and all DB2 members combined. For views per individual buffer pool, see Buffer-pools. |
STGBLLK | Lock | Tabular | Global Locking Provides statistics about global locking in the current interval Use this view to analyze global locking activity and the level of global contention. |
STGBLLKD | Lock | Detail | Global Locking Detail Provides statistics about global locking for each of these periods:
Use this view to analyze global locking activity and the level of global contention. |
STHITR | Pool/Page Set | Tabular | Hit Ratios Provides statistics about hit ratios in the current interval Use this view to analyze buffer pool performance. The values that are used in the calculations are included in the view. |
STHITRD | Pool/Page Set | Detail | Hit Ratios Detail Provides statistics about hit ratios for each of these periods:
Use this view to analyze buffer pool performance. The values used in the calculations are included in the view. |
STLOCK | Lock | Tabular | Lock Statistics Provides statistics about locks in the current interval Use this view to analyze lock activity and the amount of contention. |
STLATCH | Tabular | Latches Provides statistics about latch counters Use this view to analyze possible latch contention, which is often an early warning of system bottlenecks. | |
STLATCHD | Detail | Latches - Detail Provides statistics about latch counters for each of these periods:
Use this view to analyze possible latch contention, which is often an early warning of system bottlenecks. | |
STLOCKD | Lock | Detail | Lock Statistics Detail Provides statistics about locks for each of these periods:
Use this view to analyze lock activity and the amount of contention. |
STLOG | Logging | Tabular | Log Statistics Provides statistics about logging in the current interval Use this view to analyze activity on the active and archive logs and the BSDS. Problems such as unavailable buffers for logging, or reads from archive logs for backouts can be detected. |
STLOGD | Logging | Detail | Log Statistics Detail Provides statistics about logging for each of these periods:
Use this view to analyze activity on the active and archive logs and the BSDS. Problems such as unavailable buffers for logging, or reads from archive logs for backouts can be detected. |
STDLOGS | Logging | Hybrid | DB2 Log Status and Configuration Provides statistics about log data set configuration, Boot Strap Data Set (BSDS) configuration, and some checkpoint information |
STMONEX | Hybrid | Tabular | Monitor exceptions Displays exceptions that are detected by:
The exceptions in the view are:
Use the hyperlinks at the top of the view to display warnings, alerts, and alarms. STMONEX is similar to the DB2EX full-screen display. |
STQPAR | System Status | Tabular | Query Parallelism Provides statistics about query parallelism in the current interval You can use this view to identify how often query parallelism is being used. |
STQPARD | System Status | Detail | Query Parallelism Detail Provides statistics about query parallelism for each of these periods:
Use this view to analyze parallelism activity on one DB2. |
STRATE | System Status | Detail | Activity Rates Provides the most critical measurements as quantities and rates per second, per thread, and per commit Use this view to analyze activity in one DB2 for both the current interval and since that DB2 started. The rate calculations give you values that make it easier to compare current activity with the totals accumulated over a longer time span. |
STRATES | System Status | Detail | Activity Rates - SSI Provides the most critical measurements as rates per second for multiple DB2 subsystems in an SSI context Set alarms based on the various activity indicators provided. |
STRID | Pool/Page Set | Tabular | RID Pool Statistics Provides statistics about the RID pool per DB2 for the current interval Use this view to analyze RID pool usage and failures. |
STRIDD | Pool/Page Set | Detail | RID Pool Statistics Detail Provides statistics about the RID pool for one DB2 for each of these periods:
Use this view to analyze RID pool usage and failures. |
STROUT | User Activity | Tabular | Routine Statistics Provides statistics about cascading, stored procedure, trigger, and user-defined function routines |
STROUTD | User Activity | Detail | Routine Statistics Detail Provides statistics about cascading, stored procedure, trigger, and user-defined function routines for each of these periods:
|
STSERV | User Activity | Tabular | Subsystem Services Provides statistics about subsystem services |
STSERVD | User Activity | Detail | Subsystem Services Detail Provides statistics about subsystem services for each of these periods:
|
STROW | User Activity | Tabular | SQL Counts II Provides row activity to help you measure the impact of performance enhancements such as multi-row FETCH and INSERT statements |
STROWD | User Activity | Detail | SQL Counts II - Detail Provides row activity for each of these periods:
|
STSQL | User Activity | Tabular | SQL Counts Provides statistics about SQL counts in the current interval Use this view to see what type of SQL activity is occurring. |
STSQLD | User Activity | Detail | SQL Counts Detail Provides statistics about SQL counts for each of these periods:
Use this view to see what types of SQL activity are occurring. |
STUSUMM | User Activity | Tabular | User Summary Provides summary statistics about current user connections Use this view to determine the current level of activity per DB2. |
STUSUMMD | User Activity | Detail | User Summary Detail Provides summary statistics about each type of user connection |
STWARN | System Status | Detail | Warning/Error Conditions Provides information about the most important key indicators of failures or degradation Use this view to see all the warning indicators that are set to Yes. |
STWKTMP | System Status | Tabular | Work and Temp Files Use this view to analyze work file storage usage and monitor how often the maximum storage limit for each agent (defined by ZPARM MAXTEMPS) has been exceeded. |
STWKTPAD (PTF BPD4881 applied) | System Status | Detail | Work/Temp Files, FTP, IAG2 Provides statistics about combined work file and temporary table usage, Fast Traverse Blocks (FTB), and Insert Algorithm 2 (IAG2/Smart Insert/Fast Insert). |
STWKTPAD | System Status | Detail | Work and Temp Files Provides statistics about combined work file and temporary table usage. |
STWKTMPD | System Status | Detail | Work and Temp Files Provides statistics about combined work file and temporary table usage. |
STZOSM | System Status | Tabular | z/OS Metrics Provides CPU and storage utilization. The values are obtained from the z/OS Resource Measurement Facility (RMF) or the BMC Software CMF Monitor product. This view displays data only if the ZPARM ZOSMETRICS is enabled and the API or CMF Monitor API is active. |
STZOSMD | System Status | Detail | z/OS Metrics Detail Provides CPU and storage utilization. The values are obtained from the z/OS Resource Measurement Facility (RMF) or the BMC CMF Monitor product. This view displays data only if the ZPARM ZOSMETRICS is enabled and the RMF API or CMF Monitor API is active. |
Related topic