================================================ SEARCHDATABASE.COM'S DBA Tips and Tricks June 13, 2001 ================================================ Welcome to the searchDatabase.com DBA Tips and Tricks newsletter! Today's tip, "Effective DB2 performance when using someone else's SQL" can also be viewed online at: http://www.searchDatabase.com/tip/1,289483,sid13_gci745818,00.html ******** Sponsored by Syncsort ***************** FREE handy multi-tool from Syncsort, the makers of the toolkit of data management software for achieving cutting-edge data warehouse performance: SyncSort is a powerful data manipulation, sort and ETL product. Visual Syncsort adds an easy-to-use GUI. Sigma is a specialized aggregation engine. FilePort is a two-way data conversion utility. Backup Express is an enterprise-wide, multi-platform backup and restore solution. Visit http://www.syncsort.com/tgb610 for FREE TRIALS, more information and a FREE multi-tool. ************************************************ "Effective DB2 performance when using someone else's SQL" By Tom Moulder I see a trend in the IT industry today. It manifests itself by a call for "someone else to take care of things for me." How many IT professionals have gained a new employer as a result of a company decision to outsource? How many database administrators have gained a new application to manage as a result of a company decision to purchase an application package? I don't think this trend is going to come to halt, so here are some suggestions designed to help you cope with some of these decisions are being made outside your control. Looking at the DB2 application marketplace, IBM must be extremely pleased as the license count for DB2 on OS/390 goes through the 9,000 mark headed for numbers as high as the Dow Jones Industrial Average. A large portion of this growth must be attributed to applications that have chosen this rock solid database as their platform. So what can a capacity planner, performance analyst, system programmer or database administrator do when asked by his company to manage the performance of someone else's SQL? If performance is unacceptable, there is always one easy solution. In the words of Ted VanDuyn, "Memory solves all problems!" Therefore, you can always buy more memory and increase the size or number of your DB2 buffer pools. However, in most of the places I have traveled, that is not the politically correct answer. When a simple answer will not do, a more complicated answer should be explained. Hopefully, this answer can be simple enough for all to use. When faced with tuning someone else's SQL, you have two basic choices. First, you can add or drop indexes from the application schema or second you can add or expand DB2 buffer pools. That sounds pretty simple. It is not all that complicated. I have listened to many presentations on this subject and have left thinking that the only person that could possible execute on what was presented was the speaker. I want to convince you that you can tune someone else's SQL and make it perform efficiently. Index Design ------------ Index changes are usually called for when the access patterns for the application vary from what was anticipated by the designer. The number of indexes that an application should have is a subjective value. The number of indexes is not as important as ensuring the usefulness of the indexes that are defined and creating the indexes that would improve performance. Index analysis should be done from a big picture perspective as opposed to helping one SQL statement to cure an immediate and troubling problem. Let me start with the targeted end result of index tuning, which should be one of two goals, or both: reduced elapsed time for DB2 threads and reduced I/O traffic through DB2. This means that you would need some idea of what response time and I/O traffic are today. This can be obtained from DB2 accounting information. Any DB2 monitor will load this data into DB2 tables. Once it is there, you can summarize the data on a daily basis. Continue this practice as you change indexes and use this data to determine if an improvement has been made. Many people ask "How do I get the right indexes?" It is not a matter of right or wrong, good or bad. Look upon this design process as determining the ones that are most beneficial and having them available at appropriate times. Indexes positively affect an application when used to materialize a result set with fewer I/Os to DB2 objects. Indexes negatively affect an application when data is updated and the indexes have to be changed as well. Effective indexes are the ones that provide the greatest I/O reduction when contrasted with the time required to keep the indexes updated. Some indexes may only be valuable during certain types of application processes. If these processes are infrequent, create the indexes before the process begins and remove them when finished. The design process for application indexes requires some data to be collected. Just as you would interview the programmers for a new application, you will need to interview your vendor to obtain information about their product. You will need to know type of SQL used by the product, whether it used Static, Dynamic SQL or a combination of both. If it uses Dynamic SQL, it is important to know if the product uses host variables or literals in its predicates. >From the DB2 catalog you can also get a list of objects that are accessed by package or DBRM. As you gather this information, create a spreadsheet or table that contains the packages, the objects accessed the type of access that will be performed and some weighting factor that will give priority to those processes performed most frequently. The type of access to the objects will provide you with a picture of the application's use of its data. If there are indexes that are only updated and never used for select processing, consider these indexes prime targets for elimination. However, before removing the indexes, check for those infrequent processes that might be benefited by the index for short periods of time. For those processes that have high update activity, index evaluation is important to ensure the ones chosen are providing value. Verifying the index design process is built upon the accounting data mentioned earlier. As you change the index structure, review the response time and I/O statistics for corresponding reductions. Remember that sometimes it may take a week of processing to determine the impact of a change. For the remainder of this article, including tips on Buffer Pool Design, click here: http://www.searchDatabase.com/tip/1,289483,sid13_gci745818,00.html ABOUT THE AUTHOR: Tom Moulder is a Software Consultant at BMC Software (http://www.bmc.com/). MORE ON THIS TOPIC: The Best IBM DB2 Web Links: http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax282900,00.html The Best SQL Web Links: http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax281606,00.html Ask your toughest DB2 and SQL questions--or help out your peers by answering them--in our live discussion forums: http://searchdatabase.techtarget.com/forums/0,289802,sid13_fid1,00.html Check out our new "Ask the Experts" feature! We have Database Design, SQL, Oracle, DB2, and SQL Server gurus waiting to answer your technical questions: http://searchdatabase.techtarget.com/ateExperts/0,289622,sid13,00.html ------------------------------------------------ LEARNING ZONE FEATURED BOOK OF THE WEEK ------------------------------------------------ "Teach Yourself ADO 2.5 in 21 Days" By Christoph Willie This book's coverage includes basic implementation of ADO 2.5 (i.e., data manipulation, working with recordsets, using stored procedures), advanced techniques (i.e., how to work asynchronously, data shaping, building applications, database management), and using ADO in a distributed environment (i.e., designing COM+ apps, the in-memory database, disconnecting data, using remote data service, debugging and tuning). http://www.digitalguru.com/dgstore/product.asp?isbn=0672318733&ac_id=58 ================================================ SUBMIT A TECHNICAL TIP AND WIN A PRIZE! ================================================ Do you have a time-saving shortcut, trick, or script that you want to share with other database pros? The first fifty individuals who submit a tip will receive a free searchDatabase.com hat. The highest rated tips each month will win our "Tip of the Month" contest and receive a high-quality searchDatabase.com denim shirt AND a free book of your choice from Wrox Press. We're accepting short, focused tips or code snippets on topics of interest to DBA's and database developers, such as Oracle, DB2, SQL Server, database design, SQL, performance tuning, etc. Click here for more info and to submit your tip: http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz520733_cts520732,00.html This will be a great way to share your knowledge, cement your status as an industry expert, and maybe win a prize. Send us your tip today! ================================================ If you would like to sponsor this or any TechTarget newsletter, please contact Gabrielle DeRussy at [EMAIL PROTECTED] ================================================ If you no longer wish to receive this newsletter simply reply to this message with "REMOVE" in the subject line. Or, visit http://searchDatabase.techtarget.com/register and adjust your subscriptions accordingly. If you choose to unsubscribe using our automated processing, you must send the "REMOVE" request from the email account to which this newsletter was delivered. Please allow 24 hours for your "REMOVE" request to be processed.
