Dave Beulke

Materialized Query Tables (MQTs) are really great for DB2 data warehousing situations and also for all types of reporting applications. Combining multiple MQTs is one technique that is good for providing all types of reporting capabilities and answers. By combining multiple MQTs, the DB2 SQL optimizer can sometimes automatically rewrite the queries from the base tables to quickly execute against the combined MQTs. Unfortunately, even the DB2 optimizer is not always smart enough to rewrite the SQL to go against the correct MQT after you’ve combined MQTs. However, once your users find out how quickly they can get their reports by writing optimizing SQL, they will redirect their SQL query to the proper MQT or views over several combined MQTs to get quick answers and reports.

To design these types of DB2 data warehousing and reporting savings, look at the long running reports or queries within your system. One of the most common SQL optimization efforts and that has been successful for several of my clients is creating MQT and View combinations to provide Year-to-Date sales or other grand total types of information.

This design is very straight forward as noted in the figure below. There are two options to begin this SQL optimization: start with your main transaction operational table information and define a MQT, or run an extract process (or trigger) to put its data into another staging table. Care should be taken for this process because of the high volume and locking considerations. Then summarize the operational data into daily, weekly, quarterly, yearly, accounting periods or other specialized summaries. This process can be done through batch processes or MQT relationship set-up that refreshes the different levels of the MQT structures.

Also note that these structures can be supported by each other. The Quarterly data MQT is built from the Monthly data table, the Monthly data MQT is built from the Weekly data table that is built by the Daily sales data table thru the Transaction staging table. These summarizations again can be quickly created or updated through regular programs, triggers or defined through MQT relationships.

The Year-to-Date view is created with the proper calculation within its view definition or can be another MQT within the environment. Another solution is to have the view definition recreated every day to reflect the year-to-date figures. These structures can quickly provide year-to-date sales figures, or many other types of figures within your business environment.

MQTs are really great for providing quick answers for all types of business situations, including DB2 data warehousing. Combining various view definitions and MQTs provides a great opportunity for SQL optimization to eliminate long running queries and quickly provide business answers.

The beta of DB2 10 for z/OS continues to progress with the early indications that the relative performance processing bar is definitely being raised in this next version.  Early DB2 10 beta customers are truly experiencing 5-10% performance improvements out of the box.  Some are even experiencing greater improvements because their unique application processing fits or leverages some of the other new DB2 10 features.

Also most of the rumored new functionality is probably going to make it into the DB2 10 release.  This is great news for all the companies and applications that are ready to take advantage of features such as INCLUDE non-unique columns within a unique index, new HASH space and access method, online schema changes, pureXML, timestamp enhancements, temporal, time zone enhancements.

Also thank you all for your great comments about last week’s blog “Performance is Relative.”  I have trouble getting back to all the emails while working at clients, so your understanding is deeply appreciated.

The comments were great, relating how a few of you have experienced the DB2 Version 9.7 LUW compatibility features and quickly converted your databases from Oracle to DB2.  You commented that during and after the conversion, the application performance improved “substantially” with queries completing faster than ever before.  Also mentioned was how the licensing costs were reduced by going to the DB2 platform.

Next there were the comments from DB2 and MySQL administrators that are struggling to get processes to perform on the MySQL platform.  One person said their application works fine in their test environment but once it goes to production they convert it to DB2 for three reasons.  First DB2 has excellent documentation, support and overall performance. While MySQL is a good database and its open source nature may provide good documentation, the open source community support and overall performance is not at the “bet your business” level.  Sure Google and other large companies run their business on MySQL, but they have huge redundancy, a large support group and other techniques of getting around MYSQL performance problems or an availability failure.  For my client’s applications and business DB2 works ALL the time with minimal attention.

So last week’s “performance is relative” theme has been enhanced to this week’s theme “DB2 Redefining Performance.” The latest DB2 LUW Version 9.7 release with its compatibility and performance and the new DB2 Version 10 with its performance improvements give your business a competitive advantage and lower your overall costs.

This last week was very interesting in that I read a number of articles about databases other than DB2. It is always good to learn what the other vendors are up to. The first thing you learn is that fast and performance are a relative terms. Loading tens of millions of rows through ETL processes in hours has been done for many years with DB2 for z/OS and DB2 LUW but it is something new to some other database vendors or is considered “leading edge” for their environment.

Next, advanced data warehouses and business intelligence systems continue to grow providing performance issues for other database vendors. In recent white papers and performance studies IBM DB2 Cognos configurations have shown huge performance scalability capabilities against multi-terabyte data warehouses rolling out to hundreds and sometimes thousands of concurrent users. Other vendors are impressed referencing 100 million rows and having “potentially 500 users.”

Also the hardware, the number of processors, memory allocated and amount of disk storage, for some of these other vendor configurations is staggering. Some configurations have 48 processors with 4TB of disk storage for a bit over 1TB of data. This 4 to 1 ratio seems a bit over-allocated compared to the bare bones DB2 systems I have gotten to perform in the past. These other DBMS vendor configurations have sometimes 32-48GB of database memory for the processing. Again within the DB2 LUW systems that I have worked with over the years, not one has required this much memory allocated, and one of these DB2 LUW systems loaded over 200 million rows in under three hours.

So next time you read something that says “leading edge” or “performance is great” measure it against the proper scale. Ask those interesting questions about the number of processors, the disk storage needed, the memory allocated, hardware for test systems support, or the disaster recovery situation. Or you can just remember that DB2 for z/OS and DB2 LUW continue to provide excellent performance for the largest number of transactions around the world every single day and don’t need super charged hardware configurations to do it.

Please let me know what you see within your DB2 and non-DB2 hardware platforms. Does DB2 need more or less hardware as your other DBMSs?

Last week IBM announced the zEnterprise with its new integrated IBMPOWER7 and IBM System x blades environments. This provides users with the ability to run traditional mainframe applications alongside any UNIX and Intel based applications. These new capabilities provide a one-stop cloud environment for businesses to deploy and scale any application from any platform.

This week IBM announced the new deployment options for the DB2 pureScale system to the IBM System x environments. The ability to leverage the DB2 pureScale environment provides the same multi-member DB2 data sharing mainframe capabilities within a new SUSE Linux based solution. Testing shows that the DB2 pureScale environment performs and scales almost linearly, like the z/OS DB2 Data Sharing mainframe environment.

Providing these DB2 Data Sharing type capabilities within the System x architecture provides another great performance and scalability option within the DB2 LUW family. The DB2 pureScale option with its tremendous scalability provides a great expansion option for existing DB2 LUW systems that are running out of capacity within their single computing environment footprint. And now with the ability to deploy them to the zEnterprise, companies that have DB2 LUW systems can get the reliability and performance management of the mainframe environment.

This DB2 pureScale on System x also provides another DB2 LUW open solution for companies that need to consolidate UNIX or Intel based environments and applications. So regardless of the application or system requirements, the DB2 family provides the most open cost efficient options. Check out the full IBM DB2 pureScale announcement at IBM.

Within the recent zEnterprise announcement there were several exciting comments about the new query performance within the IBM Smart Analytics Optimizer. This smart system leverages the integrated platform, providing industry leading scalability for data warehouse and business intelligence capabilities.

Queries that scanned entire terabytes of data that were previously avoided can now be executed in seconds through the IBM Smart Analytics Optimizer processing. This capability provides data mining features for quickly getting answers to the clustering, associations, classification and predictions within your data warehousing environment.

Within some of the documentation, IBM testing of the Smart Analytics Optimizer shows huge performance boosts. Queries that scanned that were subsequently optimized sometimes showed an improvement of 54 times, the cost of the query 711 times. This type of improvement is very exciting and especially what new data warehousing and business intelligence workloads need on the zEnterprise platform. I’m told that the documentation that was handed out during the zEnterprise briefing will be posted soon. When it is I will post a link to the blog for everyone.

Check out all the new zEnterprise documentation—especially the information on the IBM Smart Analytics Optimizer. This is just the beginning of a resurgence of the new mainframe that supports any mainframe, UNIX or windows workload. Known as a company’s “private cloud,” it allows IT departments to leverage tremendous performance improvements while reducing query cost substantially.

The new zEnterprise IBM computer was announced last week and it can run any workload. Yes any workload! Your company can now run mainframe, UNIX and some Windows applications within the new environment.  The new zEnterprise is designed with performance and capacity for large-scale consolidation of any workload.  As has been done recently with consolidating UNIX systems into the mainframe environment, it’s now possible to consolidate the myriad of Windows based systems with the integration of z/VM 6.1.  This new computing architecture helps consolidate performance, integrate data better and improve overall management of the computing environment with unified standard performance security and optimization facilities for better overall availability.

The zEnterprise takes consolidation one step further as any workload can be implemented within its standard environment with dramatic energy savings using workload optimizers through zEnterprise’s integration of the IBMPOWER7 and IBM System x blades, allowing the consolidation of diverse application workloads.

This helps deliver the mainframe’s reliability, availability and security to these other platforms while helping your company lower risk, overall computing and energy costs.  The new architecture also helps centralize all the data from these diverse platforms to a central hub, eliminating the many copies and remote islands of data.  This improves integration, latency and performance as heterogeneous architectures perform more end-to-end enterprise transactions.

Consolidated computing through the new zEnterprise system is just beginning. Now all architectures, systems and applications can truly be evaluated for their performance.  Regardless of the operating system, programming language or network protocol, your company can get it all done with the lowest total cost of ownership with 96 of the potentially newest and fastest CPUs (5.2GHz) available on any computer in the world through the new zEnterprise system.

Check out all the zEnterprise information at the links below and the interesting comments about z/VM from PCWorld. Then imagine all your enterprise data integrated into a high performance, energy efficient and optimized system.

ibm.com/systems/zenterprise/
http://www.vm.ibm.com/linux/
http://www.vm.ibm.com/zvm610/
http://www.pcworld.com/businesscenter/article/160888/run_windows_on_a_mainframe.html