DB2 10.5 “Cancun Release” was released as DB2 LUW Version 10.5 Fix Pak 4. IBM has put a number of impressive features in this new update. Within DB2 10.5 “Cancun Release” Fix Pak 4 there are several new and interesting monitoring elements. These new monitoring elements provide capabilities to dig deeper into performance tuning efforts to get more information and feedback. These new monitoring elements come in relation to time spent, sort heap, and more information about the SQL within the package cache.
Know where you spent your time. Within DB2 10.5 “Cancun Release” there are new monitoring elements that track online backups and index creation processes. The first two time-spent elements, total_backup_time and total_backup_proc_time, capture the total elapsed time and the amount of non-wait processing time of the backups. Also, a total_backups monitor element counts the total number of backups completed. These backup monitoring elements give administrators additional information on their backup processing efficiency and show how much time is necessary for their backup procedures.
In addition to these backup monitoring elements, there are similar monitoring elements that track index creation, re-creation, or rebuild. For the index monitoring there are total_index_build_time and total_index_build_proc_time elements which capture the total elapsed time and the amount of non-wait processing time of the index creation or rebuild.
The monitoring element of total_index_build_time is the parent of element stmt_exec_time. The element total_index_build_proc_time is parent to the total_rqst_time element along with being parent to the stmt_exec_time element. There is also the element total_indexes_built which tracks the number of indexes built. This index monitoring elements provide especially useful information when DB2 automatically rebuilds indexes during an online reorg or when DB2 automatically rebuilds indexes after running out of space or another index error situation.
Understand your sort heap requirements. There are several new sort heap monitoring elements in the DB2 10.5 “Cancun Release.” These elements provide a huge amount of additional information about the sorting processes within your DB2 system. All of these elements help you understand more attributes of the sort consumers, how much of the sort heap is being dominated by a particular process, and how much memory these concurrent sort processes are utilizing. All of these sort heap monitors will also help automatic memory management understand your workload and manage the memory to maximize overall performance better.
With all of these new sort heap monitor elements and automatic memory management, you have a number of elements to tell what is happening within your system.
active_col_vector_consumers active_peas_top active_col_vector_consumers_top active_peds active_hash_grpbys_top active_peds_top active_hash_joins_top active_sort_consumers active_olap_funcs_top active_sort_consumers_top active_peas active_sorts_top post_threshold_col_vector_consumers sort_consumer_shrheap_top sort_consumer_heap_top total_col_vector_consumers
More Cache SQL Information. Next within DB2 10.5 “Cancun Release” there is more information provided by monitoring elements shown through the MON_GET_PKG_CACHE_STMT_DETAILS table function. These new monitoring elements help out in two main ways. The first, and probably the most important is monitoring SQL for suboptimal compilation. Through the prep_warning and prep_warning_reason monitoring elements, the table function can warn about SQL that might adversely impact your performance.
Get Cache SQL Object information. Through the more general MON_GET_PKG_CACHE_STMT table function, even more information is available about SQL in the package cache. The wide variety of data available now expands information about the statement id, plan id, and semantic_env_id which indicate whether or not the default schema was being used for the SQL statement.
All of this information along with the MON_GET_PKG_CACHE_STMT_DETAILS table function information can help monitor, warn, and help you better understand the attributes of SQL going against your most important performance sensitive objects.
Save information for later analysis. DB2 10.5 “Cancun Release” also adds new MON_GET_SECTION and MON_GET_SECTION_OBJECT table functions. These functions let you gather information and save it for later analysis. With the MON_GET_SECTION function you can save SQL information and pass it along to the EXPLAIN_FROM_DATA stored procedure to get more details on the SQL access path.
With MON_GET_SECTION_OBJECT table function DB2 translates the internal identifiers used in the package cache to the object type, object schema, object name, and object module information.
All of these new monitoring elements are only available in DB2 10.5 “Cancun Release” which should give everyone more motivation to adopt the new Fix Pak as soon as possible.
Dave Beulke is a system strategist, application architect, and performance expert specializing in Big Data, data warehouses, and high performance internet business solutions. He is an IBM Gold Consultant, Information Champion, and President of DAMA-NCR, former President of International DB2 User Group, and frequent speaker at national and international conferences. His architectures, designs, and performance tuning techniques help organization better leverage their information assets, saving millions in processing costs.