Advanced SQL & Performance for DB2 Database Applications

Why take this course?

After completing this course, you will be able to
  • correctly code single and multiple OUTER/INNER JOINS to handle NULLS.
  • use the UNION, EXCEPT, and INTERSECT operators
    to combine multiple result sets into a single result set.
  • code Nested and Common Table Expressions.
  • write recursive SQL using Common Table Expressions.
  • use Row Expressions to extend the power of predicate evaluation.
  • nest sub-queries and use them to enhance update and insert SQL as well as the HAVING clause.
  • optimize sub-query execution using Order By and Fetch First.
  • use Data Change Tables.
  • use the Case Expression within Functions.
  • use the ARRAY type and associated functions.
  • understand the major factors contributing to database application performance.
  • learn how tablespace, table, and index design effect performance.
  • have a comprehensive understanding of how application design affects performance.
  • learn about the impact of concurrency on performance.
  • understand how database optimizers works.
  • gain an in-depth knowledge of database access Paths.
  • become fully conversant in the use of tools such the Explain facility, including the use of Optimizer hints.
  • be able to judge the efficiency of SQL statements and tune ineffective queries.
  • learn to use utilities as part of the performance tuning process.
This 5-day, hands-on course examines database performance issues from the application programmer's perspective. We'll discuss the factors affecting database performance and give you practical tips and techniques for designing and implementing efficient database applications. You'll also look at advanced SQL and get an in-depth study of efficient coding techniques. Hands-on exercises ensure an effective learning experience.
Who should attend?
Application designers, programmer analysts, programmers and database administrators who design, code, or support database applications. Primary emphasis is DB2 11 for z/OS however most content is applicable to any database application environment.
Any prerequisites?
You need to be familiar with the SQL language and have some application programming background.

Dates and Fees

5 Days   ? - $ 1,747 (CAD)  ? Up to 3 - $ 3,494 Up to 6 - $ 5,244    ? Plus - $ 874
Sorry, no classes are currently scheduled, please contact us to request a class
Contact us to arrange your preferred date and/or in-class training

Advanced SQL Topics

Advanced Multi-table Inner and Outer Joins
  • Multi-Table Inner Joins
  • Multi-Table Outer Joins
  • Table Join Processing
  • Join Guidelines
Advanced Unions, Exists and Intersects
  • Advanced Unions
  • Excepts
  • Intersects
Table Expressions
  • Nested Table Expressions
  • Common Table Expressions
  • Recursive SQL
Advanced Sub-Query Usage and Optimization
  • Type of Sub-Queries
  • Scalar Full Selects
  • List Full Selects
  • Row Expressions
  • Optimization Options
Data Change Tables and Case Expressions
  • Data Change Tables
  • Case Expressions
Arrays and Array Functions
  • Array Types and Usage
  • Aggregation and Removal
  • First, Next and Prior
  • Cardinality

Application Performance Topics

Application Tuning
  • Performance Factors
  • Design vs Implementation vs Execution
  • Performance Tuning and Baselines
  • Monitoring Tools and Techniques
  • The EXPLAIN Facility
Database Design for Performance
  • Tablespace & Table Design
  • Datatypes and Performance
  • Denormalization Options
  • Leveraging Constraints, Triggers and Procedures
  • Index Design and Structure
  • Clustering, Composite and Multiple Index Options
  • Best Practices for Indexes
Application Design for Performance
  • General Tips and Techniques
    • Reduce DB2 Interactions
    • Nested Loop Pullback
    • Local Reference Tables
    • Use the Index Order
    • Defer Work Till Needed
    • Fail Fast Ordering
  • Database Interaction Design Review
    • Transaction Analysis
    • Data Usage Maps
    • Transaction Load Matrix
    • Complex Query Analysis
  • Online Design
    • Design Objectives
    • Dialog Design
    • Prompts
    • Retrieval Options
    • Update Options
  • Batch Design
    • Design Objectives
    • Promoting Prefetch
    • Recoverability and Restart
    • Use of Utilities
Concurrency Control and Performance
  • Concurrency Control
  • Locking Strategy
  • Lock Size
  • Lock Mode
  • Lock Duration
  • Reducing Contention
Database Access Paths
  • What is an access path?
  • Single Index Access
  • Multiple Index Access
  • Join Strategies
  • Nested Loop Join
  • Merge Scan Join
  • Hybrid Join
  • Using Multiple Indexes in Joins
  • Combining Join Methods
The EXPLAIN Facility
  • The Optimizer
  • EXPLAIN Facility
  • Plan Tables
SQL Processing
  • The Optimizer
  • Processing SQL
  • SQL Cost
  • Filter Factor
  • The Catalog
Coding for Performance Part I
  • General SQL Considerations
  • SQL Predicates
Coding for Performance Part II
  • Multi Table Access
  • Avoiding Sorts
  • Performance Tips