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,
          QQDBCLOB1 as Plan_Cache_Properties )
   FROM   UserLib/DBMONTable 
   WHERE  QQRID=3018)

Table 1. QQQ3018 - STRDBMON/ENDDBMON
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 C - Current J - Job name A - All
Monitor_Command	QQC12	Command type S - STRDBMON E - ENDDBMON
Monitor_Job_Information	QQC301	Monitored job information * - Current job Job number/User/Job name *ALL - All jobs
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
Plan_Cache_Properties	QQDBCLOB1	Plan cache properties in XML format.