About ABIS
All CoursesbalkjeGeneral » Introduction to HW & SW » Soft skills » TracksOperating systems » MVS - z/OS » UNIX - Linux - AIX » Mac OS X » iPad and iPhone iOSDatabases and middleware » Relational databases & SQL » DB2 for z/OS » DB2 for LUW » Oracle » SQL Server » MySQL & MariaDB » IMS » CICS » IBM MQ » WebSphere » Big data and analyticsApplication development » Methods and techniques » TOGAF » PRINCE2 » Agile development and Scrum » Programming languages » Internet development » Object Oriented systems » Java » Development tools » SAS » XML » SOA & web servicesSystems management » ITIL » SecuritybalkjePractical informationRegistration 
DB2 for z/OS SQL performance


Quality and efficiency are the main concerns 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 is able to optimally implement SQL queries, and they will be able to apply techniques to further optimize those SQL queries.

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 used by the optimizer.

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 denormalization • 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 • types of statistics information, runstats profiles, and optimizer feedback
  • The DB2 access paths: tablespace scan • matching and non-matching index use • index-only access • list prefetch • direct access via RowID • hash access
  • 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, both for static SQL as well as through the Dynamic Statement Cache, with and without Visual Explain
  • 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, like e.g. PLAN_TABLE,DSN_STATEMNT_TABLE, DSN_DETCOST_TABLE, DSN_FILTER_TABLE, and DSN_PREDICAT_TABLE
  • Use of "hints" (new style) to influence the DB2 optimizer • predicate selectivity
  • Use of the "virtual index" possibilities for carrying out a "what if" analysis
  • Concurrency: choosing the application structure and the right DB2 parameters, lock levels and locking strategy

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 and relational databases fundamentals and SQL workshop).

Training method

Lecturing with practical exercises.

Course leader

Peter Vanroose.


3 days.


You can enrol by clicking on a date
datedur.lang.  location  price
13 Dec3?Leuven  (BE)1500 EUR  (excl. VAT) 

Global score

4.1/5 (based on 132 evaluations)


Cursus is goed in orde (, )
Ik ben niet echt representatief omdat het een cursus voor gevorderden was, terwijl ik nihil wat van D2 c.q. SQL weet; het was voor mij meer een cursus om wat inzicht te krijgen zodat het mij wat zegt. (, )
Prima, weer een boel opgestoken (, )
Goed, snel en to-the-point met goede praktijkvoorbeelden (, )
Het is een goede cursus geworden die aansluit op onze gaps of knowledge (, )
uitermate nuttig, sluit goed aan en draagt bij aan huidig kennisniveau (, )
cours complet et très technique. (, )
De theorie was niet makkelijk en daarbij was het niet vaak duidelijk wat de oefeningen tot doel hadden en wat er moest gebeuren. (, )
duur van de cursus was ok, maar inhoud was te technisch. Uitleg aan bord was duidelijker dan uitleg bij slides (dan chaotischer, te veel uitzonderingen edg). Bordschema's verduidelijkten ook de inhoud van de cursus 'adv.programming' (, )
vrij moeilijk qua inhoud. Het ganse stuk over binden, compilen ed. had ik niet mee. Het ging volledig langs mij door. Echter het stuk van de querie optimizer was terug concreter en beter te volgen. (, )

Refresh this page to see other comments.