DB2 SQL Tuning Tips for z/OS Developers

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