For many database administrators and SQL application programmers, new to the IBM i (formerly known AS/400 and iSeries), DB2 for i is an unknown commodity. As a result they experience poor performance when comparing the execution of SQL statements on other platforms to the IBM i platform. In addition, existing customers, familiar with High Level Language (HLL) database access techniques and IBM i Work Management concepts, are totally stymied when attempts to convert legacy applications to SQL produce undesirable results.
This has prompted a need for a higher understanding of IBM i architecture in general and DB2 for i query optimizer.
The DB2 for i SQL Performance Workshop is available to address these issues. The workshop covers the following topics:
- The science of query optimization. This topic covers the data access methods available to the DB2 for i Query Optimizer and the conditions in which the cost based optimizer chooses these methods.
- The art of query optimization. Knowing how the query optimizer works, and what the database engine can do are the first steps in getting the most out of DB2 for i. This topic covers indexing strategies including Encoded Vector Indexes (EVI), join, sub query and view optimization techniques, etc.
- SQL performance techniques and considerations. A must for the SQL application developer. Topics include understanding SQL Access Plans and Open Data Paths (ODP), effective use of blocking, and best practices when using SQL programming interfaces such as ODBC and JDBC.
- SQL Performance Tools and Analytical Methods. These topics include in depth discussions of the SQL Plan Cache, Database Monitors, DB2 SMP (Symmetrical Multiprocessing) feature and parallelism, Query Governor, Index Advisor and others.
The workshop includes a number of labs to demonstrate and provide hands on experience with most of the topics covered in the workshop. In addition, the labs also focus on teaching the student a methodology use the various DB2 performance tools that are provided by IBM Navigator for i.
This course is intended for existing IBM i database designers, performance analysts, and application developers who are concerned about SQL and query performance. It is also highly recommended for individuals new to the platform that are interested in SQL and query performance with DB2 for i.
The following are recommended prerequisites: