Table of Contents

About DB2 UDB for AS/400 Database Performance and Query Optimization

  • Who should read the Database Performance and Query Optimization book
  • Assumptions relating to SQL statement examples in the Database Performance and Query Optimization book
  • How to interpret syntax diagrams in the Database Performance and Query Optimization book
  • What's new for V4R5 in the Database Performance and Query Optimization book

    • Database performance and query optimization: overview

  • Creating queries

    • Data access on DB2 UDB for AS/400: data access paths and methods

  • Data access: access paths
  • Table scan
  • Index
  • Encoded vector index
  • Data access: data access methods
  • Data access methods: overview
  • Ordering query results
  • Enabling parallel processing for queries
  • Spreading data automatically
  • Table scan access method
  • Parallel table prefetch access method
  • Parallel table scan method (available only when the DB2 UDB Symmetric Multiprocessing feature is installed)
  • Index scan-key selection access method
  • Parallel index scan-key selection access method (available only when the DB2 UDB Symmetric Multiprocessing feature is installed)
  • Index scan-key positioning access method
  • Parallel index scan-key positioning access method (available only when the DB2 UDB Symmetric Multiprocessing feature is installed)
  • Index Only Access Method
  • Parallel table or index based preload access method
  • Index-from-index access method
  • Hashing access method
  • Bitmap processing method
  • Sort access method

    • The DB2 UDB for AS/400 query optimizer: overview

  • General query optimization tips
  • How the query optimizer makes your queries more efficient
  • Optimizer decision-making rules
  • Cost estimation for queries
  • Access plan validation
  • Join optimization
  • Grouping optimization
  • Ordering optimization
  • View implementation

    • Optimizing query performance using query optimization tools

  • Verify the performance of SQL applications with CL commands
  • Examine query optimizer debug messages in the job log
  • Query optimization performance information messages
  • Query optimization performance information messages and open data paths
  • Gather information about embedded SQL statements with the PRTSQLINF command
  • Gather statistics about your queries with the database monitor
  • Start Database Monitor (STRDBMON) command
  • End Database Monitor (ENDDBMON) command
  • Database monitor performance rows
  • Query optimizer index advisor
  • Database monitor examples
  • Gather statistics about your queries with memory resident database monitor APIs
  • Memory resident database monitor external API description
  • Memory resident database monitor external table description
  • Sample SQL queries
  • Memory resident database monitor row identification
  • View the effectiveness of your queries with Visual Explain
  • Change the attributes of your queries with the Change Query Attributes (CHGQRYA) command
  • Control queries dynamically with the query options file QAQQINI
  • Specifying the QAQQINI file
  • Creating the QAQQINI query options file
  • Control long-running queries with the DB2 UDB for AS/400 Predictive Query Governor
  • Cancelling a query
  • Query governor implementation considerations
  • Query governor considerations for user applications: setting the time limit
  • Controlling the default reply to the query governor inquiry message
  • Testing performance with the query governor
  • Examples of setting query time limits
  • Control parallel processing for queries
  • Controlling system wide parallel processing for queries
  • Controlling job level parallel processing for queries
  • Query optimization tools: comparison table

    • Using indexes to speed access to large tables

  • Coding for effective indexes: Avoid numeric conversions
  • Coding for effective indexes: Avoid arithmetic expressions
  • Coding for effective indexes: Avoid character string padding
  • Coding for effective indexes: Avoid the use of like patterns beginning with % or _
  • Coding for effective indexes: Be aware of the instances where DB2 UDB for AS/400 does not use an index
  • Coding for effective indexes: Using indexes with sort sequence
  • Coding for effective indexes: Using indexes and sort sequence with selection, joins, or grouping
  • Coding for effective indexes: Ordering
  • Examples of indexes
  • Index example: Equals selection with no sort sequence table
  • Index example: Equals selection with a unique-weight sort sequence table
  • Index example: Equal selection with a shared-weight sort sequence table
  • Index example: Greater than selection with a unique-weight sort sequence table
  • Index example: Join selection with a unique-weight sort sequence table
  • Index example: Join selection with a shared-weight sort sequence table
  • Index example: Ordering with no sort sequence table
  • Index example: Ordering with a unique-weight sort sequence table
  • Index example: Ordering with a shared-weight sort sequence table
  • Index example: Ordering with ALWCPYDTA(*OPTIMIZE) and a unique-weight sort sequence table
  • Index example: Grouping with no sort sequence table
  • Index example: Grouping with a unique-weight sort sequence table
  • Index example: Grouping with a shared-weight sort sequence table
  • Index example: Ordering and grouping on the same columns with a unique-weight sort sequence table
  • Index example: Ordering and grouping on the same columns with ALWCPYDTA(*OPTIMIZE) and a unique-weight sort sequence table
  • Index example: Ordering and grouping on the same columns with a shared-weight sort sequence table
  • Index example: Ordering and grouping on the same columns with ALWCPYDTA(*OPTIMIZE) and a shared-weight sort sequence table
  • Index example: Ordering and grouping on different columns with a unique-weight sort sequence table
  • Index example: Ordering and grouping on different columns with ALWCPYDTA(*OPTIMIZE) and a unique-weight sort sequence table
  • Index example: Ordering and grouping on different columns with ALWCPYDTA(*OPTIMIZE) and a shared-weight sort sequence table

    • Application design tips for database performance

  • Database application design tips: Use structure parameter passing techniques
  • Database application design tips: Background information on parameter passing
  • Database application design tips: Differences in structure parameter passing techniques
  • Database application design tips: Use live data
  • Database application design tips: Reduce the number of open operations
  • Database application design tips: Retain cursor positions
  • Database application design tips: Retaining cursor positions for non-ILE program calls
  • Database application design tips: Retaining cursor positions across ILE program calls
  • Database application design tips: General rules for retaining cursor positions for all program calls

    • Programming techniques for database performance

  • Programming techniques for database performance: Use the OPTIMIZE clause
  • Programming techniques for database performance: Use FETCH FOR n ROWS
  • Programming techniques for database performance: Improve SQL blocking performance when using FETCH FOR n ROWS
  • Programming techniques for database performance: Use INSERT n ROWS
  • Programming techniques for database performance: Control database manager blocking
  • Programming techniques for database performance: Optimize the number of columns that are selected with SELECT statements
  • Programming techniques for database performance: Eliminate redundant validation with SQL PREPARE statements
  • Programming techniques for database performance: Page interactively displayed data with REFRESH(*FORWARD)

    • General DB2 UDB for AS/400 performance considerations

  • Effects on database performance when using long object names
  • Effects of precompile options on database performance
  • Effects of the ALWCPYDTA parameter on database performance
  • Tips for using VARCHAR and VARGRAPHIC data types in databases

    • Appendix A. Database monitor: DDS

  • Database monitor physical file DDS
  • Database monitor logical file DDS
  • Database monitor logical table 1000 - Summary Row for SQL Information
  • Database monitor logical table 3000 - Summary Row for Table Scan
  • Database monitor logical table 3001 - Summary Row for Index Used
  • Database monitor logical table 3002 - Summary Row for Index Created
  • Database monitor logical table 3003 - Summary Row for Query Sort
  • Database monitor logical table 3004 - Summary Row for Temp Table
  • Database monitor logical table 3005 - Summary Row for Table Locked
  • Database monitor logical table 3006 - Summary Row for Access Plan Rebuilt
  • Database monitor logical table 3007 - Summary Row for Optimizer Timed Out
  • Database monitor logical table 3008 - Summary Row for Subquery Processing
  • Database monitor logical table 3010 - Summary for HostVar & ODP Implementation
  • Database monitor logical table 3014 - Summary Row for Generic QQ Information
  • Database monitor logical table 3018 - Summary Row for STRDBMON/ENDDBMON
  • Database monitor logical table 3019 - Detail Row for Rows Retrieved
  • Database monitor logical table 3021 - Summary Row for Bitmap Created
  • Database monitor logical table 3022 - Summary Row for Bitmap Merge
  • Database monitor logical table 3023 - Summary for Temp Hash Table Created
  • Database monitor logical table 3025 - Summary Row for Distinct Processing
  • Database monitor logical table 3026 - Summary Row for Union Merge
  • Database monitor logical table 3027 - Summary Row for Subquery Merge
  • Database monitor logical table 3028 - Summary Row for Grouping
  • Database monitor logical table 3029 - Summary Row for Index Ordering
  • Database monitor logical table 3030 - Summary Row for Query Step Processing

    • Appendix B. Memory Resident Database Monitor: DDS

  • External table description (QAQQQRYI)
  • External table description (QAQQTEXT)
  • External table description (QAQQ3000)
  • External table description (QAQQ3001)
  • External table description (QAQQ3002)
  • External table description (QAQQ3003)
  • External table description (QAQQ3004)
  • External table description (QAQQ3007)
  • External table description (QAQQ3008)
  • External table description (QAQQ3010)

    • Index

    [ Top of Page | Previous Page | Next Page | Table of Contents | Index ]