1 / 19

Effective Query Debugging and Tuning with iSeries Navigator

This guide explores the process of debugging and tuning SQL queries using IBM iSeries Navigator. It covers essential monitoring tools, collection services, and work management strategies. Key features include real-time monitoring of active jobs, understanding SQL optimization through index advice, and leveraging system statistics. Additional resources include official web links for further reading on DB2 performance and query optimization. The importance of thorough information gathering before making changes is emphasized, ensuring impactful performance improvements.

kilenya
Télécharger la présentation

Effective Query Debugging and Tuning with iSeries Navigator

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Debugging/Tuning Queries via iSeries NavigatorTom McKinleyMac2@us.ibm.com

  2. Tour • What’s info is available • System Monitoring • Collection Services • Work Management • Table • Show Indexes • Index Advisor (DB, Schema or table level) BETA ONLY, removed from presentation. • Stats

  3. System Level Monitoring

  4. Example System Monitor

  5. Open Monitor

  6. Active Jobs

  7. Current SQL For A Job

  8. Current SQL for a job

  9. Explain from Current SQL

  10. Index Advised

  11. Columns Stats on a table

  12. Column Stats Details

  13. Statistics Requests

  14. Other sources of Info • DB2 for iSeries Web Site • http://www-03.ibm.com/servers/eserver/iseries/db2/ • Monitor queries • http://www-03.ibm.com/servers/eserver/iseries/db2/dbmonqrys.htm • White papers • http://www-03.ibm.com/servers/eserver/iseries/db2/awp.html • SQL performance and query optimization guide (Info Center) • Common, Tech Conferences… • Query Performance tuning workshop • http://www-03.ibm.com/servers/eserver/iseries/service/igs/db2performance.html

  15. Summary • Many things affect query plans • Try to understand the access methods • Learn the tools • Size matters • “Bad Performance does not scale”. • You need to collect info before you make changes to help understand what changed and the impact of that change. • Don’t only focus on Runtime. Look for resource hogs, or for queries that are run frequently • Good indexing strategy in some cases is critical

More Related