DB2 LUW Version 11: 5 BLU Performance Improvements for Applications

The DB2 LUW Version 11 announcement described many DB2 BLU performance improvements and below are five important ones that will help your DB2 LUW applications improve performance.  What is good about most of these DB2 LUW Version 11 BLU performance improvements is that they are built into DB2 LUW Version 11 and will improve your system and application performance immediately with no application changes.

  1. DB2 LUW Version 11 BLU Sort Support

    DB2 LUW now supports the ability to do sorts on BLU compressed and encoded data.  This allows DB2 LUW BLU to do more processing on BLU columnar data before and above the other work in the access path plan.  By investigating the Radix sort, IBM TJ Watson Research was able to develop a new PARADIS sort that highly parallelizes in-place sort activities.  The IBM Research paper details can be found here. The information about how the performance is enhanced by this new methodology is quite interesting.

    Since sorting is done everywhere, your applications and SQL can be executed faster and with less overall CPU system resources with the use of DB2 LUW Version 11 BLU.  The implementation of the new PARADIS sort algorithm will be a major improvement for a wide range of BLU applications especially OLAP and other analytics workloads.

  2. Additional Oracle Compatibility Support

    With its Oracle compatibility since DB2 LUW Version 9, DB2 has helped many companies migrate their systems from Oracle to DB2.  Converting from Oracle to DB2 has helped improve system and application performance, while minimizing software license costs and using less hardware CPUs to run the same workloads.

    There are already a huge number of Oracle compatibility items within DB2. In DB2 LUW 11 IBM adds the new Wide Rows capability.  This Wide Rows capability allows larger row size to be used.  The rows size can now be defined bigger than the DB2 page size as long as the row does not exceed a length greater than 1MB.

    Also through CODEUNITS32 support, logical character support is now available for DB2 columnar  in DB2 LUW 11.  This DB2 columnar support provides functionality so String functions can appropriately work on data at the byte or character level.

  3. BLU Now Supports Nested Loop Join Enhancements

    DB2 LUW 11 BLU now can do Nested Loop Join within the BLU table processing.  Since the Nested Loop Join is done within the BLU processing, it can tremendously speed up join activities.  Nested Loop Joins are used everywhere within standard applications and being able to do Nested Loop Joins against BLU tables provides faster join performance.

    The new DB2 LUW 11 BLU Nested Loop processing supports any type of inner plan type processing and can do early-out and outer join type processing.  So join activities and predicate evaluation that previously had to be done on the data row level can now be done within the BLU processing.  While processing within BLU, the join activity can evaluate simple predicates and evaluate more complex predicates by using a BLU TEMP table for extra BLU predicate filtering.

    All of these DB2 LUW 11 BLU Nested Loop Join improvements dramatically enhance the BLU SQL performance by pushing more of the processing within the DB2 BLU engine.

  4. Inline Optimization Hints Now Available.

    Another new enhancement in DB2 LUW 11 is the new convention and configuration for SQL Optimization Guidelines or Hints.  SQL Hints are used to lock in access paths for performance sensitive SQL statements that need particular access paths.  The new SQL Hints convention is simpler and easier because it no longer requires the use of the registry variable.

    DB2 Hints can now be implemented by adding optimization guidelines after the SQL statement between “/* */”.  Within the SQL Hint a standard XML convention is used to detail a single Hint for the SQL statement’s access path.  Being able to add the SQL Hint directly after the SQL statement provides more transparency and documentation to the entire DB2 Hint optimization guideline situation and will be welcomed by everyone.

  5. New BLU Columnar OLAP and Other SQL Functions

    Within DB2 LUW 11 there are many standard functions that are typically used with analytics and data warehousing type applications.  These many functions for ranking, making aggregates, group window aggregates, and row numbering are now enabled to go against the IBM DB2 BLU Columnar data.  By being available within the DB2 BLU columnar engine, these functions can perform much faster and give answers more quickly to OLAP type queries.

    New DB2 LUW 11 BLU OLAP functions such as RANK, DENSE_RANK, ROW_NUMBER, AVG, COUNT, COUNT_BIG, MAX, MIN, SUM, FIRST_VALUE, RATIO_TO_REPORT, as well as  some of the group by aggregate windowing functions like ROWS/RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW will be detailed in the new DB2 LUW 11 manuals for further research and usage.

These are five DB2 LUW 11 enhancements that are just the start of all the new capabilities details being announced.  There are going to be a number of great presentations from people in the DB2 LUW Beta program along with the IBM leadership and developers at IDUG conference in May so sign up today at IDUG.org.

Check out the previous blog here for 5 other features that can be found in DB2 LUW 11.

I will be giving a security seminar and two presentations at this year’s IDUG conference. Make your plans and sign up for the IDUG DB2 Technical Conference Austin, Texas coming up this May 22-26th 2016. Also plan on attending any of my sessions.

  • “How to do a DB2 Security Audit”
        Half Day seminar Tuesday 2:15-4:30 PM Room: Frio
  • “Performance Enterprise Architectures for Analytic Design Patterns “
        Presentation Wednesday, May 25th 1:00 – 02:00 PM Room: Pecos
  • “DB2 Security Best Practices: Protecting your system from the Legions of Doom”
        Presentation May 26th 8:00-9:00AM Room: Trinity A

For more details on any of these items go to www.idug.org.

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, 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.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>