My Account
Log in Registration
All
Cart
Highlights
Fiction Books
Non-Fiction Books
Children's Books
Rare Books
Music
DVD & Blu-Ray
Video Games
Category
Wishlists
Cart
All
Free Shipping in Australia
Trustpilot
Proud to be B-Corp

DB2 SQL Tuning Tips for z/OS Developers Tony Andrews

DB2 SQL Tuning Tips for z/OS Developers By Tony Andrews

DB2 SQL Tuning Tips for z/OS Developers by Tony Andrews

Reviews:
Condition - Good
Out of stock
Good
SeriesIBM Press

DB2 SQL Tuning Tips for z/OS Developers Summary

DB2 SQL Tuning Tips for z/OS Developers by Tony Andrews

The Definitive Solutions-Oriented Guide to IBM (R) DB2 (R) for z/OS (R): Now Fully Updated for Both v9 and v10! The largest database tuning performance gains can often be obtained from tuning application code, and applications that use SQL to retrieve data are the best candidates for tuning. This well-organized, easy-to-understand reference brings together more than 100 SQL-related skills and techniques that any developer can use to build and optimize DB2 applications for consistently superior performance. DB2 tuning expert Tony Andrews (Tony the Tuner) draws on more than 20 years of DB2-related experience, empowering you to take performance into your own hands, whether you're writing new software or tuning existing systems. Tony shows you exactly how to clear bottlenecks, resolve problems, and improve both speed and reliability. This book fully reflects the latest SQL programming best practices for DB2 V9 and DB2 V10 on z/OS: techniques that are taught in no other book and are rarely covered in general DB2 SQL courses. Drawing on his extensive consulting experience and highly praised training with Themis Inc., Tony also presents practical checklists and an invaluable 15-step methodology for optimizing virtually any DB2 application. Coverage includes Empowering developers on knowing what to do and where to look in resolving performance problems in queries or programs Providing many programming and SQL coding examples Establishing standards and guidelines that lead to high-performance SQL Implementing time-efficient code walkthroughs to ensure that your standards are followed Focusing on the small number of SQL statements that consume the most resources Identifying simple solutions that deliver the most sizable benefits Optimizing performance by rewriting query predicates more efficiently Providing a better understanding of SQL optimization and Runstat statistics Recognizing opportunities to tweak your code more effectively than the Optimizer Optimizing SQL code with COBOL applications Efficiently checking for the existence of data, rows, or tables Using Runstats' newest capabilities to consistently optimize paths to data

About Tony Andrews

Tony Andrews has more than 23 years' experience in the development of IBM DB2 relational database applications. Most of this time, he has provided development and consulting services to Fortune 500 companies and government agencies. Tony has written literally thousands of queries and programs during his development years, and he has also served as a DB2 database analyst. For the past 10 years, Tony has been splitting his time between consulting engagements and training. His main focus is to teach today's developers the ways of RDMS application design, development, and SQL programming- always with a special emphasis on improving performance. Tony's training, consulting, and speaking engagements are through his employer, Themis, Inc., an onsite and virtual instructor-led, hands-on IT training company recognized internationally. It offers more than 400 IT courses and helps to support International DB2 Users Group North America (IDUG NA) and Europe, Middle East, and Africa (IDUG EMEA), along with many DB2 user groups. Tony is a current IBM champion and regular lecturer at industry conferences and local user groups. You may have seen him present at such events as IDUG NA and EMEA. He is well known for his Top 25+ Tuning Tips for Developers presentation. Tony graduated from Ohio State University with a major in business and a minor in mathematical statistics. He currently resides in Dublin, Ohio.

Table of Contents

CHAPTER 1 SQL Optimization Top 100+ 1 1. Take Out Any/All Scalar Functions Coded on Columns in Predicates 2 2. Take Out Any/All Mathematics Coded on Columns in Predicates 3 3. Code Only the Columns Needed in the Select Portion of the SQL Statement 4 4. Stay Away from Distinct if Possible 4 5. Try Rewriting an In Subquery as an Exists Subquery 5 6. Always Make Sure Host Variables Are Defined to Match the Columns Datatype 6 7. Because Or Logic Can Be Problematic to the Optimizer, Try a Different Rewrite 6 8. Make Sure the Data Distribution and Other Statistics Are Good and Current in the Tables Being Processed 8 9. Use UNION ALL in Place of UNION Where Possible 9 10. Consider Hardcoding Versus Using a Host Variable 9 11. Minimize DB2's SQL Requests 11 12. Try Rewriting Range Predicates as Between Predicates 12 13. Consider Using Global Temporary Tables 13 14. Give Prominence to Stage 1 over Stage 2 Predicates 14 15. Remember That the Order of (Some) Predicates Does Matter 15 16. Streamline Multiple Subqueries 16 17. Index Correlated Subqueries 17 18. Get to Know the DB2 Explain Tool 17 19. Use Tools for Monitoring 18 20. Employ Commit and Restart Strategies 19 21. Implement Good Index Design 19 22. Avoid Discrepancies with Non-Column Expressions 20 23. Begin with All Filtering Logic Outside Application Code 21 24. Ensure That Subquery Predicates Involving Min and Max Have the Possibility of Nulls Being Returned Handled 21 25. Always Code For Fetch Only or For Read Only with Cursor Processing When a Query Is Only Selecting Data 22 26. Avoid Selecting a Row from a Table to Help Decide Whether the Logic in the Code Should Execute an Update or an Insert 23 27. Avoid Selecting a Row from a Table in Order to Get Values for an Update 23 28. Make Use of Dynamic SQL Statement Caching 23 29. Avoid Using Select * 24 30. Watch Out for Nullable Columns or Times When SQL Statements Could Have Nulls Returned from the Database Manager 25 31. Minimize the Number of Times Open and Close Cursors Are Executed 25 32. Avoid Not Logic in SQL 26 33. Use Correlation IDs for Better Readability 26 34. Keep Table and Index Files Healthy and Organized 27 35. Take Advantage of Update Where Current of Cursor and Delete Where Current of Cursor 27 36. When Using Cursors, Use ROWSET Positioning and Fetching Using Multiple-Row Fetch, Multiple-Row Update, and Multiple-Row Insert 28 37. Know the Locking Isolation Levels 28 38. Know Null Processing 30 39. Always Program with Performance in Mind 31 40. Let SQL Do the Work 32 41. Code with Lock Table 32 42. Consider OLTP Front-End Processing 33 43. Consider Using Dynamic Scrollable Cursors 34 44. Take Advantage of Materialized Query Tables to Improve Response Time (Dynamic SQL Only) 35 45. Insert with Select 37 46. Take Advantage of Multiple-Row Fetch 38 47. Take Advantage of Multiple-Row Insert 39 48. Take Advantage of Multiple-Row Update 40 49. Take Advantage of Multiple-Row Delete 42 50. Try Scalar Fullselects Within the Select Clause 42 51. Take Advantage of REOPT ONCE and REOPT AUTO in Dynamic SQL and REOPT VARS and REOPT ALWAYS in Static SQL 43 52. Identify Times for Volatile Tables 44 53. Use the ON COMMIT DROP Enhancement 45 54. Use Multiple Distincts 45 55. Take Advantage of Backward Index Scanning 46 56. Watch Out for the Like Statement 46 57. Set Your Clustering Index Correctly 47 58. Use Group By Expressions if Needed 48 59. Watch Out for Tablespace Scans 48 60. Do Not Ask for What You Already Know 49 61. Watch the Order of Tables in a Query 49 62. Use Left Outer Joins Over Right Outer Joins 51 63. Check for Non-Existence 51 64. Use Stored Procedures 52 65. Do Not Select a Column in Order to Sort on It 53 66. Always Limit the Result Set if Possible 53 67. Take Advantage of DB2 V8 Enhanced DISCARD Capabilities When It Comes to Mass Deletes 54 68. Take Advantage of the DB2 LOAD Utility for Mass Inserts 54 69. Watch Out for Materialization of Views, Nested Table Expressions, and Common Table Expressions 55 70. Consider Compressing Data 56 71. Consider Parallelism 57 72. Keep the STDDEV, STDDEV_SAMP, VAR, and VAR_SAMP Functions Apart from Other Functions 58 73. Consider Direct Row Access Using ROWID Datatype (V8) or RID Function (V9) 58 74. Test Your Queries with Realistic Statistics and a Level of Data to Reflect Performance Issues 60 75. Specify the Leading Index Columns in WHERE Clauses 61 76. Use WHERE Instead of HAVING for Filtering Whenever Possible 62 77. Keep in Mind Index Only Processing Whenever Possible 62 78. Index on Expression in DB2 V9 63 79. Consider the DB2 V9 Truncate Statement 64 80. Use DB2 V9 Fetch First and Order by Within Subqueries 65 81. Take Advantage of DB2 V9 Optimistic Locking 65 82. Use the DB2 V9 MERGE Statement 66 83. Understand the DB2 NOFOR Precompile Option 68 84. Consider Select Into Using Order By 69 85. Code Boolean Term Predicates Whenever Possible 69 86. Try Transitive Closure Coding 70 87. Avoid Sorts with Order By 71 88. Use Joins Instead of Subqueries Whenever Possible 71 89. Watch Out for Case Logic 71 90. Take Advantage of Functions in the Order By Clause 72 91. Know Your Version of DB2 72 92. Understand Date Arithmetic 73 93. Know Your High-Volume Insert Choices 73 94. Know About Skip Locked Data (V9) for Lock Avoidance. . . . . .75 95. Sort Your Input Streams 75 96. If You Need True Uniqueness, Try the V8 Generate_Unique Function 76 97. Know the New Options for Declared Temporary Tables 76 98. Watch Out When Executing Get Diagnostics 77 99. Order Your In List Appropriately 77 100. Update and Delete with Select (V9) 77 101. Execute SQL Statements Only if Necessary 78 102. Take Advantage of In-Memory Tables 78 103. Stay Away from Catchall SQL Statements 79 104. Avoid Unnecessary Sorting 79 105. Understand Expressions and Column Functions 79 106. Watch Out When Combining Predicates 80 107. Add Redundant Predicates to Search Queries 80 108. Take Advantage of Improved Dynamic Caching (V10) 81 109. Try Currently Committed for Lock Avoidance (V10) 82 110. Try System Temporal Tables for Historical Data (V10) 83 111. Try Business Temporal Tables for Historical Data (V10) 85 112. Know Your Ranking Functions (V10) 86 113. Take Advantage of Extended Indicators (V10) 87 114. Get Greater Timestamp Precision (V10) 88 115. Try Index Includes (V10) 89 116. Use With Return to Client (V10) 89 CHAPTER 2 DB2 SQL Hints 91 1. Try the Optimize for 1 Row Statement at the End of the SQL Statement 91 2. Add the A.PKEY = A.PKEY Predicate to the SQL Query, Where PKEY Equals the Primary Key Column of the Table 92 3. Disqualify an Index Choice 93 4. Change the Order of Table Processing 95 5. Use Distributed Dynamic SQL 96 CHAPTER 3 SQL Standards and Guidelines 99 For COBOL Developers 99 For All SQL Developers 102 CHAPTER 4 SQL Program Walkthroughs 107 CHAPTER 5 Existence Checking 111 Example 1 111 Example 2 113 CHAPTER 6 Runstats 115 CHAPTER 7 Initial Steps in Tuning a Query 117 APPENDIX A Predicate Rewrite Examples 121 Predicate Rewrites: Transitive Closure 122 APPENDIX B DB2 SQL Terminology 125 Index 131

Additional information

CIN0133038467G
9780133038460
0133038467
DB2 SQL Tuning Tips for z/OS Developers by Tony Andrews
Tony Andrews
IBM Press
Used - Good
Paperback
Pearson Education (US)
20121025
160
N/A
Book picture is for illustrative purposes only, actual binding, cover or edition may vary.
This is a used book - there is no escaping the fact it has been read by someone else and it will show signs of wear and previous use. Overall we expect it to be in good condition, but if you are not entirely satisfied please get in touch with us

Customer Reviews - DB2 SQL Tuning Tips for z/OS Developers