In late 2010 the major theme of customer conversations was that every company was analyzing ways to save money and trim their IT budget. Those cost savings efforts slashed budgets by about 8.1% according to a 2010 CIO poll. The main method used was virtualization, combining all those UNIX and Windows environments that have sprouted up over the years. The new DB2 release comes along at just at the right time because Version 10 DB2 performance tuning solutions are potentially huge for many of the common designs, practices and DB2 performance tuning problems that are being used today. DB2 10 Performance Solutions for Too Many DB2 Indexes Many DB2 index designs have far too many separate indexes defined on high performance tables. The DB2 10 Index INCLUDE COLUMNS feature provides the ability to consolidate some of those many indexes for improved DB2 performance. This will provide a CPU reduction for any data modification process and save storage with fewer indexes to manipulate and store. Even if your design cannot handle removing all the indexes, DB2 10 also improves INSERT performance by using more parallelism. When INSERT SQL modifies a table with multiple indexes, DB2 10 does the pre-fetch of multiple DB2 indexes in parallel. By initiating parallel I/Os for the multiple indexes, the process is not waiting for synchronous I/Os, reducing the overall INSERT processing time. This cuts down the timeframe of possible contention within your system and improves DB2 performance tuning opportunities for all your applications. DB2 10 Performance Solutions for Access Paths DB2 10 Optimizer SQL improvements are going to help everyone with their DB2 performance tuning efforts and especially applications and DB2 systems that have a large number of list pre-fetch access paths. These access paths are improved because the SQL Optimizer does more processing during the beginning of the data retrieval process Stage 1 SQL evaluation. The DB2 10 SQL optimizer now evaluates scalar functions and non-matching index predicates during the Stage 1 evaluation of the SQL access path. Data entries that previously waited until the Stage 2 process are done early in the optimization process and dramatically limit the number of qualifying data pages and rows that have to be evaluated. By eliminating these rows early in Stage 1, I/Os and CPU of these additional data entries are not passed into Stage 2, significantly reducing and dramatically improving query elapsed time and overall query performance. This DB2 10 Optimizer SQL performance tuning solution alone will make a huge difference in all of your application’s performance. DB2 10 Performance Solutions for All Applications These and other features in DB2 10 provide immediate CPU and cost savings for any company seeking to improve DB2 performance tuning. With additional new SQL, XML and data warehousing features, DB2 10 provides more availability, design options and DB2 performance tuning opportunities for new or existing applications. DB2 10 provides performance solutions and a way for your business to reduce costs and improve performance exactly when your CIO and company need it the most.
|
Three Things to Do to Retain Your DB2 Query Performance According to the majority of the DB2 10 Beta Customers, the performance figures for DB2 10 are true and sometimes better than the 5-10% saving right out of the box that is being advertised. But this is not to say that your DB2 10 migration and experience will be as good or better. Previous DB2 version migration horror stories abound and I have helped many clients tune and improve their DB2 system and query performance tuning. Some of these engagements have seen improvements in query performance tuning by correcting DSNZPARM settings that obviously got messed up. Did it happen during a migration, maybe? So here are three things you can do now in DB2 V9 that will help your application retain its current DB2 query performance tuning and get the most out of your DB2 10 system and application. Gather Current Performance and DB2 Explain Output To retain your query performance tuning going into DB2 10, you first need to understand and measure your current DB2 9 query performance tuning. Gather performance figures and EXPLAIN output for all your applications. Gather and understand the SQL access and overall processing of the application. Having these statistics before going into a DB2 10 migration is the first step to understand how much query performance tuning improvement your systems and applications are experiencing from the new DB2 10 features. Leverage DB2 Version 9 BASIC and EXTENDED Plan Stability Features To retain your query performance tuning leverage DB2 Version 9 BASIC and EXTENDED plan stability features. The DB2 9 BASIC and EXTENDED plan stability features are there to provide an easy way to preserve or fall back to a good package access path. By using this feature you can save off a good access path associated with the EXPLAIN information that was gathered in Step 1. Also by setting up the BASIC and EXTENDED plan stability features any special bind parameters or table/index statistics considerations can be exposed and documented before the migration to DB2 10. In addition the REBIND process will help your system make the transition to all DB2 packages from DB2 plans and bind everything in the current DB2 9. If you are migrating from DB2 Version 8, get all your DB2 Plans to Packages and make a copy of the Packages with a different OWNER or COLLID such as “SAVED” or something obvious. This way you can copy back, include the backup collection or manipulate your application to use these SAVED packages. Determine Impact of DB2 10 Next, determine whether there is a high, medium or low probability that the DB2 packages will be able to leverage or be influenced by the new DB2 10 features. The DB2 10 improved parallel INSERT into multiple indexes feature will improve elapsed time but not CPU time. The new Stage 1 SQL Optimization improvements will potentially cut both elapsed and CPU time for your applications’ query performance tuning. Determine which of your individual application DB2 packages could see a benefit from the many DB2 10 features. (Go to my DB2 10 White Paper for a complete list of the DB2 10 enhancements.) Analyze your applications and understand which ones will benefit or may have potential issues before your DB2 migration. Use your Plan Stability BASIC and EXTENDED packages to use the best performing access path regardless of whether it is DB2 10 or DB2 9 and you will definitely have success and a good experience once your DB2 10 migration is complete. Follow these three steps to insure your query performance tuning stays the same, or more likely, improves substantially.
|
Leverage DB2 Version 9 BASIC and EXTENDED Plan Stability Features Last week I talked about leveraging the Version 9 DB2 Plan BASIC and EXTENDED plan stability features. I also commented that if you don’t have DB2 9 and are coming from DB2 Version 8 to DB2 10 that you can still save your DB2 access plans. If you are migrating from DB2 Version 8, move all your DB2 Plans to Packages and make copies of the Packages with different OWNERs or COLLIDs such as “SAVED” or something equally obvious. This way you can copy back, include the backup collection or manipulate your application to use these SAVED packages. Keeping the old SQL optimization and access path information is vital. The following diagrams show how to setup a way save your SQL optimization access path and then back out a module. This worked for me a long time ago Version 3 or 4 and for a client with Version 7, so make sure you test out your setup and back out procedures. Then the process can be quickly executed by operations personnel with a REXX exec or library control process such as Endevor or other change management products. Since a picture is a thousand words below are two charts that illustrate how to set up a simple DB2 PLAN PKLIST access path preservation process. Gene Fuh and Terry Purcell wrote an article describing this type of DB2 Plan technique and the CURRENT PACKAGESET options for protecting your access paths. "Insurance for Your Access Paths Across Rebinds" for further detailed explanation. Remember DB2 Plan Stability with DB2 9 and DB2 10 is the best and simplest way to implement a method to protect your SQL Optimizer access paths. There are also software tools on the market for handling more complex backup and recovery situations. Use Version 10 DB2 Plan Stability as soon as possible, but there are several other techniques to protect your access paths and guarantee performance. My DB2 10 information has been put into a collaborative book with information from Roger Miller, Julian Stuhler, Surekha Pureka, DB2 10 for z/OS – Cost Savings ….. Out of the Box.
|
When getting my taxes ready every year, I review the previous year’s activity. Reviewing 2008 showed that the majority of my consulting was spent fixing and tuning DB2 Java based systems. This is not a big surprise since the trend and majority of my clients since 2005 have had DB2 Java performance tuning opportunities. Even in 2011, the trend of Java applications being built and implemented with DB2 z/OS and LUW continues at an ever-growing pace. In most of these client situations, the majority of the problem has not been the database or the DB2 LUW or z/OS system; it has been related to the DB2 Java application processing. Java is the new workhorse and many systems are being implemented and not performing well. Working with my clients, I’ve discovered a variety of issues with these systems and over the next several weeks I will highlight the most common factors that kill performance for these new DB2 Java application systems. By leveraging the new object model and great application development tools in Eclipse, according to a E-week poll in February 2009, Java applications with AJAX and JavaScript development are taking over as the dominate application programming languages at the start of this century -- almost twice as fast as .Net applications. Many object framework, architecture and programming pattern options are implemented with these languages. Given that there is no one object framework, architecture or application programming pattern that is right for every situation, there is neither a single right nor wrong way for performance success with your DB2 Java application. (To be continued next week.)
|
Over the last three years, my clients have shown that multiple framework, architecture and programming patterns are usually implemented within the same project. The problem is the poor performance lessons experienced from the application implementation are not fully understood and the performance problems are continued and replicated into the next architecture, framework or pattern iteration, including DB2 Java applications. Each application is different and each service or process within the application is unique. Step back and be flexible in your design patterns to understand that one or two architectures, frameworks or programming patterns are not correct for every situation. Your design should reflect the application requirements and the correct implementation for achieving the best DB2 Java performance might mean a variety or mix of approaches.
Objects within Java are great for flexibility and reuse. Java services and open source products such as Hibernate, iBatis, Ruby and techniques such as Java Persistence Architecture (JPA) and Data Access Objects (DAO) are great for accessing the DB2 database. Many of these techniques are common in today’s DB2 Java applications. My clients have experienced problems with these techniques when the application processing does not pay attention to the transaction integrity or the unit of work properly. When this happens the DB2 Java application processes usually have connected to the database multiple times, processed the transaction too many times or not committed or rolled back the transaction properly. These DB2 Java transaction situations usually manifest themselves in JDBC errors or poor referential integrity issues that developers blame on the database. Unfortunately, it is not the database but the application coding of the services that cause the problems. In the coming weeks I will talk further about DB2 Java applications, their processing and issues that I have experienced with my clients. I know it will help you avoid some of these problems too.
|
Given the object model of Java and the relational model of the DB2 database, accessing data properly continues to be difficult for most DB2 Java application developers. Over the history of DB2 Java development, there have been many attempts within vendor products, interfaces and open source projects to bridge this object to relational data chasm. Many object to relational mapping of (ORM) solutions exist but few applications leverage them properly or efficiently. Java ArchitecturesThe leading architectures providing good performance for my DB2 Java clients today, Enterprise Java Bean (EJB) specifications and Java Persistence Architecture (JPA) continue to evolve. Sometimes I have seen the open source Hibernate product implemented by DB2 Java projects looking for a quick database interface, but usually it is leveraged improperly and performs poorly. Sometimes the Hibernate interface even masks or creates problems in DB2 Java systems with its SQL handling and various parameter settings. Some clients have even written their own plain old Java object (POJO) interface to get to their DB2 data. Any of these ways to get to the data can work and get good performance if the proper application framework, architecture and design pattern is matched with the correct application and transaction type. Working with IMS, IDMS, CICS and MQ systems referencing DB2 shows that very large databases with high availability have a variety of frameworks, architectures and design patterns. Just because an application is using an object oriented language such as Java, C## or .Net does not mean that performance or good database standards and principles should not be implemented or expected. Sometimes the application focuses on making the application fit the framework and more attention should be paid to DB2 Java performance.
|
One of the first standards and principles neglected in the DB2 Java applications that I have seen is that the application references the database too many times to complete a single transaction. While it is good to use your ORM database interface, the architect and application programmer should know how many times the ORM layer is used during each different type of the transaction. Since these ORM frameworks mask the database as just another object, many programmers do not know when their Java class or web service is firing a SQL call to the database. Within some DB2 Java performance problem systems, I have seen several hundred DB2 Java application calls to the database to complete a single transaction. This level of activity will never provide sub-second DB2 Java application transaction performance. Comparing these new DB2 Java application database call levels against the other applications within any environment usually shows a substantial increase in overall usage. Sometimes the legacy transactions are only referencing the database 10-25 times while the new DB2 Java applications are referencing the database 130-175 times to complete a single transaction. Database usage during the Agile project development process or new scrum scenarios needs must be highlighted so everyone understands the overall performance requirements and expectations. Most object oriented applications have their database calls travel through the network, web server and application servers making performance monitoring and evaluation even more difficult. Even though the next buzz word of cloud computing is supposed to cache and make magic of all these transaction performance problems, not even a cloud can make hundreds of calls to the database perform with sub-second response time.
|
Designing the unit-of-work for a given transaction entails many components. Different techniques and methods are incorporated depending on the components such as Hibernate, iBatis, JPA or Enterprise Bean technology to process the transaction. The Java transaction framework and the object patterns incorporated with the components also affect the transaction unit-of-work. All these factors together provide complete flexibility for today's Java developers. Unfortunately, with this flexibility comes the responsibility to handle the transaction as effectively and efficiently as possible. For example, the various Hibernate, iBatis, JPA or Enterprise Bean technologies often shield the programmer from the database access. The database object is often passed through several methods or classes before it is used so a number of modifications could have already taken place. This same database object is also the same database SQL table access used for many different of processes and this is typical for the majority of the DB2 Java system reviews done recently. This same database object is usually a SQL access that is usually a generic SQL call to a single table retrieving all of its columns with minimal WHERE clause filtering to obtain DB2 Java data. Alternatively, the DB2 Java SQL call could be using a unique key to get a single row from the table. In both cases the SQL is fairly simple. When it only has minor WHERE clause filtering, the database access is too generic. When the access is a unique key, the access is usually too fine-tuned to retrieve the group of data desired. Sometimes the DB2 Java processing passes multiple instances of the database unique access object and the Java method processes all of these database objects. In all these scenarios, the SQL database access within the DB2 Java application does not fit the transaction processing or its unit-of-work. Generic access through the various Java persistence layers usually only provides basic performance for your transaction processing and usually retrieves too many rows for a given transaction. To achieve peak performance the DB2 Java transaction needs to access specific sets of database information and process them quickly. In too many DB2 Java systems this is a rare transaction processing situation.
|
DB2 Java performance is often a problem because the application processing is emulating the database which executes more efficiently or the processing is poorly designed. Either of these scenarios that my teams have found during performance or design reviews of DB2 Java performance of systems and applications always led to extended I/O activities and excessive CPU usage. Too often, when the DB2 Java application was designed, the full scope of the eventual implemented processing was unknown. The specifications or even the coding of the backbone framework processing began before everything was known or so many additional processing add-ons were bolted on the transaction that the original design no longer fits the transaction and it no longer performs well. When additional functions are added into the transaction scope many times, the additional data retrievals are not added into the existing SQL processing. Instead they are coded as additional stand-alone SQL calls. This leads to SQL statement after SQL statement being executed during the single transaction of a DB2 Java application. These add-on transaction functions typically add additional SQL to the transaction unit-of-work. This leads to the DB2 Java system transactions that seem to need hundreds or even thousands of SQL database calls to process their transaction from beginning to end. These large numbers of SQL calls usually touch and lock a large number of tables, inspect the data and finally perform the transaction processing. This situation typically uses excessive CPU and performs a large number of unneeded I/Os. By not combining or enhancing the existing SQL to retrieve the additional data, the overall number of calls continues to expand and the DB2 Java database performance continues to suffer. The application design is needlessly neglected when these new requirements come along. When the changing requirements result in additional SQL calls with the application itself evaluating or combining new SQL data with an existing object data store, the result is more CPU usage and poor response time. To avoid these types of situations in your DB2 Java application, understand all the data that is needed by your transaction. The application processing that combines or reevaluates data needs to be pushed back into the existing database SQL statements. DB2 does it much more efficiently. Retrieving additional data is bad for I/O, CPU, locking, and overall performance. So next time your DB2 Java transaction needs additional functionality don’t just add on, integrate your new functions into the existing SQL and designs of your DB2 Java application database processes.
|
In previous blog entries I have talked about transaction scope, how DB2 Java applications access the database too much and transaction units of work (UOWs) are not really analyzed properly . Too often these days the design and development of DB2 Java applications are done in an Agile or SCRUM type of project methodology where short concise project deliverables are designed to deliver working transactions. These methodologies are good for transactions but sometime are not good at overall DB2 Java performance. Since the scope of the Agile or SCRUM sessions are individual transactions, the big picture of the overall business and processing objectives sometimes gets lost. This leads to transactions that only accomplish a small discrete piece of the business. Other transactions are necessary and retrieve the same master customer or product information again and again in order to complete the processing activity. Database caching can mitigate and shield the impact on performance for repeatedly getting the same database information but cannot cache all the activity. When analyzing your various transactions, determine the overall business objectives and flow of your DB2 Java application. Combine standalone transactions or SOA services that use the same data keys as much as possible.
|
Another situation when a DB2 Java transaction runs into problems is when it must check something outside of the critical transaction path or its normal activity. For example, when a DB2 Java transaction uses seven different discrete web services to accomplish a complete transaction unit-of-work and after the third web service runs into a situation where something else needs to be checked. The processing then tries to resolve the situation by accessing another service and the new service experiences an error exception. In most DB2 Java database transaction environments the previous three services’ work would be rolled back and the entire transaction would need to be restarted. Within good DB2 Java processing designs the extra situation checking would be moved out of the standard flow and services transaction processing. Starting another unit of work is understandable for these double checking situations only because we want to retain the integrity of the first group of services activity already completed. Analysis needs to be completed to determine the number of times the exception processing is needed and how many times it errors out with an exception. One of the new features within DB2 Version 9.7, Cobra, is called autonomous transactions. This allows a transaction to commit a block of statements independent of an invoking transaction. This invoked autonomous transaction, implies that the work done is committed even if the invoking transaction itself is rolled back. This feature is perfect for this type of exception processing within DB2 Java applications and can be easily implemented within a web service structure.
|
Jeff Jonas' keynote session at IDUG Europe 2010 brought up several interesting thoughts and ideas. The sessions and conversations started and it seemed that Java, Hibernate and .Net systems have started to cause DB2 Java performance problems for a large number of companies. Many great hallway conversations pointed out how we all have great standards, code review, and EXPLAIN processes within our COBOL infrastructure, but have nothing within these other development environments, including DB2 Java. This is common and I always help clients with their DB2 Java performance by using Optimization Service Center, Visual Explain, the Optim Data Studio and Query Tuner products. All of these are great to quickly improve their DB2 Java, Hibernate and sometimes even .Net systems. Java, Hibernate and .Net ProjectsSeveral people wanted to hear about my experience with fixing DB2 Java performance, working with the Optim Data Studio products and how they can help with DB2 Java, Hibernate and .Net projects. We talked about how easy the SQL can get uncovered and then changed from dynamic JDBC Java processing to static SQL with the new IBM pureQuery product. For several companies their storage constrained DB2 systems can really use the reduction in the dynamic statement cache by getting these DB2 Java performance problems defined to be static applications. In addition, the bonus of getting a CPU reduction from Java, Hibernate and other JDBC connected applications from being static applications and not having to double check security, object existence and access plans is a huge business selling point for getting pureQuery implemented as soon as possible. Be sure to join us at IDUG 2011 in Prague where I'll be presenting "DB2 10 Temporal Database Designs for Performance” on November 14th.
|
DB2 offers application designers new functionality for their data warehousing requirements. The new DB2 10 Temporal Tables provide a way to have a snapshot in time of the status of customers, orders or any other type of business situation. DB2 Temporal Tables, with their built in functionality, automatically understand the business time or system time of the data entered into the system. This functionality is ideal for handling and documenting the condition of the any business aspect at a certain time. This functionality is driven from two new column definitions, BUSINESS_TIME and SYSTEM_TIME, defined within a table definition. Using these new time period columns within a DB2 Temporal Table definition provides a system-maintained, a period-maintained or bi-temporal time period for your data. Many systems today have manual processes or utilities that manage or migrate their real time data to history tables. The new DB2 Temporal Tables with their new system time and business time columns can be used in conjunction with a user-defined trigger to automatically migrate transactional temporal table data to another user defined HISTORY table. Having these facilities built into the database greatly improves regulatory compliance, operations and overall DB2 performance tuning. Separating out the real time transaction data versus the old data within your database using the HISTORY table requires planning and design steps. The separation of the old data from new data guarantees application and SQL performance does not suffer when your database is fully populated. Separation of the old and new data also helps DB2 performance tuning management so more resources can be delegated to maintaining base new transaction data where DB2 performance tuning matters for business operational success. Over the coming weeks I will go through the steps and design decisions required to set up a Temporal Table. We will go through the SYSTEM_TIME, BUSINESS_TIME and a bi-temporal table design.
|
As the 2011 European IDUG approaches, it’s good to remember the annual North American IDUG conference in May where the latest and greatest information about DB2 was unveiled. The IDUG Board of Directors and especially the Conference Planning Committee did a great job providing qualified users, consultants and IBM developers as speakers to discuss their technical experiences with DB2 for z/OS and LUW, including the new release of DB2 10. DB2 performance tuning, application development and DBA topics filled the agenda and everyone learned the latest techniques to help their companies gain the competitive advantage DB2 10 Beta program was the largest with testing by 24 companies and the most successful testing period ever for a new DB2 version. The DB2 10 for z/OS software is only about a year old and is definitely being tested by an ever growing number of companies. The very nature of a new release requires the regular introduction of the maintenance for both z/OS and DB2 for the best stable environment and to fully leverage its performance tuning improvements. It is recommended that companies planning to go to DB2 10 should plan on at least quarterly maintenance cycles to fully incorporate all APARs, Hipers, and other fixes or enhancements. DB2 10 continues to be enhanced due to some of the issues discovered during the beta program and other items that needed additional work. The DB2 for z/OS Version 9 Plan Stability feature is getting a lot of attention with its ability to help with production package access path preservation. The DB2 10 additional features of APCOMPARE and APREUSE are still being worked on. Even if these new enhancements don’t make it in the release, Plan stability continues to be a great feature introduced in DB2 for z/OS Version 9 for guaranteeing that access paths stay the same or improve with every new developer version. For those of you that are looking for the DB2 10 White Paper the sign up link for the free download of the PDF from the IBM website is below. You have to sign up because they want to know how many people download the DB2 10 White Paper, but it’s free and you can opt out of any email spam. So download it today at: https://www14.software.ibm.com/webapp/iwm/web/signup.do?source=sw-infomgt&S_PKG=db2_zos_reduce_costs
|
As the European IDUG conference approaches, it’s good to remember the outstanding technical experiences discussed at IDUG with DB2 for z/OS and LUW from many user companies. The knowledge is from users, consultants and IBMers who have implemented systems and applications and have that extra in-depth knowledge of the multiple versions of DB2, including DB2 10 from the beta program. The presentations always provide a little extra tidbit that helps you avoid problems because speakers discuss the details of their scars from the front lines. At IDUG North America, all the DB2 10 beta users’ presentations was really great. Most of the DB2 10 beta customers gave their impressions and testing results of DB2 10. They demonstrated why DB2 10 would be rolling into production environments soon. Some companies were even planning on consolidating DB2 systems or data sharing members because of the improved memory capabilities and since a single DB2 10 system can now handle almost 10 times more users as previous versions. Several companies were evaluating DB2 10 against SAP and PeopleSoft applications. The testing of these applications has gone well and the companies were waiting for SAP and Oracle to get back to them with their certification of DB2 10. It seems the beta customers are testing more extensively and faster than these software companies. It really doesn’t surprise me that Oracle would be late to certifying their application since Oracle continues to lose customers due to the improvements on the mainframe and other versions of DB2. Also the talk at the North American IDUG conference was that many Oracle shops that also have DB2 LUW are converting a large number of their Oracle instances to DB2 because of the crazy Oracle priced contracts. Saving money with DB2 LUW over Oracle continues as companies look at the performance and reliability of DB2. Expect that trend to continue as DB2 10 rolls out and companies are even consolidating their Oracle instances into their DB2 for z/OS environments. I look forward to getting the updates at IDUG Europe.
|
Corporations build business intelligence and data warehouse systems to gain competitive advantage. DB2 helps you create business intelligence and data warehouse systems that give the performance corporations need by providing many industry leading features. Combine DB2 MQTs for DB2 Business Intelligence Databases In other posts I talked about DB2 business intelligence and data warehouse designs that combine multiple DB2 materialized query tables (DB2 MQTs). Combining these DB2 MQTS with different Weekly, Monthly, Quarterly and Yearly data provides a great way to quickly get reporting information. But business intelligence data warehouse systems are more than just providing a platform to total sales figures quickly. As an IBM commercial pointed out, business intelligence is about getting a deeper understanding of your business. Your data warehouse design needs to be able to provide the extra data or information that provides context, comparisons and a deeper meaning to data. Drill Down for Requirements for Your DB2 Business Intelligence and Data Warehousing Systems Understanding the flow of the sales totals, noticing whether sales trends are improving or declining is only the beginning. The ability to drill into your data warehouse information quickly and meaningfully is the start of a business intelligence system. Being able to drill through big department categories and then also analyze to the tiniest pixel color levels within unstructured data items is becoming part of data warehouse requirements for new systems. When you are designing your new DB2 data warehouse business intelligence system, don’t stop with the major questions that are going to be asked. Follow up to uncover additional questions that will be asked after the first question gets answered. After you have uncovered four or five levels of questions, you’re ready to start the DB2 business intelligence data warehouse design. This way your system can provide better value and deep insights into improving the business.
|
The improvements in DB2 10 technology are definitely making building performance into business intelligence systems easier. Additional SQL data warehousing extensions such as Rank, Dense_Rank, and Materialized Query tables (MQTs) were great when they came out in DB2 8 and DB2 9. DB2 10 takes another giant step with data warehousing additional features. Use a DB2 10 Temporal TableDB2 10 provides Temporal and Bi-Temporal database tables along with the OLAP capabilities of moving sums, averages and aggregates. These additional improvements within the DB2 engine, SQL and table designs provide faster result set capabilities for overall business intelligence performance. Also the DB2 10 enhancements with the new scalar and table functions can provide performance for all types of OLAP activities. Since these business intelligence performance features are built directly into the DB2 10 engine, temporal designs, moving sums, averages and aggregates will become standard calculations and properties for all reporting requirements. Calculations will be done using different groups of temporal time periods or location-based data for product sales, store location or other common data warehouse criteria. Data warehousing performance will improve dramatically as DB2 data warehouse applications are quickly designed to leverage these common built-in features and functions for all types of business intelligence performance requirements. By using a temporal data warehouse design along with partitioning, time frame or common table design, the standard OLAP functions can provide quick calculations for complex or simple data warehouse reporting requirements. Also given the improvements within SQL, moving sums, averages and aggregates can be included in common table expressions, SQL SELECT lists or ORDER BY statements to satisfy any application requirements Leverage DB2 10 for Business Intelligence PerformanceBusiness Intelligence performance always improves when the designers and developers leverage the DB2 engine built in functions. Use temporal tables, bi-temporal, moving sums, averages and aggregates so your business intelligence performance will only get better with DB2 10.
|
It is usually pretty easy to quickly implement new DB2 features. DB2 makes it easy to improve your database performance with a new zParm or table space definition. Unlike most of these new features, however, DB2 temporal tables need research before you implement them. DB2 temporal tables offer great flexibility and many data warehouse design options that can be leveraged very effectively--or be abused--with the wrong application design. To prepare you need to evaluate whether the application is appropriate for temporal tables. First with DB2 temporal tables it is even more important to determine the frequency of the inserts, updates and deletes that are going to happen. Frequencies are always a good design point for any application but it is especially important for DB2 temporal tables because of the way BUSINESS_TIME or SYSTEM_TIME is maintained and how all the data changes are captured within the associated history temporal table. Every data change could really be two processes because rows need to be replicated into your history table. That could be a major performance consideration. The next research points are the restrictions with DB2 temporal tables and history tables. The temporal table must be a regular table with the added BUSINESS_TIME or SYSTEM_TIME. No clone table capabilities, column masks, row permissions or security label columns are allowed. The same restrictions are in place for the history table. The temporal table and its associated history table must be kept in sync. Restrictions also exist regarding the altering, adding or removing columns into the temporal table and the history table to guarantee integrity. Also backup and recovery for the temporal table and its history table must be kept in sync and there are restrictions around DB2 Utilities that could delete data from these tables. In addition once the history table is defined, its table space or table cannot be dropped. So make sure the columns desired in your DB2 temporal tables are stable and well defined for the application. There are several resources that should be reviewed before designing your first application using DB2 temporal tables. The first is the IBM DB2 10 manuals. By reading these friendly manuals you get a good understanding of the syntax, various examples and details about all the restrictions. Next there have been some presentations about DB2 temporal tables at past IDUG conferences and IOD conferences. Track down you colleagues that went to the conferences and get the CD or access to the website for the presentation downloads.
|
To figure out the best temporal table design aspects you need to think of the various options and considerations that will affect its performance. The most important aspect for your temporal table is the answers that your applications or users are expecting from it. The best way is to figure out the time aspect that the application is trying to capture. Are your applications looking for the financial value, the insurance coverage level, enrollment status, customer value or something else? The temporal table status can be contingent on two types of settings: business time or the system processing time. If the processing is delayed and the system time is later than expected, does that affect your temporal table status? Or are you using the temporal table in a real time scenario where either the business or system time will affect the meaning of the data? There are many ways to respond to the situations and questions, but the design decision should be based on the application and user questions that need to be answered. So it is best to test both SYSTEM_TIME and BUSINESS_TIME scenarios out and see which design provides the best answers with the best performance. The next design point is to figure out your timestamp type. Do your temporal table application answers require distinct timestamps throughout the system? Your DB2 10 system now has new capabilities to provide a column that is unique within the table system wide. This DB2 syntax is defined WITHOUT OVERLAPS and can be used for your temporal table only for your BUSINESS_TIME values. After the temporal table is created, an index is defined for it using your unique columns and the BUSINESS_TIME WITHOUT OVERLAPS keyword. BUSINESS_TIME is the only option the WITHOUT OVERLAPS keyword works with. When BUSINESS_TIME WITHOUT OVERLAPS is specified, the columns of the BUSINESS_TIME period must not be specified as part of the constraint. The specification of BUSINESS_TIME WITHOUT OVERLAPS adds the following to the constraints: The end column of the BUSINESS_TIME period in ascending orderThe start column of the BUSINESS_TIME period in ascending orderThe minimum value of a TIMESTAMP(12), the value is 0001-01-01-00:00:00.000000000000The maximum value of a TIMESTAMP(12), the value is 9999-12-31-24:00:00.000000000000For DATE the minimum is 0001-01-01 and the maximum value is 9999-12-31. A system generated check constraint named DB2_GENERATED_CHECK_CONSTRAINT_FOR_BUSINESS_TIME is also generated this definition process to ensure that the value for end-column-name is greater than the value for start-column-name. BUSINESS_TIME WITHOUT OVERLAPS must not be specified for a PARTITIONED index. There are a number of considerations when creating your DB2 10 temporal table. When your application needs it to be unique, the system wide the BUSINESS_TIME option provides the capabilities with some cautions. Check out other posts on temporal tables via Developer Works or at my site, www.davebeulke.com.
|
Was 22 billion rows Big Data? As I began the new data warehouse project, I knew that the data warehouse performance needed to be designed from the very first data element. The new project was very interesting with complex calculations, cross referencing reports and Big Data implications and big data warehouse performance challenges. Many standard reports that affected whole industries and government policies were depending on data warehouse performance for timely reports and 100% not even 99.999% accuracy of every bit of the Big Data. Another consideration was the implementation deadline for this high performance Big Data data warehouse system was only 120 days away and wasn’t movable. All of these reasons made this Big Data system quite the challenge. Our small team of business users, SQL analysts and technical staff were committed and we were successful in completing the implementation using agile scrum development techniques. The data warehouse performance was so improved that some of the processes went from 37 hours to under 7 minutes and some reports come back in seconds. Complex calculations and report verification done during our data quality testing found bugs in the old system’s existing reports that had been trusted for over 20 years, so we knew everything was 100%. Our overall performance, 100% accuracy and overall quality implementation were a testament to the commitment of a winning team effort. There were many success factors that drove the team, the design, development and successful implementation of this 22 billion row Big Data system. Three success factors drove the data warehouse performance. First: Keep it simple. Yes I know everyone talks about keeping it simple in every development and system design. Designing it and doing it simply can be much harder. Instead of endless discussions and meetings, design the big picture and then see how all the reports, every type of interface and users interact with the warehouse. Data warehouse performance is achieved through computing power. Simple designs that leverage parallelism, that drive I/O and calculation computing power for quickly producing reports are key. Second: Leverage all existing infrastructure and processes. The tight 120-day schedule from start to finish did not allow the team to have issues, only time delayed solutions. Since we used an agile scrum type of development methodology the agile scrum stories and the data warehouse performance of the Big Data reports were tested several times during the short development period. All used the same standard corporate testing procedures, documentation and approval processes that had been used for years. The team contacted everyone that needed to guarantee the data warehouse performance, got them involved early and got their requirements, feedback, improvements and eventual approval for all the components and implementation. Everyone knew the Big Data project schedule ahead of time so all infrastructure and processes were ready. Issues were worked on until solutions were approved and implemented. Third: Don’t be intimidated by Big Data. I have been fortunate to design, develop, deploy and study data warehouse performance since back in 1988 when I implemented my first “Big Data” system for the financial firm E.F. Hutton. At least back then it was considered Big Data and it seems that all the other data warehouse systems I have dealt with over the many years have had bigger data then the previous one. Big Data will keep getting bigger and it is just another couple of zeros on the end of your table population and design considerations. Data warehouse performance is built on the same principles as many years ago. As I mentioned with the first success factor, design for both I/O and CPU parallelism in every single item of your system. Only then can your Big Data system have the data warehouse performance it needs to speed up your processes and reports from hours to just minutes or even seconds making everyone, especially your users, happy
|
Well the votes are in, 22 billion rows is big enough data. It’s not the billions of web logs rows of a Google or Facebook but its big enough for everyone. One of the comments that struck me was that one in a million happens 22,000 times. So whatever your criterion is for big data, it is more a state of mind about the amount of data as opposed to the actual terabyte amounts or the number of rows. Regardless of what database systems you work with, big is a relative term. Just ask your SQL Server, Oracle and Sybase DBA friends what they consider a big system. Usually the answer is nowhere near what you get for DB2 z/OS or even DB2 LUW systems. I talked about this a last year in my blog ("Performance is Relative"). Other comments and questions received about last week’s blog asked for more clarification on the idea of keeping a database design simple. So below are three different ways to keep your big data data warehouse design simple. First: There are reasons Bill Inmon’s and Ralph Kimball’s decentralized and centralized data warehouse ideas are so popular, those design patterns work. Design patterns for all types of IT applications, Java/.NET MVC (model view controller), various business models and standard processes have been extensively analyzed and endorsed over many years through the design pattern books, conferences and government studies. The decentralized and centralized data warehouse design patterns work and your design should use them for your data warehouse performance. Big data or not, there is no reason to do something more complex. Starting with these types of design patterns, using and optimizing simple Fact table(s) surrounded by Dimension tables(s) design pattern will provide you data warehouse performance. Decentralize or extend these design patterns with as many Fact tables and slow moving Dimension tables will optimize and minimize the amount of big data referenced in typical transaction and your data warehouse performance won’t be an issue. Second: Make sure to normalize your big data design. It’s typical to try to consolidate everything within a data warehouse performance design. Unfortunately having too many elements in a table forces too much data into an application transaction and data warehouse performance can suffer. Just as decentralized and centralized data warehouse performance design patterns have been used for years, database table normalization has been around for even longer because it logically optimizes your design. The database design normalization process has been documented everywhere over the years and it is effective for making sure the table elements relate to the whole database table key(s). Combining table keys or designs causes excessive repeating data or data groups and over-normalization leads to excessive application joins. Normalization is striking a balance and no one does it perfectly the first time. Normalize your data warehouse performance design several times and your transaction performance can strike a balanced performance for all critical applications. Third: Design, test, redesign test and repeat. Schedule enough database and application meetings and testing time to understand all the application transactions and reports. Data warehouse performance and modeling big data can get unwieldy, so testing your design early is vital. Sometimes big data table population numbers cause tools to abort. Cut the number of zeros down and model all your application transactions against your database design, build it and run applications against it. Data warehouse performance requires real life testing and actual running of the code or SQL that interfaces with the design. No one has any time to do it perfectly but everyone will be mad if you have to redesign and do it all over right before implementation. Know your performance before production through thorough testing. Big data and data warehouse performance requires design and testing. Make sure to do these several times during your development with as much big data as possible. These are only some of the simple truths that insure your data warehouse performance for your big data system is a success.
|
Previously, I’ve talked about the new DB2 10 temporal tables and how they are great for data warehousing applications. To leverage the temporal tables and data warehousing applications within the DB2 for z/OS environment the system needs the proper DSNZPARM settings. Since everyone has too many systems, some organizations only review system DSNZPARMs during migrations, meaning that many of the new settings might not be set up or enabled. It’s a good idea to review the DSNZPARMs on a regular basis and get them set up to maximize the performance of your data warehousing application SQL by leveraging all the DB2 optimizer capabilities. First, get a listing of the current DSNZPARMs for your data warehouse system’s settings. This can be done a number of different ways: through your performance monitor, through the JCL that creates the DSNZPARMs or through the IBM REXX exec of the DB2 stored procedure DSNWZP. This DSNWZP stored procedure routine is one of the many DB2 administration procedures and is set up during the system install. The data warehouse DSNZPARMs affect the performance of the DB2 system and its applications in many different ways, from the DB2 optimizer’s access path choices to the number of parallel processes DB2 will perform during data retrieval. To make sure that all DB2’s performance features and functions are available to improve performance I've created an alphabetic list of DB2 data warehouse DSNZPARMs. Making sure these data warehouse DSNZPARMs are enabled and adjusted for your system will help your application fully exploit DB2 and get the best performance possible for your data warehouse.
|
Previously we talked about the first alphabetic group of DB2 data warehouse DSNZPARMS that can improve your access paths and overall application performance. This week the second set of DSNZPARMS are discussed. Many of the data warehouse DSNZPARMS discussed are somewhat hidden within the regular DSNZPARM install panels. All of these DSNZPARMS discussed are available in DB2 for z/OS DB2 Version 9. Some are available in DB2 Version 8 or DB2 Version 10. Caution needs to be taken with all system settings and especially these data warehouse DSNZPARMS. These DSNZPARMS are meant to change access paths and improve them, but each data warehouse design is unique along with each application access path, so results will vary. If the data warehouse DB2 subsystem is shared with other OLTP or operational applications, I highly recommend fully documenting and setting up a full PLAN STABLITIY plan and package management structure for your current access paths before changing any DSNZPARMS. This documentation along with a good PLAN STABILITY DB2 plan and package management implementation and back out practices helps your environment quickly react and back out any detrimental access paths encountered through unexpected rebind of any program. Some of the comments I’ve received regarding this issue highlighted the resurgent of data warehousing on the z/OS platform and why running a data warehouse on z/OS provides many advantages over other platforms. One that was noted from several people is when your data warehouse runs on z/OS, the huge ETL processes usually don’t have to transmit the data over a network. Even though the network bandwidth is robust, avoiding this extra bottleneck can sometimes save hours of extra overhead, guaranteeing that your refresh data jobs have enough time every day to provide critical refreshes of you data within your data warehouse. Additionally most of your source data warehouse data comes from the z/OS operational systems and can quickly be put into operational business intelligence data warehouses. This fresh data increases sales, provides real time inventory or product availability updates and, the most important factor, removes latency for all your critical single point master data source of record for the enterprise. Improve your system and application performance by adjusting these data warehouse DSNZPARMS to improve your access paths and by using the superior DB2 optimizer technology and most efficient performance available.
|
The previous Part 1 and Part 2 discussions highlighted and discussed the various DB2 Data Warehouse DSNZPARMS for z/OS within Versions 8 and 9. With the new DB2 Temporal Tables the system needs all the DB2 Data Warehouse DSNZPARMS to be enabled and all the other new and improved DB2 10 DSNZPARMS for maximizing performance. To ensure the new DB2 DSNZPARMS are enabled, below is a listing of the Depreciated, Improved or new DB2 DSNZPARMS to make sure your data warehouse and other applications get the best performance available within your DB2 10 environment. Since the storage model of the new DB2 10 system has moved much of the processing components above the 2GB memory line, most of these new and improved DB2 DSNZPARMS deal with new maximum memory settings. If you are monitoring system paging, running on hardware that has enough memory or running on one of the new 196 hardware platforms, leverage the new DB2 memory capabilities and the new hardware as soon as possible. Remember even with a number of the DB2 10 components moving above the 2GB memory bar, there are still many components (sometimes as much as 25%) below the bar. Monitor your system paging and the size of DB2 memory footprint and adjust your settings incrementally and carefully. Remember that monitoring, analyzing, improving and repeating in small increments is the best way to provide the best DB2 Data Warehouse DSNZPARMS and a stable high performance environment. To access the DB2 10 DSNZPARM data warehouse chart in PDF format, please go to DB2 Information on www.davebeulke.com.
|
During the 2010 IBM Z Summit road show, there were several presentations detailing the mainframe platform advantages over UNIX and Windows platforms such as the lowest total cost of ownership, the best availability and unparalleled scalability. These presentations cut through the rumors with detailed facts and figures of different platform configurations. Download these presentations and distribute them to your management for a little reminder why the mainframe continues to be the best platform for your enterprise applications.The Windows and UNIX platforms proponents always discount and minimize the total cost of ownership, availability and scalability topics. It is our duty to periodically remind management of the extra costs of these UNIX and Windows systems with their huge power consumption costs, software license fees, and software maintenance costs of working with several hundred or thousands of disparate systems. The mainframe quietly continues to process the majority of the transactions at the Fortune 500 companies and everyone, especially younger management types who think the world can run on an iPhone, needs to understand that the System Z infrastructure is the best backbone for any company. The System Z mainframe is also evolving since it now has specialized processors such as the IFL, zIIP and zAAP to reduce overall operational and licensing costs. These specialty processors, along with the smaller configuration of the System Z offer a single small platform that can consolidate any number of UNIX workloads into a single footprint with a smaller greener energy footprint and better licensing configuration. The presentations detail benchmarks, licensing fees and labor costs of various mainframe versus UNIX platform configurations. The figures show it sometimes takes double the number of processor cores on a UNIX configuration to start to scale out a configuration. Even more UNIX processors are required to achieve transaction rates that are still only performing one-fourth of what the mainframe System Z executes. These UNIX systems are also dedicated to the production transaction environment with no thought of supporting testing, QA or failover facilities that have yet to be priced or considered, features that come standard within the System Z environment. System Z also continues to grow because of its faster chips. Ask any PC or UNIX platform personnel “what platform has the fastest clock speed processors” and you will quickly find out who keeps up with the industry information. The chip clock speeds of the System Z and other IBM platforms have improved like the rest of the PC industry. In fact, the System Z z10 chip operates at 4.4 GHz and comes in a 64-way quad core configuration that can speed up any application performance problem. This is almost twice as fast as the HP Superdome processors and a third faster than the Intel Nehalem chips. So the mainframe continues to lead the industry. Does your management know the cost savings and performance figures of System Z? Tell them and show them the presentations before someone tries to “replace the mainframe” again with a more troublesome, power hunger, bad performing clustered iPhone configuration.
|
After the original post of “Reminding Management of the Advantages of System Z,” many people commented on how their management is increasingly out of touch with the mainframe. Also, comments also stated that the System Z environment is really processing almost all of the transactions in their company and how all the Windows platform systems continue to have scalability issues. Although the mainframe revenue for IBM suffered in 2009 because of its upgrade cycle, the introduction of the z10 System platform demonstrates it is the best open system. Yes, that is correct, the mainframe is the most open system available because it runs all types of workloads: the legacy standards of Assembler, COBOL, PL1 etc. but also C++, C#, Java, PHP and the rest of the languages that run on UNIX and Windows boxes. Also, some people are starting to run “virtualized” windows on the mainframe environments. PCWORLD highlighted this capability in early 2009With the System Z speed, scalability and network, the mainframe continues to be the best solution for all types of workloads. A nice short demo of z/Vos is on YouTube along with many other videos that you can show to your iPhone-obsessed boss, demonstrating that consolidating those hundreds of MS SQL Server instances is also possible. The story of virtualization continues to drive UNIX consolidation to the mainframe. In 2009 Allianz consolidated 60 servers into a single mainframe, saving substantial operating, licensing and energy costs while improving scalability. This story detailed in this ComputerWorld article is being repeated at many companies as the mainframe IFL, zIIP and zAAP specialty engines continue to bring processing power at PC or minimized prices. This consolidation activity has a very short term return on investment as these efforts pay for themselves usually in the first year and reduce power consumption dramatically making it a “green” project. The next time the hundreds of windows or UNIX server configurations need an OS, database or other software upgrade make sure to mention how System Z is saving other companies time and operating costs and overall costs through consolidating these environments to the best cost alternative -- the mainframe System Z.
|
Whether you are currently using DB2 Version 8 or Version 9, getting ready for all the CPU efficiencies in DB2 10 is easy. Your company may not be thinking about migrating to DB2 10 yet, but DB2 10 is getting huge industry wide support as the cost savings began to be realized by management. Everyone wants to save money and with everyone trying to squeeze their budgets, a faster adoption of DB2 10 is in your future. It is best to be prepared, so here are a few things you can do in DB2 Version 8 that will make it easier to get to DB2 10. Embrace System Managed Storage SMSNo, I am not Darth Vader telling you to embrace the dark side, but to let you know that DB2 10 catalog and directory use SMS. Using SMS is a hard thing to do for many companies and personally, I am not a real big fan of SMS. DB2 10 and additional improvements in storage have made me think twice and SMS may be more help than a problem. Being a performance geek, I recommend limiting or not sharing storage volumes within the DB2 SMS definitions. The SMS performance key, if possible, is to segregate the DB2 SMS definitions completely, yes completely, away from other storage definitions. SMS horror stories about the I/O troubles are well known and through my consulting clients I see SMS at the center of many I/O problems. Embrace SMS but be diligent guarding your DB2 system’s I/O performance and especially your DB2 catalog and directory data sets. Sharpen Your Security I am a big supporter of compliance, protecting data assets, security audits and all the security team’s efforts for protecting the company and all the business information. DB2 10 offers more privileges and authority granularity in your DB2 systems and security experts will need to embrace these features to separate out your data access, utility operations and day-to-day work. Start auditing your security usage of your systems area, various DBA support groups, different local and distributed applications, and analyze their needs. Do the activities require data access? Could the DB2 DBA work maybe been done using a different security level such as DB2 SYSOPR or another security authority? More DB2 10 security privileges and authority granularity improvements are going to affect your security profiles. Get ready and start the security analysis now. Learn more about DB2 10 through the replay of Roger Miller’s and my 2010 webcast: DB2 10 for z/OS – Helping you improve operational efficiencies and gain competitive advantage. The webcast replay is available here… http://www-01.ibm.com/software/os/systemz/webcast/13apr/index.html?S_TACT=100GU00M&S_CMP=SP
|
DB2 upgrades are not the same in any two companies and may not even be the same within the same company. As multiple DB2 systems sometimes support different applications with varying requirements, the Return on Investment (ROI) is completely different. Also, comparing DB2 z/OS or DB2 LUW to Oracle system upgrades also does not even begin to be similar or comparable as all these databases have completely different contract clauses, ROI requirements and upgrade factors. Apples and oranges are both edible, but that is where the comparison and similarities stop.DB2 upgrades depend on the technology. First-hand experience has shown that bad research, poor database design, poor application development techniques, bad choices in third party vendor tools and an assortment of other factors make ROI equations for upgrades complex or almost impossible. Having helped fix bad technology decisions, redesigned bad database designs and fixed SQL application performance problems, I know that reducing costs is always on everyone’s mind. Upgrading any software especially raises cost consciousness. To minimize upgrade costs, strive for better understanding and knowledge of the DB2 technology. This will help everyone avoid unnecessary costs and bad decisions. Work on minimizing the DB2 10 upgrade costs through better management of your DB2 systems today. I’ve mentioned the considerations of SMS environments and improving your security definitions to get ready to separate security data access. This week the suggestion for preparing for DB2 10 is to begin analyzing your DB2 application techniques. The first thing to analyze is how your plans and packages are put together. IBM’s DB2 engineers have been telling everyone to get to DB2 packages within their environment for many releases. In DB2 10 the old plan management technique of DBRMs going directly into plans needs to be replaced by collecting them within COLLIDs and then binding into your DB2 plans. It is a small difference but it has big implications. If your shop still uses the technique of putting DBRMs directly into plans, it is time to start a process to migrate to the new method. Next, the compliers that your applications utilize also need to be current. DB2 10 changes within the COBOL pre-compiler generate new SQLCA and SQLDA structures along with generating COMP-5 binary data attributes instead of COMP-4 or COMP. Even though there are still some ways to handle old compilers it is best to review your COBOL compiler versions to make sure they will be ready for your eventual DB2 migration. ___________________________________________________________________ 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.
|
The North American IDUG Conference and the IOD EMEA conference highlighted all the functionality and features within DB2 10. The more you hear about these functions and features, the more you realize they are really going to save your company money right away. Justifying a DB2 upgrade is always necessary for management and for the new DB2 10 it is going to be a lot easier with its many CPU-saving features. With all the new features saving from 5-7% right out of the box in conversion mode, DB2 10 offers existing systems and applications immediate savings. Add in all the new options such as Hash Access, Index Include Columns and other features and developer and DBA activities become a lot easier, reducing your overall DB2 CPU capacity requirements possibly up to 20%. Think of getting a 20% decrease in your costs and the costs of the associated third party software and you can see why management is going to push for getting DB2 10 installed as soon as possible. One of first ways DB2 10 reduces your CPU within your systems and applications, is through a new access path called Range-List Index Scan. This new access path should immediately assist many of your applications that have multiple WHERE predicates that reference the same index. In DB2 9 the access paths scanned the index for each of the WHERE predicate references. Within DB2 10 the Range-List Index Scan scans the index only once to retrieve all the RIDs, saving the extra CPU and I/Os of multiple separate scans, RID consolidation and elimination of duplicate RID entries. This can be a huge improvement for applications that have paging or other types of searching SQL WHERE predicates When your applications are being prepared for migration to DB2, the plan stability features started in Version 9 and improved with DB2 10 will assist you. Within DB2 10 this enhancement lets the application manager lock down any package for a particular access path. If the access path is changed through an attempt to rebind the package, the process can supply an error or provide a warning within the Bind process. This protects your access paths against access path problems and alerts your developers to a possible problem. So if you want a 20% decrease in your CPU demand or just want to start thinking about staying up with your competition, start your plans for DB2 10 now __________________________ Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 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.
|
Within all database systems there are tables that hold transactions or some type of data based on time. These transactions unfortunately build up over time to handle regulatory compliance, infrequent access or support development of historical and trending reports. Within your database design, this time-based transaction data builds up quickly and managing it so the volume does not affect performance is critical. Sometimes the frequency or grain of transactions such as web clicks, banking transactions, stock transactions or other transactions build up to terabytes of data per day, quickly overwhelming robust storage arrays. Separating out the real time transaction data versus the old data within your database requires planning and design steps. These planning and design steps help the separation of the old data versus new data and guarantee application and SQL performance does not suffer when your database is fully populated A separation of the old and new data also helps performance management so more resources can be delegated to maintain transaction performance for business operational success. DB2 10 Temporal Tables with their built-in functionality automatically understand the business time or system time that the data was entered into the system. This functionality is great for finding out the condition of the business as of a certain time. There are two new BUSINESS_TIME and SYSTEM_TIME table period definitions columns available. These new time period column definitions are used for the new DB2 temporal table definitions to provide system-maintained, period-maintained or bi-temporal (when both system and period maintained) database tables. Many systems today have manual applications or utilities which migrate their real time data to history tables. These new temporal tables with their built-in system and business time columns can be used in conjunction with a user-defined trigger to automatically migrate transactional temporal table data to another user defined HISTORY table. Having these facilities built into the database greatly improves regulatory compliance, operations and overall performance. More information can be found on DB2 10 Temporal Tables and other DB2 10 features in the IBM White Paper: IBM DB2 10 for z/OS Beta -- Reduce Costs with Improved Performance.
|
The zEnterprise IBM computer was announced in July 2010 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
|
Within the zEnterprise announcement in July 2010 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 and then
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.
Check out all the
zEnterprise documentation—especially the information on the IBM Smart Analytics
Optimizer. This was 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.
|
In July 2010 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.
In August 2010 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.
|
As each
version of DB2 for z/OS comes out there are always the projects that need
justification for going to the next DB2 version. The huge number of DB2
performance features in DB2 Version 9 for z/OS should be an imperative to get
to the DB2 9 or DB2 10 release. This large number of DB2 performance features
in DB2 V9 for z/OS can make a huge difference in your system, database and
application performance.
These
large number of performance enhancements in DB2 Version 9 for z/OS also are
accompanied by a large number of operational and availability enhancements that
provide new functionality. The combination of all these new Version 9 features
can make your life as a DBA or an application developer dramatically better.
Some of
these features are discussed in my new presentation, “Improving Performance with DB2 V9 for zOS,” that has been posted for download. Also in this
presentation there is a full list of all the Version 9 enhancements that is great
to include as reference for a justification that management wants for your
migration to the latest DB2 Version 9 for z/OS.
_______________________________________________________
Dave Beulke is an
internationally recognized DB2 consultant, DB2 training and DB2 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. Search for more of Dave Beulke’s DB2
Performance blogs on DeveloperWorks and at www.davebeulke.com.
|
Recently
working with a client’s SOA environment showed several interesting DB2 performance
issues. One DB2 performance issue that was quite stunning was the large number
of connections that the .Net and Java applications were making to DB2 and other
systems. Researching the system and application further uncovered a wide
disparity in the handling and the amount of connections each of the many
application modules were using. Proper connection handling is very important to
DB2 performance because of three main reasons: acquiring new connections is
expensive, application connections maintain database locks and connections are
unit of work transactions.
First,
getting a database connection is expensive because of all those great things
that a database provides such as security and integrity. Since the database is
important, every connection request must have its security checked and be
authorized. This security authorization against the database system and the
data desired is quick, but takes time. Next, when database processing
guarantees integrity, it is through its transaction logging of the unit of
work. Starting a new database unit of work again is fast but must be managed
within the database so that it can be backed out should the transaction
processing fail.
Next,
within the database connection, the SQL processing selects, inserts, updates, or
deletes data. These actions holds locks against the data referenced and prevent
applications from trying to update the same data or reference the same deleted
data. The DB2 applications have several mechanisms to control and handle this
locking within the application and system. The best way for DB2 performance is
to Bind the application against the database is using the Bind parameters for
cursor stability ISOLATION(CS) and CURRENTDATA(NO). This minimizes the
immediate locks held and allows other transactions more concurrency to the
data. If the application is read only and is not concerned with other
transaction manipulating the data then use uncommitted read ISOLATION(UR).
Using the ISOLATION(UR) setting is preferred for application referencing data
that doesn’t change.
Next, the
application unit of work must maintain the connection. Large application
workloads that perform too many updates, inserts or deletes within a unit of
work hold on to too many locks and can cause extended back out times when an application
fails and impact DB2 performance. It is very important to have the proper
transaction commit scope, issue appropriate commits to minimize the amount of
locks and amount of work that the database may have to back out. It is also
critical for the applications to reference the database tables and perform
their updates in the same sequence. Referencing the data in same order acquires
and releases locks synchronously, allowing more application concurrency. Since
your application wants to minimize the number of locks and the time those locks
are held, it is always best to do your data updates and inserts right before
your application performs a commit or ends your transaction. This minimizes the
time the locks are held and again provides more workload concurrency.
While all
of this information is standard practice for most applications, within the new
SOA architectures the services may not know much about the unit of work or
connection situation. Within one client SOA architecture, recent research showed
that a particular module had seven different connections active within its
service. The service had several connections; DB2 for z/OS, DB2 for LUW,
Oracle, MQ series inbound and outbound Queues and connections to application
and web servers for AJAX activities. It is a bit much to have all of these
connections within a single service and when some minor changes caused this
module to fail many processes could not function. Also debugging was very
difficult because one connection failure caused all the connection participants
to back out their transactions, causing more locking and data integrity issues.
So make
sure your application handles connections properly because they are expensive
to acquire and impact DB2 performance. Minimize the number of database
activities within a transaction to minimize the locks and understand the number
of connections that are involved within a particular unit-of-work so that you
can get the best DB2 performance from your applications. _______________________________________________________
Dave Beulke is an
internationally recognized DB2 consultant, DB2 training and DB2 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. Search for more of Dave Beulke’s DB2 Performance
blogs on DeveloperWorks and at www.davebeulke.com.
|
Many years
ago, IBM System Z announced the introduction of additional new specialty
processors. The first System Z specialty engine introduced for data sharing was
the internal coupling facility (ICF), next the integrated facility for Linux
(IFL), next the application assist processor (zAAP) and the latest is the
integrate information processor (zIIP). All of these specialty engines take the
burden off the general processors, reducing the software licensing costs within
System Z environments.
The
savings from the zIIP and zAAP specialty engines are especially dramatic when
processing different types of DB2 workloads. Within the System Z environment,
the zAAP engines are available for Java workloads and XML System services. This
Java processing is especially important for the System Z application server
workloads running on WebSphere, Tomcat and other Java web and application
processing. Next, as the unstructured data world integrates into database
systems, the XML processing workload offloaded to zAAP engines continues to
assist the growth within everyone’s environment.
The zIIP
engines are especially great for handling some very popular and intense
processing within DB2. The first is for the processing of remote DB2 access
processing, parallel SQL query work, IPSec network encryption, additional XML
services, and assisting with the DB2 utility sort processing. It is critical to
understand and analyze the potentially substantial quantity of these types of
processing used by these specialty processors in your environment.
At a
recent client, the DBAs saw large requirements of these types of workloads and
prompted the capacity planners to dig into the zIIP and zAAP utilization
analysis. Analysis discovered that the environment workload was maxing out the
zIIP engines and the workload was taking capacity from the general processors.
The client ordered and installed more zIIP processors and reduced their general
processor utilization by providing more zIIP capacity. This also reduced their
overall applications response time and provided more overall capacity at a
minimum of cost.
The zIIP
and zAAP processors utilization is not always on the radar but needs attention
periodically. The capacity planners’ and DBAs’ analysis should dig deeper into
the overall utilization of these specialty processors and analyze whether
additional specialty engines might relieve the burden on your general
processors. Not only will these zIIP and zAAP processors relieve the stress on
your general processors, but they can also reduce your overall peak CPU
utilization and your MSU software licensing costs. This will save your company
money while zIIPing and ZAAPing your DB2 performance and your overall
application environment.
_______________________________________________________
Dave Beulke is an
internationally recognized DB2 consultant, DB2 training and DB2 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. Search for more of Dave Beulke’s DB2
Performance blogs on DeveloperWorks and at www.davebeulke.com.
|
In 2009
IBM announced DB2’s newest feature, DB2 pureScale.This new feature within the
DB2 Family of products represents the next step in overall database performance
because of its unlimited capacity, application transparency and continuous
availability.The DB2 pureScale feature provides the ability to balance the
workload across a dynamic number of DB2 servers that share a common set of
disks while managing all the database concurrency issues.
Currently,
DB2 pureScale is implemented across DB2 cluster service nodes running on the
IBM Power Servers leveraging InfiniBand network connectivity using the Tivoli
System Automation.DB2 pureScale will be available first on the IBM Power
Servers and then rolled out to other configurations in the future.Through these
facilities, the DB2 pureScale feature is able to dynamically add new server
capacity transparently for any application.This provides continuous
availability and instantaneous scalability capabilities for all applications.
By being
able to grow or shrink your DB2 cluster servers on demand, your business can
provide resources where they are needed and only when they are needed.DB2
pureScale provides the ability and flexibility to deploy DB2 resources for days
or weeks and only pay for the software stack when those resources are in
use.This technology flexibility limits the business cost by only paying for the
resources for this short period of time when they are in use.This can
dramatically reduce overall hardware and software costs for any business and
especially for businesses that have seasonal or fluctuating workload demands.
IBM’s
testing of the DB2 pureScale feature shows tremendous scalability as the number
of DB2 servers are increased for the workload.By balancing the workload across
the DB2 database servers the configuration retains 95% of its scalability
through 32 server nodes and 81% scalability up to 112 DB2 server nodes.All of
this capacity with application transparency and automatic fail over provides a
tremendous computing power for any application requirements.
This DB2
pureScale feature leverages unique optimization of the global locking and
caching coherency interfaces of the DB2 cluster nodes through DB2 Cluster
Services.These Cluster Services provide centralized and duplexed mechanisms,
like a DB2 for z/OS data sharing coupling facility, for managing the global
locking and caching needs of the entire cluster application workload. The
centralization of the Cluster Services provides many advantages over other
clustering technologies, like Oracle RAC, by minimizing the lock and cache
communication to multiple servers.This is another reason DB2 continues to lead
the industry in innovation, performance, scalability and continuous
availability.
For more
information go to the IBM DB2 pureScale web site at
http://www-01.ibm.com/software/data/db2/9/editions-features-purescale.html
or the DB2 pureScale press release can be found at
http://www-03.ibm.com/press/us/en/pressrelease/28593.wss
_______________________________________________________
Dave
Beulke is an internationally recognized DB2 consultant, DB2 training and DB2
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. Search for more of Dave Beulke’s DB2
Performance blogs on DeveloperWorks and at www.davebeulke.com.
|
I received some great comments and
questions based on the blog entry/video about the many new features in
DB2 Cobra. One of the more interesting questions was asked “How much work or
how compatible is the new DB2 with Oracle?”The answer DB2 is very compatible,
so compatible in fact, that many people have migrated from Oracle to DB2 in only
days.
So what are the reasons or justification
for management to consider changing their database from Oracle to DB2? The top
four reasons I have heard recently are:
·
Oracle
contract pricing continues to go up. Every time we look Oracle wants more money
because we had to add CPU cores to get application performance.
·
Application
performance for our BI OLAP Oracle work does not respond. We always have to
rewrite the SQL or create new indexes to make it work. When we tried it on DB2,
their optimizer automatically rewrites the SQL avoiding the manual SQL rewrite,
improving application performance and overall response time.
·
DB2
disaster recovery and HADR feature is much better than any Oracle’s stand-by or
their fail-over solution. Now that the application is compatible with DB2 there
is no reason to risk the business because of an Oracle failure.
·
Saving
IT budget money is paramount these days and with our Oracle database, we
continue to spend money on expanding disk requirements. DB2 data compression
and the new index compression provide a way to reduce disk costs, improve
database performance and minimize backup and recovery while reducing disk
requirements and saving money.
Converting your database from Oracle to DB2 is very easy. You can save
money for your company, your IT department, and maybe your job by migrating
today.
_______________________________________________________
Dave Beulke is an
internationally recognized DB2 consultant, DB2 training and DB2 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. Search for more of Dave Beulke’s DB2
Performance blogs on DeveloperWorks and at www.davebeulke.com.
|
Another great
feature that is in the new Version 9.7 of DB2 is its new compatibility with
Oracle. This compatibility takes many forms within the database such as
understanding operational syntax, stored procedures keyword syntax and improved
operational concurrency that I covered in this DB2 Performance blog.
The DB2
compatibility with Oracle was developed because many clients are migrating from
Oracle to DB2 for its improved DB2 performance, lower licensing costs, ease of
use and lower total cost of ownership (TCO). All of these factors are very
compelling given the current economic times for cost savings, consolidation of
servers and lower CPU and energy costs of processing within DB2 as compared to
other DBMSs.
DB2
Handles Oracle’s Non-Standard Names
Many of the
operational aspects of standard databases such as the data definition language
(DDL), data manipulation language (DML) and structured query language (SQL) and
data control language (DCL) are standard across databases such as SQL Server
Oracle and DB2 Family. Over the years Oracle has called parts of these DDL, DML
and DCL, non-ISO standard names, to perform operations. DB2 has added these
unique Oracle non-standard operational keywords to its lexicon of syntax to
perform these operational activities. The most popular is the Oracle TRUNCATE
statement that is used everywhere to clear the contents of a table.
DB2
and Oracle Stored Procedures Compatibility
Another major item
is stored procedures compatibility because they are utilized everywhere. Again
with all the standard programming languages such as Java C++ C# and others,
over the years the syntax was controlled by the ISO and other standards
committees. The various DBMS vendors took liberties with their stored procedure
languages do perform certain processes. Again DB2 has added these Oracle syntax
items so that any developer that is familiar with Oracle will be able to
quickly and effectively code for any of these DBMSs. This one feature is
critical for many companies because all companies have multiple different DBMS
vendors and have a programming staff that needs to write stored procedures on
all of them.
Oracle
to DB2 Migration Success
I recently worked
with a client during the DB2 Version 9.7 Cobra program and found that the
syntax for both the operational aspects and the stored procedures is about 95%
there for general conversions from Oracle to DB2. Working with the dedicated
client staff, some of my consultant associates and the IBM Cobra people, we
were able to migrate a vendor product from Oracle to DB2 Cobra and then take
the DB2 code from the Linux, UNIX and Windows platform and run the application
on DB2 for z/OS environment. Given these Oracle to DB2 compatibilities, the
client is positioning to leverage the lowest TCO in the industry of DB2 on z/OS
with a vendor and their solution. This is remarkable because they didn’t even
understand the mainframe concepts only three months ago.
Another Oracle to
DB2 migration project done by a friend of mine was completed in only three
days, so the conversion work can be done very quickly and you can become the
next hero lowering your company’s total costs within IT. Migrate to DB2 today
and save your company money, it might even save your job or your coworker’s
job.
_______________________________________________________
Dave Beulke is an
internationally recognized DB2 consultant, DB2 training and DB2 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. Search for more of Dave Beulke’s DB2
Performance blogs on DeveloperWorks and at www.davebeulke.com.
|
In the past, we
have examined in-depth the DB2 performance problems that might be found in a
typical DB2 installation. My purpose in doing this was to show you how a
methodical review can help you discover ways to reduce system costs. Good application
review and positive changes can even save substantial investment in new
hardware.
Be sure to check
through the various blogs to hone in on the type of analysis essential for your
site. Below is a review of some of the topics we've covered over the past four
months.
Analyze Your System
- Use RUNSTATS to determine what is
really going on in your system. Redo and review them for analysis as the
system is implemented and grows.
- Using performance reports and drilling
down into the DB2 catalog can give you valuable information about specific
performance problems. Use these tools to discover:
- Processes
that use excessive resources
- Underlying
data objects that might be causing some performance problems.
- Reasons why
data object definition, particularly index definition, might be causing
performance problems.
- Determine the number of abends,
deadlocks and dumps. These can take a tremendous amount of CPU resources
and should be researched and eliminated as soon as possible.
- Check secondary extents for all data sets,
including SortWorks
- Check to see if there are lots of
times that a write engine is not available. Look at the size of your
buffer pools, look at the deferred write threshold and look at the timing
of your jobs and possibly eliminate the times write engines are not available.
- Verify the checkpoint frequency of
your systems. Having the correct checkpoint frequency can provide the
proper checkpoints for disaster and regular recoveries of your databases
and system. The recommended checkpoint frequency is about every 10 to 15
minutes. So depending on how busy your system is adjust the number of log
records accordingly.
- Make sure that your Work Load Manger
(WLM) is set up properly to distribute the CPU resources adequately and
properly to the various database systems and applications. Having the
database at the same or below the applications can cause huge performance
and overall throughput problems.
- Insure that your DB2 System
Maintenance is at the appropriate level. Do you have maintenance plans
that include checking the Service Pack levels?
To be continued...
_______________________________________________________
Dave Beulke is an
internationally recognized DB2 consultant, DB2 training and DB2 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. Search for more of Dave Beulke’s DB2
Performance blogs on DeveloperWorks and at www.davebeulke.com.
|
Make sure that you are using the correct database design to improve DB2 performance: - Use partitioning for robust tables that play central parts in the processing to help ease locking and encourage parallelism. Also leverage database partitioning for large tables (over 500,000 rows) which might benefit from spreading out the I/O to different partitions and different devices.
- Use database compression for those databases that have a large row sizes and a majority of read access profiles.
- Do you have any simple tablespaces that should be changed to segmented tablespaces for better DB2 performance? Are your segmented tablespaces properly sized?
- Do you have frequently updated tables with a large number of indexes? Double check to see if any of these indexes can be eliminated to improve DB2 performance. Having extra indexes over busy tables can be a huge DB2 performance overhead. Deleting two or three extra indexes can sometimes cut one-quarter or one-half of a second off your transaction response time. So make sure you only have the indexes that are being used defined on your tables.
Use application programming and SQL techniques correctly- It is important to drive processing to the SQL engine as much as possible. Review applications in your company for:
- Lack of SQL Joins
- SQL Sub queries
- Table expressions
- CASE expressions
- Limited fetch
- Review applications in your company for:
- Excessive use of tablespace scans
- Excessive use of list prefetch and index scans
- Use the new DB2 pureQuery for DB2 Java applications to improve DB2 performance, security and statically bind the application access path within your environment.
- Do you have too many locks against your tables? How many lock escalations happened today? The answer to these questions lies in looking at the order of your application processing and your programming methodologies so that concurrent transactions can occur easily.
- Data performance reviews need to be exhaustive in order to achieve all the possible savings possible. Data access reviews are an important part of these reviews. Also:
- Analyze the types of access paths that are active in your environment.
- Review the number of invalid plans and packages
- Check the use of table compression for large tables with frequent Select statements run against them.
_______________________________________________________ Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 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. Search for more of Dave Beulke’s DB2 Performance blogs on DeveloperWorks and at www.davebeulke.com.
|
Make sure that the interface with CICS works well to improve performance: - Validate the settings between your DB2 CICS transaction configurations. Make sure the maximum workload from one TOR, AOR or DOR is not overwhelming another CICS processing partner.
- If your system is heavily dependent on CICS transactions:
- Make sure that you are reviewing the CICS Statistics DB2 Connections reports on a regular basis.
- Update the RDO/RCT settings so that the DB2 workload is using mainly dedicated and reused threads.
- Insure that your RDO/RCT settings use a minimum of pool threads.
- Make sure that your workload never has to wait for a thread to execute work in your DB2 CICS environment.
- Handle DB2 security correctly to improve DB2 performance:
- Make sure that you aren't duplicating security checking with both DB2 and another security product.
- Use group ids for your DB2 authorizations as much as possible so that plan, package and CICS thread authorizations can be reused.
- Review DB2 bufferpool activity to improve DB2 performance:
- A DB2 bufferpool should be sized correctly for its activity. Make sure to put data objects and index objects in different bufferpools. Data objects with similar access characteristics should be defined to the same bufferpools.
- EDM pools maintain enough free space.
- RID pool is adequately sized and RID pool overflows are not happening in your environment.
- Is your Dynamic Statement Cache Pool set to the appropriate size for your system? Are your settings encouraging SQL caching?
- Check the health of your database objects to improve DB2 performance:
- Are there DB2 tablespaces and tables that need to be reorganized? Check for a large amount of data set extents and a large number in FAROFFPOS.
- In order to use indexes optimally,
- Check to insure that indexes leverage uniqueness
- Make sure that clustering indexes make sense and that the table is clustered in the most popular application processing sequence order and maintains a CLUSTERRATIO of 95% or higher
- Make sure that index columns are in optimum order for your most frequently executed application WHERE clauses.
- Make sure that all defined indexes are being used
- Do you have a reorganization schedule an appropriate freespace that keeps DB2 tables cleaned up with a minimum of outage?
If you do the intensive review suggested here, you'll find that you will become the "go to" person for DB2 performance. In these economic times, saving the corporation money will make a positive impression within the company. _______________________________________________________ Dave Beulke is an internationally recognized DB2 consultant, DB2 training and DB2 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. Search for more of Dave Beulke’s DB2 Performance blogs on DeveloperWorks and at www.davebeulke.com.
|
In this post, I’ll delve a little deeper into the system architecture and CPU reduction opportunities found in a major DB2 system at a large financial institution. ( To see first part of case study, click here.) AnalysisIn order to do a complete performance analysis of the system, the system and application statistics were reviewed using the standard DB2 performance reports. This data provided a basis of the various system, database, application and SQL observations and improvement recommendations. These statistics along with system, process, and application documentation, interviews with application programmers and observations of the workload guided the investigation of the CPU consumption and CPU reduction effort. Current Enterprise ArchitectureThe enterprise architecture had evolved over the years to support many diverse database systems. This caused several databases to be cloned and their transactions workloads to be intermixed. This combination of CICS transactions provided a diverse workload of different data requirements, run time durations and application types. This combination of workloads runs on a single CPU mainframe environment that supports both the test and production environments. Workloads come into the system through a variety of interfaces: CICS, Visual Basic and Web applications using MQ Series Connect and batch jobs throughout the day. These applications access a variety of database tables that support the corporation’s nation-wide business needs. The enterprise applications environment with a mix of applications operates efficiently experiencing occasional dramatic CPU application requirement spikes. These application CPU requirement spikes manifest themselves throughout the day when CICS program errors occur and dumps are created. These dumps cause the system to pause and dump the transaction situation. This occurs too frequently; almost once every 15 minutes in the production CICS region. Busy business periods of multiple concurrent transactions with a large memory footprint also show stress within the systems. Work Load Manager The architecture of the system and its performance are controlled through a variety of software with Work Load Manager (WLM) playing a central role in overall system performance.WLM controls CPU and provides priorities of the different subsystems, online workload and batch processes. Analysis of the WLM settings needed to be done to determine the optimum and most efficient workload software settings and determine whether the DB2, CICS, and batch transaction have the compatible settings to maximize throughput. Observing the system processing discovered that the workflow accomplished is fluctuating when the systems has errors or dumps occurring in the various CICS regions. These dumps against the system workflow showed that the system CPU peaked and workflow was severely impacted. When an on-line program error or dump occurs its core dump documentation and resolution are the highest priority within the system stopping or pausing all other work. An example of the problem occurred by 10:30 a.m. on a summer day. Five regions had 27 errors/dumps occur by that time, which is one every four minutes (27/150 minutes) during the production work day. Industry standards typically have a very small number of these errors or dumps occur in their production regions. This problem directly related to the application quality assurance testing and this situation will only continue to degrade the overall workflow and overall performance of the systems. CICS Region Configuration and Allocations The architecture of the CICS systems and the on-line programs reflects how additional data and capabilities have been added. New CICS regions and databases have been added to the workload as additional systems were added to the workload and additional features added to the applications. These workloads were each separated into their own regions. To improve the overall workflow and provide further room to efficiently grow the CICS transaction workload a Sysplex architecture could be considered. The CICS Sysplex architecture separates the workload out to terminal owning regions (TOR), application owning regions (AOR) and data owning regions (DOR) that can be finely tuned to service each specific type of workload. These regions work together to spread and balance the peak transaction workloads. Summary All of these architecture, system, database, application and SQL considerations provide the opportunity for CPU cost reductions. These cost reductions could be achieved through system tuning, database design analysis, application SQL documentation and application coding standards and reviews. Implementing these has the potential of saving tremendous CPU capacity and delaying a CPU upgrade. - Analyze the number of abends, deadlocks and the number of dumps within different parts of your applications. These deadlocks and dumps take a tremendous amount of CPU resources at critical times within your system.
- Make sure that your Work Load Manger (WLM) is set up properly to distribute the CPU resources adequately and properly to the various database systems and applications. Having the database at the same or below the applications can cause performance and overall throughput problems.
- Validate the settings between your CICS transaction configurations. Make sure the maximum workload from one TOR, AOR or DOR is not overwhelming another CICS processing partner.
________________________________________ 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.
|
With this post we are going to delve deeper into problems and recommendations to improve DB2 performance tuning and DB2 security. DB2 Performance Discoveries and Recommendations The operating system, CICS and DB2 systems were operating efficiently and managed well within the overall environment. As the number of transactions and systems continue to grow, the systems will require more CPU and performance tuning attention. As the number of different diverse workloads, the software packages and number of transaction increases the amount of time and resources to manage and tune the efficiency of the systems will continue to increase regardless of the platform, database or application. DB2 CICS RDO Thread Reuse The connection between DB2 and CICS is very important. These connections settings are handled through the RDO interface that has many DB2 thread reuse and security settings. Based on my experience, these settings can have a dramatic impact on performance and CPU consumption. The settings were reviewed for the DB2 workload transactions running through the systems CICS environments. These environments averaged approximately over a million transactions per day and had extensive DB2 workloads. The current CICS and DB2 connection settings did not fully leverage the performance settings that were available to improve connection security, thread and DB2 package or plan reuse. My recommendation was that the settings should be monitored and updated monthly to reflect the transaction workload and dedicated threads defined for many of the larger volume CICS DB2 transactions. Given that the company has just begun implementing these RDO settings and improvements, there were still opportunities for RDO tuning through the priority, security and plan settings. The dedicated threads were recommended to be run at the normal priority setting, with a security group profile that allows users to reuse their DB2 plan execution authorizations. These settings along with the number of threads allocated, protected and use in the CICS DB2 Pool needed to be fined tuned for each of the environments. Reviewing and improving these settings through the CICS Statistics DB2 Connection reports that are buried inside the overall CICS Statistics report provides great details on the transactions and thread usage. Changing these RDO settings had an immediate positive effect by reducing CPU consumption. The company could save more by fine tuning all their RDO settings. DB2 Security Improvements The DB2 environment had security settings that were used in conjunction with the security system, Top Secret. The company was using both DB2 and Top Secret security extensively, causing additional system-checking overhead. DB2 and Top Secret should be configured to leverage the use of secondary authorization security verification. The use of secondary authorization security checking is common at many large government and banking clients and should be implemented as soon as possible. By using secondary security, the system checking security is done once and reused because of the grouping that allows CICS transaction threads to be reused. This will save the extra system overhead of double-checking security for every transaction execution and save CPU. Summary - If your system is heavily dependent on CICS transactions:
- Make sure that you are reviewing the CICS Statistics DB2 Connections reports on a regular basis.
- Update the RDO settings so that the DB2 workload is using mainly dedicated and reused threads.
- Insure that your RDO settings use a minimum of pool threads.
- Make sure that your workload never has to wait for a thread to execute work in your DB2 CICS environment.
- Make sure that you aren’t duplicating security checking with both DB2 and another security product.
- Use group ids for your DB2 authorizations as much as possible so that plan, package and CICS thread authorizations can be reused.
________________________________________ 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.
|
We’ve talked about CICS transaction performance improvements and security performance improvements. Now let’s check into the DB2 Sort Work, EDM, RID and Buffer Pools. DB2 Sort Work PoolThe DB2 system work area consists of a number of DB2 DSNDB07 work tablespace data sets to handle the DB2 SQL sort requirements. The DB2 sort work pool data sets were in many extents in the system I examined. They needed to be consolidated to minimize the extra I/Os using these extended data sets. Additionally, these sort work data sets needed to have larger primary allocations and a zero secondary allocations quantity to avoid secondary extents. The 32k sort pool needed its extents consolidated also. The usage of the pool should also be monitored to discover poor program performance or programs that are executing poor SQL that is referencing result sets greater than 4k. Those sorts are forced into the 32k sort pool. Buffer Pool Sizing and SettingsOne of the company’s systems used a number of buffer pools to cache the various database data and index information and improve the overall efficiency of the database activity. The number, size, and mixture of different database objects allocated to the number of buffer pools can have a dramatic positive performance impact on the processing. By starting to cache similar database objects, objects with similar read or write access processing patterns, the data can better cached and improve performance immediately. By caching the correct data in the buffer pools and combining or isolating different database objects, more data can be cached and CPU and I/O requirements reduced. Further evidence that the buffer pools should be analyzed was that some buffer pools were very busy while some were not being used at all. For example, one buffer pool supported a number of database objects, making it very busy throughout the day, while two other buffer pools with high allocations were not being used at all. Additionally, the buffer pools needed to be sized according to their usage, taking buffers from the less busy pools and adding to the busiest buffer pools. Finally, the company needed to consider having different buffer pool configurations at different points of time. Some clients benefit tremendously by having different buffer pool configurations for on-line daytime workloads and then modifying their buffer pool for a night time batch workload. Since the system had very distinct database tables that were active during each of these periods, having different buffer pool allocations for daytime and nighttime processing cut 1.5 hours of elapsed time and CPU demand off their DB2 processing. EDM Pool Sizing and Settings The DB2 environment has many caching mechanisms to improve system and application performance. One of these pools is the EDM pool which helps handle transactions in the system. It consistently had 5-10% free memory space. It is vital that free space be maintained in the EDM pool but only having this much free space consistently indicates some issues. Because the company was behind in maintenance, I recommended that they bring their DB2 maintenance up to date. This was particularly important since there were a number of EDM pool fixes (PTFs) in the DB2 maintenance. Once the maintenance was applied, the size of the EDM pool size needed to be monitored and analyzed. The EDM pool memory within this DB2 Version 8 system was rather large and the maintenance and freeing of some old DBRMS within some big DB2 Plans helped reduce EDM workload and add some needed free space. RID Pool Sizing and Settings The row id (RID) pool is used for the RID sorts that accompany optimizer access path techniques such as list pre-fetch, hybrid join, and multi-index access. These access paths were very common within the company’s environment and the RID pool was overflowing with work. When these overflow conditions occurred, the SQL access method changed to a tablespace scan or in DB2 10 the RID pool entries overflow to work files within the DSNDB07 sort area causing a huge increase in the number of pages accessed and the required resources to retrieve the information. This bad situation happened 613 times in one day, causing many RID limit failures, leading to thousands of additional I/Os and high CPU usage. I recommended that the RID pool size should be increased as soon as possible. The RID Pool can be defined up to 1 GB for Version 7 systems and much larger now since it is above the line as of DB2 Version 8. Once that was done, further analysis would detail the transaction frequency and the number of rows referenced through the pre-fetch, hybrid join and multi-index access. SummaryIt is important to monitor and analyze the various pools in your systems. Check for the following: - Sort work pools aren’t going into secondary extents.
- Buffer pools are sized correctly for their I/O activity rate.
- Data objects and index objects are in separate buffer pools.
- Data objects with similar access characteristics are defined to the same buffer pools.
- EDM pools should be defined big enough to maintain 10 to 15% free space if possible
- RID pool is adequately sized and RID pool overflows are not happening within the environment.
________________________________________ 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.
|
We’ve checked into the DB2 Sort Work, EDM, RID and Buffer Pools. During this post, I’ll talk about some of the other standard places to check for performance improvements. DB2 System MaintenanceThe DB2 system software maintenance from IBM contains many fixes and performance adjustments in its software maintenance stream. When investigating this company’s maintenance levels, I discovered that their DB2 system is behind on its maintenance level, which does not allow the latest performance improvements to be leveraged. Maintenance also needs to be coordinated with the implementation of pre-tested Service Packs related to other IBM software products. These Service Packs test the compatibility between z/OS, IMS, MQ Series, CICS and DB2 and can help eliminate maintenance compatibility issues. By evaluating the latest release compatible with operating system, MQ Series, CICS and other software connecting to DB2, the company can apply the correct maintenance level for their DB2 Version. Yearly maintenance plans need to be developed to help all departments understand the dependencies and the need to apply maintenance on a regular schedule. Dynamic Statement Cache Pool Sizing and Settings Additional analysis showed that the Dynamic Statement Cache (DSC) was being leveraged for application efficiency. This recently implemented feature was working well and only needed to be fine-tuned. (The DSC holds SQL statements executed frequently and does not have to re-determine the access path, verify object existence or re-check security if various settings are the same in subsequent executions.) A good portion of the SQL statements at the company were being cached letting, DB2 use the previously optimized SQL executing in the system. Leveraging the DSC area has usually shown a 2 to 3% CPU savings per SQL transaction and should be monitored closely to make sure to maintain its efficiency. If your environment executes a large percent of dynamic SQL applications, the savings from leveraging the DSC area deserves on-going attention. Summary Checking the various aspects of your DB2 system can have a great impact on the performance of your system. Take a look at these areas to improve system performance: - Is your DB2 System Maintenance at the appropriate level? Do you have maintenance plans that include checking the Service Pack levels to ease the integration with IMS, CICS, MQ Series and other software within your environment?
- Is your Dynamic Statement Cache Pool set to the appropriate size for your system? Are your settings encouraging SQL caching?
________________________________________ 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.
|
Last time I wrote about DB2 Checkpoint and index definitions over your busiest tables. This week I will discuss the old style of tablespace definitions and their potential performance impact. Tablespaces Need to Be Reorganized The first thing to look at is the current state of the database tables and indexes. Usually I find that there are many database objects in need of reorganization and normal on-going maintenance. This is indicated by the number of extents found on the each of the various tablespace and indexspaces which can usually be obtained through a standard data set extent report. This is also discovered by querying the DB2 catalog information and highlighting the number of FAROFFPOS rows. FAROFFPOS indicates that retrieving these data rows requires extra I/Os when referenced in the system because they are not stored on their optimum position within the database tablespace or indexespace. These FAROFFPOS rows are usually found within several large tablespaces and their indexes of database that have not had normal maintenance done on them during the normal business cycle. All database tables and indexes need to be analyzed to understand their daily activity (insert, update and delete frequency) so that their free space can be adjusted through the FREEPAGE and PCTFREE DB2 allocation parameters. These parameters and space allocations needed to be adjusted to handle the workload for a period of six months without database reorganization. The analysis and adjustments will pay off in better system and application performance, while freeing staff from database object reorganizations for an extended period of time. Simple Tablespaces Should be Changed to Segmented Tablespaces Simple tablespaces are not allowed in DB2 Version 10. Usually when I am doing a performance review on an old DB2 Version 9 system I find a small number of tablespaces that were still defined as a simple tablespace type. These tablespaces need to be redefined as segmented or Universal Table Space tablespaces to leverage the better space management and processing features of these tablespaces. Given that the simple tablespace definition is going away, it is a good idea to change these database tablespaces before the migration requirement creeps up on you and messes up or constrains your schedule. Tablespace Segment Size is Too Small Sometimes when I am looking into the segment tablespace definitions I find bad definitions or tablespace definitions that have taken the defaults. When this happens I usually find database tablespace segment size of 4 pages which is too small for normal databases. This segment size is used in secondary allocation efforts and pre-fetch operations. Having a small segment size limits the efficiency of pre-fetch operations and could require additional I/O during normal operations. It is best to have a large segment size of 32, if possible, depending on the number of partitions and whether or not you are using the new Universal tablespace type. When defining a regular segmented tablespace use a large segment size whenever possible to improve I/O and prefetch operations. Minimal Partitioning In some shops partitioning is not really used for the databases. In another performance review I found that a majority of the current database tablespaces used a segmented tablespace definition for handling data. There were a number of tables that had a large number of rows. Those tablespaces needed be analyzed to determine whether they should be redefined as partitioned tablespaces. Partitioning these tablespaces would split up the data into a number of smaller data sets and spread out the I/O and locking within the database. Partitioning allows better data set and space management for isolating processing or data set secondary extents. Partitioning also enables the processing to possibly use parallel access to reduce the elapsed time of any long running batch processes. It is best to partition tables that have over one million rows. The DB2 Catalog can easily be queried to determine tables with over one million rows that aren't partitioned. Table Reorganization Schedule Maintenance procedures needed be put in place to regularly review and schedule database utilities and database tools against the production database environment. These schedules and tools are very important to the ongoing efficiency of the database system. Performance problems can be very disruptive to the business environment. Analysis of the environment uncovered tablespaces and indexes in multiple extents in need of reorganization. Workflow analysis and database change statistics need to be gathered to develop an efficient maintenance schedule. Also standard jobs, downtime maintenance windows and operational procedures are needed to minimize the business disruption impact. Summary Honing in on your tablespace and table structures can give you lots of places to improve DB2 performance. Take a look at these areas in your own shop: - Are there tablespaces and tables that need to be reorganized? Check for a large amount of data set extents and a large number in rows that are in a FAROFFPOS position.
- Do you have any simple tablespaces that should be changed to segmented tablespaces for better performance? Are your segmented tablespaces properly sized?
- Do you have frequently updated tables with a large number of indexes? Double check to see if any of these indexes can be eliminated to improve performance.
- Are there large segmented tablespaces that really should be redefined as partitioned? Check for tables with over a million rows.
- Do you have a reorganization schedule an appropriate FREEPAGE and PCTFREE freespace settings that keeps tables cleaned up with a minimum of outage?
See you when we continue on with our exploration of this fascinating case study. ________________________________________ 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.
|
We’ve checked out numerous areas to check for performance. Now it’s time to see how the application performance is doing within the DB2 system. During this post, I’ll talk about some of the other standard places to check for performance improvements.
Locking Tables and Lock Escalations
Looking into the DB2 system and application monitor showed huge spikes of CPU during the morning hours. These CPU spikes were also happening when there were a number of applications that are experiencing lock escalation in the system. Lock escalation can be caused by numerous situations and needs to be minimized for best processing throughput possible. For example, in a production performance report there were LOCK TABLES = 14,667 and LOCK ESCALATIONS = 25 during a given day.
The high number of LOCK TABLES is a major consideration because it prevents concurrent work. These situations need further research to determine whether they could be modified and eliminated.
The lock escalations were also troubling in terms of system performance. Twenty-five lock escalations during the daytime online window could cause throughput and CPU performance problems. These lock escalations needed to be further researched to determine their origin and the resources that were being locked.
Write Engine Not Available
Looking into the DB2 system and application monitor showed extended read and write times during peak processing. The disk configuration showed normal disk I/O rates and no particular data sets had particular bad read or write times or performance issues.
Looking further into the DB2 monitor statistic showed that in some of the buffer pools the amount of write activity was very robust during certain peak periods. During these extreme peak periods there were some instances where the write activity was so robust that all of DB2’s write engines assigned to that buffer pool were busy.
This situation is denoted in various performance monitor reports under each individual buffer pool report as Write Engine not available. The condition is caused by updated or dirty pages remaining in the buffer longer than necessary and reducing the number of buffer pages available for other processes.
Once the number of buffer pool pages update becomes too big the write engine try to write all the updated pages to the disk. Since the activity was so robust, DB2 could not keep up and all the write engines were busy trying to keep up. This situation can be fixed in a variety of ways depending on your existing configuration.
The first method can be to expand buffer pool allowing more write cushion or changed the deferred write threshold setting to write more frequently or change the workload to spread out the peak timeframe. All methods will potentially improve the situation and allow DB2 to keep up with the write activity before it runs out of DB2 write engines.
In Summary
-
Do you have too many locks against your tables? How many lock escalations happened today? The answer to these questions lies in looking at the order of your application processing and your programming methodologies so that concurrent transactions can occur easily.
-
Check to see if there are lots of times that a write engine is not available. Look at the size of your buffer pools, look at the deferred write threshold and look at the timing of your jobs and limit the times write engines are not available.
_______________________________________
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.
|
Last time I wrote about some of the application performance aspects. This week we’ll delve deeper into DB2 Checkpoint aspects for recoveries and index definitions over your busiest tables and their potential performance impact.
DB2 Checkpoint System Frequency
The DB2 system runs transactions and takes system wide recovery checkpoints for system consistency and integrity. These system wide checkpoints synchronize the system across all applications and can be disruptive because they hold up transactions to get a synchronization point. It is a common industry rule-of-thumb to take these checkpoints every ten to fifteen minutes to have good recovery points and not hold up processing.
Within the examined system these checkpoints were being taken very infrequently because the active log data sets were very large. This frequency, while good for processing through put, exposes the DB2 system to having an elongated recovery window should a disaster happen. The system checkpoint frequency needed to be evaluated and adjusted to provide better recoverability.
This situation also can cause issues during the batch cycle as programs write over 250,000 or 500,000 log records without the system taking a checkpoint.
Too Many Indexes Defined on Busy Tables
Looking at the workload for the overall system found that one of the main insert, update and delete processing tables also had a large number of indexes defined on it. Querying the DB2 catalog, found 11 indexes defined on this very busy table. Analysis also showed that the majority of the processing was inserts, updates and deletes as opposed to select activity.
Other very busy tables also had 7 indexes. These tables and each of their indexes needed to be analyzed for further usage analysis versus their overhead.
Too Many Indexes Not Being Used
Looking further into the indexes that were defined on the application database also showed many indexes that were not being used. Querying the DB2 catalog I often find a large number of indexes defined that are not being used. Sometimes these indexes that aren’t being used are even on the largest or the busiest tables.
Further research showed that some of these indexes were defined for one time processes and weren’t deleted after the processing was complete. One other index was being used and was critical for the dynamic SQL workload. So just because you queried the DB2 Catalog with the query below to find the index plan and package dependencies does not mean that you can delete the index.
SELECT DISTINCT TBNAME, NAME AS INDEXNAME
FROM SYSIBM.SYSINDEXES
WHERE DBNAME LIKE '<database-name>%'
ANDNAME NOT IN
(SELECT BNAME
FROM SYSIBM.SYSPACKDEP
UNION
SELECT BNAME
FROM SYSIBM.SYSPLANDEP)
Run the query and use the indexes listed as your starting point for further analysis. Each index listed could be critical for dynamic SQL performance so be very careful before deleted any indexes from your system.
In Summary
Verify the checkpoint frequency of your systems. Having the correct checkpoint frequency can provide the proper checkpoints for disaster and regular recoveries of your databases and system. The recommended checkpoint frequency is about every 10 to 15 minutes. So depending on how busy your system is adjust the number of log records accordingly.
Having extra indexes over busy tables can be a huge performance overhead. Deleting two or three extra indexes can sometimes cut one-quarter or one-half of a second off your transaction response time. So make sure you only have the indexes that are being used defined on your tables.
________________________________________
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.
|
Let's delve into the SQL used in the applications of a major DB2 system.
Taking Advantage of Relational Technology
During reviews of various client systems, analyzing the SQL used is very helpful for understanding how sophisticated the shop and system are at leveraging relational technology. When the shop and system are well designed, the tables, indexes and application properly utilize object designs through effective object reuse and consistent design patterns. All of these application components are exposed when the application access methods are designed and formulated into SQL. Usually if a system is designed properly the SQL contains good access paths. SQL with good access paths use the following:
-
SQL Joins
-
SQL Sub queries
-
Table expressions
-
CASE expressions
-
Limited fetch
The more work done within in the DB2 relational technology engine, usually the faster the data can be turned into information for application reports, web pages, etc. At a recent client there were zero table expressions, limited fetches and very few of any of the other SQL options to leverage DB2 and relational technology. Rewriting one of the applications with an SQL Join and a SQL Table expression dramatically reduced processing elapsed time and CPU requirements taking the application processing from hours to only a few minutes.
Use SQL Joins
Analysis of another client's system showed that the application programmers were not using SQL Joins in any of their applications. Most of the application data processing focused on retrieving one row at a time and often from only one table.
The basis of relational technology is set processing or processing a large number of rows through a single SQL statement. Application programmers need to understand and leverage relational technology so that the application programs process more data with each interaction with the database. Often when SQL is used to process single database rows, the application program can be rewritten with SQL Joins of tables using indexes and proper WHERE clauses and dramatically cut the CPU and elapsed processing time. Using any of the several of SQL Join methods also optimizes I/O and CPU usage of the application by pushing the work from the program into the DB2 engine.
Experience with several past clients has shown significant elapsed time process improvement and tremendous CPU savings by rewriting application with SQL Joins. For example, an application designed with poor SQL was rewritten with SQL Joins and went from 12 hours of execution time to only 5 minutes.
Experience has shown that applications that yield the biggest improvements are those that are executed often and open cursors to access a large number of rows. Once these applications are identified, an applications team can redesign these programs to use more efficient relational technology and SQL join set processing.
Summary
It is important to drive processing to the SQL engine as much as possible. Review applications in your company for:
-
Lack of SQL Joins
-
SQL Sub queries
-
Table expressions
-
CASE expressions
-
Limited fetch
________________________________________
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.
|