Database monitor view 3018 - STRDBMON/ENDDBMON
Displays the SQL logical view format for database monitor QQQ3018.
Create View QQQ3018 as
(SELECT QQRID as Row_ID,
QQTIME as Time_Created,
QQJFLD as Join_Column,
QQRDBN as Relational_Database_Name,
QQSYS as System_Name,
QQJOB as Job_Name,
QQUSER as Job_User,
QQJNUM as Job_Number,
QQI9 as Thread_ID,
QQC11 as Monitored_Job_type,
QQC12 as Monitor_Command,
QQC301 as Monitor_Job_Information,
QQ1000L as STRDBMON_Command_Text,
QQC101 as Monitor_ID,
QQC102 as Version_Release_Mod,
QQC103 as Group_PTF,
QVC11 as Initial_AQP_Processing,
QQSMINT1 as Current_Plan_Cache_Threshold,
QQSMINT2 as Current_Plan_Cache_Subcaches,
QQINT01 as Number_of_Currently_Active_Queries,
QQINT02 as Current_Plan_Cache_Size,
QQINT03 as Current_Plan_Cache_Size_Threshold,
QQINT04 as Number_of_SMP_Threads,
QQINT05 as Current_Number_of_MTIs,
QQINT06 as Number_of_Pruning_Listeners,
QQINT07 as Number_of_Plan_Cache_Awakenings,
QQINT08 as Number_of_Plan_Cache_Naps,
QQINT09 as Number_Pseudo_Open_queries_Hard_Closed
QQINT0A as Number_of_MTIs_Created,
QQINT0B as Number_of_MTIs_Deleted,
QQINT0C as Number_AQP_Wakeups,
QQINT0D as Number_AQP_Plans_Replaced,
QQINT0E as Number_of_Active_Queries,
QVP151 as Number_of_Plans_in_Cache,
QVP152 as Number_of_ROQs_in_Cache,
QVP153 as Number_of_Temp_ROQs_in_Cache,
QVP154 as Number_of_Reuseable_ROQs_in_Cache,
QVP155 as Size_of_Temporary_Objects_stored_in_Cache,
QVP156 as Number_of_Plans_Built_Since_Start,
QVP157 as Number_of_Plans_Used_ROQ,
QVP158 as Number_of_Plans_Used_nonROQ,
QVP159 as Number_of_Plans_Used_No_ROQ,
QVP15A as Number_of_Plan_Cache_Probes,
QVP15B as Number_of_Plans_Used_from_Cache,
QVP15C as Number_of_Plan_Cache_No_Matches,
QVP15D as Number_of_Plans_Pruned,
QVP15E as Number_of_Plans_Removed,
QVP15F as Number_of_Queries_Run_Since_Start,
QQI1 as Number_of_Query_Full_Opens_Since_Start,
QQI2 as Number_of_Full_Opens_Which_Reused_ROQ,
QQI3 as Number_Full_Optimizations,
QQI4 as Number_Reopts_with_Existing_Valid_Plans
FROM UserLib/DBMONTable
WHERE QQRID=3018)
View Column Name | Table Column Name | Description |
---|---|---|
Row_ID | QQRID | Row identification |
Time_Created | QQTIME | Time row was created |
Join_Column | QQJFLD | Join column (unique per job) |
Relational_Database_Name | QQRDBN | Relational database name |
System_Name | QQSYS | System name |
Job_Name | QQJOB | Job name |
Job_User | QQUSER | Job user |
Job_Number | QQJNUM | Job number |
Thread_ID | QQI9 | Thread identifier |
Monitored_Job_type | QQC11 | Type of job monitored
|
Monitor_Command | QQC12 | Command type
|
Monitor_Job_Information | QQC301 | Monitored job information
|
STRDBMON_Command_Text | QQ1000L | STRDBMON command text. |
Monitor_ID | QQC101 | Monitor ID |
Version_Release_Mod | QQC102 | Version Release and modification level |
Group_PTF | QQC103 | Installed Group PTF number and level. For example, 'SF99601 3' indicates that Database Group release V6R1M0, version 3 is installed. |
Initial_AQP_Processing | QVC11 | Initial AQP processing. Y indicates that the monitor file has already been processed to handle the AQP plan iteration number. All iterations other than the last one have been changed to a negative number. All other values indicate that the monitor file has not been processed. |
Current_PC_Threshold | QQSMINT1 | Database Plan Cache threshold, a value between 1-100 to represent a percent |
Current_PC_Subcaches | QQSMINT2 | Number of sub-caches within the database Plan Cache |
Num_Active_Queries | QQINT01 | Number of queries currently active |
Current_PC_Size | QQINT02 | Current size of database Plan Cache, in MB |
Current_PC_Size_Threshold | QQINT03 | Size threshold of database Plan Cache, in MB |
Number_of_SMP_Threads | QQINT04 | Number of SMP Threads |
Current_Number_of_MTIs | QQINT05 | Number of temporary indexes |
Num_of_Pruning_Monitors | QQINT06 | Number of event monitors used to prune the database Plan Cache |
Num_of_PC_Prunnings | QQINT07 | Number of times plans were pruned from the database Plan Cache |
Num_of_Plan_Cache_Naps | QQINT08 | Number of times the database Plan Cache became inactive |
Num_POpen_Hard_Closed | QQINT09 | Number of pseudo-opened queries that were hard closed |
Num_of_MTIs_Created | QQINT0A | Number of temporary indexes that were created |
Num_of_MTIs_Deleted | QQINT0B | Number of temporary indexes that were deleted |
Num_AQP_Active | QQINT0C | Number of times AQP became active, whether a new plan was created or not |
Num_AQP_Plans_Replaced | QQINT0D | Number of plans that were rebuilt due to AQP |
Num_of_Plans_in_Cache | QVP151 | Number of plans in the database Plan Cache |
Num_of_ROQs_in_Cache | QVP152 | Number of plans with a Read Only Query (ROQ) in the database Plan Cache |
Num_of_Temp_ROQs_in_Cache | QVP153 | Number of plans with temporary ROQs in the database Plan Cache |
Num_of_Reuseable_ROQs | QVP154 | Number of plans with reusable ROQs in the database Plan Cache |
Size_Temp_Objects_in_Cache | QVP155 | Size of temporary objects in the database Plan Cache, in MB |
Num_Plans_Built_Since_Start | QVP156 | Number of new plans built |
Num_Plans_Used_ROQ | QVP157 | Number of times a plan with a reusable ROQ was run |
Num_Plans_Used_nonROQ | QVP158 | Number of times a plan with a non-reusable ROQ was run |
Num_Plans_Used_No_ROQ | QVP159 | Number of times a plan without a ROQ was run |
Num_Plan_Cache_Probes | QVP15A | Number of times the database Plan Cache was probed in hopes of finding a matching plan |
Num_Plans_Used_from_Cache | QVP15B | Number of times a matching plan was found in the database Plan Cache |
Num_Plan_Cache_No_Matches | QVP15C | Number of times a matching plan was not found in the database Plan Cache |
Num_Plans_Pruned | QVP15D | Number of plans that were removed from the database Plan Cache due to size restrictions |
Num_Plans_Removed | QVP15E | Number of obsolete plans that were removed from the database Plan Cache |
Num_Run_Since_Start | QVP15F | Number of queries that were run |
Num_FullOpens_Since_Start | QQI1 | Number of queries that performed a full open when they were run |
Num_FullOpens_Reused_ROQ | QQI2 | Number of queries that performed a full open and reused an existing ROQ when run |
Num_Full_Optimizations | QQI3 | Number of queries that required a full optimization when run |
Num_Valid_Plan_Reopts | QQI4 | Number of queries that performed a full optimization even when a valid plan existed |