home 
 
 
 
nlfr
 
Home
About ABIS
Services
Courses
Resources
Contact
MyABIS
All CoursesbalkjeGeneral » Introduction to HW & SW » TracksOperating systems » MVS - z/OS » UNIX - Linux - AIX » VMwareDatabases, 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 » Business intelligence and data warehouseApplication development » Methods and techniques » TOGAF » PRINCE2 » Programming languages » Internet development » Object Oriented systems » Java » IBM development tools » SAS » XML » SOA & web servicesSystems management » ITILbalkjePractical 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.

Background

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.

Duration

3 days.

Schedule

datelanguageplacepriceremarks
19/03/2012ELeuven1410 EUR
20/06/2012NWoerden1410 EUR
Legend
  • You can enrol by clicking on the dates in the calendar.
  • N: Dutch; F: French; E: English; No code: language depends on the participants.
  • All prices are VAT excluded.
  • Countries:
    Belgium 
    Netherlands 
    Elsewhere 

Objectives

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