The International DB2 User Group (IDUG) North American conference is always full of great DB2 Family presentations. I try to go through all of them because they are always full of great ideas, best practices, hints and tips. I guess that’s why IDUG celebrated both DB2 for z/OS’ 30-year anniversary and its own 25-year anniversary.
The new BLU Acceleration and Columnar Data store are great new technologies and should prompt every installation to do a DB2 10.5 upgrade immediately. I discussed some of the features before and these features were highlighted in many of the IDUG presentations. The presentations really brought out all the advantages of these new technologies. Below are the 10 best reasons that I found in a variety of IDUG presentations that demonstrate the case for upgrading to DB2 10.5 as soon as possible.
1. DB2 Columnar offers massive compression
- With the new DB2 BLU Acceleration and DB2 Columnar data store there are three levels of compression within DB2 10.5: standard table compression, adaptive compression and the new DB2 columnar data store compression. All compression saves I/Os and improves all aspects of performance. Some customers using DB2 10.5 columnar tables are getting 90-95% compression of their data.
2. Leveraging CPU hardware
- The Single Instruction Multiple Data (SIMD) BLU technology is important because instead of having to issue multiple instructions to interrogate data within the chip’s buffer, only one instruction needs to be issued. By leveraging SIMD hardware capabilities DB2 is able to interrogate data simultaneously throughout the entire CPU chip hardware, speeding processing substantially.
3. Parallelism through the CPU core
- By leveraging SIMD hardware DB2 is able to bring multiple data references into the computer chip CPU and memory hardware and interrogate them in parallel, providing improved performance for SQL processing.
4. Columnar efficiencies minimizes I/O
- With the ability to store only one columnar data value for each row occurrence a minimum of I/O is needed to interrogate and retrieve data. Huge compression and a minimum of I/O are reached, especially important for data warehouse types of repeating data applications.
5. Data Skipping
- Data Skipping is a tremendous performance boost brought about by skipping data not needed by the SQL result set or overall processing. For example, if a SQL query has WHERE CUST_LASTNAME IN (‘BEULKE’, ‘KATZNELSON’,’ ZIKOPOULOS’), data skipping can skip through the data. doing a minimum of I/Os and only interrogating a minimum of data beginning with ‘B’, ‘K’ and ‘Z’ to realize the answer set extremely fast with a minimum of resources.
6. Better WLM capabilities
- The Workload Manager (WLM) algorithms within DB2 10.5 have been improved under the DB2_WORKLOAD=ANALYTICS setting to improve the workings of the DB2 Kernel to moderate the number of queries that consume resources. This helps improve performance by minimizing database and resource contention.
7. Maximizes internal CPU and RAM operations on the data
- Through better WLM algorithms the DB2 Kernel is able to maximize the CPU, memory and I/O devoted to the various SQL workloads. Since DB2 is leveraging all aspects of the computing CPU, memory and I/O hardware infrastructure, maximizing performance and reducing contention is best left to the IBM lab and DB2 optimized algorithms by leveraging the WLM algorithms.
8. New “Not Enforced Uniqueness” constraint
- Similar to the Not Enforce Referential constraints DB2 provides the ability to not enforce a unique constraint over your data. This avoids requiring the creation of an index over a database table key to enforce a unique constraint. Remember DB2 Columnar does not need indexes because it is so fast.
9. Automatic space reclaim
- DB2 automatically reclaims space in data extents that have had their data deleted. You no longer need a separate REORG or DBA management to get unutilized space within your storage.
10. Simple to implement
- All of these features, especially the DB2 Columnar data store, are seamlessly built into DB2 10.5 so no application changes are necessary. The DB2 columnar tables can be used within the same database, application and SQL statement as any other DB2 tables.
Also my article entitled “Five Imperatives for Superior Java Application Performance” was published during the IDUG conference in the latest Enterprise Systems Journal. You can link to it here.
I am presenting my Big Data presentation about how a 22 billion row data warehouse was built in only six months in the DB2 Night Show on May 17th. You can sign up at the DB2 Night Show web page.
17 MAY 2013
Agile Big Data Analytics: Implementing a 22 billion row data warehouse
Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor. Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.