As a DBA I worked on a number of projects fighting fires and putting them off successfully. However in the beginning of my career I was not sure where to start, as I matured as a DBA there is a pattern that developed to troubleshoot performance issues. Each of the following areas need different kind of strategy

1. Tuning SQL
2. Creating and/or tuning indexes.
3. Physical database design.
4. Troubleshooting waits or bottlenecks (this will be the topic for the rest of this blog)

When troubleshooting bottlenecks or waits I use a number of tools such as db2top, db2mon (it provides great info), activity event monitors etc. However, if I need to quickly get an overview of my database wait statistics, I go after MON_GET_PKG_CACHE_STMT table function. If your package cache is sized appropriately, this can be invaluable in troubleshooting performance issues. Please remember that this will only provide you the wait statistics for the SQL statements that still exist in the package cache.

If you would like to see all the wait statistics available to you that are part of activities please visit below link, these same metrics are available to you via ACTIVITY event monitors in the ACTIVITY_METRICS table.

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.mon.doc/doc/c0055434.html

In this blog post, I am going to show you couple of SQL that gives you an overall picture of activity waits on your database as per the point in time snapshot of the package cache.

SQL 1:

Below SQL will provide the percentage of time your database activities spent waiting on some thing, when compared to the total activity time. Lesser this value is the better the health of your database. What is a healthy/normal value really depends on a lot of factors and business tolerance levels, I have databases that have WAIT percentages at around 10% and some of them around 30% and business is equally happy with both the databases.

SELECT VARCHAR_FORMAT(DEC(DEC(SUM(TOTAL_ACT_WAIT_TIME),20,2)/DEC(SUM(TOTAL_ACT_TIME),20,2)*100,10,2), '00.00') AS WAIT_PERCENTAGE 
FROM TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, NULL, -2)) as T WITH UR

Output from the above SQL would look some thing like below.

Output 1: From database one, non-pureScale.

Output 2: From database two, pureScale.

Now you know there are some waits in the database, read on to learn more about the waits.

SQL 2:

Now, to determine what finer components are contributing to the total wait time, you can run below SQL. This SQL looks lenghty but there is not a lot to it, I just had to PIVOT the result set using plain old SQL for the data to look pleasing on the eyes.

SELECT METRIC.TIME,
	   CASE METRIC.TIME
			WHEN 'PREP_TIME'                        	THEN WAITS.PREP_TIME                        
            WHEN 'TOTAL_ACT_WAIT_TIME'              	THEN WAITS.TOTAL_ACT_WAIT_TIME                                 
            WHEN 'DIRECT_READ_TIME'                 	THEN WAITS.DIRECT_READ_TIME                 
            WHEN 'DIRECT_WRITE_TIME'                	THEN WAITS.DIRECT_WRITE_TIME                
            WHEN 'LOCK_WAIT_TIME'                   	THEN WAITS.LOCK_WAIT_TIME                      
            WHEN 'WLM_QUEUE_TIME_TOTAL'             	THEN WAITS.WLM_QUEUE_TIME_TOTAL             
            WHEN 'FCM_SEND_WAIT_TIME'               	THEN WAITS.FCM_SEND_WAIT_TIME               
            WHEN 'LOG_BUFFER_WAIT_TIME'             	THEN WAITS.LOG_BUFFER_WAIT_TIME             
            WHEN 'LOG_DISK_WAIT_TIME'               	THEN WAITS.LOG_DISK_WAIT_TIME                                                                   
            WHEN 'LOCK_WAIT_TIME_GLOBAL'            	THEN WAITS.LOCK_WAIT_TIME_GLOBAL            
            WHEN 'RECLAIM_WAIT_TIME'                	THEN WAITS.RECLAIM_WAIT_TIME                
            WHEN 'SPACEMAPPAGE_RECLAIM_WAIT_TIME'   	THEN WAITS.SPACEMAPPAGE_RECLAIM_WAIT_TIME   
            WHEN 'CF_WAIT_TIME'                     	THEN WAITS.CF_WAIT_TIME                     
            WHEN 'AUDIT_FILE_WRITE_WAIT_TIME'       	THEN WAITS.AUDIT_FILE_WRITE_WAIT_TIME       
            WHEN 'AUDIT_SUBSYSTEM_WAIT_TIME'        	THEN WAITS.AUDIT_SUBSYSTEM_WAIT_TIME        
            WHEN 'DIAGLOG_WRITE_WAIT_TIME'          	THEN WAITS.DIAGLOG_WRITE_WAIT_TIME          
            WHEN 'FCM_MESSAGE_RECV_WAIT_TIME'       	THEN WAITS.FCM_MESSAGE_RECV_WAIT_TIME       
            WHEN 'FCM_MESSAGE_SEND_WAIT_TIME'       	THEN WAITS.FCM_MESSAGE_SEND_WAIT_TIME       
            WHEN 'FCM_TQ_RECV_WAIT_TIME'            	THEN WAITS.FCM_TQ_RECV_WAIT_TIME            
            WHEN 'FCM_TQ_SEND_WAIT_TIME'            	THEN WAITS.FCM_TQ_SEND_WAIT_TIME            
            WHEN 'TOTAL_ROUTINE_USER_CODE_PROC_TIME'	THEN WAITS.TOTAL_ROUTINE_USER_CODE_PROC_TIME
            WHEN 'TOTAL_ROUTINE_USER_CODE_TIME'     	THEN WAITS.TOTAL_ROUTINE_USER_CODE_TIME     
            WHEN 'EVMON_WAIT_TIME'                  	THEN WAITS.EVMON_WAIT_TIME                  
            WHEN 'TOTAL_EXTENDED_LATCH_WAIT_TIME'   	THEN WAITS.TOTAL_EXTENDED_LATCH_WAIT_TIME         
            WHEN 'TOTAL_DISP_RUN_QUEUE_TIME'        	THEN WAITS.TOTAL_DISP_RUN_QUEUE_TIME        
            WHEN 'TOTAL_STATS_FABRICATION_TIME'     	THEN WAITS.TOTAL_STATS_FABRICATION_TIME     
            WHEN 'TOTAL_SYNC_RUNSTATS_TIME'         	THEN WAITS.TOTAL_SYNC_RUNSTATS_TIME         
            WHEN 'PREFETCH_WAIT_TIME'               	THEN WAITS.PREFETCH_WAIT_TIME               
            WHEN 'IDA_SEND_WAIT_TIME'               	THEN WAITS.IDA_SEND_WAIT_TIME               
            WHEN 'IDA_RECV_WAIT_TIME'               	THEN WAITS.IDA_RECV_WAIT_TIME               
            WHEN 'TOTAL_COL_TIME'                   	THEN WAITS.TOTAL_COL_TIME                   
            WHEN 'TOTAL_COL_PROC_TIME'              	THEN WAITS.TOTAL_COL_PROC_TIME              
            WHEN 'COMM_EXIT_WAIT_TIME'              	THEN WAITS.COMM_EXIT_WAIT_TIME              
            WHEN 'POOL_CACHING_TIER_PAGE_READ_TIME' 	THEN WAITS.POOL_CACHING_TIER_PAGE_READ_TIME 
            WHEN 'POOL_CACHING_TIER_PAGE_WRITE_TIME'	THEN WAITS.POOL_CACHING_TIER_PAGE_WRITE_TIME
            WHEN 'EXT_TABLE_RECV_WAIT_TIME'         	THEN WAITS.EXT_TABLE_RECV_WAIT_TIME         
            WHEN 'EXT_TABLE_SEND_WAIT_TIME'         	THEN WAITS.EXT_TABLE_SEND_WAIT_TIME           
            WHEN 'TOTAL_COL_SYNOPSIS_TIME'          	THEN WAITS.TOTAL_COL_SYNOPSIS_TIME          
            WHEN 'TOTAL_COL_SYNOPSIS_PROC_TIME'     	THEN WAITS.TOTAL_COL_SYNOPSIS_PROC_TIME     
            WHEN 'LOB_PREFETCH_WAIT_TIME'           	THEN WAITS.LOB_PREFETCH_WAIT_TIME           
            WHEN 'FED_WAIT_TIME'                    	THEN WAITS.FED_WAIT_TIME
			WHEN 'POOL_READ_TIME'						THEN WAITS.POOL_READ_TIME
			WHEN 'POOL_WRITE_TIME'						THEN WAITS.POOL_WRITE_TIME
	   END AS WAIT_TIME,
	   CASE METRIC.TIME
			WHEN 'PREP_TIME'                        	THEN 
			CASE WHEN WAITS.PREP_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.PREP_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time' 
			END
            WHEN 'TOTAL_ACT_WAIT_TIME'              	THEN
			CASE WHEN WAITS.TOTAL_ACT_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time'
				 ELSE
					'0% Of Total Wait Time'
			END
            WHEN 'DIRECT_READ_TIME'                 	THEN
			CASE WHEN WAITS.DIRECT_READ_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.DIRECT_READ_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
			END			
            WHEN 'DIRECT_WRITE_TIME'                	THEN
			CASE WHEN WAITS.DIRECT_WRITE_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.DIRECT_WRITE_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'LOCK_WAIT_TIME'                   	THEN
			CASE WHEN WAITS.LOCK_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.LOCK_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'WLM_QUEUE_TIME_TOTAL'             	THEN
			CASE WHEN WAITS.WLM_QUEUE_TIME_TOTAL > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.WLM_QUEUE_TIME_TOTAL,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'FCM_SEND_WAIT_TIME'               	THEN
			CASE WHEN WAITS.FCM_SEND_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.FCM_SEND_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'LOG_BUFFER_WAIT_TIME'             	THEN
			CASE WHEN WAITS.LOG_BUFFER_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.LOG_BUFFER_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
			END			
            WHEN 'LOG_DISK_WAIT_TIME'               	THEN
			CASE WHEN WAITS.LOG_DISK_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.LOG_DISK_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END					
            WHEN 'LOCK_WAIT_TIME_GLOBAL'            	THEN
			CASE WHEN WAITS.LOCK_WAIT_TIME_GLOBAL > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.LOCK_WAIT_TIME_GLOBAL,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END            
            WHEN 'RECLAIM_WAIT_TIME'                	THEN
			CASE WHEN WAITS.RECLAIM_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.RECLAIM_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'SPACEMAPPAGE_RECLAIM_WAIT_TIME'   	THEN
			CASE WHEN WAITS.SPACEMAPPAGE_RECLAIM_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.SPACEMAPPAGE_RECLAIM_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'CF_WAIT_TIME'                     	THEN
			CASE WHEN WAITS.CF_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.CF_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'AUDIT_FILE_WRITE_WAIT_TIME'       	THEN
			CASE WHEN WAITS.AUDIT_FILE_WRITE_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.AUDIT_FILE_WRITE_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'AUDIT_SUBSYSTEM_WAIT_TIME'        	THEN
			CASE WHEN WAITS.AUDIT_SUBSYSTEM_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.AUDIT_SUBSYSTEM_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'DIAGLOG_WRITE_WAIT_TIME'          	THEN
			CASE WHEN WAITS.DIAGLOG_WRITE_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.DIAGLOG_WRITE_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'FCM_MESSAGE_RECV_WAIT_TIME'       	THEN
			CASE WHEN WAITS.FCM_MESSAGE_RECV_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.FCM_MESSAGE_RECV_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'FCM_MESSAGE_SEND_WAIT_TIME'       	THEN
			CASE WHEN WAITS.FCM_MESSAGE_SEND_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.FCM_MESSAGE_SEND_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'FCM_TQ_RECV_WAIT_TIME'            	THEN
			CASE WHEN WAITS.FCM_TQ_RECV_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.FCM_TQ_RECV_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END            
            WHEN 'FCM_TQ_SEND_WAIT_TIME'            	THEN
			CASE WHEN WAITS.FCM_TQ_SEND_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.FCM_TQ_SEND_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END            
            WHEN 'TOTAL_ROUTINE_USER_CODE_PROC_TIME'	THEN
			CASE WHEN WAITS.TOTAL_ROUTINE_USER_CODE_PROC_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.TOTAL_ROUTINE_USER_CODE_PROC_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END
            WHEN 'TOTAL_ROUTINE_USER_CODE_TIME'     	THEN
			CASE WHEN WAITS.TOTAL_ROUTINE_USER_CODE_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.TOTAL_ROUTINE_USER_CODE_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'EVMON_WAIT_TIME'                  	THEN
			CASE WHEN WAITS.EVMON_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.EVMON_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'TOTAL_EXTENDED_LATCH_WAIT_TIME'   	THEN
			CASE WHEN WAITS.TOTAL_EXTENDED_LATCH_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.TOTAL_EXTENDED_LATCH_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'TOTAL_DISP_RUN_QUEUE_TIME'        	THEN
			CASE WHEN WAITS.TOTAL_DISP_RUN_QUEUE_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.TOTAL_DISP_RUN_QUEUE_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'TOTAL_STATS_FABRICATION_TIME'     	THEN
			CASE WHEN WAITS.TOTAL_STATS_FABRICATION_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.TOTAL_STATS_FABRICATION_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'TOTAL_SYNC_RUNSTATS_TIME'         	THEN
			CASE WHEN WAITS.TOTAL_SYNC_RUNSTATS_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.TOTAL_SYNC_RUNSTATS_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'PREFETCH_WAIT_TIME'               	THEN
			CASE WHEN WAITS.PREFETCH_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.PREFETCH_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'IDA_SEND_WAIT_TIME'               	THEN
			CASE WHEN WAITS.IDA_SEND_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.IDA_SEND_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'IDA_RECV_WAIT_TIME'               	THEN
			CASE WHEN WAITS.IDA_RECV_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.IDA_RECV_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'TOTAL_COL_TIME'                   	THEN
			CASE WHEN WAITS.TOTAL_COL_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.TOTAL_COL_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'TOTAL_COL_PROC_TIME'              	THEN
			CASE WHEN WAITS.TOTAL_COL_PROC_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.TOTAL_COL_PROC_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'COMM_EXIT_WAIT_TIME'              	THEN
			CASE WHEN WAITS.COMM_EXIT_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.COMM_EXIT_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'POOL_CACHING_TIER_PAGE_READ_TIME' 	THEN
			CASE WHEN WAITS.POOL_CACHING_TIER_PAGE_READ_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.POOL_CACHING_TIER_PAGE_READ_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'POOL_CACHING_TIER_PAGE_WRITE_TIME'	THEN
						CASE WHEN WAITS.POOL_CACHING_TIER_PAGE_WRITE_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.POOL_CACHING_TIER_PAGE_WRITE_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END
            WHEN 'EXT_TABLE_RECV_WAIT_TIME'         	THEN
			CASE WHEN WAITS.EXT_TABLE_RECV_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.EXT_TABLE_RECV_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'EXT_TABLE_SEND_WAIT_TIME'         	THEN
			CASE WHEN WAITS.EXT_TABLE_SEND_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.EXT_TABLE_SEND_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'TOTAL_COL_SYNOPSIS_TIME'          	THEN
			CASE WHEN WAITS.TOTAL_COL_SYNOPSIS_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.TOTAL_COL_SYNOPSIS_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'TOTAL_COL_SYNOPSIS_PROC_TIME'     	THEN
			CASE WHEN WAITS.TOTAL_COL_SYNOPSIS_PROC_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.TOTAL_COL_SYNOPSIS_PROC_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'LOB_PREFETCH_WAIT_TIME'           	THEN
			CASE WHEN WAITS.LOB_PREFETCH_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.LOB_PREFETCH_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END			
            WHEN 'FED_WAIT_TIME'                    	THEN
			CASE WHEN WAITS.FED_WAIT_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.FED_WAIT_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END
			WHEN 'POOL_READ_TIME'						THEN
			CASE WHEN WAITS.POOL_READ_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.POOL_READ_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END
			WHEN 'POOL_WRITE_TIME'						THEN
			CASE WHEN WAITS.POOL_WRITE_TIME > 0 
				 THEN VARCHAR_FORMAT(DEC(DEC(WAITS.POOL_WRITE_TIME,20,4)/DEC(WAITS.TOTAL_ACT_WAIT_TIME,20,4)*100,10,2), '00.00')
					  || '% Of Total Wait Time' 
				 ELSE
					'0% Of Total Wait Time'					  
			END
		END AS PERCENTAGE_WAIT_TIME	   
FROM
(SELECT
        SUM(PREP_TIME)                         AS PREP_TIME                        ,
        SUM(TOTAL_ACT_WAIT_TIME)               AS TOTAL_ACT_WAIT_TIME              ,
        SUM(DIRECT_READ_TIME)                  AS DIRECT_READ_TIME                 ,
        SUM(DIRECT_WRITE_TIME)                 AS DIRECT_WRITE_TIME                ,
        SUM(LOCK_WAIT_TIME)                    AS LOCK_WAIT_TIME                   ,
        SUM(WLM_QUEUE_TIME_TOTAL)              AS WLM_QUEUE_TIME_TOTAL             ,
        SUM(FCM_SEND_WAIT_TIME)                AS FCM_SEND_WAIT_TIME               ,
        SUM(LOG_BUFFER_WAIT_TIME)              AS LOG_BUFFER_WAIT_TIME             ,
        SUM(LOG_DISK_WAIT_TIME)                AS LOG_DISK_WAIT_TIME               ,
        SUM(LOCK_WAIT_TIME_GLOBAL)             AS LOCK_WAIT_TIME_GLOBAL            ,
        SUM(RECLAIM_WAIT_TIME)                 AS RECLAIM_WAIT_TIME                ,
        SUM(SPACEMAPPAGE_RECLAIM_WAIT_TIME)    AS SPACEMAPPAGE_RECLAIM_WAIT_TIME   ,
        SUM(CF_WAIT_TIME)                      AS CF_WAIT_TIME                     ,
        SUM(AUDIT_FILE_WRITE_WAIT_TIME)        AS AUDIT_FILE_WRITE_WAIT_TIME       ,
        SUM(AUDIT_SUBSYSTEM_WAIT_TIME)         AS AUDIT_SUBSYSTEM_WAIT_TIME        ,
        SUM(DIAGLOG_WRITE_WAIT_TIME)           AS DIAGLOG_WRITE_WAIT_TIME          ,
        SUM(FCM_MESSAGE_RECV_WAIT_TIME)        AS FCM_MESSAGE_RECV_WAIT_TIME       ,
        SUM(FCM_MESSAGE_SEND_WAIT_TIME)        AS FCM_MESSAGE_SEND_WAIT_TIME       ,
        SUM(FCM_TQ_RECV_WAIT_TIME)             AS FCM_TQ_RECV_WAIT_TIME            ,
        SUM(FCM_TQ_SEND_WAIT_TIME)             AS FCM_TQ_SEND_WAIT_TIME            ,
        SUM(TOTAL_ROUTINE_USER_CODE_PROC_TIME) AS TOTAL_ROUTINE_USER_CODE_PROC_TIME,
        SUM(TOTAL_ROUTINE_USER_CODE_TIME)      AS TOTAL_ROUTINE_USER_CODE_TIME     ,
        SUM(EVMON_WAIT_TIME)                   AS EVMON_WAIT_TIME                  ,
        SUM(TOTAL_EXTENDED_LATCH_WAIT_TIME)    AS TOTAL_EXTENDED_LATCH_WAIT_TIME   ,
        SUM(TOTAL_DISP_RUN_QUEUE_TIME)         AS TOTAL_DISP_RUN_QUEUE_TIME        ,
        SUM(TOTAL_STATS_FABRICATION_TIME)      AS TOTAL_STATS_FABRICATION_TIME     ,
        SUM(TOTAL_SYNC_RUNSTATS_TIME)          AS TOTAL_SYNC_RUNSTATS_TIME         ,
        SUM(PREFETCH_WAIT_TIME)                AS PREFETCH_WAIT_TIME               ,
        SUM(IDA_SEND_WAIT_TIME)                AS IDA_SEND_WAIT_TIME               ,
        SUM(IDA_RECV_WAIT_TIME)                AS IDA_RECV_WAIT_TIME               ,
        SUM(TOTAL_COL_TIME)                    AS TOTAL_COL_TIME                   ,
        SUM(TOTAL_COL_PROC_TIME)               AS TOTAL_COL_PROC_TIME              ,
        SUM(COMM_EXIT_WAIT_TIME)               AS COMM_EXIT_WAIT_TIME              ,
        SUM(POOL_CACHING_TIER_PAGE_READ_TIME)  AS POOL_CACHING_TIER_PAGE_READ_TIME ,
        SUM(POOL_CACHING_TIER_PAGE_WRITE_TIME) AS POOL_CACHING_TIER_PAGE_WRITE_TIME,
        SUM(EXT_TABLE_RECV_WAIT_TIME)          AS EXT_TABLE_RECV_WAIT_TIME         ,
        SUM(EXT_TABLE_SEND_WAIT_TIME)          AS EXT_TABLE_SEND_WAIT_TIME         ,
        SUM(TOTAL_COL_SYNOPSIS_TIME)           AS TOTAL_COL_SYNOPSIS_TIME          ,
        SUM(TOTAL_COL_SYNOPSIS_PROC_TIME)      AS TOTAL_COL_SYNOPSIS_PROC_TIME     ,
        SUM(LOB_PREFETCH_WAIT_TIME)            AS LOB_PREFETCH_WAIT_TIME           ,
        SUM(FED_WAIT_TIME)                     AS FED_WAIT_TIME					   ,
		SUM(POOL_READ_TIME)					   AS POOL_READ_TIME				   ,
		SUM(POOL_WRITE_TIME)				   AS POOL_WRITE_TIME				   
FROM
        TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, NULL, -2)) AS T) AS WAITS,
(SELECT 'PREP_TIME'                          AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'TOTAL_ACT_WAIT_TIME'                AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'DIRECT_READ_TIME'                   AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'DIRECT_WRITE_TIME'                  AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'LOCK_WAIT_TIME'                     AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'WLM_QUEUE_TIME_TOTAL'               AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'FCM_SEND_WAIT_TIME'                 AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'LOG_BUFFER_WAIT_TIME'               AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'LOG_DISK_WAIT_TIME'                 AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'LOCK_WAIT_TIME_GLOBAL'              AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'RECLAIM_WAIT_TIME'                  AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'SPACEMAPPAGE_RECLAIM_WAIT_TIME'     AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'CF_WAIT_TIME'                       AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'AUDIT_FILE_WRITE_WAIT_TIME'         AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'AUDIT_SUBSYSTEM_WAIT_TIME'          AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'DIAGLOG_WRITE_WAIT_TIME'            AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'FCM_MESSAGE_RECV_WAIT_TIME'         AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'FCM_MESSAGE_SEND_WAIT_TIME'         AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'FCM_TQ_RECV_WAIT_TIME'              AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'FCM_TQ_SEND_WAIT_TIME'              AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'TOTAL_ROUTINE_USER_CODE_PROC_TIME'  AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'TOTAL_ROUTINE_USER_CODE_TIME'       AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'EVMON_WAIT_TIME'                    AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'TOTAL_EXTENDED_LATCH_WAIT_TIME'     AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'TOTAL_DISP_RUN_QUEUE_TIME'          AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'TOTAL_STATS_FABRICATION_TIME'       AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'TOTAL_SYNC_RUNSTATS_TIME'           AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'PREFETCH_WAIT_TIME'                 AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'IDA_SEND_WAIT_TIME'                 AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'IDA_RECV_WAIT_TIME'                 AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'TOTAL_COL_TIME'                     AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'TOTAL_COL_PROC_TIME'                AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'COMM_EXIT_WAIT_TIME'                AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'POOL_CACHING_TIER_PAGE_READ_TIME'   AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'POOL_CACHING_TIER_PAGE_WRITE_TIME'  AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'EXT_TABLE_RECV_WAIT_TIME'           AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'EXT_TABLE_SEND_WAIT_TIME'           AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'TOTAL_COL_SYNOPSIS_TIME'            AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'TOTAL_COL_SYNOPSIS_PROC_TIME'       AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'LOB_PREFETCH_WAIT_TIME'             AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'FED_WAIT_TIME'                      AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'POOL_READ_TIME'                     AS TIME FROM SYSIBM.SYSDUMMY1 UNION ALL
 SELECT 'POOL_WRITE_TIME'                    AS TIME FROM SYSIBM.SYSDUMMY1) AS METRIC
 WHERE TIME <> 'TOTAL_ACT_WAIT_TIME'
 ORDER BY 2 DESC

Output from the above SQL will look like below.

Output 1: This output corresponds to the output 1 of previous SQL. Below output is interesting because I have a semi analytical database thats taking advantage of intra partition parallelism and as a result 45.53% of 12.53% (from SQL 1) percentage of my total time is being spent with in FCM table queues for the parallelism (i.e. approximately 5.46% is the over head of the intra partition parallelism of my total activity time). As you can see there are some latch wait times and log disk wait times etc.

Output 2: Below output is from one of my pureScale databases, corresponding output of SQL 1. For my pureScale the wait metrics look entirely different compared to my non-pureScale intra partition database. As you can see my top wait metric here is physical read time i.e. POOL_READ_TIME. Most of my wait is on disk and remind you this is not bad for my environment at all. I still have healthy hit ratios and manageable physical IO. However going down you notice that there is CF_WAIT_TIME which is specific to the pureScale and it accounts for approximately 30% of the total 16.25% wait time (approx 4.8%) over head due to the CF.

Metrics like these are invaluable to understanding your workload and taking action to fix any suboptimal areas with in your database system. I hope you learned something from this article and let me know what simple SQL you use on a regular basis to troubleshoot your Db2 environments ?