As DB2 reaches its 30th anniversary and IDUG reaches its 25th anniversary, I’ve thought about many DB2 tips and best practices that have been incorporated by companies, projects and applications that have led to great successes. The ones that have been most common and have had the greatest impact are listed below:
1. Plan, prototype, improve and repeat. The bigger the project, system or application, the more important it is to rapidly start with a small prototype and grow it quickly. By prioritizing the processes that need to be completed first and implementing them in a prototype, the entire team is engaged and exposed to all aspects of the infrastructure, database design and business processing requirements. This also forces the developers to team with the company’s different department’s personnel. This team building quickly helps everyone to focus on the requirements, expectations and business issues the project is going to solve. Start small and make prototype improvements quickly, often, and bigger with each prototype having more functionality and scalability to test.
2. Design for the business. I have been fortunate to design, build and consult on the designs of many databases and very large data warehouses throughout my career. Happily many of the first systems and databases that I designed are still operating today decades later. The reason I believe these systems and application stood the test of time was because they were designed for the business and its processing. Break down and group the business processing into logical modules, use the natural business keys and minimize the data referenced by partitioning, normalizing and having a good archiving strategy.
3. Application SQL coding is only half the battle for getting good DB2 performance. First make sure that all the RUNSTATs statistics have been done against all the SQL dependent objects. The DB2 optimizer usually makes the best decisions when it has the most current complete information on all the tables, indexes and the details of the columns produced through the RUNSTATs utility.
4. Within your test systems define indexes for all your processes. Define extra indexes, one for every process that improves its performance. Then during each step of propagating your database and index designs into your quality assurance and production environments whittle down the number of indexes, compare, combine and optimize the column definitions of the indexes. Indexes that provide uniqueness with extra non-unique columns INCLUDEd with them are a DB2 10 enhancement that always helps application performance. By understanding the index overhead and its performance impact, the performance benefit for every application index will be justified and your design will have the best performance possible with the minimum of indexes.
5. Always do an SQL DB2 Explain for both your static and dynamic applications and save it to understand the DB2 Access Path improvements or degradation history that happens to your application over time. The DB2 Explain output is critical for understanding the use and non-use of DB2 indexes and their columns and the number Sorts within each of your statement’s DB2 SQL processing
6. Always retain historical statistics of your application performance. As your system, database and application go through time they will be upgraded, enhanced and changed for new functions. Keeping track of the original performance and understanding the performance implications of processing changes are vital for proper database design enhancements. Putting more or different types of processing on a database designed for one type of functionality processing can have a negative impact on the original processing. Keeping historical performance statistics documents all the performance impacts and is critical for proper performance database management.
7. Remember performance is always the most important factor because it drives time to market, successful triumphs over your competitors; faster completion of a project to get the answer first, and ultimately getting the transaction done more quickly. Performance has been and will always be the most important factor in computing. It will continue to drive business to invest in new unproven software technologies to get the business answers faster for as long as IT has been around. When designing your database application and SQL always think of performance first.
Dave Beulke (www.davebeulke.com) 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.