DB2 SQL tuning tips for z/OS developers by Tony Andrews

By Tony Andrews

Bankruptcy 1 SQL Optimization best a hundred 1 1. Take Out Any/All Scalar capabilities Coded on Columns in Predicates 2 2. Take Out Any/All arithmetic Coded on Columns in Predicates three three. Code simply the Columns wanted within the pick out component of the SQL assertion four four. keep away from designated if attainable four five. test Rewriting an In Subquery as an Exists Subquery five 6. constantly be sure Host Variables Are outlined to check the Columns Datatype 6 7. simply because Or good judgment will be not easy to the Optimizer, attempt a special Rewrite 6 eight. ensure the facts Distribution and different data Are reliable and present within the Tables Being Processed eight nine. Use UNION ALL in preference to UNION the place attainable nine 10. examine Hardcoding as opposed to utilizing a bunch Variable nine eleven. reduce DB2's SQL Requests eleven 12. try out Rewriting variety Predicates as among Predicates 12 thirteen. think about using worldwide transitority Tables thirteen 14. provide Prominence to level 1 over degree 2 Predicates 14 15. keep in mind that the Order of (Some) Predicates Does subject 15 sixteen. Streamline a number of Subqueries sixteen 17. Index Correlated Subqueries 17 18. Get to understand the DB2 clarify instrument 17 19. Use instruments for tracking 18 20. hire dedicate and Restart techniques 19 21. enforce reliable Index layout 19 22. steer clear of Discrepancies with Non-Column Expressions 20 23. start with All Filtering common sense outdoor program Code 21 24. make sure that Subquery Predicates concerning Min and Max Have the opportunity of Nulls Being again dealt with 21 25. continuously Code For Fetch basically or For learn simply with Cursor Processing whilst a question is just picking out facts 22 26. steer clear of choosing a Row from a desk to assist come to a decision no matter if the good judgment within the Code should still Execute an replace or an Insert 23 27. keep away from choosing a Row from a desk as a way to Get Values for an replace 23 28. utilize Dynamic SQL assertion Caching 23 29. steer clear of utilizing choose * 24 30. beware of Nullable Columns or instances whilst SQL Statements may have Nulls lower back from the Database supervisor 25 31. reduce the variety of instances Open and shut Cursors Are completed 25 32. keep away from now not good judgment in SQL 26 33. Use Correlation IDs for greater clarity 26 34. maintain desk and Index records fit and arranged 27 35. benefit from replace the place present of Cursor and Delete the place present of Cursor 27 36. while utilizing Cursors, Use ROWSET Positioning and Fetching utilizing Multiple-Row Fetch, Multiple-Row replace, and Multiple-Row Insert 28 37. understand the Locking Isolation degrees 28 38. be aware of Null Processing 30 39. continuously application with functionality in brain 31 forty. allow SQL Do the paintings 32 forty-one. Code with Lock desk 32 forty two. contemplate OLTP Front-End Processing 33 forty three. think about using Dynamic Scrollable Cursors 34 forty four. benefit from Materialized question Tables to enhance reaction Time (Dynamic SQL in basic terms) 35 forty five. Insert with decide upon 37 forty six. make the most of Multiple-Row Fetch 38 forty seven. benefit from Multiple-Row Insert 39 forty eight. benefit from Multiple-Row replace forty forty nine. benefit from Multiple-Row Delete forty two 50. try out Scalar Fullselects in the choose Clause forty two fifty one. reap the benefits of REOPT as soon as and REOPT vehicle in Dynamic SQL and REOPT VARS and REOPT constantly in Static SQL forty three fifty two. determine occasions for unstable Tables forty four fifty three. Use the ON dedicate DROP Enhancement forty five fifty four. Use a number of Distincts forty five fifty five. make the most of Backward Index Scanning forty six fifty six. beware of the Like assertion forty six fifty seven. Set Your Clustering Index adequately forty seven fifty eight. Use staff by way of Expressions if wanted forty eight fifty nine. beware of Tablespace Scans forty eight 60. don't Ask for What forty nine sixty one. Watch the Order of Tables in a question forty nine sixty two. Use Left Outer Joins Over correct Outer Joins fifty one sixty three. fee for Non-Existence fifty one sixty four. Use saved methods fifty two sixty five. don't opt for a Column with a purpose to style on It fifty three sixty six. constantly restrict the end result Set if attainable fifty three sixty seven. benefit from DB2 V8 more desirable DISCARD services by way of Mass Deletes fifty four sixty eight. benefit from the DB2 LOAD software for Mass Inserts fifty four sixty nine. beware of Materialization of perspectives, Nested desk Expressions, and customary desk Expressions fifty five 70. think about Compressing facts fifty six seventy one. contemplate Parallelism fifty seven seventy two. maintain the STDDEV, STDDEV_SAMP, VAR, and VAR_SAMP capabilities except different services fifty eight seventy three. ponder Direct Row entry utilizing ROWID Datatype (V8) or RID functionality (V9) fifty eight seventy four. attempt Your Queries with lifelike records and a degree of information to mirror functionality concerns 60 seventy five. Specify the best Index Columns in the place Clauses sixty one seventy six. Use the place rather than HAVING for Filtering each time attainable sixty two seventy seven. take note of Index purely Processing every time attainable sixty two seventy eight. Index on Expression in DB2 V9 sixty three seventy nine. ponder the DB2 V9 Truncate assertion sixty four eighty. Use DB2 V9 Fetch First and Order through inside of Subqueries sixty five eighty one. reap the benefits of DB2 V9 confident Locking sixty five eighty two. Use the DB2 V9 MERGE assertion sixty six eighty three. comprehend the DB2 NOFOR Precompile alternative sixty eight eighty four. think of decide upon Into utilizing Order by way of sixty nine eighty five. Code Boolean time period Predicates at any time when attainable sixty nine 86. test Transitive Closure Coding 70 87. steer clear of varieties with Order via seventy one 88. Use Joins rather than Subqueries every time attainable seventy one 89. beware of Case good judgment seventy one ninety. make the most of services within the Order by way of Clause seventy two ninety one. recognize Your model of DB2 seventy two ninety two. comprehend Date mathematics seventy three ninety three. understand Your High-Volume Insert offerings seventy three ninety four. find out about pass Locked info (V9) for Lock Avoidance...75 ninety five. kind Your enter Streams seventy five ninety six. if you want precise strong point, attempt the V8 Generate_Unique functionality seventy six ninety seven. recognize the hot strategies for Declared transitority Tables seventy six ninety eight. be careful while Executing Get Diagnostics seventy seven ninety nine. Order Your In checklist adequately seventy seven a hundred. replace and Delete with decide upon (V9) seventy seven a hundred and one. Execute SQL Statements provided that worthwhile seventy eight 102. reap the benefits of In-Memory Tables seventy eight 103. keep away from Catchall SQL Statements seventy nine 104. steer clear of pointless Sorting seventy nine a hundred and five. comprehend Expressions and Column services seventy nine 106. be careful while Combining Predicates eighty 107. upload Redundant Predicates to go looking Queries eighty 108. reap the benefits of more desirable Dynamic Caching (V10) eighty one 109. test at the moment dedicated for Lock Avoidance (V10) eighty two one hundred ten. attempt method Temporal Tables for old information (V10) eighty three 111. test enterprise Temporal Tables for old facts (V10) eighty five 112. comprehend Your rating services (V10) 86 113. make the most of prolonged symptoms (V10) 87 114. Get larger Timestamp Precision (V10) 88 one hundred fifteen. try out Index comprises (V10) 89 116. Use With go back to buyer (V10) 89 bankruptcy 2 DB2 SQL tricks ninety one 1. test the Optimize for 1 Row assertion on the finish of the SQL assertion ninety one 2. upload the A.PKEY = A.PKEY Predicate to the SQL question, the place PKEY Equals the first Key Column of the desk ninety two three. Disqualify an Index selection ninety three four. switch the Order of desk Processing ninety five five. Use dispensed Dynamic SQL ninety six bankruptcy three SQL criteria and directions ninety nine For COBOL builders ninety nine For All SQL builders 102 bankruptcy four SQL software Walkthroughs 107 bankruptcy five lifestyles Checking 111 instance 1 111 instance 2 113 bankruptcy 6 Runstats one hundred fifteen bankruptcy 7 preliminary Steps in Tuning a question 117 APPENDIX A Predicate Rewrite Examples 121 Predicate Rewrites: Transitive Closure 122 APPENDIX B DB2 SQL Terminology a hundred twenty five Index 131

Show description

Read Online or Download DB2 SQL tuning tips for z/OS developers PDF

Similar databases books

Oracle Workflow Guide

Welcome to the Oracle Workflow advisor. This advisor assumes you've gotten a operating wisdom of the following:• the foundations and common practices of your online business sector. • Oracle Workflow. when you have by no means used Oracle Workflow, we advise you attend a number of of the Oracle Workflow education sessions to be had via Oracle college.

Learn Filemaker Pro 9

Research FileMaker® seasoned nine offers a logical, hands-on method of realizing the good points of FileMaker professional, either for rookies and those that have event with past types of the software program. From defining database phrases and changing records created in earlier FileMaker seasoned types to generating reviews and utilizing records on the net, this ebook covers the basics in addition to the most recent positive factors that significantly improve FileMaker Pro’s database administration services.

Extra resources for DB2 SQL tuning tips for z/OS developers

Sample text

00); DB2 processes this SQL statement by first evaluating the nested SELECT statement to retrieve all WORKDEPTs where the SALARY is more than 50,000. It then matches rows in the DEPT table that correspond to the WORKDEPT values retrieved by the nested SELECT. This match produces a results table that lists the name of all departments where any employee earns more than 50,000. Of course, if more than one employee earns more than 50,000 per department, the same DEPTNAME may be listed multiple times in the results set.

The value will be changed whether there is only 1 row that applies or 1 million rows. It the WHERE clause where not specified, every row would be changed to the value 222222, regardless of its current value. The set-level benefits of SQL provide great power to the SQL UPDATE and DELETE statements. Because UPDATE and DELETE can act on sets of data, a single SQL statement can be used to update or delete all rows meeting certain conditions. Great care must be taken always to provide the appropriate WHERE clause or more data may be changed than desired.

There are two JOIN keywords specified, so the magnitude of the join is 2+1, or 3. SQL Tools of the Trade 19 Tables can be joined to themselves also. DEPTNO; 1 SELECT FROM This join returns a listing of all department numbers and names, along with the associated department number and name to which the department reports. Self-referencing lists such as this one would not be possible without the capability to join a table to itself. Joins are possible because all data relationships in DB2 are defined by values in columns instead of by other methods (such as pointers).

Download PDF sample

Rated 4.16 of 5 – based on 43 votes