The DB2 10.5 “Cancun Release” was previously available as DB2 LUW Version 10.5 Fix Pak 4.
The short list of prerequisites and summary DB2 10.5 “Cancun Release” features list is here at the IBM DB2 10.5 “Cancun Release” Fix Pak Knowledge Center website.
Within the release there are many important features, and some of the most interesting ones are related to the new DB2 Explain information. New Explain columns provide more information on the DB2 SQL access path attributes used within our applications.
There are 14 new columns which have many values to support a variety of DB2 optimizer access path information. These are highlighted below, along with a description of what the new Explain provides for our debugging and performance research.
• APREUSE
- Provides an indicator related to whether the SQL Explain output was built from SQL that was reused. This can be important to identify SQL that could be reused from your system statement cache, be resident for a long time, and not the new refreshed version that was just pushed into the test environment.
• BUFFSORT
- Indicates whether the sort process is used to buffer the result set information as it is retrieved from the database.
• BYDPART and RANDACC
- Help identify and describe the Zig Zag Join access path. These columns indicate if the DB2 access part is partition dependent which is especially important for pureScale systems. The RANDACC Explain parameter highlights whether the Zig Zag Join uses a TEMP table to facilitate and improve random access.
• DPFXMLMV
- Indicates that XML data may be moved between DPF partitions during the execution of the SQL.
• IDXMSTLY
- Indicates that the access path references the index block entries within a Multi-Dimensional Clustered (MDC) index to FETCH the result set data. This is similar to index-only access on a standard table.
• OPROFERR
- Indicates DB2 SQL optimization error(s) occurred during the parsing or applying of SQL Profiles, a new feature. By defining specialized SQL optimization profiles, access paths can be improved or standardized for access-path-challenged tables. Sometimes by applying this profile information, the parsing or optimization can encounter an error. This column, along with the diagnostic messages, can help debug the error situation.
• SKIPLOCK
- Tells whether the bind option for skipping locked data during the SQL execution is enabled. Skipping locked data can be troublesome especially in robust systems with detailed result set requirements. Be careful and monitor this for accounting and other critical result set data applications.
• PLANID, STMTID and EXECUTID
- Help uniquely identify the SQL statements for the DB2 plan, statement, and the execution id used for the Explain. These unique identifiers are very important due to the new ability to set up execution profiles which the optimizer can use to favor critical DB2 access paths for better performance.
• BUSTSENS and SYSTSENS
- Indicate whether the SQL could be impacted by the value of the CURRENT TEMPORAL SYSTEM_TIME special register. This is very important since SQL can have specific DB2 temporal table business time or system time requirements. The temporal special register and the process’s SQL temporal requirements now provide more information about the flexibility for using the business or system temporal time.
• TBISOLVL
- Indicates the DB2 access path isolation level used for the SQL against a specific table. Since the isolation level can be adjusted through a number of mechanisms, this Explain column provides the information indicating whether it is Uncommitted Read, Read Stability, Cursor Stability, or Repeatable Read.
All of these new Explain columns in the new DB2 10.5 “Cancun Release” provide more critical information about the development of DB2 SQL access paths by the DB2 optimizer. Knowing how the DB2 optimizer is treating your SQL is always better for getting the best DB2 SQL performance 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.
Also look to the IBM Insight (formerly Information on Demand-IOD conference) for more information. For more details go here.
Support IDUG through the European IDUG conference in Prague, CR November 9-14. For more details go to www.idug.org.
Leave a Reply