Know Db2

Advanced SQL Coding & Performance Tuning

Part 1 - DETAIL COURSE OUTLINE - 1/2 Day Classes

This hands-on workshop trains the Db2 Query User, Application Developer and DBA to code complex SQL statements to achieve optimum performance.

Topics Covered:

Duration: 4 - 1/2 day sessions.

Audience: Anyone who will be coding SQL queries and is concerned with optimum performance; including end-users, programmers, application designers and database administrators.

Prerequisites:Working knowledge of basic SQL, TSO, and SPUFI.

Advanced SQL Coding & Performance Tuning Part 1 - Class Outline:

Day 1

Db2 for z/OS Review

  • Db2 Environment
  • Db2 Subsystem Review
  • SSAS, DBAS, IRLM
  • Subsystem Details in SQL Use
  • Thread - The flow of a Transaction
  • Storage - Above / Below the bar
  • - SORT Storage
  • - EDM Storage
  • - Dynamic Storage
  • Buffer Pools
  • - How are they Assigned?
  • - How are they used?
  • - SQL and Buffer Pools    
  • - Thread Storage Use
  • Data Structures to Know About
  • - Database, Storage Group
  • - Catalog Tables
  • - Structure & Use
  • - Tables You Need to Know
  • - Finding Plan, Packages, Indexes, Tables
  • - Examples of Catalog Queries
  • - Directory Tables
  • Structure & Uses
  • Hands-on LAB
  • SQL & Db2 Subsystem & Catalog Exercises    
  • Questions for Review
  • End of 1/2 day
  •  

Day 2

Table Space Terms & Types

  • Partitioned
  • Partitioning
  • Segmented
  • Non - Partitioned
  • Universal Table Spaces
  • Partition By Growth
  • Partition By Range
  • Data Set Sizing in DB2
  • Data Set Naming Standards
  • CREATE TABLESPACE
  • - Basic Parameterc, DSSIZE, PRIQTY, SECQTY
  • - Catalog Definitions
  • CREATE TABLE
  • - Data Types Review
  • - LOBs
  • - XML
  • - CLONE/EXCHANGE
  • - Considerations
  • Unicode and SQL Impact
  • Hands-on LAB
  • CREATE, Catalog Use, Objects
  • Questions for Review
  • End of 1/2 day
  •  

Day 3

Complex Single Table SQL

  • SELECT Topics to Know
  • How to evaluate a SELECT statement
  • SQL Performance Basics
  • Indexes
  • Structure of Indexes
  • Compression
  • Asymmetrical Splitting
  • Hot Spots
  • RENAME INDEX
  • Index on an Expression
  • PADDED/NOT PADDED
  • Look Aside function
  • CHECK INDEX
  • Random
  • Page Sizes and SQL use
  • INSERT, UPDATE, DELETE
  • Predicate Types Overview
  • Indexable
  • Stage 1
  • Stage 2
  • Predicate Evaluation Rules
  • WHERE / ORDER BY / Result Sets
  • Functions, Expressions, Special Registers
  • Order of Predicate Evaluation
  • Hands-on Lab
  • Evaluating SQL – Best Performance
  • Questions for Review
  • End of ½ day
  •  

Day 4

Introduction to Performance

  • What is the Optimizer?
  • Predicates - More Details
  • - Indexes, Stage 1, Stage 2
  • Access Paths and How they work

EXPLAIN

  • PLAN _ TABLE
  • Columns Definitions
  • Columns to know
  • What EXPLAIN does Not Say
  • Access Paths - Definitions
  • Review Index Access Path Types
  • Sort and Work files
  • DSN _ STATEMNT _ TABLE
  • - Cost Estimations
  • - Columns and Uses
  • Other EXPLAIN Table Definitions
  • SQL Coding Guidelines
  • Hands - on LAB
  • EXPLAIN Exercises
  • Questions for Review
  • End of PART 1
  •  
  • ©2017 Computer Business International, Inc.

Would you like to schedule or customize this class?
Please call our toll free number (866-224-4968) or email us at info@cbi4you.com.