Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS - Second Edition - September 2006

Sep 21, 2026

Upload by Eden

Technical guide on preparing for and tuning the SQL Query Engine (SQE) in DB2 for i5/OS, centered on the DB2 architecture introduced with OS/400 V5R2 and later. Covers query processing components such as the Query Dispatcher, SQE Optimizer, Statistics Manager, and data access primitives, plus the plan cache. Explains available data access methods and operational details, then details Statistics Manager concepts, collections, APIs, and practical management via iSeries Navigator. Concludes with approaches for monitoring, debugging, and tuning SQE performance.

LanguageEnglish

CategoryTechnology

FormatPDF

Pages212

Reading Time8 h 54 min

AccessFree to download

Chapter 1. Overview of DB2 for i5/OS

The ‘new’ application world

The database design

The implementation

Design criteria and features of SQE

Chapter 2. The architecture of DB2 for i5/OS

SQL in a nutshell

Query processing starting with OS/400 V5R2

The Query Dispatcher

The SQE Optimizer

Statistics Manager

Data access primitives

The plan cache

Chapter 3. Data access methods

Available data access methods

SQE Primitives in action

Details about the data access methods

Chapter 4. Statistics Manager

Statistics and statistics collections

Using iSeries Navigator to manage statistics

Proactive statistics collection

Statistics Manager APIs

Chapter 5. Monitoring and tuning SQE

Query optimization feedback

Debug messages

Print SQL Information

Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS Understand the new architecture for query processing Learn about the data access methods and Statistics Manager See how to monitor and tune the SQL engine Hernando Bedoya Monti Abrahams Nicolas Bueso Luis Guirigay Sabine Jordan Dave Martin Barry Thorn Steve Tlusty JaeHee Yang [ibm.com](ibm.com/redbooks)[/redbooks](ibm.com/redbooks) International Technical Support Organization Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS September 2006 Note: Before using this information and the product it supports, read the information in “Notices” on page vii. Second Edition (September 2006) This edition applies to Version 5, Release 4 of IBM i5/OS, Program Number 5722-SS1 . © Copyright International Business Machines Corporation 2003, 2006. All rights reserved. Note to U.S. Government Users Restricted Rights-- Use, duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp. Contents Notices ................................................................. vii Trademarks ............................................................. viii Preface ................................................................. ix The team that wrote this redbook .............................................. ix Become a published author .................................................. xi Comments welcome ........................................................ xii Chapter 1. Overview of DB2 for i5/OS ........................................ 1 1.1 The history of DB2 for i5/OS .............................................. 2 1.2 The ‘new’ application world ............................................... 3 1.3 The database design .................................................... 4 1.4 The implementation ..................................................... 5 1.5 Design criteria and features of SQE ........................................ 7 1.5.1 The objectives ..................................................... 7 1.5.2 The SQE design ................................................... 8 Chapter 2. The architecture of DB2 for i5/OS ................................. 11 2.1 SQL in a nutshell ...................................................... 12 2.1.1 Static SQL ....................................................... 12 2.1.2 Dynamic SQL .................................................... 12 2.1.3 Extended dynamic SQL ............................................ 12 2.2 Query processing before OS/400 V5R2 .................................... 13 2.3 Query processing starting with OS/400 V5R2 ................................ 13 2.3.1 The Query Dispatcher .............................................. 16 2.3.2 The SQE Optimizer ................................................ 20 2.3.3 Statistics Manager ................................................ 22 2.3.4 Data access primitives ............................................. 24 2.3.5 The plan cache ................................................... 24 2.4 Summary ............................................................ 26 Chapter 3. Data access methods ........................................... 27 3.1 Available data access methods ........................................... 28 3.2 SQE Primitives in action ................................................ 30 3.3 Details about the data access methods .................................... 31 3.3.1 Permanent table operations ......................................... 31 3.3.2 Temporary result object methods ..................................... 40 3.3.3 Access methods in V5R4 ........................................... 59 Chapter 4. Statistics Manager ............................................. 63 4.1 Statistics and statistics collections ........................................ 64 4.1.1 Optimizer statistics request types ...............................

FAQs

What is covered in the book’s overview of DB2 for i5/OS?

It introduces the history of DB2 for i5/OS, the “new” application world, database design and implementation, and the design criteria and features of the SQL Query Engine (SQE).

How does query processing change starting with OS/400 V5R2?

It describes the SQE architecture and key components, including the Query Dispatcher, SQE Optimizer, Statistics Manager, data access primitives, and the plan cache.

What does the Statistics Manager section focus on?

It covers statistics concepts and collections, optimizer statistics request types, automatic statistics collection and refresh, how to manage statistics via iSeries Navigator, proactive statistics collection, and Statistics Manager APIs.

Sep 21, 2026

Upload by Eden