About ABIS
All CoursesbalkjeGeneral Soft skills Introduction to HW & SW TracksOperating systems MVS - z/OS UNIX - Linux - AIX Mac OS X iPad and iPhone iOSDatabases, transaction systems and middleware DB2 for z/OS DB2 UDB for Linux, UNIX and Windows Oracle SQL Server MySQL IMS CICS WebSphere MQ WebSphere Application Server Data warehouse, business analytics and big dataApplication development Methods and techniques TOGAF PRINCE2 Agile development and Scrum Programming languages Internet development Object Oriented systems Java IBM development tools SAS XML SOA & web servicesSystems management ITIL SecuritybalkjePractical informationRegistration 
DB2 for z/OS SQL performance

Intended for

Application developers, analysts and database administrators. Also anyone responsible for the optimization of ad-hoc queries, for example in an infocenter environment, or the queries used in the context of a Data Warehouse.


Knowledge of DB2 (see DB2 for z/OS fundamentals course) and of SQL (courses SQL fundamentals and SQL workshop).

Training method

Lecturing with practical exercises.

Course leader

Peter Vanroose.


3 days.


No public sessions are currently scheduled. We will be pleased to set up an on-site course or to schedule an extra public session (in case of a sufficient number of candidates). Interested ? Please contact ABIS.


Quality is the main concern for application developers. This also applies to the aspects of data access. After following this course, participants will be able to understand how the DB2 optimizer handles queries and they will be able to apply different techniques to optimize applications.

Query optimization and a good concurrency are emphasized. Also discussed are some aspects of logical and physical design, and the statistical information in the DB2 catalog necessary to take the right decisions.

During the practical sessions the participants will have ample opportunity to practise all the discussed topics.

Main topics

  • Strategy: steps, actions and required information
  • Logical design and physical structure: normalization and denormalisation • optimal implementation of table spaces, indexes and tables
  • The catalog: content • significance and use of the information • update by means of RUNSTATS or SQL • simulation of the production environment in a test environment
  • The DB2 access paths: tablespace scan • matching and non-matching index use • index-only access • list prefetch • direct access via RowID • hash
  • Relation between the SQL query and the access paths: stage-1 versus stage-2 • multiple index use • correlated versus non-correlated subqueries • the join methods • influence of ORDER BY, optimize for, ... • views and CTEs • materialisation (work files)
  • The EXPLAIN function: exercises in reading and interpreting the access path descriptions
  • Query optimization: what do we know about the optimizer? • how do we use this information? • how to transform a stage-2 predicate into a stage-1 indexable predicate? • which filter factors are assumed by the optimizer? • are these realistic? • how to choose the optimal alternative for joins, correlated/non-correlated subqueries? • how do we know which index is the optimal one? • how do we influence the optimizer's decision? • case studies
  • Use of one or more of the explain tables in the performance investigation: PLAN_TABLE,DSN_STATEMNT_TABLE, DSN_DETCOST_TABLE, DSN_FILTER_TABLE, DSN_PREDICAT_TABLE
  • Use of "hints" to influence the DB2 optimizer
  • Concurrency: choosing the application structure and the right DB2 parameters, lock levels and locking strategy